Has anyone ever successfully converted a large legacy Apache/PHP/MySQL site to UTF-8? If so you deserve an award. utf8mb4 is just one issue and not the worst.
https://github.com/s9y/Serendipity/blob/05f58f90d743fe9ade24... is just the detection function I wrote for a PHP blog engine to detect whether it would even be possible to use utf8mb4 on the system. We completely ran into this issue and didn't know how to handle it for literally years. Lots of blogs to migrate, and who knows whether it worked for all of them...
The craziest issue I had was I couldn't predict what char encoding the text in my database was in. Most users entered Windows-1252, some text blobs were UTF-16, others were European character sets, and some were UTF-8. Some were Japanese SHIFT_JIS. Don't ask me how any of this happened. I retrospect, I should have dumped all the tables from MySQL and used the excellent PyPy Chardet [1] library to see what I was dealing with, do the conversions and then re-import the data. But then someone could copy UTF-16 from a Windows document and paste it in, so you have to convert going in to the database.
You have set Apache to UTF-8, PHP to UTF-8, MySQL to UTF-8, and the MySQL driver you are using to UTF-8. It's not clear how these setting interact. Are there silent conversions happening or do you always have to detect the encoding on data coming from the server? HTML pages have a character encoding specifier, but the BOM at the start of the file takes precedence (I think.) I got it to work by always detecting encoding for any text coming from the database and using iconv, but this turned out to be really slow and unreliable. It was truly the biggest mess by an order of magnitude than any other programming problem I faced in my career.
Yes. I had the exact same issue with '<' in HTML content stored in the database -- cms pages and also some JS that was stored in the DB and injected into the HEAD of all pages.
Technical lead for vanillaforums. It’s a huge PHP MySQL app. Probably 10b+ rows in production DBs. We did the the migration in 2017 if I remember correctly.
Not to take anything away but by legacy I meant late last century sites. VanillaForums was released in July 2006 according to Wikipedia, which makes it Web 2.0. Wikipedia also says "UTF-8 has been the most common encoding for the World Wide Web since 2008" I'm glad you posted though because now I know that sites are ok starting in that time frame, not before and then never touch the old ones again.
Been there, done that, with a phpbb installation. I manually converted all the non-UTF data in the database to UTF8, and updated the PHP code and database settings.
At least once, but it's been a while. From the top of my head:
- set encoding at HTML head
- also at any HTML form element
- Set database collation correctly
- I believe it also needs to be done per table
- The PHP -> MySQL connection itself may need to have the encoding set
- All string manipulation to be done with mb_
- Even ensure your code files themselves are in UTF8
> Database systems have subtle bugs and oddities, and you can avoid a lot of bugs by avoiding database systems.
Wat? That is... not a great takeaway from this experience. "Oh, this library had bug reported in it! I know, that means I should just write my own, because clearly they're all buggy and my software is always perfect."
Well, I mean, the good news is that if you write your own database system, you'll have so many other bugs in the way that you'll likely never reach this particular one!
I suggest it can be called the Sirius Database.
"It is very easy to be blinded to the essential uselessness of [the new database you just wrote] by the sense of achievement you get from getting them to work at all. In other words—and this is the rock solid principle on which the whole of the Corporation's Galaxy-wide success is founded—their fundamental design flaws are completely hidden by their superficial design flaws."
It's especially specific to MySQL — switching to Postgres is great because in addition to better performance and features there are all of these rakes in the grass you can forget about. Unicode just works, you never lose data because someone didn't scrupulously check the warnings after every query, etc.
I am not sure if that procedure covers major versions (13 -> 14 for example), but if it does, they are ahead of the curve. On GCP, you have to backup the database and restore the backup to a new database. On self-hosted installs, you have to pick one of these processes: https://www.postgresql.org/docs/14/upgrading.html
They do an in_place upgrade using pg_upgrade I believe. They do have to regenerate system tables - they also automate I think some updates to system statistics. But for very large databases this really is the way to go I think.
They do suggest a backup before you run this in case there are problems, most folks have periodic backups anyways, but the backup is not used.
> When you say "complex ride" what does that mean?
On managed postgresql services like RDS it's easy, yes, because Amazon does all of the nasty work for you and has already seen all the kinks you may run into.
If you are on your own and using Docker, you essentially need a third-party image that has the old and new binaries (https://github.com/tianon/docker-postgres-upgrade). Good f...ing luck if you dare run PostgreSQL inside a Kubernetes environment, because adapting that guide to that is a can of worms large enough to make anyone squirm.
If you are on your own and use postgres from source, well... have fun, there's a reason why I prefer using distribution-maintained packages when possible.
Fun fact InnoDB in place updates have plenty of caveats too: must be last column, cannot have non-default on-update/delete clauses. And if you workaround with any table-rename solutions like pt-online-schema-change then you might crash your server. (Some MySQL 8 versions crash on table renames.)
EDIT: Oops, guess you meant version updates not DDL. Leaving comment for posterity.
Postgres doesn't have a way to change the order of columns. Of course you could make a view, but my code doesn't use a view and I don't really want to make one just for when I want to check the database.
Out of curiosity, how does this cause a problem for you? I’ve been using SQL databases heavily since the mid-90s and I’m not sure I’ve ever been in a situation where I had to use SELECT * but couldn’t either process the results by column name or have the client read in whatever order needed. I strongly favor only selecting the fields I care about, however, so I’m assuming there’s a case you’ve encountered.
I love that expression "rakes in the grass", it brings back childhood memories of quicksand, stop/drop/roll, and stepping on rakes as being things I'd always have to watch out for as an adult, plus all the loony toon cartoons who used it.
In my experience that’s only true for very simple queries which play to MySQL’s strongest points, not complex ones or under high transaction volume. I haven’t found that to be a good win since the 2000s because caches usually soak up the easy queries and as your app gains complexity you hit performance cliffs which are harder to deal with, especially without some of the more advanced SQL features. MySQL 8 seems to have improved in this regard.
"Please don't pick the most provocative thing in an article or post to complain about in the thread. Find something interesting to respond to instead."
Maybe this is a cynical take - but we used https://pgloader.io/ a few years ago to migrate to Postgres, and have never been happier. MySQL has a lot of stupid decisions like this.
I'm a big posgresql fan, but I'd maybe argue that you're making tradeoffs. PG has weird behaviour sometimes too.
I don't think it's always smart to just change out your data layer.
But, if you're starting a new project, I do think PG is one of the better options and tends to follow the principle of least surprise. (hence: big fan)
As a huge Postgres fan working at a shop using MySQL I'll point out the first thing I saw MySQL does that Postgres doesn't:
Index hints.
If you aren't hitting an index in Postgres you have to dig in to table stats and figure out what is wrong but MySQL gives you more control.
However, I would still rather work with Postgres AND have to juggle a connection pooler than deal with MySQL. Transactions on DDL are _great_ and the ability to use foreign keys across partitioned tables is how it should be.
But at least Postgres has much better tools for learning about the query plans it might use. The Postgres output of “EXPLAIN” is much much better than MySQL.
It’s a shame that almost every job I worked at uses MySQL and not Postgres. But that could be because those companies all got their start like a decade or more ago when Postgres was not as well known.
> I would still rather work with Postgres AND have to juggle a connection pooler
Is this comment relating to the overhead of idle connections, which has historically necessitated the use of a pooler in front of PG? If so, I believe this is resolved in postgres 14
Perhaps, but I'd argue that the "weird" behavior of postgres just tends to be clearly thought out design decisions that they made for a valid reason that may cause you some pain with how you use it (e.g. their process-per-connection model).
MySQL's "weird" behavior, on the other hand, just tends to be completely invalid footguns like this. Despite what some people are arguing in this thread, a 3-byte version of UTF-8 was never in any spec anywhere and was an invalid shortcut from day 1.
I love, love, love Postgres! The one feature MySQL has that Postgres doesn't is the ability to add a new column in an arbitrary position in the table. I like my tables to have a common "layout" and I would love to have this feature in Postgres.
If your data is tiny then within reason nothing matters, any popular database will be just fine. Where you care is precisely when your data gets big enough to be unwieldy.
People say this all the time but never elaborate on what they are nor link to any sources that help educate people what they are, so I just assume that when people say this, they don't know what they're talking about and are just parroting.
Postgres defaults to the system's collation for UTF-8, which caught me by surprise. Now I'm stuck with a production DB where text prefix queries, which should be a perfect use of a btree, are a full table scan. Probably gonna have to take some maintenance downtime to fix the situation :-\
As for Postgres, I have enormous respect for it and its engineering and capabilities, but, for me, it’s just too damn operationally scary. In my experience it’s much worse than MySQL for operational footguns and performance cliffs, where using it slightly wrong can utterly tank your performance or availability. In addition, because MySQL is, in my experience, more widely deployed, it’s easier to find and hire engineers with experience deploying and operating it. Postgres is a fine choice, especially if you already have expertise using it on your team, but I’ve personally been burned too many times.
MySQL or MariaDB/PerconaDB most likely. For all the footguns people are bringing up in this thread it's an extremely well trodden path where anything you could ever imagine doing with a DB someone has already done a hundred times with MySQL and documented it.
Since I didn't specify the default collation when creating my database, Postgres inferred it from my system, so I ended up with "en_US.UTF-8". The "C" collation is probably what most developers expect.
The only supported way to set the database's default collation is at creation time, so I'd need to create a new database, copy the data over, and switch my application over. (There appears to be some Postgres voodoo to change the default collation for an existing database, but that's too risky for me.)
Until I get that done, I need to explicitly specify the collation for all new text fields, e.g.
Oh whoops, my previous response was about which collation I (want to) use. Were you asking which DB I use?
The answer is Postgres. I have some previous experience with MySQL and there are advantages and disadvantages. The biggest advantage so far is partial indexes.
But since I don't have as much experience with Postgres, there's still the occasional surprise, e.g. index names not being scoped to the table, this collation thing. And from what I've read online, I should probably set up some monitoring for vacuuming issues.
The article about Postgres operational footguns recommends outsourcing those issues by using a managed Postgres service (e.g. AWS RDS or Google Cloud SQL), and that's what I do.
I think the behavior of the "C" collation is what most software developers expect. Plus, there is an advantage to the behavior being the same everywhere, rather than dependent on the system/environment settings.
But those preferences may be specific to my situation -- a software developer running an HA web application.
* If you're not a software developer, you might be surprised by the "C" collation, which puts "Z" before "a".
* If you're not running an HA web application, then it's easy to change the collation later -- just dump/restore the DB.
If you have any non-ASCII characters wouldn’t the C collation be very surprising? I’m having a hard time seeing how the C collation is a sensible default for UTF encoded text. If you want to limit to ASCII the why are you using UTF?
I also helped with a project once that used pgloader to migration an old MySQL db to Postgres, and I think this article may actually explain one of the issues we found. We had a UTF-8-configured table in MySQL (we also discovered in the project, that MySQL can have encodings set differently per-table, while Postgres sets the encoding for the entire database) with UTF-8 data, and when we migrated it into a UTF-8-configured Postgres database, some of the UTF-8 characters were silently corrupted. They were UTF-8 characters in both databases, so the corruption didn't raise any errors, but they were different characters, so when the data was read back out by the application, the text was different.
We only caught this, because thankfully, we had written a manual checksum script, which looped through every table, read out all values from each row into the application, and hashed the results, then compared between when the app was connected to the source MySQL database vs the destination Postgres database. We ended up having to massage and fix those silently-corrupted characters.
This is also a security vulnerability because MySQL/MariaDB silently truncate strings at the first invalid character. This can result in data manipulation attacks where a higher level layer validates the complete input as UTF-8 for insertion into the DB, but the database only stores half the string.
I do find this behaviour rather incomprehensible actually. I understand that if the "utf8" is BMP it can't have invalid characters in it. What puzzles me is that the approach they went for seems to be almost the worst possible one from a security and data integrity perspective.
Assuming a SQL error can't be thrown for some legacy reason (and you'd think a flag to opt into that would be an option), why not replace the invalid parse with U+FFFD like most things do? Or at a bare minimum, only strip the invalid character which at least makes attacks a bit harder and results in a little less data loss.
Just wanted to reply to myself that I just learned from evanelias' comment a "strict mode" flag is indeed an option. I suppose it's time to audit all my mysqls to see which ones have this enabled, and what other mysql-isms this flag might break.
I know it's definitely not enabled on one updated and maintained BSD - this could be because they didn't want to break existing systems during upgrade.
I suspect this problem is still extremely common. But good to know there's some form of defense.
Any data in your database that can come from external input should be treated as untrusted and validated before it's used. Otherwise validation bugs or bypasses will result in bad data and exploits that persist beyond the fix.
Edit: I’m not arguing against the need to use utf8mb4
Agreed. The confusing part here as I see it is where validation layer A (correctly) asserts the data is valid UTF-8 and safe then assumes the database persists what it passes to it, since no error is reported.
Then, subsystem B trusts reading the database field (since it passed validation layer A).
Obviously more validation layers can be added, but at this point validation layer C called by subsystem B needs to know what the initial input from layer A is in order to differentiate it from the db value which was manipulated - a rather tricky thing to do sometimes. (I guess you could add a hash to the db to check the db is storing your strings, but really.. come on)
Upgrading to utf8mb4 is probably safer than hoping enough validation layers thrown at it solves the problem.
Yes. I’ve been burned by this exact issue in the past, not realizing that utf8 was not really utf8 in mysql-land. That is a major, major WTF IMO.
But I’ve also seen people relying on validation at the time of insertion so many times that I wanted to warn against that, too. Not in argument against the need for utf8mb4.
While that’s true, I trust PostgreSQL to store exactly what I’ve asked it to store. At some point, you have to trust something to do its job, or else everything built on top of it is a castle of sand.
Imagine a bug like this in ext4. No one would reasonably contend that the layers on top of it should be validating that the files you write out are the ones you’ll read back in. We write unit tests for all kinds of stuff, but we’re not that thorough.
Sure, but the issue here is that the default mysql "utf8" encoding is not actually utf8. You can write as many validation layers as you want, but if they are assuming that utf8 actually means utf8, they won't help, and mysql will potentially screw it up when it gets stored.
Another fun one that came up at my first job: If you run up against the size limit of a text column in MySQL, it silently truncates.
This came up when a developer used YAML for some configuration file. There was all sorts of confounding behaviour because YAML is still valid when truncated.
Every database has limits, but at least alert the user! I’d rather a query fail than alter data.
Edit:
This is something that might be useful in CS classes, to reflect what being a real-world engineer can often entail. Set an assignment with some silly nonsense like this and students have to debug it.
There's a setting for this, but like many settings MySQL has (along with InnoDB, the storage engine) historically chosen exceptionally bad defaults. It's possible to tune the thing to be screaming fast and pretty robust, but it takes a lot of expertise and TBH these days most people would be much better off going either NoSQL or Postgres instead.
What percentage of CS graduates go onto become computer scientists? Seems like vast majority of CS degree holders aren't practicing "pure" computer science, as you would seemingly hope.
Probably a very low amount. When I was studying I participated as a student representative at the CS programme I was attending and this was something that was actively being addressed.
The problem was multi-pronged. The courses had become increasingly geared toward industry, and students that had an interest weren't really fit for continuing their studies.
In the end, the longer 5 year program (MSE) was allowed to remain more vocational while those seeking a bachelor's were brought along a much more theoretical path so they could then do a master's followed by a PhD.
iirc it was to allow customization of an e-commerce item. Basically a column as a document (in the sense of a document database) with custom attributes that could be…large.
It wasn’t my implementation. This was another team
It's really weird that they've chosen YAML for it, since its sole (questionable) benefit over the alternatives is that it's geared more towards being edited directly by humans... which is generally not something that you need to do with a database field. JSON is more usual for scenarios like this, not the least because most databases actually provide tools to parse and query it.
It's pretty weird to put any configuration-languages in a relational database in general. Large variable-length objects is not what relational databases are good at. You're mostly getting the drawbacks, and very few of the benefits in that use case. Even a filesystem is probably more suitable.
Drupal seems keep a large amount of PHP serialisation of configuration in its database (a JSONish thing), probably because it's the standard data store for the site. Noticed this in fixing a dump one time. Got to use vim's function syntax for regex to ensure the character length count of the string matched the value of the new string.
I think virtually everything generically configurable in the UI that isn't part of a small core with dedicated fields (or in the sitewide PHP config include) is handled that way.
Most databases have some kind of special support for XML and/or JSON these days. And sometimes you just need to associate structured hierarchical data with each record without knowing the precise schema in advance. If it's non-hierarchical, you can always use a separate key/value table; but tree queries are not exactly convenient (or fast) in SQL.
Maybe also do a checksum or something, seriously it could just be CRC32. I get that it's not something you instinctively think to do. For configurations that are most likely parsed directly by something else though, may check that nothing broke in transit.
Silent truncation only occurs if strict sql_mode is disabled. Strict sql_mode has been the default since MySQL 5.7, released over 6 years ago. All prior versions have hit EOL support, so there is literally no supported version of MySQL which has this behavior out-of-the-box.
That's good to know, however I was encountering this MySQL truncation just last year in COTS software, so clearly there's a long tail. As a related example, log4j 1.x has been deprecated for years, but 100% of atlassian uses it with their own maintained version.
In fact, when the log4j thing came up, we audited all our existing COTS/internal and virtually all of it was still on the "EOL" log4j 1.x.
Since you're familiar with this. What happens with an existing upgrade - is strict mode enabled by a typical distro package upgrade or does it only impact new installs?
For sure, there's a huge long tail. However, if a company is using 6+ year old EOL software that has flaws, that's outside of the vendor's control.
> What happens with an existing upgrade
sql_mode is a setting, so it depends on whether the default is being overridden: e.g. does the my.cnf config file specify a value explicitly? Does your application's connection logic set a session-level override for each connection? DBaaS vendors add an additional wrinkle since they tend to have custom UIs and APIs for global settings.
> typical distro package upgrade
Many Linux distros made it so that attempting to install "mysql" actually installs MariaDB instead, so when going this route, it's unfortunately always very hard to predict what you'll get or what will happen!
> Many Linux distros made it so that attempting to install "mysql" actually installs MariaDB instead, so when going this route, it's unfortunately always very hard to predict what you'll get or what will happen!
MariaDB is indeed pretty pleasant to use and for most use cases can be a drop in replacement for MySQL, should you not want to use MySQL, but also wouldn't want/need/be able to use PostgreSQL or something else: https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility/
I might be in the minority here, but i actually rather enjoy MySQL/MariaDB, since MySQL Workbench is still the best DBA software that i've used, especially because of the ER modelling and schema sync functionality, and their approach to schemas/users within a DB feels more reasonable than that of Oracle/PostgreSQL.
That said, both PL/SQL and PL/pgSQL feel more powerful than whatever MySQL/MariaDB has. Also, in regards to MariaDB in particular, their versions have diverged from MySQL somewhat, so you might still want to look into the differences, if migrating over: https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility/
As for predictability - nowadays i just run specific versions in containers (e.g. 10.7.1 or maybe 10.7 which corresponds to the patest PATCH release), to sidestep the distro differences altogether. It also lets me easily launch new instances, give resource limits to them, have multiple versions in parallel and mount the data directories wherever i need them, for easier backups etc.
Same. If I'm building and managing any special instances/VMs these days I avoid installing any packages but those required to run containers. There's certainly tradeoffs but it makes software management so much easier. Upgrading is a matter of stopping and starting a container using an upgraded image and if it's supported rolling back is just a matter of stopping the newer container and restarting the old. A container hosting service/platform and it's abstractions are better but if you have to run some one offs outside of that it's so much nicer to use containers. I call these pet cattle.
I still bind mount directories though. Docker volumes seem really convenient but a few years back I dealt with helping out a coworker with some data loss (pre-volumes) in a container's filesystem so I have some trust issues with how they implement storage.
> I still bind mount directories though. Docker volumes seem really convenient but a few years back I dealt with helping out a coworker with some data loss (pre-volumes) in a container's filesystem so I have some trust issues with how they implement storage.
This mirrors my experience and despite how Docker seems to suggest that non-abstracted storage is a liability and we should use volumes instead in many cases, i disagree: https://docs.docker.com/storage/bind-mounts/ In my eyes, the current implementation for volumes is for data that i don't really care much about, but would still consider extracting/moving later and have it be persistent, like when trying out new software.
Personally, bind mounts also make it extremely easy to operate with whole installs of software - for example, should i want to upgrade something like Nextcloud (and have enough space), i can just stop the containers, make a copy at /docker/nextcloud_stack/data/nextcloud_mysql_old and run a new container version against /docker/nextcloud_stack/data/nextcloud_mysql and see if it all works.
If the update has corrupted everything, i can just delete the broken folder, rename my backup and proceed with restoring the older version. If i want to mess around with the broken version, i can do it with 0 worries about what else could break. It's amazing, way better than relying on the POSIX approach to a file system, where your software, its configuration and its persistent data is all over the place.
I once had to manage a Tomcat install which had the webapps, config, logs directories in their idiomatic locations rather than one folder, which was pretty horrible from a management perspective. I had to constantly jump around and wasn't entirely sure how it all fits together (probably lots of symlinks).
It's curious that some/many Kubernetes distros lock down the local storage driver, though - there's a preset data directory, where all of your mounted data ends up, which feels like a step backwards from a customization perspective. I had to change the data directory for the entire Kubernetes install to be able to store my data in a painfully obvious /app or /data mount point. Then again, their ability to limit the sizes of these persistent volumes is pretty cool.
Wouldn't it be a nice world where you can just declare something EOL and it disappears and somehow magically it's not a problem anymore for anyone end-to-end? Thing's just don't work that way. Like, suddenly, a product that was working perfectly fine for who knows how long suddenly needs to modified because some ass-hats thinks that EOL isn't suitable for production.
There are a number of reasons to upgrade a dependency or a product; an EOL label shouldn't be a part of the calculus. EOL may imply certain conditions that may influence the decision to upgrade but for the sake of EOL you can eat a bag of dicks. Think harder why you're proposing an upgrade is needed and how it'll add value to the business. EOL software is perfectly fine especially because it's been battle tested.
We also use log4j 1.x all over the place and I feel zero need to change that.
It is perfectly reasonable to continue to use EOL software, but you have to assume maintenance costs in that case. It would be irresponsible to continue to use EOL software as is. EOL is a perfectly natural concept. Nothing in this world lasts forever, including our machines, electronics, cars, batteries, lives, and yes, software.
For the end user, not the maintainers. Who's gonna sign up to keep that ENIAC, PDP-11, ALGOL 58, python2.3, win3.1, etc, code running, stable, and not a source of exploits?
Or do you mean in the abstract, fairy-tale ending sense?
Ah makes sense. I should've looked it up and double checked before commenting, my bad.
Honestly, in that case I tend to agree with GP. As long as you recognize EOL means you are in charge of supporting it and possibly backporting security fixes and you're okay with that - why not use it?
I agree EOL is too often interpreted to mean 'Insecure' - in the case of closed solutions this is absolutely the case (presumably, it is often difficult to audit closed and obfuscated closed source solutions).
In many cases it is not, it is purely a 'ticking time bomb' - if new CVEs or requirements arise which cause issues in EOL versions, you may not even become aware of the vuln.
In counterbalance, new versions of code have new, unknown CVEs, new bugs, etc.
The issue is that of support, the fallacy and process issue is that 'current' support is of greater quality than 'historical' support, or that old software has a disproportionately higher support costs than 'current' software.
The true issue is overdependence on FOSS software and unwillingess to contribute - all software has costs, major vendors are 'coasting' on work provided by free volunteers and panic when those volunteers decide to no longer work for free.
If vendors were doing the legwork necessary to properly inhouse their dependencies, I would wager that much less software would end up discarded in 'fear', for a lack of a better word than this - modern development is supposedly built on features and patches, any codebase should be maintainable for a much longer period than its supposed EOL.
Sure, but that's not MySQL's fault. MySQL/Oracle has no control over what ill-conceived setting overrides are used by DBaaS providers or shared hosting providers.
There are similarly dumb misconfigurations that can theoretically be made in most hosted open source software.
Well, the things it would have been nice for MySQL to have done would have been not implementing it this way in the first place (yes, that's probably not Oracle's fault), and moving utf8 to utf8mb4 a bit more quickly. (good to know that's finally happening).
Also, I gotta say, now I have to look into this strict mode and what else it breaks, when in this particular case a "don't silently truncate strings" flag would be preferable and probably lower impact, since "strict" vs "security flaw mitigation" is not quite the same thing (does such a more granular flag exist?).
... unless that's all the strict mode does.
Hm, and since you brought this up elsewhere. Is this "strict by default" in MariaDB - which is what most (linux) people are (probably) thinking of.
Pretty sure the name "utf8" isn't metaphysically wired to a defective physical representation. All they have to do is wait until the next minor release, add a compatibility name, make the generic name refer to a non-defective representation, and update the documentation and release notes accordingly.
If they were really conservative, they could wait until the next major release to repoint the name like that. Haven't there been four major releases since 2003?
This would be problematic for existing applications. Even if you leave existing binary data as-is (in existing tables utf8 becomes utf8mb3), changing the meaning of "utf8" in only new tables is still very problematic. Think about logical dumps, e.g. CREATE TABLE statements which use the "utf8" alias. For example, any self-host application which runs a .sql file in its install process. Or spinning up a dev environment for a new hire. Basically, anything that contains a CREATE TABLE with the utf8 alias now has a landmine:
In older versions of MySQL, InnoDB indexes were limited to 767 bytes. Meanwhile, VARCHAR(N) can store N characters. So with utf8mb3, that's a max of 3N bytes worst-case; but with utf8mb4 ("proper" utf8), it's now 4N. This meant that VARCHAR(255) CHARACTER SET utf8mb4 could not be indexed (in its entirety) in these older versions; the CREATE TABLE would fail if you try.
When you have a massive installed base of users, and those users have lots of dumps / install scripts containing "VARCHAR(255) CHARACTER SET utf8", you simply cannot change the meaning of "utf8" in a minor release.
I fully agree that MySQL could have made this transition faster/earlier than they did, but I disagree that it could be done trivially or terribly quickly.
Advertising the ability to store UTF-8 characters when you can do no such thing is a serious problem. Obviously you have to do whatever it takes to fix it, including fixing a deficient indexing implementation if necessary.
I believe the limitation you mention was fixed in MySQL 5.7, which would have been a reasonable opportunity to make "utf8" live up to its name.
Keep in mind that prior to 2010, MySQL and InnoDB were owned by different companies. Unilaterally deciding to increase the InnoDB index size limitation to permit utf8mb4 varchar(255) was not physically possible for the MySQL team prior to that point.
Anyway, I fully agree that the utf8 alias swap should have been done in MySQL 5.7, or perhaps even 5.6. I'm just disagreeing with your assertions that this would have been a trivial change.
Nor do I think it should have been a "stop the world" high-priority change prior to emoji existing in unicode (~late 2010). Non-BMP characters were very rare before that point. Hard to justify a business need for a complex migration just to store archaic hieroglyphs. For another point of comparison, MS SQL Server only added support for non-BMP characters in 2012, and only added support for utf8 encoding in 2019!
> now I have to look into this strict mode and what else it breaks
It's a mode to ensure nothing happens silently: illegal data type conversions, attempting to insert invalid values for the data type (too large, too long, invalid characters, etc), attempting to do an INSERT which doesn't specify some columns that lack defaults, etc. With strict mode enabled, all of these things throw hard errors, like in most other relational databases. Generally, it covers the entire class of things that are typically the most common complaints about MySQL on HN.
Interesting wording re: "breaks" -- for practical purposes this is a real concern for existing applications, which may be doing bad things which previously didn't throw errors with strict mode off. Do you want your DB to suddenly fail loudly for these things (strict mode), or do you want your existing application to continue working in these cases? Can't have it both ways.
> Is this "strict by default" in MariaDB - which is what most (linux) people are (probably) thinking of.
MariaDB made it default in 10.2, ~5 years ago.
I don't agree that most Linux people conflate MySQL and MariaDB though. At large US tech companies that use MySQL, they're almost all literally using Oracle MySQL, or a patch-set like Percona Server which directly tracks MySQL, or an internal patch-set that does the same.
In recent years, MySQL and MariaDB have diverged a bit on feature set and focus; each has some interesting features that the other lacks. They are not drop-in replacements for each other.
So... while I'm broadly in agreement with you, and a huge fan of strict typing, I will note people use languages like Javascript and usually are ok with things like string to integer coercion.
It seems to be that there's a world of difference between "silently truncate string" and "type coercion" even if I readily agree both are sources of security vulnerabilities.
Thanks for the replies though, even though I clearly annoyed at least 2 people due to the downvotes.
I enabled strict sql mode in MariaDB for an website just last year, after noticing the database was silently corrupting data. What made the transition much less painful was noticing that when strict sql mode is disabled, MariaDB produces a warning for the queries that corrupted the data, but you must run SHOW WARNINGS; to see the warnings after running such a query. Knowing that, I made the website run SHOW WARNINGS; after every database query and logging if a warning occurred. This allowed me to deal with the most of the warnings before they were promoted to errors by enabling strict sql mode.
Implementing silent string truncation in the first place (by default(!)) is an excellent indication of how fast and loose and out of touch the original MySQL developers were with the database world. It is the worst database "feature" I have ever seen.
It made more sense at the time. Earlier days of the web, dynamic scripting languages used everywhere, etc. No emoji, barely any use of 4-byte characters at all. Large corps weren't using open source DBs, or sometimes open source anything. Simpler times!
And for sake of comparison -- if I understand correctly, prior to just 2 months ago, sqlite didn't even have the option for strict type enforcement [1]. Do you judge sqlite as harshly, or think its creator is out of touch with the database world?
Relational databases have been used in production since the late 1970s. You would be hard pressed to find any that silently corrupted user data like that, ever, until MySQL came along.
Sqlite is more of an embedded database, but if it silently truncated character data under some conditions that would make it equally unusable. Better to have no type enforcement than defective type enforcement.
I think you misunderstood my point. Yes, relational databases have been around a long time. But in the early days of MySQL, most of the companies using MySQL differed quite a bit from the companies typically using DB2, Sybase, Oracle, etc. I've been using MySQL professionally since 2003 and am speaking from first-hand experience here: the web development world was largely separate from the traditional enterprise world at that time. It was considered a different use case, it's not like today where the biggest companies in the world are tech companies built on open source software.
Regarding "silent", MySQL at least emits a warning when this happens. Still not great / obviously wrong in a modern context, but just for clarity's sake it wasn't completely silent.
As for "defective type enforcement", that's a subjective opinion. In C, atoi("123hello456") returns 123 -- once you hit an invalid character, everything from that point is truncated. Is C atoi defective? Perhaps, but it's an opinion.
With strict mode disabled, four-byte characters worked the same way in MySQL utf8mb3: four-byte characters are invalid in this data type, and everything gets truncated at the first invalid character. Would simply stripping the invalid characters, but keeping valid chars after them, necessarily be more "correct"? No, it's an implementation decision, especially considering that emoji did not exist at the time and barely any non-BMP (4-byte) utf8 characters were used anywhere.
It is certainly true that most users of relational databases before MySQL used them for applications where throwing away data is a potential catastrophe, and most uses of MySQL were for toy applications where if that happened no big deal. It was about a decade before MySQL even supported transactions.
atoi is deficient by modern standards, but at least it clearly documents what the behavior is and always has. It doesn't truncate anything. For a database, truncating data is unforgivable. That is what errors are for, so your database doesn't act as a black hole where your data may silently disappear forever.
And yes, if you can't return an error for some reason, you should preserve as much data as possible. Throwing away hundreds or thousands of characters after an initial anomaly is insane. But a database should never do that anyway, it should either store what you give it, or return an error. Same with a filesystem or any other trustworthy means of data storage. Sorry we were too lazy to tell you we threw your data away isn't acceptable, nor is relying on anyone to check for warnings that should be hard errors.
I would argue yes, that atoi is defective. Stripping characters is equally defective.
A QA walks into a bar and orders "123hello456" beers. The correct answer is not for the barkeep to give them 123 beers, its to ask "I beg your pardon?" So the truncation in general is the problem. If the operation fails - too many bytes, wrong encoding, string too long - the operation should fail. It's not the db's job, that is the business logic's job.
All competent software tokenizes a input string before calling atoi on an extracted piece of it. Otherwise you can't even tell the difference between "0" and an empty string.
On error resume next was the idea about error resilience back then. MSSQL has a similar feature: when you run a script and an error happens it just keeps running statements. If you want to interrupt it, you should wrap the whole thing in the try/catch statement and also roolback a transaction if any or it will commit a partial result.
It's less a question of fault and more a question of whether developers need to be aware and look out for those gotchas. A security problem doesn't stop being a problem because your hosting provider did something vs. MySQL doing it by default.
If you don't have an explicit value set in your my.cnf config and the client sends no explicit value upon connecting you get the current built in default of the version you are using. If you set something somewhere it will be respected.
The day I realized that is the day I stopped using MySQL. It was a very long time ago, I was burned by this exact issue. I don't know what's more fundamental to a database than storing the data you ask it to. If it's storing something else, then it failed at its job spectacularly. At the time I experienced this bug I think it didn't even give a warning, but I think that's actually worse with one : it knows it's storing incorrect data but does so anyways.
And it still has not been fixed after all those years. I don't know what use is a character encoding that partially supports Unicode (you just have to try to find out in what way) and is used by nobody else. I don't really care that at some point in the future utf8 is going to become an alias to utf8mb4, a DB capable of this behaviour cannot be trusted, and it allowed me to discover postgres which seems superior in just about every way, so I don't need to go back.
The author mentioned MariaDB in the intro and the went on about MySQL, not sure which he actually used.
I think the latest recommendation for MySQL was utf8mb4_0900_ai_ci and utf8mb4_general_ci for MariaDB.
While I agree about the Postgres recommendation, it's a non-starter in the PHP world, unless doing something from scratch. Even then, the support for MySQL/MariaDB is much better.
For one, it's out of reach for lots of folks who use shared hosting services, where there's a managed LAMP stack. It's rare if they have Postgres. If you use AWS, of course, everything is up to you.
There's also a connection pooling and different startup for MySQL/Mariadb vs Postgres. PHP engine generates the page and closes itself and connections. Postgres assumes you are more persistent.
Also, there's a difference in how GROUP BY works in MySQL/Mariadb vs Postgres, and wrapping your head around it, after using it the MySQL way for years, can take time. So not a drop-in replacement.
Does this even fully fix the problem? It looks like utf8mb4 is limited to 4 byte sequences, but as far as I understand, utf-8 is variable width. Can utf8mb4 even encode the scottish flag https://www.iemoji.com/view/emoji/2476/flags/scotland ?
Flags are not single code points. UTF-8 refers to how code points are stored. If you look in your link at "Hex Code Point(s)", it is that first one that would be a problem with "utf8" in MySQL, because of the 1 in the 0x10000 position. The other six code points required would fit in fine.
Unicode is developing more and more things that require code points. I'm not sure what the longest legal non-redundant series of code points that can validly represent a glyph somewhere is, but it's getting up there with all the emoji skin modifiers and such.
Emoji modifiers, both for gender and skintone, do not produce that many extra code points. Combinations are made in the same way ¨ + e can combine into ë: a code point, followed by a combination code point, followed by a modifier. During text rendering, these code points are converted back into a single glyph.
All UTF-8 codepoints in use today can be encoded with four bytes. Theoretically the Unicode system can be used to create 6 byte code points if that ever becomes necessary, but it won't be for a while. Crossing the 4 byte boundary would also introduce compatibility issues with UTF-16, so I'm sure the Unicode Consortium will do their best to prevent this from happening as long as they can.
Aaaah, that makes sense. I think the flag of Scotland () is the longest usable one I've seen, but you could stack near infinite items in Zalgo form on top of normal letters if you count those. I don't think Unicode has any restrictions on the amount of co combinatory characters, though most text parsers will probably enforce some kind of limit.
The biggest codepoint in Unicode fits into 4 bytes of UTF-8. UTF-8 would allow up to 6 bytes, but those codepoints are not in use currently. If they ever become in use, yes, you'd probably need a new character set again. But then a lot more things will break, as higher codepoints would be incompatible with UTF-16 also.
UTF-8 is variable width. The biggest valid codepoint is U+10FFFF, which has a 4-byte encoding in UTF-8. Other codepoints have 1-, 2-, or 3-byte encodings.
Is this a problem? Flags in unicode are defined by several special characters. The flag of Scotland isn't really a single character, it's "<waving flag><tag g><tag b><tag s><tag c><tag t><cancel>".
All of these characters are multi byte combinations. The hex for the flag is not a single, super wide character, it's 0xF09F8FB4 0xF3A081A7 0xF3A081A2 0xF3A081B3 0xF3A081A3 0xF3A081B4 0xF3A081BF. You might get some weird results if you take substrings from that, but it won't be a problem for the backing database store; each separate "binary character" is a four byte sequence (as denoted by the 0xF at the front of the number).
Variable width is unlikely to be a problem. At 3:5 the Scottish flag does not have an unusual aspect ratio. This is unlike the flag of Qatar, with a ratio of 10:28, or Nepal with both a 3:4 (approximate, not exact) aspect ratio plus an irregular shape.
Bad idea. If they deprecate "utf8" they will deprecate a standard. It was designed and specified at a time when the Unicode code space had a 21 bit limit. There are some technical (storage space-related) considerations with the suggestion to "just use utf8mb4 everywhere instead" because of how InnoDB's indices work.
MySQL "utf8" is still "utf8mb3", which is not a standard anywhere except MySQL. It cannot store the full range of 21-bit code points; I don't know why you keep repeating this. The maximum codepoint in 3-byte UTF-8 is 0xFFFF, which is 16 bits.
> If they deprecate "utf8" they will deprecate a standard.
No, they won't. They will be deprecating something they created that never conformed to the UTF-8 standard. That fact that unicode didn't have codes beyond 21 bits at that point is pretty irrelevant.
It was an invalid implementation from day 1, at least with the name of "utf8".
>It was designed and specified at a time when the Unicode code space had a 21 bit limit
21 bits is the current Unicode limit. Unfortunately UTF-8 in 3 Bytes only has 16 usable bits - the first byte starts with 1110 and the two continuation bytes start with 10, so it's 4 + 6 + 6 = 16 bits.
> You should also be aware that the utf8mb3 character set is deprecated and you should expect it to be removed in a future MySQL release. Please use utf8mb4 instead.
This is an old article and it was incorrect when written, and it's incorrect today. The author may possibly have learned since writing it that UTF-8, by design, encodes up to 21 bits of character code point in at most 4 bytes of data. It works and behaves as it should.
Same. Inbound SMSes with emojis. Outbound SMSes with emojis. Names and labels with emojis etc. But the implementation of "utf8" still isn't wrong, nor a bug - just like the 20 year old specs of UTF-8, they just failed to take the future into account.
The plain utf8 covers the "basic multilingual plane" (x0000-xFFFF), so it will get you very far, actually.
In our app, we finally went for utf8mb4 to allow people to enter emoji. As a side "bonus" you will also soon see some clever people entering their names to stand out, such as "𝙹𝚘𝚑𝚗". Note that this is not "John". It is actually a series of mathematical symbols:
U+1D679 MATHEMATICAL MONOSPACE CAPITAL J
U+1D698 MATHEMATICAL MONOSPACE SMALL O
U+1D691 MATHEMATICAL MONOSPACE SMALL H
U+1D697 MATHEMATICAL MONOSPACE SMALL N
One fun thing you can do in MariaDB is define a collation based off of the Unicode confusables list. The result of this is that select * from usernames where user = 'John' would still work :)
It also avoids registering both 'John' and 'John'. (changed the o)
That's a great feature. I was running a community site once where impersonation became a popular game. So we had dozens of users registering accounts with Cyrillic i,o,a,e etc.
For looking up relevant further material, this is called a 'homograph' or 'homoglyph attack'.
It was also popular for site-spoofing by sending links with these cyrillic character to lead people to a fake bank/search_engine page, until that became widely used, and then owners of the tech-stack (browsers, registrars, dns-operators, etc) shut down this attack vector mostly.
This is unfortunate as it makes things unreadable for screen readers. I have seen many tweets from companies and organisations that use these characters to replace funny "fonts" or create emphasis effects in the text, rendering them inaccessible in the process.
In my own service I plan to offer users the ability to define a "stylised" nickname and an alternative text for screen readers, should they be interested in it (like an alt text for images). If Twitter had such a feature, I'd use it because I really like the effects, even if they're cheesy. :)
Of course, nobody "refused" to fix a "bug". Instead, a non-conformant behavior was already relied upon by legacy systems out in the wild and the "fix" was added in a backwards-compatible way.
Edit: Three bytes are enough to fit nearly any of the chars in use in any language, including Chinese and Japanese, so I can only assume someone "smart" in the MySQL dev team decided to "save space" (before emoji were a thing).
utf8 is an encoding for unicode codepoints. Those codepoints are spread on a space that is extremely vast (up to ~4 billion) for which that can be represented with up to 4 bytes. It turns out emojis are positioned in a place where the first byte will never be 0, so even if there were only one it would require the full 4 bytes to encode them.
It could be he's thinking of the historical definition which included support for 5 character encoding with a maximum codepoint of U+7FFFFFFF or ~2 billion.
That was restricted I believe primarily for compatibility with more limited encodings like UTF-16.
I guess it's possible that at some future point in human history when UTF-16 has been purged from memory, the 5 character encoding might be allowed again. :)
Now, technically this scheme could expand to 6-byte characters without getting confused with things like BOM/etc, however any code points larger than 2^21 wouldn't be representable in UTF-16, which has its own set of constraints. This means the unicode consortium has basically limited themselves to two million or so possible code points, which is why UTF-8 doesn't need to go more than 4 bytes. (I wonder if a future unicode version will require a larger limit and would thus create a new "utf8mb6" scheme, and drop UTF-16 altogether?)
> (I wonder if a future unicode version will require a larger limit and would thus create a new "utf8mb6" scheme, and drop UTF-16 altogether?)
On a thread a couple of years ago (https://news.ycombinator.com/item?id=20600873) it was mentioned that the UTF-8 encoding scheme can be cleanly extended to 36 bits, so even "utf8mb7" would be a possibility.
Unicode specifically limited itself to the range zero to U+10FFFF
Obviously nothing in the laws of nature forbids "a future Unicode version" from disavowing this limit, but we could say the same for whether "a future United States of America" could disavow the status of independent Indian Tribes it has previously recognised.
That's a terrible excuse. MySQL should have fixed this in a major release. I had to work with a production system that had all kinds of issues because of this bug (engineers assumed, with good reason, that UTF-8 meant UTF-8, when it did not).
This kind of reasoning is how we end up with vulnerabilities like the recent one in Log4j. Just because a behavior made sense in the past, or an unfortunate bug made it into production, is no excuse to let it inflict damage in perpetuity.
I don't see how introducing a new major release would fix this? people would use the old version (because of the breaking change) for a while still, you might even end up in a python2/3 situation.
To the extent that it's "a python2/3 situation"... Isn't that exactly what it is now, too, then? If you have a big change to do and postpone it "because of the installed base", then that just makes it worse the longer you wait. (Unless of course you're counting on your user base to shrink over time.)
Even that doesn't make any sense, because refusing to encode characters that require four bytes doesn't save any space; it just makes it impossible to encode those characters. Nothing about the other encoding lengths changes.
The only thing I can figure is that something somewhere is using a 16-bit quantity for decoded codepoints. Four-byte encodings are for codepoints above FFFF. (Which I guess is still someone's idea of "saving space.")
Edit: Apparently the max encoding length used to be six bytes, so there's literally no plausible explanation for this that doesn't end with "thank god I stopped having to deal with MySQL over a decade ago."
Three bytes are enough to fit nearly any of the chars in use in any language, including Chinese and Japanese
With only 3 bytes you'll completely miss plane 2, the "Supplementary Ideographic Plane" which includes tons of Chinese-Japanese-Korean Han characters.
I wish people would stop saying the supplementary characters are just for "emoji". Asian unification was very controversial and ultimately unsuccessful. Plane 1 and Plane 2 are important, especially if you're going to sell software or products in China or Japan where they are mandated.
> Three bytes are enough to fit nearly any of the chars in use in any language
This is not a fixed length encoding scheme, it is a subset of UTF-8, which is a variable length encoding designed so that the most common characters only take one byte of storage. As a consequence you do not get 2^24 possible characters in three bytes, you get much less than that (less than 2^16 actually), and the benefit is compatibility and compression.
To represent the full range, UTF-8 requires up to four bytes, even though 21 bits or three bytes would do for a fixed length encoding. UTF-8 is far more efficient in storage and transfer bandwidth than a fixed length encoding would be without further compression, to the point where using a fixed UCS-4/UTF-32 style encoding is an effective way to nearly quadruple the memory requirements of a large class of programs.
Let's say for the sake of discussion that your MySQL db has a lot of tables encoded as "utf8." Are there any known drawbacks or gotchas to converting them en masse to "utf8mb4"? Is this a lengthy operation?
Schema changes to large tables are always huge pain in the ass in MySQL. Every team I’ve worked on goes to great lengths to avoid changing the schema, which leads to all kinds of exciting anti-patterns like recycling old fields even if their name no longer matches what is stored in them.
I suspect this is because for large databases, schema changes lock the tables for extended periods of time, and if this time exceeds a reasonable time for a service window, you need good database replication. And you probably don’t have good MySQL database replication unless you’re on AWS or similar.
That’s exactly what it is. If you have a big table you are gonna have a pretty long outage while you update that schema. Like in the last place I worked our largest table would take more than an hour for a schema update. So we never did schema updates.
At my last workplace, we had a service window of one weekend. Prior to this, an application had to be restructured to even make the schema upgrade able to finish in one weekend.
So you're right but also wrong thanks to good tooling. If you have enough discipline to not put triggers on your tables then pt-online-schema-change has got you sorted. Does it take forever? Yep, but does it matter that it takes forver? Nope because it doesn't lock tables.
There are collation changes between utf8 and utf8mb4. Specifically, utf8mb4 uses the (much better) UCA 9.0 algorithm.
But this implies that all indexes will have to be regenerated, if they contain a column that is utf8 and will be converted. As they sort differently, this requires rebuilding the index from scratch. Depending on data size this can take measurable time and effort.
This is a forum of professionals though, how many production apps can actually run a database with no HA, no replication, single process access, no remote access, and is weakly typed?
And if you suggest "$project that's built on SQLite" to do all that then congrats you just reinvented MySQL or Postgres but with SQLite as the DB engine.
According to the docs, MySQL is a bit more explicit and calls the old "utf8" "utf8mb3" nowadays, and notes that it may be removed at some point. Although "utf8" is still aliased to the footgun at the moment.
They also seem to be slowly phasing it out. Internally it's utf8mb3 and utf8 is just an alias. The idea is to eventually make utf8 an alias to utf8mb4.
Back then MySQL used the memory engine for implicit temporary tables, and memory engine has no variable width data types. So a "varchar" becomes a "char", and a "varchar(255) charset utf8" becomes an allocation of six times 255 = 1530 bytes (filled with a lot of padding).
Reducing this to 3 bytes is still bad, but only half as bad as before.
Only MySQL 8 introduced innodb typed temporary tables (unlogged, even), so with MySQL 8 this is no longer a problem.
In MySQL, if you want to make sure that your Unicode fields are mangled, never use ‘utf8’, use ‘utf8mb3’. Since ‘utf8’ is an alias to ‘utf8mb3’, that alias might eventually be updated to point to ‘utf8mb4’ which won’t mangle your characters.
The only safe choice here is to explicitly use ‘utf8mb3’.
361 comments
[ 3.4 ms ] story [ 213 ms ] threadYou have set Apache to UTF-8, PHP to UTF-8, MySQL to UTF-8, and the MySQL driver you are using to UTF-8. It's not clear how these setting interact. Are there silent conversions happening or do you always have to detect the encoding on data coming from the server? HTML pages have a character encoding specifier, but the BOM at the start of the file takes precedence (I think.) I got it to work by always detecting encoding for any text coming from the database and using iconv, but this turned out to be really slow and unreliable. It was truly the biggest mess by an order of magnitude than any other programming problem I faced in my career.
Would not attempt again.
[1] https://github.com/chardet/chardet
It worked fine, no major issues, just some work.
- set encoding at HTML head - also at any HTML form element - Set database collation correctly - I believe it also needs to be done per table - The PHP -> MySQL connection itself may need to have the encoding set - All string manipulation to be done with mb_ - Even ensure your code files themselves are in UTF8
Wat? That is... not a great takeaway from this experience. "Oh, this library had bug reported in it! I know, that means I should just write my own, because clearly they're all buggy and my software is always perfect."
I suggest it can be called the Sirius Database.
"It is very easy to be blinded to the essential uselessness of [the new database you just wrote] by the sense of achievement you get from getting them to work at all. In other words—and this is the rock solid principle on which the whole of the Corporation's Galaxy-wide success is founded—their fundamental design flaws are completely hidden by their superficial design flaws."
That hit me right in my 9-5 "not invented here" nightmare.
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_...
When you say "complex ride" what does that mean?
They do suggest a backup before you run this in case there are problems, most folks have periodic backups anyways, but the backup is not used.
On managed postgresql services like RDS it's easy, yes, because Amazon does all of the nasty work for you and has already seen all the kinks you may run into.
If you are on your own and using Docker, you essentially need a third-party image that has the old and new binaries (https://github.com/tianon/docker-postgres-upgrade). Good f...ing luck if you dare run PostgreSQL inside a Kubernetes environment, because adapting that guide to that is a can of worms large enough to make anyone squirm.
If you are on your own and are using distribution packages, it's more complicated (https://blog.samuel.domains/blog/tutorials/from-stretch-to-b...).
If you are on your own and use postgres from source, well... have fun, there's a reason why I prefer using distribution-maintained packages when possible.
EDIT: Oops, guess you meant version updates not DDL. Leaving comment for posterity.
0: https://brianchildress.co/reorder-columns-postgres/
You're misreading this on multiple levels
https://news.ycombinator.com/newsguidelines.html
I don't think it's always smart to just change out your data layer.
But, if you're starting a new project, I do think PG is one of the better options and tends to follow the principle of least surprise. (hence: big fan)
Index hints.
If you aren't hitting an index in Postgres you have to dig in to table stats and figure out what is wrong but MySQL gives you more control.
However, I would still rather work with Postgres AND have to juggle a connection pooler than deal with MySQL. Transactions on DDL are _great_ and the ability to use foreign keys across partitioned tables is how it should be.
It’s a shame that almost every job I worked at uses MySQL and not Postgres. But that could be because those companies all got their start like a decade or more ago when Postgres was not as well known.
> Index hints.
Eh, it's an extension. One you shouldn't use, but it's there.
Is this comment relating to the overhead of idle connections, which has historically necessitated the use of a pooler in front of PG? If so, I believe this is resolved in postgres 14
https://pganalyze.com/blog/postgres-14-performance-monitorin...
Perhaps, but I'd argue that the "weird" behavior of postgres just tends to be clearly thought out design decisions that they made for a valid reason that may cause you some pain with how you use it (e.g. their process-per-connection model).
MySQL's "weird" behavior, on the other hand, just tends to be completely invalid footguns like this. Despite what some people are arguing in this thread, a 3-byte version of UTF-8 was never in any spec anywhere and was an invalid shortcut from day 1.
I hated how postgres forced you to create a system user to connect I wonder if it still requires this.
Postgres defaults to the system's collation for UTF-8, which caught me by surprise. Now I'm stuck with a production DB where text prefix queries, which should be a perfect use of a btree, are a full table scan. Probably gonna have to take some maintenance downtime to fix the situation :-\
And more generally about operating Postgres in production: https://blog.nelhage.com/post/some-opinionated-sql-takes/
As for Postgres, I have enormous respect for it and its engineering and capabilities, but, for me, it’s just too damn operationally scary. In my experience it’s much worse than MySQL for operational footguns and performance cliffs, where using it slightly wrong can utterly tank your performance or availability. In addition, because MySQL is, in my experience, more widely deployed, it’s easier to find and hire engineers with experience deploying and operating it. Postgres is a fine choice, especially if you already have expertise using it on your team, but I’ve personally been burned too many times.
The only supported way to set the database's default collation is at creation time, so I'd need to create a new database, copy the data over, and switch my application over. (There appears to be some Postgres voodoo to change the default collation for an existing database, but that's too risky for me.)
Until I get that done, I need to explicitly specify the collation for all new text fields, e.g.
https://simply.name/pg-lc-collate.htmlThe answer is Postgres. I have some previous experience with MySQL and there are advantages and disadvantages. The biggest advantage so far is partial indexes.
But since I don't have as much experience with Postgres, there's still the occasional surprise, e.g. index names not being scoped to the table, this collation thing. And from what I've read online, I should probably set up some monitoring for vacuuming issues.
The article about Postgres operational footguns recommends outsourcing those issues by using a managed Postgres service (e.g. AWS RDS or Google Cloud SQL), and that's what I do.
But those preferences may be specific to my situation -- a software developer running an HA web application.
* If you're not a software developer, you might be surprised by the "C" collation, which puts "Z" before "a".
* If you're not running an HA web application, then it's easy to change the collation later -- just dump/restore the DB.
We only caught this, because thankfully, we had written a manual checksum script, which looped through every table, read out all values from each row into the application, and hashed the results, then compared between when the app was connected to the source MySQL database vs the destination Postgres database. We ended up having to massage and fix those silently-corrupted characters.
https://bugzilla.mozilla.org/show_bug.cgi?id=1253201
Assuming a SQL error can't be thrown for some legacy reason (and you'd think a flag to opt into that would be an option), why not replace the invalid parse with U+FFFD like most things do? Or at a bare minimum, only strip the invalid character which at least makes attacks a bit harder and results in a little less data loss.
I know it's definitely not enabled on one updated and maintained BSD - this could be because they didn't want to break existing systems during upgrade.
I suspect this problem is still extremely common. But good to know there's some form of defense.
Then, subsystem B trusts reading the database field (since it passed validation layer A).
Obviously more validation layers can be added, but at this point validation layer C called by subsystem B needs to know what the initial input from layer A is in order to differentiate it from the db value which was manipulated - a rather tricky thing to do sometimes. (I guess you could add a hash to the db to check the db is storing your strings, but really.. come on)
Upgrading to utf8mb4 is probably safer than hoping enough validation layers thrown at it solves the problem.
But I’ve also seen people relying on validation at the time of insertion so many times that I wanted to warn against that, too. Not in argument against the need for utf8mb4.
Imagine a bug like this in ext4. No one would reasonably contend that the layers on top of it should be validating that the files you write out are the ones you’ll read back in. We write unit tests for all kinds of stuff, but we’re not that thorough.
This came up when a developer used YAML for some configuration file. There was all sorts of confounding behaviour because YAML is still valid when truncated.
Every database has limits, but at least alert the user! I’d rather a query fail than alter data.
Edit:
This is something that might be useful in CS classes, to reflect what being a real-world engineer can often entail. Set an assignment with some silly nonsense like this and students have to debug it.
The problem was multi-pronged. The courses had become increasingly geared toward industry, and students that had an interest weren't really fit for continuing their studies.
In the end, the longer 5 year program (MSE) was allowed to remain more vocational while those seeking a bachelor's were brought along a much more theoretical path so they could then do a master's followed by a PhD.
It wasn’t my implementation. This was another team
I think virtually everything generically configurable in the UI that isn't part of a small core with dedicated fields (or in the sitewide PHP config include) is handled that way.
The topic of MySQL utf8 vs utf8mb4 was discussed in depth just six days ago on HN: https://news.ycombinator.com/item?id=29793916
In fact, when the log4j thing came up, we audited all our existing COTS/internal and virtually all of it was still on the "EOL" log4j 1.x.
Since you're familiar with this. What happens with an existing upgrade - is strict mode enabled by a typical distro package upgrade or does it only impact new installs?
> What happens with an existing upgrade
sql_mode is a setting, so it depends on whether the default is being overridden: e.g. does the my.cnf config file specify a value explicitly? Does your application's connection logic set a session-level override for each connection? DBaaS vendors add an additional wrinkle since they tend to have custom UIs and APIs for global settings.
> typical distro package upgrade
Many Linux distros made it so that attempting to install "mysql" actually installs MariaDB instead, so when going this route, it's unfortunately always very hard to predict what you'll get or what will happen!
MariaDB is indeed pretty pleasant to use and for most use cases can be a drop in replacement for MySQL, should you not want to use MySQL, but also wouldn't want/need/be able to use PostgreSQL or something else: https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility/
I might be in the minority here, but i actually rather enjoy MySQL/MariaDB, since MySQL Workbench is still the best DBA software that i've used, especially because of the ER modelling and schema sync functionality, and their approach to schemas/users within a DB feels more reasonable than that of Oracle/PostgreSQL.
That said, both PL/SQL and PL/pgSQL feel more powerful than whatever MySQL/MariaDB has. Also, in regards to MariaDB in particular, their versions have diverged from MySQL somewhat, so you might still want to look into the differences, if migrating over: https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility/
As for predictability - nowadays i just run specific versions in containers (e.g. 10.7.1 or maybe 10.7 which corresponds to the patest PATCH release), to sidestep the distro differences altogether. It also lets me easily launch new instances, give resource limits to them, have multiple versions in parallel and mount the data directories wherever i need them, for easier backups etc.
I still bind mount directories though. Docker volumes seem really convenient but a few years back I dealt with helping out a coworker with some data loss (pre-volumes) in a container's filesystem so I have some trust issues with how they implement storage.
This mirrors my experience and despite how Docker seems to suggest that non-abstracted storage is a liability and we should use volumes instead in many cases, i disagree: https://docs.docker.com/storage/bind-mounts/ In my eyes, the current implementation for volumes is for data that i don't really care much about, but would still consider extracting/moving later and have it be persistent, like when trying out new software.
Personally, bind mounts also make it extremely easy to operate with whole installs of software - for example, should i want to upgrade something like Nextcloud (and have enough space), i can just stop the containers, make a copy at /docker/nextcloud_stack/data/nextcloud_mysql_old and run a new container version against /docker/nextcloud_stack/data/nextcloud_mysql and see if it all works.
If the update has corrupted everything, i can just delete the broken folder, rename my backup and proceed with restoring the older version. If i want to mess around with the broken version, i can do it with 0 worries about what else could break. It's amazing, way better than relying on the POSIX approach to a file system, where your software, its configuration and its persistent data is all over the place.
I once had to manage a Tomcat install which had the webapps, config, logs directories in their idiomatic locations rather than one folder, which was pretty horrible from a management perspective. I had to constantly jump around and wasn't entirely sure how it all fits together (probably lots of symlinks).
It's curious that some/many Kubernetes distros lock down the local storage driver, though - there's a preset data directory, where all of your mounted data ends up, which feels like a step backwards from a customization perspective. I had to change the data directory for the entire Kubernetes install to be able to store my data in a painfully obvious /app or /data mount point. Then again, their ability to limit the sizes of these persistent volumes is pretty cool.
Wouldn't it be a nice world where you can just declare something EOL and it disappears and somehow magically it's not a problem anymore for anyone end-to-end? Thing's just don't work that way. Like, suddenly, a product that was working perfectly fine for who knows how long suddenly needs to modified because some ass-hats thinks that EOL isn't suitable for production.
There are a number of reasons to upgrade a dependency or a product; an EOL label shouldn't be a part of the calculus. EOL may imply certain conditions that may influence the decision to upgrade but for the sake of EOL you can eat a bag of dicks. Think harder why you're proposing an upgrade is needed and how it'll add value to the business. EOL software is perfectly fine especially because it's been battle tested.
We also use log4j 1.x all over the place and I feel zero need to change that.
Or do you mean in the abstract, fairy-tale ending sense?
And "EOL" is a convenient shorthand for those conditions.
Most use cases care about never getting patches for security problems ever again.
I dunno, a widly known RCE seems a pretty good reason to take something out of production.
Honestly, in that case I tend to agree with GP. As long as you recognize EOL means you are in charge of supporting it and possibly backporting security fixes and you're okay with that - why not use it?
In many cases it is not, it is purely a 'ticking time bomb' - if new CVEs or requirements arise which cause issues in EOL versions, you may not even become aware of the vuln.
In counterbalance, new versions of code have new, unknown CVEs, new bugs, etc.
The issue is that of support, the fallacy and process issue is that 'current' support is of greater quality than 'historical' support, or that old software has a disproportionately higher support costs than 'current' software.
The true issue is overdependence on FOSS software and unwillingess to contribute - all software has costs, major vendors are 'coasting' on work provided by free volunteers and panic when those volunteers decide to no longer work for free.
If vendors were doing the legwork necessary to properly inhouse their dependencies, I would wager that much less software would end up discarded in 'fear', for a lack of a better word than this - modern development is supposedly built on features and patches, any codebase should be maintainable for a much longer period than its supposed EOL.
If the end user of the product or their needs don't matter, sure.
There are similarly dumb misconfigurations that can theoretically be made in most hosted open source software.
Also, I gotta say, now I have to look into this strict mode and what else it breaks, when in this particular case a "don't silently truncate strings" flag would be preferable and probably lower impact, since "strict" vs "security flaw mitigation" is not quite the same thing (does such a more granular flag exist?). ... unless that's all the strict mode does.
Hm, and since you brought this up elsewhere. Is this "strict by default" in MariaDB - which is what most (linux) people are (probably) thinking of.
That happened in 2010 - I'm not sure 12 years counts as "finally happening", or just how much quicker you would like than 12 years ago?
Other than a time machine, I'm not sure what else you would like them to do.
Do you actually use MySQL or are you just complaining from a theoretical POV?
If they were really conservative, they could wait until the next major release to repoint the name like that. Haven't there been four major releases since 2003?
In older versions of MySQL, InnoDB indexes were limited to 767 bytes. Meanwhile, VARCHAR(N) can store N characters. So with utf8mb3, that's a max of 3N bytes worst-case; but with utf8mb4 ("proper" utf8), it's now 4N. This meant that VARCHAR(255) CHARACTER SET utf8mb4 could not be indexed (in its entirety) in these older versions; the CREATE TABLE would fail if you try.
When you have a massive installed base of users, and those users have lots of dumps / install scripts containing "VARCHAR(255) CHARACTER SET utf8", you simply cannot change the meaning of "utf8" in a minor release.
I fully agree that MySQL could have made this transition faster/earlier than they did, but I disagree that it could be done trivially or terribly quickly.
I believe the limitation you mention was fixed in MySQL 5.7, which would have been a reasonable opportunity to make "utf8" live up to its name.
Anyway, I fully agree that the utf8 alias swap should have been done in MySQL 5.7, or perhaps even 5.6. I'm just disagreeing with your assertions that this would have been a trivial change.
Nor do I think it should have been a "stop the world" high-priority change prior to emoji existing in unicode (~late 2010). Non-BMP characters were very rare before that point. Hard to justify a business need for a complex migration just to store archaic hieroglyphs. For another point of comparison, MS SQL Server only added support for non-BMP characters in 2012, and only added support for utf8 encoding in 2019!
https://blog.koehntopp.info/2022/01/12/utf8mb4.html#so-what-...
See https://news.ycombinator.com/item?id=29910023
I definitely agree!
> now I have to look into this strict mode and what else it breaks
It's a mode to ensure nothing happens silently: illegal data type conversions, attempting to insert invalid values for the data type (too large, too long, invalid characters, etc), attempting to do an INSERT which doesn't specify some columns that lack defaults, etc. With strict mode enabled, all of these things throw hard errors, like in most other relational databases. Generally, it covers the entire class of things that are typically the most common complaints about MySQL on HN.
Interesting wording re: "breaks" -- for practical purposes this is a real concern for existing applications, which may be doing bad things which previously didn't throw errors with strict mode off. Do you want your DB to suddenly fail loudly for these things (strict mode), or do you want your existing application to continue working in these cases? Can't have it both ways.
> Is this "strict by default" in MariaDB - which is what most (linux) people are (probably) thinking of.
MariaDB made it default in 10.2, ~5 years ago.
I don't agree that most Linux people conflate MySQL and MariaDB though. At large US tech companies that use MySQL, they're almost all literally using Oracle MySQL, or a patch-set like Percona Server which directly tracks MySQL, or an internal patch-set that does the same.
In recent years, MySQL and MariaDB have diverged a bit on feature set and focus; each has some interesting features that the other lacks. They are not drop-in replacements for each other.
It seems to be that there's a world of difference between "silently truncate string" and "type coercion" even if I readily agree both are sources of security vulnerabilities.
Thanks for the replies though, even though I clearly annoyed at least 2 people due to the downvotes.
And for sake of comparison -- if I understand correctly, prior to just 2 months ago, sqlite didn't even have the option for strict type enforcement [1]. Do you judge sqlite as harshly, or think its creator is out of touch with the database world?
[1] https://www.sqlite.org/datatype3.html
Sqlite is more of an embedded database, but if it silently truncated character data under some conditions that would make it equally unusable. Better to have no type enforcement than defective type enforcement.
Regarding "silent", MySQL at least emits a warning when this happens. Still not great / obviously wrong in a modern context, but just for clarity's sake it wasn't completely silent.
As for "defective type enforcement", that's a subjective opinion. In C, atoi("123hello456") returns 123 -- once you hit an invalid character, everything from that point is truncated. Is C atoi defective? Perhaps, but it's an opinion.
With strict mode disabled, four-byte characters worked the same way in MySQL utf8mb3: four-byte characters are invalid in this data type, and everything gets truncated at the first invalid character. Would simply stripping the invalid characters, but keeping valid chars after them, necessarily be more "correct"? No, it's an implementation decision, especially considering that emoji did not exist at the time and barely any non-BMP (4-byte) utf8 characters were used anywhere.
atoi is deficient by modern standards, but at least it clearly documents what the behavior is and always has. It doesn't truncate anything. For a database, truncating data is unforgivable. That is what errors are for, so your database doesn't act as a black hole where your data may silently disappear forever.
And yes, if you can't return an error for some reason, you should preserve as much data as possible. Throwing away hundreds or thousands of characters after an initial anomaly is insane. But a database should never do that anyway, it should either store what you give it, or return an error. Same with a filesystem or any other trustworthy means of data storage. Sorry we were too lazy to tell you we threw your data away isn't acceptable, nor is relying on anyone to check for warnings that should be hard errors.
A QA walks into a bar and orders "123hello456" beers. The correct answer is not for the barkeep to give them 123 beers, its to ask "I beg your pardon?" So the truncation in general is the problem. If the operation fails - too many bytes, wrong encoding, string too long - the operation should fail. It's not the db's job, that is the business logic's job.
The world is moving towards strict typing, and since this is what my brain is wired for I'm very happy.
What happens if you upgrade from older version? Are defaults from previous version used, or is it default only on fresh installs?
And it still has not been fixed after all those years. I don't know what use is a character encoding that partially supports Unicode (you just have to try to find out in what way) and is used by nobody else. I don't really care that at some point in the future utf8 is going to become an alias to utf8mb4, a DB capable of this behaviour cannot be trusted, and it allowed me to discover postgres which seems superior in just about every way, so I don't need to go back.
I think the latest recommendation for MySQL was utf8mb4_0900_ai_ci and utf8mb4_general_ci for MariaDB.
While I agree about the Postgres recommendation, it's a non-starter in the PHP world, unless doing something from scratch. Even then, the support for MySQL/MariaDB is much better.
Unicode is developing more and more things that require code points. I'm not sure what the longest legal non-redundant series of code points that can validly represent a glyph somewhere is, but it's getting up there with all the emoji skin modifiers and such.
All UTF-8 codepoints in use today can be encoded with four bytes. Theoretically the Unicode system can be used to create 6 byte code points if that ever becomes necessary, but it won't be for a while. Crossing the 4 byte boundary would also introduce compatibility issues with UTF-16, so I'm sure the Unicode Consortium will do their best to prevent this from happening as long as they can.
AFAIK The flags are a weird multi-code point encoding of the ISO country codes, and each individual code point is less than 4 bytes.
All of these characters are multi byte combinations. The hex for the flag is not a single, super wide character, it's 0xF09F8FB4 0xF3A081A7 0xF3A081A2 0xF3A081B3 0xF3A081A3 0xF3A081B4 0xF3A081BF. You might get some weird results if you take substrings from that, but it won't be a problem for the backing database store; each separate "binary character" is a four byte sequence (as denoted by the 0xF at the front of the number).
MySQL should deprecate utf8 and give a warning if you try to use it.
No, they won't. They will be deprecating something they created that never conformed to the UTF-8 standard. That fact that unicode didn't have codes beyond 21 bits at that point is pretty irrelevant.
It was an invalid implementation from day 1, at least with the name of "utf8".
21 bits is the current Unicode limit. Unfortunately UTF-8 in 3 Bytes only has 16 usable bits - the first byte starts with 1110 and the two continuation bytes start with 10, so it's 4 + 6 + 6 = 16 bits.
> You should also be aware that the utf8mb3 character set is deprecated and you should expect it to be removed in a future MySQL release. Please use utf8mb4 instead.
https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8...
The "utf8" mode in MySQL can only encode 3 bytes of data (up to 16 bit codepoints), which is less than 4 (less than 21 bit codepoints).
"utf8mb4" correctly encodes all UTF-8 codepoints by current standards.
Yes, all good and known, but not really what the misunderstanding is about.
"utf8" follows the definitions of UTF-8 as they stood at the time, adhering to Unicode's former code space limit.
In our app, we finally went for utf8mb4 to allow people to enter emoji. As a side "bonus" you will also soon see some clever people entering their names to stand out, such as "𝙹𝚘𝚑𝚗". Note that this is not "John". It is actually a series of mathematical symbols:
It also avoids registering both 'John' and 'John'. (changed the o)
Hedgewars user registration does this.
It was also popular for site-spoofing by sending links with these cyrillic character to lead people to a fake bank/search_engine page, until that became widely used, and then owners of the tech-stack (browsers, registrars, dns-operators, etc) shut down this attack vector mostly.
In my own service I plan to offer users the ability to define a "stylised" nickname and an alternative text for screen readers, should they be interested in it (like an alt text for images). If Twitter had such a feature, I'd use it because I really like the effects, even if they're cheesy. :)
Edit: Three bytes are enough to fit nearly any of the chars in use in any language, including Chinese and Japanese, so I can only assume someone "smart" in the MySQL dev team decided to "save space" (before emoji were a thing).
You are off by a lot. The maximum code point is about 21bit high (0x10FFFF). The space is only 1.1 million large.
https://en.wikipedia.org/wiki/UTF-8#History
That was restricted I believe primarily for compatibility with more limited encodings like UTF-16.
I guess it's possible that at some future point in human history when UTF-16 has been purged from memory, the 5 character encoding might be allowed again. :)
Characters <128 are encoded with a single byte: 0xxxxxxx
Characters >128 are encoded with multiple bytes.
A two-byte character looks like:
110xxxxx 10xxxxxx (11 useful bits, representing code points 128-2047)
A three-byte character looks like:
1110xxxx 10xxxxxx 10xxxxxx (16 useful bits, representing code points 2048-65535)
A four-byte character looks like:
11110xxx 10xxxxxx 10xxxxxx 10xxxxxx (21 useful bits, representing code points 65536-2097151)
Now, technically this scheme could expand to 6-byte characters without getting confused with things like BOM/etc, however any code points larger than 2^21 wouldn't be representable in UTF-16, which has its own set of constraints. This means the unicode consortium has basically limited themselves to two million or so possible code points, which is why UTF-8 doesn't need to go more than 4 bytes. (I wonder if a future unicode version will require a larger limit and would thus create a new "utf8mb6" scheme, and drop UTF-16 altogether?)
On a thread a couple of years ago (https://news.ycombinator.com/item?id=20600873) it was mentioned that the UTF-8 encoding scheme can be cleanly extended to 36 bits, so even "utf8mb7" would be a possibility.
Obviously nothing in the laws of nature forbids "a future Unicode version" from disavowing this limit, but we could say the same for whether "a future United States of America" could disavow the status of independent Indian Tribes it has previously recognised.
This kind of reasoning is how we end up with vulnerabilities like the recent one in Log4j. Just because a behavior made sense in the past, or an unfortunate bug made it into production, is no excuse to let it inflict damage in perpetuity.
The only thing I can figure is that something somewhere is using a 16-bit quantity for decoded codepoints. Four-byte encodings are for codepoints above FFFF. (Which I guess is still someone's idea of "saving space.")
Edit: Apparently the max encoding length used to be six bytes, so there's literally no plausible explanation for this that doesn't end with "thank god I stopped having to deal with MySQL over a decade ago."
With only 3 bytes you'll completely miss plane 2, the "Supplementary Ideographic Plane" which includes tons of Chinese-Japanese-Korean Han characters.
I wish people would stop saying the supplementary characters are just for "emoji". Asian unification was very controversial and ultimately unsuccessful. Plane 1 and Plane 2 are important, especially if you're going to sell software or products in China or Japan where they are mandated.
This is not a fixed length encoding scheme, it is a subset of UTF-8, which is a variable length encoding designed so that the most common characters only take one byte of storage. As a consequence you do not get 2^24 possible characters in three bytes, you get much less than that (less than 2^16 actually), and the benefit is compatibility and compression.
To represent the full range, UTF-8 requires up to four bytes, even though 21 bits or three bytes would do for a fixed length encoding. UTF-8 is far more efficient in storage and transfer bandwidth than a fixed length encoding would be without further compression, to the point where using a fixed UCS-4/UTF-32 style encoding is an effective way to nearly quadruple the memory requirements of a large class of programs.
Can’t believe that neither MySQL or MariaDB has created a utility to do the conversions for you automatically.
And even if you do have triggers https://github.com/github/gh-ost you can still do it.
https://mathiasbynens.be/notes/mysql-utf8mb4#utf8-to-utf8mb4
And yeah, there's gotchas in there, the main one being that strings that barely fit now won't fit, and also maximum lengths on indexable strings.
But this implies that all indexes will have to be regenerated, if they contain a column that is utf8 and will be converted. As they sort differently, this requires rebuilding the index from scratch. Depending on data size this can take measurable time and effort.
Is the issue still an issue?
https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8...
> The utf8 Character Set (Alias for utf8mb3)
Sure. But for many use cases, SQLite is enough.
And if you suggest "$project that's built on SQLite" to do all that then congrats you just reinvented MySQL or Postgres but with SQLite as the DB engine.
Not as embarrassing as being owned by Oracle.
https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8...
https://www.hellodevops.blog/posts/database-character-sets-a...
And the ensuing discussion:
https://news.ycombinator.com/item?id=29793916
Turns out some of the db tables were using utf8 and latin1; changing them to `utf8mb4` fixed it right up!
https://josh.works/troubleshooting-chinese-character-sets-in...
It was a fun bug to work on. I learned a lot about character encoding, and enjoyed bringing a refined "process" to the table.
They also seem to be slowly phasing it out. Internally it's utf8mb3 and utf8 is just an alias. The idea is to eventually make utf8 an alias to utf8mb4.
Reducing this to 3 bytes is still bad, but only half as bad as before.
Only MySQL 8 introduced innodb typed temporary tables (unlogged, even), so with MySQL 8 this is no longer a problem.
https://jdon.at/JTHj2G
Update - here's a video, it's making hundreds of graphql requests a minute…
https://jdon.at/z05ImC
The only safe choice here is to explicitly use ‘utf8mb3’.