Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 37.8Mb
PDF (A4) - 37.8Mb
PDF (RPM) - 32.9Mb
HTML Download (TGZ) - 8.0Mb
HTML Download (Zip) - 8.0Mb
HTML Download (RPM) - 6.9Mb
Man Pages (TGZ) - 132.8Kb
Man Pages (Zip) - 189.4Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  /  ...  /  Changes Affecting Upgrades to MySQL 8.0

2.11.1.2 Changes Affecting Upgrades to MySQL 8.0

Before upgrading to MySQL 8.0, review the changes described in this section to identify upgrade issues that apply to your MySQL installation and applications.

Changes marked as either Known issue or Incompatible change are incompatibilities with earlier versions of MySQL, and may require your attention before you upgrade. Our aim is to avoid these changes, but occasionally they are necessary to correct problems that would be worse than an incompatibility between releases. If any upgrade issue applicable to your installation involves an incompatibility that requires special handling, follow the instructions given in the description.

Data Dictionary Changes

MySQL Server 8.0 incorporates a global data dictionary containing information about database objects in transactional tables. In previous MySQL series, dictionary data was stored in metadata files and nontransactional system tables. As a result, the upgrade procedure is somewhat different from previous MySQL releases and requires that you verify the upgrade readiness of your installation by checking specific prerequisites. For more information, see Verifying Upgrade Prerequisites for Your MySQL 5.7 Installation. A data dictionary-enabled server entails some general operational differences; see Section 14.7, “Data Dictionary Usage Differences”.

caching_sha2_password as the Preferred Authentication Plugin

The caching_sha2_password and sha256_password authentication plugins provide more secure password encryption than the mysql_native_password plugin, and caching_sha2_password provides better performance than sha256_password. Due to these superior security and performance characteristics of caching_sha2_password, it is as of MySQL 8.0.4 the preferred authentication plugin, and is also the default authentication plugin rather than mysql_native_password. This change affects both the server and the libmysqlclient client library:

  • 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.4 or later. 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:

    ALTER USER user
      IDENTIFIED WITH caching_sha2_password
      BY 'password';
  • The libmysqlclient library treats caching_sha2_password as the default authentication plugin rather than mysql_native_password.

The following sections discuss the implications of the more prominent role of caching_sha2_password:

caching_sha2_password-Compatible Clients and Connectors

If a client or connector is available that has been updated to know about caching_sha2_password, using it is the best way to ensure compatibility when connecting to a MySQL 8.0 server configured with caching_sha2_password as the default authentication plugin.

These clients and connectors have been upgraded to support caching_sha2_password:

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

  • 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.

caching_sha2_password Compatibility Issues and Solutions
Important

If your MySQL installation must serve pre-8.0 clients and you encounter compatibility issues after upgrading to MySQL 8.0.4 or later, the simplest way to address those issues and restore pre-8.0 compatibility is to reconfigure the server to revert to the previous default authentication plugin (mysql_native_password). For example, use these lines in the server option file:

[mysqld]
default_authentication_plugin=mysql_native_password

That setting enables pre-8.0 clients to connect to 8.0 servers until such time as the clients and connectors in use at your installation are upgraded to know about caching_sha2_password. However, the setting should be viewed as temporary, not as a long term or permanent solution, because it causes new accounts created with the setting in effect to forego the improved authentication security provided by caching_sha2_password.

The use of caching_sha2_password offers more secure password hashing than mysql_native_password (and consequent improved client connection authentication). However, it also has compatibility implications that may affect existing MySQL installations:

  • 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.5.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.4 or higher, or obtain an updated connector that recognizes caching_sha2_password.

  • 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 this:

    Authentication plugin 'caching_sha2_password' is not supported

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

  • 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.

The libmysqlclient client library for pre-8.0 MySQL versions is able to connect to MySQL 8.0 servers (except for accounts that authenticate with caching_sha2_password). That means pre-8.0 clients based on libmysqlclient should also be able to connect. Examples:

  • 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.

When an existing MySQL 8.0 installation is upgraded to MySQL 8.0.4 or higher, some older libmysqlclient-based clients may automatically upgrade if they are dynamically linked, because they use the new client library installed by the upgrade. For example, if the DBD::mysql driver for Perl DBI uses dynamic linking, it can use the libmysqlclient in place after an upgrade to MySQL 8.0.4 or higher, with this result:

  • 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.

However, the preceding results occur because libmysqlclient instances from MySQL 8.0 installations prior to 8.0.4 are binary compatible: They both use a shared library major version number of 21. For clients linked to libmysqlclient from MySQL 5.7 or older, they link to a shared library with a different version number that is not binary compatible. In this case, the client must be recompiled against libmysqlclient from 8.0.4 or higher for full compatibility with MySQL 8.0 servers and caching_sha2_password accounts.

MySQL Connector/J 5.1 through 8.0.8 is able to connect to MySQL 8.0 servers, except for accounts that authenticate with caching_sha2_password. (Connector/J 8.0.9 or higher is required to connect to caching_sha2_password accounts.)

