Are you planning to upgrade an existing app and DB from Mysql 5 to 8 any time soon?
Well, if you do, maybe you want to read the next paragraphs.
I had to do it, and let me tell you: There are some pitfalls awaiting.
The following is a note of a few things I encountered and how I dealt with it.
Preparation
In order to prepare and verify I first created not only a backup, but also a textual commit
of the current schema (.sql file of schema).
After the update, doing the same again, I am able to (git) diff the SQL schemas and see
if there are any noteworthy differences.
Legacy or not
Even if you don’t have a super legacy system from like 2010, odds are your database schema
has still a few incompatible relics.
Detecting them, creating migrations early on and moving away from those is the best strategy
before actually upgrading. This prevents data loss, both actual and semantic.
One example:
integer is dropping the long deprecated "length", so integer(5) will just be integer moving forward.
But one piece of the code was kind of coupled to it, deciding on the length how many digits the number can have in validation.
So having validation now not output anything anymore (even for numbers larger 99999), can be also
problematic for data integrity.
What I did here: Moving the length to the comment as [schema] length: 5 and making sure validation is using that instead.
One could also hardcode this directly in the app. But based on the framework, this is a bit more agnostic approach
that should work also across different DBs/apps if needed.
Booleans
Now this is the most serious one I encountered:
Once upon a time I moved all int/tinyint columns from signed to unsigned where negativ numbers would never be stored and make no sense.
Part might have been (nano)optimization, but overall the most useful aspect of this is that in the application we don’t have to worry about handling
non-positive numbers coming from DB.
Now, tinyint(1) always was and is special: Since MySQL doesn’t have a native bool type, they are using this more or less officially as their boolean representation.
MariaDB has a more native approach on this and provides even constants as aliases.
And this means: tinyint(1) is literally the only int based type that is valid with a (1) length. It will be kept, whereas tinyint(2) already loses it and internally becomes tinyint(4), displayed only as tinyint.
But either way I now had tons of tinyint(1) unsigned NOT NULL default '0' columns for all the booleans in my tables.
Turns out that Mysql 8 did a rather stupid or inconsistent thing here:
A tinyint(1) MUST now be signed to be valid as boolean representation.
If is it unsigned, instead of switching the sign or allowing signed/unsigned to work (which would both make sense) it will lose the length completely, becoming a non-boolean suddenly.
So this is a huge issue if you forget to change this prior to the migration as afterwards it is not distinguishable from any normal tinyint column.
I did write a small script that essentially migrates this ad-hoc where needed:
if (preg_match('/^tinyint\(1\) unsigned/', $type)) {
$type = 'tinyint(1) NOT NULL DEFAULT \'0\'';
$todo[] = 'ALTER TABLE' . ' ' . $table['table_name'] . ' CHANGE `' . $name . '` `' . $name . '` ' . $type . ';';
}
The full script can also be found here. One can also just copy and paste the needed parts as PHP agnostic version.
In closing I can also say that I wish MySQL 8 would have finally added the same native bool type other DBs already have here.
And/or make the upgrade less breaking in the "unsigned" part.
Date(time)
Some of the fixture or test data was set to 0000-00-00 or 0000-00-00 00:00 for not null date/datetime columns.
This will now also break.
So best to also run update statements to set them to some random (valid) datetime.
Other than that I didn’t run into any larger issues.
Hope it helps for you to avoid some of these traps.
Further resources
Apparently, there is also an upgrade-checker-utility available that can point out some breaking changes.
See also this more detailed resource.