2.11.4 Changes in MySQL 8.0

For the server, the default value of the default_authentication_plugin system variable changes from mysql_native_password to caching_sha2_password.

This change applies only to new accounts created after installing or upgrading to MySQL 8.0 or higher. For accounts already existing in an upgraded installation, their authentication plugin remains unchanged. Existing users who wish to switch to caching_sha2_password can do so using the ALTER USER statement:

The libmysqlclient library treats caching_sha2_password as the default authentication plugin rather than mysql_native_password.

caching_sha2_password Compatibility Issues and Solutions

caching_sha2_password-Compatible Clients and Connectors

caching_sha2_password and the root Administrative Account

caching_sha2_password and Replication

Clients and connectors that have not been updated to know about caching_sha2_password may have trouble connecting to a MySQL 8.0 server configured with caching_sha2_password as the default authentication plugin, even to use accounts that do not authenticate with caching_sha2_password. This issue occurs because the server specifies the name of its default authentication plugin to clients. If a client or connector is based on a client/server protocol implementation that does not gracefully handle an unrecognized default authentication plugin, it may fail with an error such as one of these:

Authentication plugin 'caching_sha2_password' is not supported

Authentication plugin 'caching_sha2_password' cannot be loaded:
dlopen(/usr/local/mysql/lib/plugin/caching_sha2_password.so, 2):
image not found

Warning: mysqli_connect(): The server requested authentication
method unknown to the client [caching_sha2_password]

For information about writing connectors to gracefully handle requests from the server for unknown default authentication plugins, see Authentication Plugin Connector-Writing Considerations.

Clients that use an account that authenticates with caching_sha2_password must use either a secure connection (made using TCP using TLS/SSL credentials, a Unix socket file, or shared memory), or an unencrypted connection that supports password exchange using an RSA key pair. This security requirement does not apply to mysql_native_passsword, so the switch to caching_sha2_password may require additional configuration (see Section 6.4.1.3, “Caching SHA-2 Pluggable Authentication”). However, client connections in MySQL 8.0 prefer use of TLS/SSL by default, so clients that already conform to that preference may need no additional configuration.

Clients and connectors that have not been updated to know about caching_sha2_password cannot connect to accounts that authenticate with caching_sha2_password because they do not recognize this plugin as valid. (This is a particular instance of how client/server authentication plugin compatibility requirements apply, as discussed at Authentication Plugin Client/Server Compatibility.) To work around this issue, relink clients against libmysqlclient from MySQL 8.0 or higher, or obtain an updated connector that recognizes caching_sha2_password.

Because caching_sha2_password is also now the default authentication plugin in the libmysqlclient client library, authentication requires an extra round trip in the client/server protocol for connections from MySQL 8.0 clients to accounts that use mysql_native_password (the previous default authentication plugin), unless the client program is invoked with a --default-auth=mysql_native_password option.

Standard MySQL clients such as mysql and mysqladmin are libmysqlclient-based.

The DBD::mysql driver for Perl DBI is libmysqlclient-based.

MySQL Connector/Python has a C Extension module that is libmysqlclient-based. To use it, include the use_pure=False option at connect time.

Prior to the upgrade, DBI scripts that use DBD::mysql can connect to a MySQL 8.0 server, except for accounts that authenticate with caching_sha2_password.

After the upgrade, the same scripts become able to use caching_sha2_password accounts as well.

Some MySQL clients support a --default-auth option. (Standard MySQL clients such as mysql and mysqladmin support this option but can successfully connect to 8.0 servers without it. However, other clients may support a similar option. If so, it is worth trying it.)

Programs that use the libmysqlclient C API can call the mysql_options() function with the MYSQL_DEFAULT_AUTH option.

MySQL Connector/Python scripts that use the native Python implementation of the client/server protocol can specify the auth_plugin connection option. (Alternatively, use the Connector/Python C Extension, which is able to connect to MySQL 8.0 servers without the need for auth_plugin.)

The libmysqlclient client library in MySQL 8.0 (8.0.4 or higher). Standard MySQL clients such as mysql and mysqladmin are libmysqlclient-based, so they are compatible as well.

The libmysqlclient client library in MySQL 5.7 (5.7.23 or higher). Standard MySQL clients such as mysql and mysqladmin are libmysqlclient-based, so they are compatible as well.

MySQL Connector/C++ 1.1.11 or higher or 8.0.7 or higher.

MySQL Connector/J 8.0.9 or higher.

MySQL Connector/NET 8.0.10 or higher (through the classic MySQL protocol).

MySQL Connector/Node.js 8.0.9 or higher.

