What MySQL 5.7 EOL Means for WordPress Developers

After eight years of general availability, no one can say MySQL 5.7 didn’t have a good run. MySQL 5.7 officially reaches its End of Life (EOL) in October 2023. The good news is that MySQL 8.0 offers enhanced security, potential boosts in performance, new data types, and new features like common table expressions that give WordPress developers an expanded toolbox. In this article, we explore the differences between MySQL 5.7 and 8.0 and discuss how these differences impact how you work with MySQL in WordPress.

What’s New in MySQL 8.0?

Some of the changes in MySQL 8.0 require attention before upgrading. Let’s take a quick look at some of the major changes and the steps you may need to take. MySQL has a more detailed list of possible configuration conflicts you might experience when upgrading.

Data Dictionary: MySQL 8.0 stores database metadata in transactional tables, rather than metadata files and non-transactional tables as in previous versions. If you have tables with conflicting names in your mysql database, you’ll need to rename or drop them before upgrading. Executing the following query in MySQL 5.7 should identify those tables.

SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE LOWER(TABLE_SCHEMA) = 'mysql'
and LOWER(TABLE_NAME) IN
(
'catalogs',
'character_sets',
'check_constraints',
'collations',
'column_statistics',
'column_type_elements',
'columns',
'dd_properties',
'events',
'foreign_key_column_usage',
'foreign_keys',
'index_column_usage',
'index_partitions',
'index_stats',
'indexes',
'parameter_type_elements',
'parameters',
'resource_groups',
'routines',
'schemata',
'st_spatial_reference_systems',
'table_partition_values',
'table_partitions',
'table_stats',
'tables',
'tablespace_files',
'tablespaces',
'triggers',
'view_routine_usage',
'view_table_usage'
);

Obsolete Data Types: MySQL 5.7 supports a number of data types that become obsolete in previous versions, but are not supported in MySQL 8.0. Tables with temporal columns using the pre-5.6.4 format will need to be upgraded using REPAIR_TABLE. You can check for these tables on the command line:

mysqlcheck -u root -p --all-databases --check-upgrade

This will also check to see if you have any orphan .frm files, as well as detecting any triggers with missing or empty definers.

Partitioning: Support for partitioned tables using a storage engine without native partitioning support was deprecated in MySQL 5.7, and completely removed in 8.0. You can identify those tables with this query:

SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE ENGINE NOT IN ('innodb', 'ndbcluster')
AND CREATE_OPTIONS LIKE '%partitioned%';

Generic partitioning support is no longer provided. In MySQL 8.0, the only supported storage engines with native partitioning handlers are InnoDB and NDB. You must alter any partitioned tables using other storage engines before upgrading to MySQL 8,0, or the table will not be available for use. Alteration can consist of converting the table or removing its partitioning.

Reserved Keywords: MySQL 8.0 has keywords that were not previously reserved, which can cause keywords previously used as identifiers to become illegal. See the list of reserved keywords in MySQL 8.0 .
Default Character Set: The default character set and collation have changed in MySQL 8.0. The defaults for character_set_server and character_set_database have changed from latin1 to utf8mb4, and collation_server and collation_database changed from latin1_swedish_ci to utf8mb4_0900_ai_ci.

New databases and objects will use these defaults unless specified otherwise.

MySQL 8.0 enforces checks on permitted characters. Before upgrading, ensure table, file, and index comments do not contain prohibited characters. You can fix this by changing the character set or removing the offending characters.

Improved Security and Authentication

Beginning with version 8.0, MySQL has introduced a more secure default authentication plugin, caching_sha2_password, strengthening security compared to prior versions.

Previously, the default authentication plugin was mysql_native_password, but it is considered less secure by today’s standards. One weakness is that it transmits passwords to the server in plaintext. The new caching_sha2_password plugin addresses this by hashing passwords using the SHA-256 algorithm before transmission. This protects passwords even if the network communication between the client and server is intercepted. The plugin also supports password expiration and updating, adding another layer of security not available with the older plugin.

