In many cloud migrations the database is one line in the plan: move to a managed service. That is usually right, and it hides the fact that the move almost always forces a version jump, because older versions are no longer offered there.
That turns one project into two: a change of location and a change of version. The change of location is craft. The version jump brings behaviour changes that produce no error message, just different results.
This article describes the four classes of breakage between MySQL 5.7 and 8.0, how to find them before the move, and what the managed service additionally does differently.
Why the jump is forced
MySQL 5.7 left extended support in October 2023. Managed services offer such versions for a while through a paid extension and then stop.
In practice: moving to a managed service on 5.7 is either impossible or an interim arrangement with a surcharge and an end date. Anybody planning the move plans the version jump with it, and the honest question is only whether both happen together or one after the other.
My recommendation is one after the other, in this order: raise the version first, in your own data centre, then move. The reason is the same as everywhere in this series: if a result is different after the move, there should be only one possible cause.
Where the schedule does not allow that, because the data centre is being vacated on a fixed date, both together works too. Then the section on the way back at the end of this article is required reading.
The four classes of breakage
What breaks between 5.7 and 8.0 falls into four groups. The first two announce themselves, the last two do not.
One: reserved words. MySQL 8.0 added a number of reserved words, and if a column is named that way, every query against it is invalid unless quoted. The most common hits are rank, groups, system, rows and lead. That is the friendliest of the four classes, because it produces a hard error.
Two: the SQL mode. ONLY_FULL_GROUP_BY is on by default from 8.0 and was not in many 5.7 installations. Every query selecting columns that are neither grouped nor aggregated gets rejected. In grown code there are many of those, and they have all run unnoticed for years.
-- Runs in 5.7 with a relaxed mode, rejected in 8.0: name is neither
-- grouped nor aggregated. Which name came back before? An arbitrary
-- one. Which is exactly why the rejection is right.
SELECT customer_id, name, SUM(amount)
FROM orders
GROUP BY customer_id;Three: collations and sorting. The default changes from utf8mb4_general_ci to utf8mb4_0900_ai_ci. That is not a detail: the new collation sorts differently, compares accented characters differently and treats some characters as equal that were distinct before. A query with WHERE name = 'Müller' can return more or fewer rows than before.
Four: order without ORDER BY. The optimizer in 8.0 decides differently in many places, so queries with no explicit sort regularly return a different order. Formally that is not breakage, because no order was ever promised without ORDER BY. In an application that has seen the same order for ten years it is one anyway.
The third and fourth classes are the expensive ones. They produce no error, they produce a different result, and that surfaces in a report rather than in operations.
How to find the breakage in advance
For the first two classes there are tools, for the other two you need a comparison.
Reserved words and schema problems are found by the check tool MySQL ships. It runs against the old database and names what no longer works in 8.0:
# With the 8.0 client tools against the 5.7 server
mysqlcheck --check-upgrade --all-databases -u checker -p
# And the collations in the existing schema, by frequency
mysql -e "SELECT table_collation, COUNT(*) FROM information_schema.tables
WHERE table_schema = 'application' GROUP BY table_collation;"The SQL mode is best checked by turning it on early. That works on the old version, for a single session, and produces exactly the errors that would come later:
SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,
NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO';
-- Then run the test suite. What fails now would fail later in
-- production.That is the single most useful step of the whole preparation, because it clears the second class entirely on the old version.
For collation and ordering no tool helps, a comparison does: the same queries on both versions, results side by side. In practice a selection is enough, and the selection already exists somewhere, namely in the slow query log and in the list of reports.
That is the same idea as a golden master in code: you compare outputs rather than assumptions.
What the managed service additionally does differently
On top of the version jump come properties of the service itself, and three of them regularly surprise.
There is no root access to the machine. Everything that used to run through files on the file system goes away: import through LOAD DATA INFILE with local paths, custom entries in the configuration file, reading log files from the console. That is rarely a problem, but it is regularly a surprise in the middle of a migration.
Parameters live in groups, not in a file. And some cannot be changed. Before the move, a comparison pays off: which deviations from the defaults does your installation have, and are those even possible in the service?
-- Everything that differs from the default, on the old installation
SELECT variable_name, variable_value
FROM performance_schema.global_variables
WHERE variable_name IN ('sql_mode','character_set_server','collation_server',
'innodb_buffer_pool_size','max_allowed_packet','group_concat_max_len',
'transaction_isolation','innodb_flush_log_at_trx_commit');Backup is different and better, once you understand it. Automatic backups and point-in-time recovery are there and do not have to be built. What is missing is the habit of checking them: a restore creates a new instance, and how long that takes for your data volume is something you only know once you have done it. That belongs before the move, not after.
Some of the places that surface here are older than any database version: The real legacy is your database schema.
The move without a long maintenance window
For smaller databases the route is simple: export, import, switch. Up to around twenty gigabytes that fits into a window nobody minds.
Above that, the export becomes the problem, and then replication is the route. The sequence has four steps and has been the same for years.
- Load a dump of the old database, with the position in the binary log. That may take hours, it bothers nobody.
- Switch replication on. The new instance catches up from the noted position and then trails by a few seconds.
- Wait until the lag is zero, and test against the new instance read-only during that time. That is the real gain of the procedure: you can exercise the new version with real data while the old one keeps running.
- Switch. Stop writes, wait for lag zero, change the connection string, stop replication. The window is a matter of minutes.
Two details decide the fourth step. The application has to read the database address from configuration rather than have it hard-wired, or switching needs a release. And there has to be a way to stop writes briefly, or the last operations are lost.
The next forced date is already set, it just has to be written down: An end-of-life calendar for the whole stack.
What is measurably different afterwards
Two things regularly change after the jump, and both belong measured rather than expected.
Some queries get slower. The optimizer in 8.0 is better overall and in individual cases decides worse than before, particularly on queries with many joins. That is not a regression but a different cost estimate, and it usually affects a handful of queries.
So the slow query log belongs switched on in the first week after the move, with a lower threshold than usual. What shows up there and was not there before is the list of queries needing a look.
The bill has a different shape. A managed service charges by instance size, storage and, depending on the setting, by input and output operations. The last item is the surprising one, because it did not exist in your own data centre. An application with many small queries can cost more there than the instance itself.
The way back
Until the switch, the way back is trivial: the old database keeps running, you change the connection string back.
After that it is not, and that is the point to describe in advance. As soon as writes land on the new instance, it holds data the old one does not. Going back then only works with replication in the opposite direction, and that has to be set up beforehand, not at the moment of the decision.
For most projects the more honest answer is a different one: the way back is a time window, not a permanent option. Two hours after the switch, a decision is made about whether it stays. What surfaces in that time are connection errors and obviously wrong results, and the window is enough for those.
What surfaces later, say a collation sorting a report differently, gets fixed forwards. That is not a failure of planning, it is the recognition that going back after two days costs more than the correction.
How an AWS migration works overall is on its own page. The database is the building block there where the effort can be shortened the least, and the only one where a mistake affects the data rather than just operations.
This article belongs to a series about systems that already exist. The retrospective orders every article in it by situation.
