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
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
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.
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, 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.
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
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
to the new
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.
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.
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.
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';
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;
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 yet the default version when you provision a new MySQL instance. It’s still the 5.7 version.
What Is Coming Next?
We’ll unveil the availability of MySQL 8 clusters on Scalingo in the coming weeks. Stay tuned!