PHP: the X DevAPI PHP extension (mysql_xdevapi) supports caching_sha2_password.

PHP: the PDO_MySQL and ext/mysqli extensions do not support caching_sha2_password. In addition, when used with PHP versions before 7.1.16 and PHP 7.2 before 7.2.4, they fail to connect with default_authentication_plugin=caching_sha2_password even if caching_sha2_password is not used.

Supply a --default-authentication-plugin=mysql_native_password option along with --initialize or --initialize-insecure.

Set default_authentication_plugin to mysql_native_password in an option file, and name that option file using a --defaults-file option along with --initialize or --initialize-insecure. (In this case, if you continue to use that option file for subsequent server startups, new accounts will be created with mysql_native_password rather than caching_sha2_password unless you remove the default_authentication_plugin setting from the option file.)

Use any of the following CHANGE MASTER TO options:

Alternatively, you can use the RSA public key-related options if the required keys are supplied at server startup.

For MySQL built using OpenSSL, set any of the following system variables:

Alternatively, you can use the RSA public key-related options if the required keys are supplied at server startup.

Incompatible change: A MySQL storage engine is now responsible for providing its own partitioning handler, and the MySQL server no longer provides generic partitioning support. InnoDB and NDB are the only storage engines that provide a native partitioning handler that is supported in MySQL 8.0. A partitioned table using any other storage engine must be altered—either to convert it to InnoDB or NDB, or to remove its partitioning—before upgrading the server, else it cannot be used afterwards.

For information about converting MyISAM tables to InnoDB, see Section 15.6.1.3, “Converting Tables from MyISAM to InnoDB”.

A table creation statement that would result in a partitioned table using a storage engine without such support fails with an error (ER_CHECK_NOT_IMPLEMENTED) in MySQL 8.0. If you import databases from a dump file created in MySQL 5.7 (or earlier) using mysqldump into a MySQL 8.0 server, you must make sure that any statements creating partitioned tables do not also specify an unsupported storage engine, either by removing any references to partitioning, or by specifying the storage engine as InnoDB or allowing it to be set as InnoDB by default.

See Section 23.6.2, “Partitioning Limitations Relating to Storage Engines”, for further information.

Incompatible change: Several server error codes are not used and have been removed (for a list, see Features Removed in MySQL 8.0). Applications that test specifically for any of them should be updated.

Important change: The default character set has changed from latin1 to utf8mb4. These system variables are affected:

As a result, the default character set and collation for new objects differ from previously unless an explicit character set and collation are specified. This includes databases and objects within them, such as tables, views, and stored programs. Assuming that the previous defaults were used, one way to preserve them is to start the server with these lines in the my.cnf file:

[mysqld]
character_set_server=latin1
collation_server=latin1_swedish_ci

In a replicated setting, when upgrading from MySQL 5.7 to 8.0, it is advisable to change the default character set back to the character set used in MySQL 5.7 before upgrading. After the upgrade is completed, the default character set can be changed to utf8mb4.

Incompatible change: As of MySQL 8.0.11, it is prohibited to start the server with a lower_case_table_names setting that is different from the setting used when the server was initialized. The restriction is necessary because collations used by various data dictionary table fields are based on the lower_case_table_names setting that was defined when the server was initialized, and restarting the server with a different setting would introduce inconsistencies with respect to how identifiers are ordered and compared.

In MySQL 8.0.11, several deprecated features related to account management have been removed, such as use of the GRANT statement to modify nonprivilege characteristics of user accounts, the NO_AUTO_CREATE_USER SQL mode, the PASSWORD() function, and the old_passwords system variable.

Replication from MySQL 5.7 to 8.0 of statements that refer to these removed features can cause replication failure. Applications that use any of the removed features should be revised to avoid them and use alternatives when possible, as described in Features Removed in MySQL 8.0.

To avoid a startup failure on MySQL 8.0, remove any instance of NO_AUTO_CREATE_USER from sql_mode system variable settings in MySQL option files.

Loading a dump file that includes the NO_AUTO_CREATE_USER SQL mode in stored program definitions into a MySQL 8.0 server causes a failure. As of MySQL 5.7.24 and MySQL 8.0.13, mysqldump removes NO_AUTO_CREATE_USER from stored program definitions. Dump files created with an earlier version of mysqldump must be modified manually to remove instances of NO_AUTO_CREATE_USER.

In MySQL 8.0.11, these deprecated compatibility SQL modes were removed: DB2, MAXDB, MSSQL, MYSQL323, MYSQL40, ORACLE, POSTGRESQL, NO_FIELD_OPTIONS, NO_KEY_OPTIONS, NO_TABLE_OPTIONS. They can no longer be assigned to the sql_mode system variable or used as permitted values for the mysqldump --compatible option.

