MJ Convento

/

The MySQL 8 upgrade: collations, sql_mode, and the CTEs we finally used

2,051 words, about 9 min read

The upgrade itself took a weekend, and 41 minutes of that was downtime. The bug that reached production was one join, on one partner-facing filter page, and it took eleven days to show up. That ratio is the whole story of moving a seven-year-old schema from MySQL 5.7 to 8.0: the part you schedule is easy and the part that hurts is the part where 8.0’s new defaults meet tables that were created under old ones.

The database in question has 214 tables and about 61 GB on disk. The oldest tables were created in 2015 under MySQL 5.5 and have been carried forward by ALTER TABLE ever since. We are on 8.0.30 now. Here is what broke, in the order it broke.

The preflight is the actual work

MySQL 8.0 stores dictionary data in transactional tables instead of metadata files, which is why the upgrade prerequisites page reads like a list of ways your 5.7 installation can be unfit to convert. Orphan .frm files. Triggers with a missing definer. Foreign key constraint names over 64 characters. Views with explicitly named columns over 64 characters, which were legal up to 255 in 5.7. Partitioned tables on an engine without native partitioning.

mysqlcheck -u root -p --all-databases --check-upgrade
mysqlsh -- util check-for-server-upgrade root@localhost:3306 
  --target-version=8.0.30 --config-path=/etc/mysql/my.cnf

We ran both, a week apart. The Shell utility is the one worth your time, and I ran it the first time without --config-path, so it never looked at the option file, which is why the next two problems were a surprise at 11pm on a Saturday rather than a ticket on the Thursday. Between them the two checks reported three things: two views with long column names inherited from a reporting tool, and one column named rank, which became a reserved word in 8.0. The fix for that last one is either identifier quoting everywhere or renaming the column, and we renamed it, because quoting it in twelve places and hoping nobody adds a thirteenth is not a fix.

The good news, and it is genuinely good: since 8.0.16 the server performs the upgrade tasks itself on first start, so there is no mysql_upgrade step to forget. Stop 5.7, install 8.0 packages, start, wait. Ours took 41 minutes, almost all of it in the data dictionary conversion.

Two startup failures, both from my.cnf

It did not start on the first attempt, and it did not start on the second either. Both were the same file.

Our sql_mode line had been in my.cnf since 2016 and still contained NO_AUTO_CREATE_USER, which 8.0 removed along with the rest of the old account management syntax. The changes in MySQL 8.0 page says it plainly: remove any instance of it from sql_mode settings in option files to avoid a startup failure. The second attempt died on query_cache_type, because the query cache is gone in 8.0. Not deprecated. Removed, along with query_cache_size, query_cache_limit, the FLUSH QUERY CACHE and RESET QUERY CACHE statements, the Qcache_* status variables and the SQL_CACHE modifier.

[mysqld]
-sql_mode = "NO_AUTO_CREATE_USER,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
-query_cache_type = 1
-query_cache_size = 268435456
 innodb_buffer_pool_size = 24G

Losing the query cache cost us nothing measurable. Our p95 on the twenty busiest endpoints moved by less than 4 ms, because the heavy reports had been sitting behind a Redis cache since 2020 and the cache was being invalidated by every write to the tables it covered anyway, which is the failure mode the query cache always had.

Deleting the whole sql_mode line, though, is what set up the next two days.

ONLY_FULL_GROUP_BY broke three reports

With no override in the file, the server takes its defaults, and the default SQL mode in 8.0 is ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO and NO_ENGINE_SUBSTITUTION. That list is not new. 5.7 had the same defaults apart from NO_AUTO_CREATE_USER. We had simply turned ONLY_FULL_GROUP_BY off years earlier and forgotten, which meant the codebase had been free to write whatever GROUP BY it liked for six years.

Three reports failed on the Monday. The error is long and it tells you exactly what it wants:

ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP
BY clause and contains nonaggregated column 'app.shipment.partner_id'
which is not functionally dependent on columns in GROUP BY clause;
this is incompatible with sql_mode=only_full_group_by

Two of the three were genuinely wrong. They grouped by a date and selected a partner name alongside it, and the value they printed was whichever row the server happened to pick. Nobody had noticed because in practice each group only ever held one partner. The GROUP BY handling documentation is clear that the choice is nondeterministic and that ORDER BY cannot influence it, which is worth reading out loud to anyone who wants the mode turned back off.

The third one was fine and I fixed it with ANY_VALUE(), which is the documented way to tell MySQL that you know the column is constant within the group. I have mixed feelings. ANY_VALUE() is an assertion about your data that the server cannot check, so it is a comment that happens to compile. Used once, with the reason in the commit message, fine. Used to silence forty of these, it is the mode turned off with extra steps.

The collation mismatch is the one that reached production

This is the interesting failure, and it is entirely about defaults.