Clients that use an implementation of the client/server protocol other than libmysqlclient may need to be upgraded to a newer version that understands the new authentication plugin. For example, in PHP, MySQL connectivity usually is based on mysqlnd, which currently does not know about caching_sha2_password. Until an updated version of mysqlnd is available, the way to enable PHP clients to connect to MySQL 8.0 is to reconfigure the server to revert to mysql_native_password as the default authentication plugin, as previously discussed.

If a client or connector supports an option to explicitly specify a default authentication plugin, use it to name a plugin other than caching_sha2_password. Examples:

  • 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.)

caching_sha2_password and the root Administrative Account

For upgrades to MySQL 8.0, the authentication plugin existing accounts remains unchanged, including the plugin for the 'root'@'localhost' administrative account.

For new MySQL 8.0 installations, when you initialize the data directory (using the instructions at Section 2.10.1.1, “Initializing the Data Directory Manually Using mysqld”), the 'root'@'localhost' account is created, and that account uses caching_sha2_password by default. To connect to the server following data directory initialization, you must therefore use a client or connector that supports caching_sha2_password. If you can do this but prefer that the root account use mysql_native_password after installation, install MySQL and initialize the data directory as you normally would. Then connect to the server as root and use ALTER USER as follows to change the account authentication plugin and password:

ALTER USER 'root'@'localhost'
  IDENTIFIED WITH mysql_native_password
  BY 'password';

If the client or connector that you use does not yet support caching_sha2_password, you can use a modified data directory-initialization procedure that associates the root account with mysql_native_password as soon as the account is created. To do so, use either of these techniques:

caching_sha2_password and Replication

In replication scenarios for which all servers have been upgraded to MySQL 8.0.4 or higher, slave/replica connections to master/primary servers can use accounts that authenticate with caching_sha2_password. For such connections, the same requirement applies as for other clients that use accounts that authenticate with caching_sha2_password: Use a secure connection or RSA-based password exchange.

To connect to a caching_sha2_password account for master/slave replication:

  • For MySQL built using OpenSSL, use any of the following CHANGE MASTER TO options:

    MASTER_SSL = 1
    GET_MASTER_PUBLIC_KEY = 1
    MASTER_PUBLIC_KEY_PATH='path to RSA public key file'
  • For MySQL built using wolfSSL, use MASTER_SSL=1 with CHANGE MASTER TO.

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

To connect to a caching_sha2_password account for Group Replication:

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

    SET GLOBAL group_replication_recovery_use_ssl = ON;
    SET GLOBAL group_replication_recovery_get_public_key = 1;
    SET GLOBAL group_replication_recovery_public_key_path = 'path to RSA public key file';
  • For MySQL built using wolfSSL, set this system variable:

    SET GLOBAL group_replication_recovery_use_ssl = ON;

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

Configuration Changes
  • 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 is the only storage engine providing a native partitioning handler that is supported in MySQL 8.0. (The NDB storage engine also provides native partitioning support, but it is not yet supported in MySQL 8.0.) A partitioned table using any other storage engine must be altered—either to convert it to InnoDB, 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.8.1.4, “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.

    Note

    The procedure given at Verifying Upgrade Prerequisites for Your MySQL 5.7 Installation, describes how to identify partitioned tables that must be altered before upgrading to MySQL 8.0.

    See Section 22.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 setting defined when the server is initialized, and restarting the server with a different setting would introduce inconsistencies with respect to how identifiers are ordered and compared.

Server Changes
  • 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.

  • 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 wish the optimizer to consider SPATIAL indexes on spatial columns that are not SRID-restricted, each such column should be modified:

    • Verify that all values within the column have the same SRID. To determine the SRIDs contained in a geometry column col_name, use the following query:

      SELECT DISTINCT ST_SRID(col_name) FROM tbl_name;

      If the query returns more than one row, the column contains a mix of SRIDs. In that case, modify its contents so all values have the same SRID.

    • Redefine the column to have an explicit SRID attribute.

    • Recreate the SPATIAL index.

  • 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 later.

InnoDB Changes
  • 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:

    Table 2.15 Renamed InnoDB Information Schema Views

    Old Name New Name
    INNODB_SYS_COLUMNS INNODB_COLUMNS
    INNODB_SYS_DATAFILES INNODB_DATAFILES
    INNODB_SYS_FIELDS INNODB_FIELDS
    INNODB_SYS_FOREIGN INNODB_FOREIGN
    INNODB_SYS_FOREIGN_COLS INNODB_FOREIGN_COLS
    INNODB_SYS_INDEXES INNODB_INDEXES
    INNODB_SYS_TABLES INNODB_TABLES
    INNODB_SYS_TABLESPACES INNODB_TABLESPACES
    INNODB_SYS_TABLESTATS INNODB_TABLESTATS
    INNODB_SYS_VIRTUAL INNODB_VIRTUAL

    After upgrading to MySQL 8.0.3 or later, 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:

    SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES \G
SQL Changes
  • 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.


User Comments
Sign Up Login You must be logged in to post a comment.