Removal of MAXDB means that the TIMESTAMP data type for CREATE TABLE or ALTER TABLE is no longer treated as DATETIME.

Replication from MySQL 5.7 to 8.0 of statements that refer to the removed SQL modes can cause replication failure. This includes replication of CREATE statements for stored programs (stored procedures and functions, triggers, and events) that are executed while the current sql_mode value includes any of the removed modes. Applications that use any of the removed modes should be revised to avoid them.

As of MySQL 8.0.3, spatial data types permit an SRID attribute, to explicitly indicate the spatial reference system (SRS) for values stored in the column. See Section 11.5.1, “Spatial Data Types”.

A spatial column with an explicit SRID attribute is SRID-restricted: The column takes only values with that ID, and SPATIAL indexes on the column become subject to use by the optimizer. The optimizer ignores SPATIAL indexes on spatial columns with no SRID attribute. See Section 8.3.3, “SPATIAL Index Optimization”. If you want the optimizer to consider SPATIAL indexes on spatial columns that are not SRID-restricted, each such column should be modified:

Several spatial functions were removed in MySQL 8.0.0 due to a spatial function namespace change that implemented an ST_ prefix for functions that perform an exact operation, or an MBR prefix for functions that perform an operation based on minimum bounding rectangles. The use of removed spatial functions in generated column definitions could cause an upgrade failure. Before upgrading, run mysqlcheck --check-upgrade for removed spatial functions and replace any that you find with their ST_ or MBR named replacements. For a list of removed spatial functions, refer to Features Removed in MySQL 8.0.

The BACKUP_ADMIN privilege is automatically granted to users with the RELOAD privilege when performing an in-place upgrade to MySQL 8.0.3 or higher.

From MySQL 8.0.13, because of differences between row-based or mixed replication mode and statement-based replication mode in the way that temporary tables are handled, there are new restrictions on switching the binary logging format at runtime.

INFORMATION_SCHEMA views based on InnoDB system tables were replaced by internal system views on data dictionary tables. Affected InnoDB INFORMATION_SCHEMA views were renamed:

After upgrading to MySQL 8.0.3 or higher, update any scripts that reference previous InnoDB INFORMATION_SCHEMA view names.

The zlib library version bundled with MySQL was raised from version 1.2.3 to version 1.2.11.

The zlib compressBound() function in zlib 1.2.11 returns a slightly higher estimate of the buffer size required to compress a given length of bytes than it did in zlib version 1.2.3. The compressBound() function is called by InnoDB functions that determine the maximum row size permitted when creating compressed InnoDB tables or inserting rows into compressed InnoDB tables. As a result, CREATE TABLE ... ROW_FORMAT=COMPRESSED or INSERT operations with row sizes very close to the maximum row size that were successful in earlier releases could now fail.

If you have compressed InnoDB tables with large rows, it is recommended that you test compressed table CREATE TABLE statements on a MySQL 8.0 test instance prior to upgrading.

With the introduction of the --innodb-directories feature, the location of file-per-table and general tablespace files created with an absolute path or in a location outside of the data directory should be added to the innodb_directories argument value. Otherwise, InnoDB is not able to locate these files during recovery. To view tablespace file locations, query the INFORMATION_SCHEMA.FILES table:

Undo logs can no longer reside in the system tablespace. In MySQL 8.0, undo logs reside in two undo tablespaces by default. For more information, see Section 15.6.3.4, “Undo Tablespaces”.

When upgrading from MySQL 5.7 to MySQL 8.0, any undo tablespaces that exist in the MySQL 5.7 instance are removed and replaced by two new default undo tablespaces. Default undo tablespaces are created in the location defined by the innodb_undo_directory variable. If the innodb_undo_directory variable is undefined, undo tablespaces are created in the data directory. Upgrade from MySQL 5.7 to MySQL 8.0 requires a slow shutdown which ensures that undo tablespaces in the MySQL 5.7 instance are empty, permitting them to be removed safely.

When upgrading to MySQL 8.0.14 or later from an earlier MySQL 8.0 release, undo tablespaces that exist in the pre-upgrade instance as a result of an innodb_undo_tablespaces setting greater than 2 are treated as user-defined undo tablespaces, which can be deactivated and dropped using ALTER UNDO TABLESPACE and DROP UNDO TABLESPACE syntax, respectively, after upgrading. Upgrade within the MySQL 8.0 release series may not always require a slow shutdown which means that existing undo tablespaces could contain undo logs. Therefore, existing undo tablespaces are not removed by the upgrade process.