In 8.0 the server’s default character set changed from latin1 to utf8mb4, and the default collation from latin1_swedish_ci to utf8mb4_0900_ai_ci. The upgrade does not touch existing objects. It changes what new ones get when nobody says otherwise.

Our old tables are utf8mb4_unicode_ci, because that was the sensible choice in 2016. Our development machines and CI still carried collation-server = utf8mb4_unicode_ci in their config from the 5.7 days. Production, rebuilt from the 8.0 packaged config, did not. So a migration that created a new table without an explicit COLLATE clause produced utf8mb4_unicode_ci on every developer’s machine and in CI, and utf8mb4_0900_ai_ci in production. Every test passed. Then somebody filtered the partner list by reference and got this:

ERROR 1267 (HY000): Illegal mix of collations
(utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT)
for operation '='

The server error reference lists this as ER_CANT_AGGREGATE_2COLLATIONS, and the reason it is an error rather than a silent conversion is in the collation coercibility rules. Column collations both have a coercibility value of 2, and when both sides tie and both are Unicode, MySQL refuses to guess. A literal would have lost to a column and been converted. Two columns cannot resolve it. That is why a mismatch between two tables is a hard failure while the same mismatch against a string in your query is invisible.

The stopgap was a COLLATE clause in the join, which we shipped in an hour and I would not leave in place, because it forces a conversion and kills index use on the joined column. The real fix was ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci on the four new tables, plus deleting the collation lines from the developer configs so that everyone is now wrong in the same direction. And this query now runs in CI against a schema built from migrations:

SELECT TABLE_NAME, TABLE_COLLATION
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_COLLATION <> 'utf8mb4_unicode_ci'
ORDER BY TABLE_NAME;

It fails the build if it returns a row. One pinned collation, checked by a machine, is worth more than any amount of discipline.

caching_sha2_password and an old client we did not own

8.0 makes caching_sha2_password the default authentication plugin instead of mysql_native_password. That only applies to accounts created after the upgrade; existing accounts keep the plugin they had, so our application user connected on the first try.

What did not connect was a client’s own reporting script on an old PHP box, which failed with the warning the MySQL manual quotes directly:

Warning: mysqli_connect(): The server requested authentication
method unknown to the client [caching_sha2_password]

The trap is in the middle of that sentence. The server announces its default plugin during the handshake, so a client that cannot parse an unknown plugin name fails even for an account that does not use it. The manual offers default_authentication_plugin=mysql_native_password as the quick way out and says in the same paragraph that it should be temporary. The PHP manual puts the fix on the other side: the affected releases are before 7.1.16 and 7.2 before 7.2.4, and caching_sha2_password is fully supported as of PHP 7.4.4. We left the server alone and the client upgraded their PHP. Reverting the server default would have meant every account we create for the next five years quietly gets the weaker plugin.

What we actually gained: CTEs and window functions

The payoff was not performance. It was being able to delete SQL.

The worst report we own ranks partners by volume per month and shows each partner’s share of the month’s total. On 5.7 that was a derived table, the same derived table repeated a second time for the totals, and a correlated subquery for the rank. 61 lines. With common table expressions and window functions it is this:

WITH monthly AS (
    SELECT
        DATE_FORMAT(s.booked_at, '%Y-%m-01') AS month_start,
        s.partner_id,
        COUNT(*) AS shipments
    FROM shipment s
    WHERE s.booked_at >= '2022-01-01'
      AND s.status <> 'cancelled'
    GROUP BY month_start, s.partner_id
)
SELECT
    m.month_start,
    p.name,
    m.shipments,
    SUM(m.shipments) OVER (PARTITION BY m.month_start) AS month_total,
    ROUND(100 * m.shipments
        / SUM(m.shipments) OVER (PARTITION BY m.month_start), 1) AS pct,
    RANK() OVER (PARTITION BY m.month_start ORDER BY m.shipments DESC) AS rnk
FROM monthly m
JOIN partner p ON p.id = m.partner_id
ORDER BY m.month_start, rnk;

23 lines, one pass over the aggregate, and the grouping logic appears once. On our data, 1.9 million shipment rows in the window, it went from 2.9 seconds to 480 ms, measured with the query cache off on both sides so the comparison means something. The OVER clause is permitted after most aggregate functions, so SUM() is doing double duty here as an aggregate inside the CTE and a window function outside it. WITH ROLLUP and GROUPING() cover the super-aggregate case, which we have not needed yet.

What is left is the part I keep deferring. Fourteen tables are still utf8mb3, the three-byte character set that 8.0 deprecates and that SHOW CREATE TABLE now spells out in full instead of calling utf8. Two of them are the largest tables in the schema, converting them rewrites every row, and the honest estimate is a maintenance window longer than the upgrade itself. So they sit there, in a CI report nobody has scheduled time for, which is exactly how they will still be there next August.

Sources