When upgrading to MySQL 8.0, the default authentication will change to caching_sha2_password for all new user accounts. However, existing accounts created with mysql_native_password in a prior version will still authenticate with that plugin for backwards compatibility.

Updating Existing Accounts

Existing accounts can be updated through the MySQL command line, using either the ALTER DEFAULT PLUGIN or ALTER USER commands. The following command will update a single user to the new default authentication plugin.

ALTER USER 'username'@'host' IDENTIFIED WITH caching_sha2_password BY 'password';

In most cases, it’s preferable to use ALTER DEFAULT PLUGIN, as this will allow you to update multiple users at the same time. The commands below update the server configuration dynamically and do not require restarting the MySQL service.

Update MySQL Authentication on Linux/MacOS

Open the MySQL command line client

mysql -u root -p

Enter the root password when prompted
Run the ALTER DEFAULT PLUGIN statement:

ALTER DEFAULT PLUGIN AUTHORIZATION caching_sha2_password;

Optionally run individual ALTER USER statements

Update MySQL Authentication on Windows

Open the command prompt/terminal
Change to the directory containing the MySQL binaries (often C:\Program Files\MySQL\MySQL Server 8.0\bin)
Run the MySQL client executable:

mysql.exe -u root -p

Enter the root password when prompted
Run the ALTER DEFAULT PLUGIN statement:

ALTER DEFAULT PLUGIN AUTHORIZATION caching_sha2_password;

Optionally run individual ALTER USER statements

Enhanced Performance and Scalability

Query execution efficiency has seen notable improvements in MySQL 8.0, thanks to better optimization in the query parser and improved caching of query plans and results. The test results show significant speed increases, with a query to retrieve column names for all InnoDB tables executing hundreds of times faster on 8.0 compared to MySQL 5.7.

Much of the enhanced performance offered by MySQL 8.0 over previous versions comes down to improvements made to its INFORMATION_SCHEMA.

New Data Types and Features

In addition to performance and security enhancements, MySQL 8.0 introduced several new data types and features that expand its capabilities:

JSON Support: MySQL 8.0 adds support for the JSON_MERGE_PATCH () function, which combines multiple JSON documents and returns the merged result. This behaves in a similar way to JSON_MERGE_PRESERVE, but with two exceptions.
Window Functions – Window functions allow row calculations that consider neighboring rows, similar to analytic functions in other databases. There are currently 11 nonaggregate window functions, including LAG (), which calculates the value of an argument from the row lagging the current row within its partition, and NTILE (), which calculates the bucket number of the current row within its partition. Many existing aggregate functions can also be used as window functions in MySQL 8.0.
Common Table Expressions (CTEs): – Recursive and nonrecursive CTEs are now supported in MySQL. In essence, CTEs allow defining subqueries and treating them as temporary tables or views within the scope of a statement. CTEs are specified using a WITH clause and at least one subclause that provides a subquery, producing a result set and associating a name with the subquery.

Upgrading Your WordPress Site to MySQL 8.0

The exact process used to upgrade MySQL differs depending on your environment. With that said, here’s a a brief overview of the typical upgrade process:

Make a backup of your database and files. Make sure you have a recent backup in case any issues arise.
Check plugin and theme compatibility. Verify third-party extensions are compatible with MySQL 8.0. Updates may be needed.
Test the upgrade. Do a test upgrade on a development/staging site. We recommend Local. Monitor for compatibility or performance issues.
Upgrade MySQL using the instructions provided for your platform, or install it from the source code.

Wrapping Up

There are a number of important considerations before upgrading from MySQL 5.7 to 8.0, but the benefits include better security and increased performance. This is over and above the simple fact that 5.7 is coming up on its EOL in just a few months.

Have you upgraded any sites to 8.0 from 5.7? What issues did you encounter? Let us know in the comments.

This entry was tagged WordPress, MySQL.

WP Migrate