MySQL 5.7 was released nine years ago, and the last version, which only included minor fixes, came out almost two years ago. The next version is MySQL 8, so it seems like a big leap. Should you be concerned about this? This is especially relevant for projects hosted on Acquia because they are starting to deploy MySQL 8 this September.
So, should it be a concern? Probably not. First, MySQL skipped the 6 and 7 releases. They were never published, just as PHP skipped its 6 version and released PHP 7 after PHP 5. Moving from MySQL 5.7 to 8 is simply moving to the next stable release, with no gaps.
Secondly, the Drupal core and the modules it provides are compatible with MySQL 8 in all versions supported since about four years ago. By compatible versions, I mean Drupal 10 and 11. I will discuss Drupal 7 later on.
Perfect! Then, is that all? Is migrating completely safe? Not really; there are more things to consider than just the core.
The devil is in the details
Although Drupal core is compatible with MySQL 8, a site is likely to be using many contributed modules. It can't be assumed that all available Drupal modules are compatible. There are more than 7,000 modules with a supported stable version, and many more if we take into account development, alpha, and beta releases.
However, most popular modules, if not all, are compatible with any supported Drupal version. Currently, there are no issue reports of modules conflicting with MySQL 8. It is also unlikely that a widely-used module hasn't been used on a recent MySQL server yet. No need to worry here.
What about custom code? Here be dragons, to mimic the old medieval maps with dragon illustrations used to indicate dangerous or unexplored territories. Because custom code is, well, custom, nobody is testing it apart from the project that required and developed it. Any project with custom code is potentially incompatible with MySQL 8.
Luckily, the odds are very low. The main problems are words that MySQL previously allowed to be used for table names and other objects that are now prohibited. One of these words is 'groups'. As you might expect, the 'Groups' module used this name for one of its tables. But even in these cases, core provides automatic fixes, so under standard circumstances, migration is probably fine.
Another possible source of trouble is long-standing projects created a while ago. Those projects may have some weird MySQL configurations and exotic table data that may conflict with MySQL 8. Again, this is not probable, but it is possible.
Ok, a project is probably compatible with MySQL 8, but how can you be 100% sure?
A safety net
That is what you need.
How do you get one? You have to deploy your project in a development environment and check that everything is working ok. This can be done manually, but it is not recommended. At Metadrop, we heavily rely on automatic testing to be sure projects are not going to break unexpectedly. We not only check that user workflows complete without issues; we also test for visual regression, accessibility, or performance. If your site is not covered by tests, you should think about starting to do so. The difference is between stressful deployments and routine deployments.
A development environment can be a local dev box or a fully-featured remote environment. Usually, you will start with a local environment, where a developer can directly review any possible errors. We use Aljibe for this, an extension of DDEV, the de facto official local development environment for Drupal (and other CMSs). It provides automatic testing and, more importantly, it allows you to change the database version very easily, which is exactly what we need for testing our project with MySQL 8.
Later, the project would be uploaded to a CI/CD system using MySQL 8 for the final tests.
Acquia
Let's take the Acquia upgrade this September as an example. They'll automatically deploy MySQL 8 at some point this month in their development environments. After that, customers will have around two weeks to test it, which is enough time to detect any major flaws.
The procedure would be:
- Copy your production environment to a test environment. If possible, perform as little data sanitisation as possible, because some issues with MySQL 8 may only arise with certain data. This can be done automatically in a CI/CD system to prevent developers from accessing real data for privacy reasons.
- Deploy MySQL 8 in that environment.
- Run automatic tests. (I don't like to repeat myself, but you really want to have automatic tests.)
- Run any manual checks, for example, allowing editors to perform their daily tasks.
Once no errors are detected, the site is ready for the upgrade. Just wait until Acquia deploys the new MySQL. The process is fairly quick, and no downtime is expected. The database will be in read-only mode, however, so for a few minutes, no edits can be made. For visitors, this usually goes unnoticed as they are being served with cached pages.
What if the site isn't compatible, and you can't fix all the issues before Acquia upgrades MySQL in the next few days? There's an option: the MySQL 5.7 and MariaDB 10.3 database driver module. It adds a compatibility layer, so even if your Drupal 10 or 11 site has issues with MySQL 8, you can still use it. The module has been developed by Acquia and has many installations, so you can be fairly sure it's a good piece of software.
What about other providers?
With Platform.sh, Pantheon, Amazee.io, or Amazon’s AWS cloud platform—the usual suspects—you would follow a similar procedure.
Platform.sh has offered MySQL for a long time. If you are still using MySQL 5.7, you can follow the same procedure, except you can decide when the upgrade takes place. This is because they provide a lot of flexibility and control over the software versions in use. Take into account that when MySQL 8 is run for the first time, it needs to run a conversion process on the data files from MySQL 5.7 to convert them to the MySQL 8 data format. This can be slow if the database is big enough.
The procedure would be (after you have tested it in your development environment):
- Make a database backup.
- Deploy MySQL 8.
- Check that everything is running smoothly.
Amazee.io and Pantheon are more PaaS-oriented, and they have probably upgraded MySQL a while ago. If something was wrong, you would probably know by now.
With AWS, it can be any option because you can go from a PaaS approach, probably using an Aurora database compatible with MySQL, to completely controlling all the software versions you use. In the first case, assuming you can select when to use MySQL 8 or Aurora compatible, the approach is similar to the previous one. In the latter case, you probably already have a dedicated team to handle the infrastructure, and they surely know how to handle the migration, if it hasn't already been done.
Drupal 7
Drupal 7's end of life was reached this January, nine months ago. Still, there are many projects running on Drupal 7. That is why we are offering migration services from Drupal 7 to Drupal 11.
Drupal 7 core is also compatible with MySQL 8, but modules may not be. Some module maintainers may have decided that the effort was not worth it and did not work on that compatibility. Also, custom modules are more prone to errors, simply because they go through fewer checks and less testing.
With Drupal 7, it is very important to deeply test the site. Run tests, edit content, run any batch process, and stress it as much as you can. Because if you are still using Drupal 7, it is probably because the project is old, big, and complex and requires a lot of resources to migrate. This scenario is more likely to encounter issues with MySQL 8.
TL;DR
Drupal has been compatible with MySQL 8 for a while now. However, your custom code and certain obscure circumstances may cause issues. To be sure, deploy and test your site with real data in a development environment. Real data is important because some issues are related to how data is stored by MySQL. If you use testing data or heavily sanitised data, you may overlook the problem.
It is very unlikely that your site is not compatible, but it is better to invest some time and be sure than to have a nasty surprise in production.
Give me the dirty technical stuff
This article tries to avoid technical details, but if you insist, let me drop some.
MySQL 8 has new and revamped features like the optimiser, the part that decides the most efficient way to execute a query. While it is expected to have better judgment, it is possible that in certain situations, it can lead to slower performance. That is why it is important to check the performance.
Another change is the tx_isolation variable. It has been removed, replaced by transaction_isolation. You can read more details on the Setting the MySQL transaction isolation level page of the official Drupal documentation. In short, if you are using the tx_isolation variable, change to transaction_isolation. It's the same function, just a different name.
Also, as said above, some words are now reserved: groups, grouping, cube, or empty, for example. If any of your queries or objects in the database have these words as names, you have to make sure quotes are used when referencing them. Otherwise, the engine will take them as part of the sentence and will fail. As far as I know, the quoting is done by the core, so if you use the Database API—and this is mostly always the case—you are, again, safe.
Another change is related to character sets and collation. In MySQL, utf8 implementation usually meant utf8mb3, which does not support all Unicode characters. Specifically, emojis are out because they use 4 bytes, but utf8mb3 only uses 3. This should not be a problem, but it is nice to know that with MySQL 8, your site can be full of emojis everywhere, if that is what you want. I won't judge you.
For more stuff like this, you can dive into the MySQL 8 Support issue, where the compatibility with MySQL 8 was discussed.
Upgrading to MySQL 8 should probably be easy and without any issues. The general process and main topics are covered in this article, but if you still have doubts or need help with this upgrade, don't hesitate to contact us!
