Stumbling Blocks

Naming Issues

Some foreign key constraints from prior versions of MySQL can be incompatible because of excessive length. Specifically, a partially new constraint is that foreign key names cannot exceed 64 characters; in general, the limit was already 64 characters; however, in some cases, InnoDB would generate longer foreign key constraint names, typically as a result of long table names in non-English languages with multibyte characters.

In a similar vein, before MySQL 8.0, views could have column names up to 255 characters; however, to unify column name restrictions, explicit column names should only be 64 characters long. It's unlikely most installations will experience this problem, but if you do, the automated MySQL upgrade check scripts I discuss later should catch the issue.

As has been the case with prior major version upgrades, the number of reserved words has increased. Note that simply because a term is a reserved word does not mean you cannot have a column or table with that name. It simply means that to use that word you have to enclose it in backticks. Query generation tools such as ActiveRecord or SQLAlchemy automatically use backticks when appropriate; however, it may be wisest simply to avoid the use of such reserved words to eliminate the possibilities. Several new reserved words do have names that might be plausibly found as a column or table name (e.g., active and admin); the list of reserved words, which you can find in the MySQL documentation, is worth reviewing.

MySQL used to support ordering in the GROUP BY clause; MySQL 8.0 drops that support, so queries like

SELECT <...> FROM <table> GROUP BY <something> ASC;

will need to be rewritten as:

SELECT <...> FROM <table> GROUP BY <something> ORDER BY <something> ASC;

GRANT Statement Changes

In MySQL 8.0 the GRANT statement has much less functionality than it did before. Previously, it could create users if they didn't already exist, and it could alter user metadata. Now, GRANT statements can only be used as the name implies: to grant privileges to already created users. If you have administration scripts that create users with a GRANT statement, you should rewrite these to explicitly use CREATE USER statements to avoid issues. Likewise, other changes to a user's metadata can be made with the ALTER USER statement.

MySQL 5.7 has both of these statements already, so these changes can be made before the MySQL 8.0 upgrade process.

Authentication Methods Break Older Clients

A very significant change was made to the default authentication method in MySQL 8.0. MySQL has pluggable authentication methods, so you can use different methods for different installations. The default method for MySQL 5.7 is called mysql_native_password . The default in MySQL 8.0, however, is caching_sha2_password .

In some cases, this transition will be seamless. Pre-existing user accounts will not be automatically changed but will be updated to the new default when their passwords are changed. Newly created accounts will use the caching_sha2_password plugin.

Some older applications might not understand how to interact with the caching_sha2_plugin . Unfortunately, such applications might fail when connecting to a server with a default authentication method of caching_sha2_plugin , even when connecting to a not-yet-updated user.

Ideally, you would update such old applications. If this isn't an option, you can set the following option in my.cnf to re-enable the old plugin:

default-authentication-plugin=mysql_native_password

Note that this option still might not fix authentication issues if your users had been created during the period of time when the caching_sha2_plugin was the default; you can manually adjust such users with statements such as

ALTER USER '<username>'@'<somehost>' IDENTIFIED WITH mysql_native_password BY '<a_secure_password>';

On a related note, the PASSWORD() function is no longer available in MySQL 8.0. If you have scripts that create users or change user passwords, they will likely need to be rewritten, as is also the case with the GRANT changes. For example, the code

SET PASSWORD FOR 'tom'@'bob' = PASSWORD('test');

can be rewritten as

ALTER USER 'jeffrey'@'localhost' IDENTIFIED BY '<new_password>'

Likewise, if you're using the PASSWORD function for other purposes, you'll need to rewrite the query. Note that MySQL 8.0 does include cryptographic functionality, such as the SHA2 function, which returns a SHA2 hash of its input. You can likely replace non-MySQL authentication-related uses of the PASSWORD function with that.