One thing I like at my current employer Check24 is the variety in topics and the trust in me to master them. This motivates me to take my work very serious, but also gives me a lot of responsibility since our products represent Germany’s biggest platform for comparing almost everything.
But Check24 is not only great because they delegate responsibility to their employees. We also try to stay up to date and upgrade our software versions if it makes sense. One of these upgrades is the migration from a lower version to MySQL 5.7 which I want to write about in this post.
Requirements
First of all, it is necessary to understand the changes MySQL brings with version 5.7. Before starting to check what other products did, I tried to understand the changes and requirements. Here is a list about what I found (and what is relevant):
- less downtime during schema changes
- improvements regarding to replication
- JSON support (JSON based data fields)
- password lifetime in my.cnf
I will come back in particular to the last point. But first, let’s talk about the requirements affecting a developers everyday life:
- zero date and datetime fields are not allowed anymore (
0000-00-00and0000-00-00 00:00:00) - values in
"group by"have to be aggregated (min, max, etc.) - strings too long for a field result in errors (so far, the got trimmed to the length of the field)
- negative values result in errors for unsigned fields (so far a 0 was inserted)
- division by 0 results in error
- and others
This list is by far not complete, but from my point of view it lists the most important points.
How to Migrate
The major changes are the ban of zero date(time) strings and the way how group by works. If you have any fields containing zero date(time), you need to replace with a valid date(time) string or set to null. This makes sense because there is no 0000-00-00 date and the unix epoch starts at 1970-01-01.
MySQL has allowed queries with a weird group by structure. Unless 5.7, MySQL “magically” knew that your field listed in group by is somehow aggregated. After 5.7, MySQL changed this behaviour and follows now a SQL standard introduced 1992 (not kidding). Saying this, if you want to migrate, you need to make sure that your fields listed in group by are somehow aggregated – or just remove them from group by.
The other points listed above are self-explanatory. You need to trim your strings to the length of your database fields and make sure that you do not insert negative values in unsigned fields (or remove the “unsigned” property).
Thoughts
Let’s talk about the new features and behaviour. I think that the JSON support could be very nice since it moves MySQL a bit towards schemaless databases (NoSQL). And this can extend the area where an open source product like MySQL is used. Further, you can host a relational and non-relational database in one DBMS.
The zero date(time) changes and the group by “fix” are reasonable, but too late. I call it a “fix” because at the end of the day, the null values will do the same work as the zero values. The change only makes sense from a logical point of view, not from a technical point of view. And that is why I think that people will not migrate because it requires a lot of developing and testing effort.
Password Lifetime is the biggest fail with version 5.7. The MySQL config file contains an option default_password_lifetime defining the lifetime of the user’s password. With version 5.7.11, the value to this option changed from 0 to 360 which means, that your passwords are no longer valid after 360 days regardless of the fact that the user connected to the database during the period.
The confusion was so great that this “feature” is reversed in 5.7.30 but will come back in the future. Setting the option to 0 again will deactivate this behaviour.
Conclusion
Our products are successfully migrated to MySQL 5.7. The list above was enough for our case but be aware that there are other breaking changes which can be relevant for you.
The MySQL migration is done but there many other projects waiting for me. I will keep my blog up to date but until then, I am very excited to learn new stuff and contribute to “Deutschlands größtem Vergleichsportal” …