servd

Documentation

Documentation > Cookbook >

Upgrading a Project From MariaDB to MySQL 8

Craft 5 no longer officially supports the MariaDB database which has been our default since Servd first launched. In order to ensure both new and existing projects can run Craft 5 optimally, we've switched out default database to MySQL 8.0 using the Percona XtraDB distribution.

Existing projects can upgrade from MariaDB to MySQL 8.0 using the upgrade button in our dashboard on the Project Settings > Build & Deploy page.

Important Changes in MySQL 8.0 #

  • MySQL 8.0 removes the built in 'query cache'. The performance benefits of this feature have always been questionable however Servd has had it enabled by default with a conservative configuration. Its removal in MySQL 8 could therefore lead to changes in performance for some workloads (both positive and negative), however we expect an overall positive performance improvement due to...
  • MySQL 8.0 contains a new query optimiser which should improve the speed of queries that include complex or large table joins. Craft itself is also optimised against this query optimiser implementation.
  • MySQL 8.0 uses a new, more secure authentication plugin called caching_sha2_password. Any tools used to connect directly to the database will need to support this. All Servd components are already compatible, however you might need to build a new Bundle in order to get the latest PHP base software which allows Craft's Control Panel database dump utility to work.
  • MySQL 8.0 supports a lower maximum number of columns in a single table compared to MariaDB. This is due to a long standing bug in MySQL/InnoDB which incorrectly calculates the byte-row length of a set of columns (https://bugs.mysql.com/bug.php...). MySQL 8 therefore only supports a maximum of 196 TEXT/BLOB/VARCHAR columns in a single table, whereas MariaDB supports more. As a result, our database migration process checks for any tables in an existing project's database with greater than 190 columns and aborts if any exist.

The Upgrade Process #

MariaDB and MySQL are not drop-in compatible so a straight switch from one to the other is not possible. The upgrade process therefore uses the following process:

  1. Backups of any non-production environments are taken
  2. [Optional] The project is switched to an offline mode, responding to all traffic with a 503 error and maintenance page
  3. A backup of the production environment is taken
  4. The MariaDB database is shut down and filesystem scrubbed
  5. The MySQL 8.0 database is started and initialised
  6. The production environment backup is imported
  7. [Optional] The project is taken out of offline mode
  8. Non-production environment backups are imported

Offline Mode #

When triggering the upgrade you have the option to enable Offline Mode for the production environment's data migration. Offline Mode returns a 503 error for all incoming requests along with a generic, non-branded 'maintenance' page.

This can be useful for three reasons:

  • It prevents users from making changes to data during and after the production backup being taken which would then be lost
  • It prevents Craft error pages from being shown during the production import phase
  • It prevents any upstream caches from caching responses generated half way through the process which might include errors or incorrect data

We recommend enabling Offline Mode for any transactional and commerce projects, or very large databases. However, if your project is using static caching and has a modestly sized database, you can leave Offline Mode disabled and end users are unlikely to notice anything during the process as the static cache will remain functional throughout.