Now Available on Scalingo - MySQL 8

We just released the latest major version of the famous database MySQL. As usual, upgrading is an easy one-click process.

What Is New in MySQL 8.0?

MySQL 8.0 comes with a great number of new features. We will cover the most important of them in this blog article. You can find a comprehensive list on this official blog post.

The Developer Side

From the developer perspective, various improvements came along with this major release.

JSON and GIS Improved Support

MySQL improved the support for both the JSON-formatted fields and the geospatial data.

On the JSON side, the MySQL team added new functions and improved the performance of the existing ones. For instance, you can now use the JSON_TABLE in order to create a relational view of your JSON data and make use of the SQL power to query results out of your JSON data. When it comes to manipulating the data, you might want to leverage the new partial update functions such as JSON_REMOVE and JSON_SET. These functions let you modify a part of a stored JSON document.

MySQL 8 also comes with a much better geography support. It can understand geographic coordinates and compute, for instance, distance between points.

Descending Indexes

Before MySQL 8, creating a descending index led to a performance drop compared to an ascending index. Starting with MySQL 8, when a user creates a descending index, the MySQL engine scans it in reverse order, removing the earlier performance hit.

Don't Wait for the Lock Release

Usually, when a row is locked (e.g. because of an UPDATE query), any other transaction will need to wait for the completion of the running query in order to access the locked row. MySQL 8 introduced two new keywords in case you don't want to wait if one of your transactions wants to access a locked row: NOWAIT and SKIP LOCKED.

Using NOWAIT, your query will never wait for the release of the lock but will, instead, fail with an error:

mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE NOWAIT;
ERROR 3572 (HY000): Do not wait for lock.

Using SKIP LOCKED, the behaviour is slightly different. Your query will never wait for the release of the lock and will exclude the locked rows from the result set.

Security

New Authentication Mechanism

The most important change which may impact your application is the new default authentication mechanism. MySQL 8 chose to move from the legacy mysql_native_password to the new caching_sha2_password. This new authentication mechanism provides better security and better performance. The drawback is that it requires a compatible client.

On Scalingo, upgrading to MySQL 8 will automatically leverage this new authentication mechanism. As such, you should make sure that the library used by your application is compatible with it.

Brute Force Protection

MySQL 8 introduced a new simple mechanism to seriously slow down brute force attack on your database. There is now a delay between unsuccessful login attempts.

Performance

Upgraded Cost Model

When you execute a query, MySQL builds various query plans. A query plan correspond to the set of steps used to access the requested data. MySQL then compute the cost of the query plans to select the best one. Starting with MySQL 8, the cost is not the same if data already are in memory or if data must be read from the disk. By including this into the cost model, the query optimizer will select a plan with a more optimal access depending on the location of the data!

You can find more information in this blog post.

Benchmarks

The MySQL team announced performance improvements for all kind of workloads. This assertion is supported by benchmarks done by other entities.

For instance, Severalnines benchmark highlight that MySQL 8 outperforms 5.7: shorter transaction processing time, bigger amount of transaction processed during the same time interval, less CPU resource used.

Cluster Management on Steroids

MySQL 8 also greatly improved the support for Group Replication and introduced InnoDB Cluster. Let's leave that aside for a future blog post!

MySQL 8 Prerequisites

MySQL 8 is a great improvement and is a big milestone in MySQL life. But it comes with a few mandatory prerequisites to be able to take advantage of it. These prerequisites are thoroughly explained in our documentation. If your database does not fulfil one of this prerequisite, a helpful error message will be displayed on the web dashboard after trying to upgrade:

This section sums up these prerequisites.

Storage Engine

All tables and databases must use the InnoDB storage engine. You can check which tables use a different storage engine with the following SQL command:

SELECT table_schema, table_name FROM information_schema.tables \
  WHERE table_schema IN ('my-app-3030') AND engine != 'InnoDB';

Primary Key

All tables must have a primary key configured. You can check which tables use a different storage engine with the SQL command:

SELECT information_schema.tables.table_schema, information_schema.tables.table_name \
  FROM information_schema.tables LEFT JOIN information_schema.key_column_usage AS c ON (\
    information_schema.tables.table_name = c.table_name AND \
    c.constraint_schema = information_schema.tables.table_schema AND \
    c.constraint_name = 'PRIMARY' \
  ) \
  WHERE information_schema.tables.table_schema IN ('my-app-3030') AND \
  c.constraint_name IS NULL;

Upgrade Process

As usual, the upgrading process on Scalingo is as simple as clicking a button on the database dashboard. The following picture shows the message you will see on your database dashboard if you are running the latest 5.7 version (i.e. MySQL 5.7.29 at the time of writing):

This upgrade can take some time for the biggest databases and induces a short downtime.

Version 8.0.21 has been released. You can find the corresponding Docker images on our Docker hub repository.

You are strongly advised to test that your code still works with the new database version. To ease this task, you can pull the MySQL version you need from our public Docker hub repository and test it locally, with your code, on your workstation before updating. You can also get the same container that we have built for your app and which is running on our infrastructure using our Docker Image Addon. You can also follow this great blog post by Frédéric Descamp to migrate an application to MySQL 8 without breaking your application.

Because of the prerequisites mentionned above, MySQL 8 is not the default version when you provision a new MySQL instance. It's still the 5.7 version.

Photo by Joel Filip on Unsplash