Incompatible change: As of MySQL 8.0.17, the CREATE TABLESPACE ... ADD DATAFILE clause does not permit circular directory references. For example, the circular directory reference (/../) in the following statement is not permitted:

An exception to the restriction exists on Linux, where a circular directory reference is permitted if the preceding directory is a symbolic link. For example, the data file path in the example above is permitted if any_directory is a symbolic link. (It is still permitted for data file paths to begin with '../'.)

To avoid upgrade issues, remove any circular directory references from tablespace data file paths before upgrading to MySQL 8.0.17 or higher. To inspect tablespace paths, query the INFORMATION_SCHEMA.INNODB_DATAFILES table.

Due to a regression introduced in MySQL 8.0.14, in-place upgrade on a case sensitive file system from MySQL 5.7 or a MySQL 8.0 release prior to MySQL 8.0.14 to MySQL 8.0.16 failed for instances with partitioned tables and lower_case_table_names=1. The failure was caused by a case mismatch issue related to partitioned table file names. The fix that introduced the regression was reverted, which permits upgrades to MySQL 8.0.17 from MySQL 5.7 or MySQL 8.0 releases prior to MySQL 8.0.14 to function as normal. However, the regression is still present in the MySQL 8.0.14, 8.0.15, and 8.0.16 releases.

In-place upgrade on a case sensitive file system from MySQL 8.0.14, 8.0.15, or 8.0.16 to MySQL 8.0.17 fails with the following error when starting the server after upgrading binaries or packages to MySQL 8.0.17 if partitioned tables are present and lower_case_table_names=1:

Upgrading from server version version_number with 
partitioned tables and lower_case_table_names == 1 on a case sensitive file 
system may cause issues, and is therefore prohibited. To upgrade anyway, restart 
the new server version with the command line option 'upgrade=FORCE'. When 
upgrade is completed, please execute 'RENAME TABLE part_table_name 
TO new_table_name; RENAME TABLE new_table_name 
TO part_table_name;' for each of the partitioned tables. 
Please see the documentation for further information.

If you encounter this error when upgrading to MySQL 8.0.17, perform the following workaround:

Because of the regression still present in the MySQL 8.0.14, 8.0.15, and 8.0.16 releases, importing partitioned tables from MySQL 8.0.14, 8.0.15, or 8.0.16 to MySQL 8.0.17 is not supported on case sensitive file systems where lower_case_table_names=1. Attempting to do so results in a “Tablespace is missing for table” error.

Incompatible change: As of MySQL 8.0.13, the deprecated ASC or DESC qualifiers for GROUP BY clauses have been removed. Queries that previously relied on GROUP BY sorting may produce results that differ from previous MySQL versions. To produce a given sort order, provide an ORDER BY clause.

Queries and stored program definitions from MySQL 8.0.12 or lower that use ASC or DESC qualifiers for GROUP BY clauses should be amended. Otherwise, upgrading to MySQL 8.0.13 or higher may fail, as may replicating to MySQL 8.0.13 or higher slave servers.

Some keywords may be reserved in MySQL 8.0 that were not reserved in MySQL 5.7. See Section 9.3, “Keywords and Reserved Words”. This can cause words previously used as identifiers to become illegal. To fix affected statements, use identifier quoting. See Section 9.2, “Schema Object Names”.

After upgrading, it is recommended that you test optimizer hints specified in application code to ensure that the hints are still required to achieve the desired optimization strategy. Optimizer enhancements can sometimes render certain optimizer hints unnecessary. In some cases, an unnecessary optimizer hint may even be counterproductive.

Incompatible change: In MySQL 5.7, specifying a FOREIGN KEY definition for an InnoDB table without a CONSTRAINT symbol clause, or specifying the CONSTRAINT keyword without a symbol, causes InnoDB to use a generated constraint name. That behavior changed in MySQL 8.0, with InnoDB using the FOREIGN KEY index_name value instead of a generated name. Because constraint names must be unique per schema (database), the change caused errors due to foreign key index names that were not unique per schema. To avoid such errors, the new constraint naming behavior has been reverted in MySQL 8.0.16, and InnoDB once again uses a generated constraint name.

For consistency with InnoDB, NDB releases based on MySQL 8.0.16 or higher use a generated constraint name if the CONSTRAINT symbol clause is not specified, or the CONSTRAINT keyword is specified without a symbol. NDB releases based on MySQL 5.7 and earlier MySQL 8.0 releases used the FOREIGN KEY index_name value.

The changes described above may introduce incompatibilities for applications that depend on the previous foreign key constraint naming behavior.