5 ms·

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 ma

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?

chris_wot 5y ago
I can’t imagine there would be too many, other than it is a tedious operation not easily automated. Depending on your data, might take a while. Can’t believe that neither MySQL or MariaDB has created a utility to do the conversions for you automatically.
spookthesunset 5y ago
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.
- sshine 5y ago
  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.
  - spookthesunset 5y ago
    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.
    - cerved 5y ago
      sounds like how I approach laundry
    - sshine 5y ago
      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.
- Spivak 5y ago
  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. And even if you do have triggers https://github.com/github/gh-ost https://github.com/github/gh-ost you can still do it.
kerblang 5y ago
The author of original article links to this one in their post: https://mathiasbynens.be/notes/mysql-utf8mb4#utf8-to-utf8mb4 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.
isotopp 5y ago
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.

chris_wot 5y ago

I can’t imagine there would be too many, other than it is a tedious operation not easily automated. Depending on your data, might take a while. Can’t believe that neither MySQL or MariaDB has created a utility to do the conversions for you automatically.

spookthesunset 5y ago

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.

sshine 5y ago
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.
- spookthesunset 5y ago
  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.
  - cerved 5y ago
    sounds like how I approach laundry
  - sshine 5y ago
    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.
Spivak 5y ago
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. And even if you do have triggers https://github.com/github/gh-ost https://github.com/github/gh-ost you can still do it.

kerblang 5y ago

The author of original article links to this one in their post: https://mathiasbynens.be/notes/mysql-utf8mb4#utf8-to-utf8mb4 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.

isotopp 5y ago

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.