2.11.1.1 Changes Affecting Upgrades to MySQL 5.7

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

Note

In addition to the changes outlined in this section, review the Release Notes and other important information outlined in Before You Begin.

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 incompatibility description. Sometimes this involves dumping and reloading tables, or use of a statement such as CHECK TABLE or REPAIR TABLE.

For dump and reload instructions, see Section 2.11.3, “Rebuilding or Repairing Tables or Indexes”. Any procedure that involves REPAIR TABLE with the USE_FRM option must be done before upgrading. Use of this statement with a version of MySQL different from the one used to create the table (that is, using it after upgrading) may damage the table. See Section 14.7.2.5, “REPAIR TABLE Syntax”.

• Configuration Changes

• System Table Changes

• Server Changes

• InnoDB Changes

• SQL Changes

Configuration Changes

• Incompatible change: As of MySQL 5.7.12, the default --early-plugin-load value is empty. To load the keyring_file plugin, you must use an explicit --early-plugin-load option with a nonempty value.

  In MySQL 5.7.11, the default --early-plugin-load value was the name of the keyring_file plugin library file, so that plugin was loaded by default. InnoDB tablespace encryption requires the keyring_file plugin to be loaded prior to InnoDB initialization, so this change of default --early-plugin-load value introduces an incompatibility for upgrades from 5.7.11 to 5.7.12 or higher. Administrators who have encrypted InnoDB tablespaces must take explicit action to ensure continued loading of the keyring_file plugin: Start the server with an --early-plugin-load option that names the plugin library file. For additional information, see Section 7.5.4.1, “Keyring Plugin Installation”.

• Incompatible change: The INFORMATION_SCHEMA has tables that contain system and status variable information (see Section 23.10, “The INFORMATION_SCHEMA GLOBAL_VARIABLES and SESSION_VARIABLES Tables”, and Section 23.9, “The INFORMATION_SCHEMA GLOBAL_STATUS and SESSION_STATUS Tables”). As of MySQL 5.7.6, the Performance Schema also contains system and status variable tables (see Section 24.11.13, “Performance Schema System Variable Tables”, and Section 24.11.14, “Performance Schema Status Variable Tables”). The Performance Schema tables are intended to replace the INFORMATION_SCHEMA tables, which are deprecated as of MySQL 5.7.6 and will be removed in a future MySQL release.

  For advice on migrating away from the INFORMATION_SCHEMA tables to the Performance Schema tables, see Section 24.19, “Migrating to Performance Schema System and Status Variable Tables”. To assist in the migration, you can use the show_compatibility_56 system variable, which affects how system and status variable information is provided by the INFORMATION_SCHEMA and Performance Schema tables, and also by the SHOW VARIABLES and SHOW STATUS statements. show_compatibility_56 is enabled by default in 5.7.6 and 5.7.7, and disabled by default in MySQL 5.7.8.

  For details about the effects of show_compatibility_56, see Section 6.1.5, “Server System Variables” For better understanding, it is strongly recommended that you read also these sections:

  • Section 24.11.13, “Performance Schema System Variable Tables”

  • Section 24.11.14, “Performance Schema Status Variable Tables”

  • Section 24.11.15.10, “Status Variable Summary Tables”

• Incompatible change: As of MySQL 5.7.6, for Linux systems on which MySQL is installed using RPM packages, server startup and shutdown now is managed using systemd rather than mysqld_safe, and mysqld_safe is no longer installed. This may require some adjustment to the manner in which you specify server options. For details, see Section 2.5.10, “Managing MySQL Server with systemd”.

• Incompatible change: In MySQL 5.7.5, the executable binary version of mysql_install_db is located in the bin installation directory, whereas the Perl version was located in the scripts installation directory. For upgrades from an older version of MySQL, you may find a version in both directories. To avoid confusion, remove the version in the scripts directory. For fresh installations of MySQL 5.7.5 or later, mysql_install_db is only found in the bin directory, and the scripts directory is no longer present. Applications that expect to find mysql_install_db in the scripts directory should be updated to look in the bin directory instead.

  The location of mysql_install_db becomes less material as of MySQL 5.7.6 because as of that version it is deprecated in favor of mysqld --initialize (or mysqld --initialize-insecure). See Section 2.10.1.1, “Initializing the Data Directory Manually Using mysqld”

• Incompatible change: In MySQL 5.7.5, these SQL mode changes were made:

  • Strict SQL mode for transactional storage engines (STRICT_TRANS_TABLES) is now enabled by default.

  • Implementation of the ONLY_FULL_GROUP_BY SQL mode has been made more sophisticated, to no longer reject deterministic queries that previously were rejected. In consequence, ONLY_FULL_GROUP_BY is now enabled by default, to prohibit nondeterministic queries containing expressions not guaranteed to be uniquely determined within a group.

  • The changes to the default SQL mode result in a default sql_mode system variable value with these modes enabled: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ENGINE_SUBSTITUTION.

  • The ONLY_FULL_GROUP_BY mode is also now included in the modes comprised by the ANSI SQL mode.

  If you find that having ONLY_FULL_GROUP_BY enabled causes queries for existing applications to be rejected, either of these actions should restore operation:

  • If it is possible to modify an offending query, do so, either so that nondeterministic nonaggregated columns are functionally dependent on GROUP BY columns, or by referring to nonaggregated columns using ANY_VALUE().

  • If it is not possible to modify an offending query (for example, if it is generated by a third-party application), set the sql_mode system variable at server startup to not enable ONLY_FULL_GROUP_BY.

  For more information about SQL modes and GROUP BY queries, see Section 6.1.8, “Server SQL Modes”, and Section 13.19.3, “MySQL Handling of GROUP BY”.

System Table Changes

• Incompatible change: The Password column of the mysql.user table was removed in MySQL 5.7.6. All credentials are stored in the authentication_string column, including those formerly stored in the Password column. If performing an in-place upgrade to MySQL 5.7.6 or later, run mysql_upgrade as directed by the in-place upgrade procedure to migrate the Password column contents to the authentication_string column.

  If performing a logical upgrade using a mysqldump dump file from a pre-5.7.6 MySQL installation, you must observe these conditions for the mysqldump command used to generate the dump file:

  • You must include the --add-drop-table option

  • You must not include the --flush-privileges option

  As outlined in the logical upgrade procedure, load the pre-5.7.6 dump file into the 5.7.6 (or later) server before running mysql_upgrade.

Server Changes

• Incompatible change: As of MySQL 5.7.5, support for passwords that use the older pre-4.1 password hashing format is removed, which involves the following changes. Applications that use any feature no longer supported must be modified.

  • The mysql_old_password authentication plugin is removed. Accounts that use this plugin are disabled at startup and the server writes an “unknown plugin” message to the error log. For instructions on upgrading accounts that use this plugin, see Section 7.5.1.3, “Migrating Away from Pre-4.1 Password Hashing and the mysql_old_password Plugin”.

  • The --secure-auth option to the server and client programs is the default, but is now a no-op. It is deprecated and will be removed in a future MySQL release.

  • The --skip-secure-auth option to the server and client programs is no longer supported and using it produces an error.

  • The secure_auth system variable permits only a value of 1; a value of 0 is no longer permitted.

  • For the old_passwords system variable, a value of 1 (produce pre-4.1 hashes) is no longer permitted.

  • The OLD_PASSWORD() function is removed.

• Incompatible change: In MySQL 5.6.6, the YEAR(2) data type was deprecated. In MySQL 5.7.5, support for YEAR(2) is removed. Once you upgrade to MySQL 5.7.5 or higher, any remaining YEAR(2) columns must be converted to YEAR(4) to become usable again. For conversion strategies, see Section 12.3.4, “YEAR(2) Limitations and Migrating to YEAR(4)”. Running mysql_upgrade after upgrading is one of the possible conversion strategies.

• As of MySQL 5.7.7, CHECK TABLE ... FOR UPGRADE reports a table as needing a rebuild if it contains old temporal columns in pre-5.6.4 format (TIME, DATETIME, and TIMESTAMP columns without support for fractional seconds precision) and the avoid_temporal_upgrade system variable is disabled. This helps mysql_upgrade to detect and upgrade tables containing old temporal columns. If avoid_temporal_upgrade is enabled, FOR UPGRADE ignores the old temporal columns present in the table; consequently, mysql_upgrade does not upgrade them.

  As of MySQL 5.7.7, REPAIR TABLE upgrades a table if it contains old temporal columns in pre-5.6.4 format and the avoid_temporal_upgrade system variable is disabled. If avoid_temporal_upgrade is enabled, REPAIR TABLE ignores the old temporal columns present in the table and does not upgrade them.

  To check for tables that contain such temporal columns and need a rebuild, disable avoid_temporal_upgrade before executing CHECK TABLE ... FOR UPGRADE.

  To upgrade tables that contain such temporal columns, disable avoid_temporal_upgrade before executing REPAIR TABLE or mysql_upgrade.

• Incompatible change: As of MySQL 5.7.2, the server requires account rows in the mysql.user table to have a nonempty plugin column value and disables accounts with an empty value. This requires that you upgrade your mysql.user table to fill in all plugin values. As of MySQL 5.7.6, use this procedure:

  If you plan to upgrade using the data directory from your existing MySQL installation:

  1. Stop the old (MySQL 5.6) server

  2. Upgrade the MySQL binaries in place by replacing the old binaries with the new ones

  3. Start the MySQL 5.7 server normally (no special options)

  4. Run mysql_upgrade to upgrade the system tables

  5. Restart the MySQL 5.7 server

  If you plan to upgrade by reloading a dump file generated from your existing MySQL installation:

  1. To generate the dump file, run mysqldump with the --add-drop-table option and without the --flush-privileges option

  2. Stop the old (MySQL 5.6) server

  3. Upgrade the MySQL binaries in place (replace the old binaries with the new ones)

  4. Start the MySQL 5.7 server normally (no special options)

  5. Reload the dump file (mysql < dump_file)

  6. Run mysql_upgrade to upgrade the system tables

  7. Restart the MySQL 5.7 server

  Before MySQL 5.7.6, the procedure is more involved:

  If you plan to upgrade using the data directory from your existing MySQL installation:

  1. Stop the old (MySQL 5.6) server

  2. Upgrade the MySQL binaries in place (replace the old binaries with the new ones)

  3. Restart the server with the --skip-grant-tables option to disable privilege checking

  4. Run mysql_upgrade to upgrade the system tables

  5. Restart the server normally (without --skip-grant-tables)

  If you plan to upgrade by reloading a dump file generated from your existing MySQL installation:

  1. To generate the dump file, run mysqldump without the --flush-privileges option

  2. Stop the old (MySQL 5.6) server

  3. Upgrade the MySQL binaries in place (replace the old binaries with the new ones)

  4. Restart the server with the --skip-grant-tables option to disable privilege checking

  5. Reload the dump file (mysql < dump_file)

  6. Run mysql_upgrade to upgrade the system tables

  7. Restart the server normally (without --skip-grant-tables)

  mysql_upgrade runs by default as the MySQL root user. For the preceding procedures, if the root password is expired when you run mysql_upgrade, you will see a message that your password is expired and that mysql_upgrade failed as a result. To correct this, reset the root password to unexpire it and run mysql_upgrade again:

  shell> mysql -u root -p
  Enter password: ****  <- enter root password here
  mysql> ALTER USER USER() IDENTIFIED BY 'root-password'; # MySQL 5.7.6 and up
  mysql> SET PASSWORD = PASSWORD('root-password');        # Before MySQL 5.7.6
  mysql> quit
  
  shell> mysql_upgrade -p
  Enter password: ****  <- enter root password here
  

  The password-resetting statement normally does not work if the server is started with --skip-grant-tables, but the first invocation of mysql_upgrade flushes the privileges, so when you run mysql, the statement is accepted.

  If mysql_upgrade itself expires the root password, you will need to reset it password again in the same manner.

  After following the preceding instructions, DBAs are advised also to convert accounts that use the mysql_old_password authentication plugin to use mysql_native_password instead, because support for mysql_old_password has been removed. For account upgrade instructions, see Section 7.5.1.3, “Migrating Away from Pre-4.1 Password Hashing and the mysql_old_password Plugin”.

• Incompatible change: It is possible for a column DEFAULT value to be valid for the sql_mode value at table-creation time but invalid for the sql_mode value when rows are inserted or updated. Example:

  SET sql_mode = '';
  CREATE TABLE t (d DATE DEFAULT 0);
  SET sql_mode = 'NO_ZERO_DATE,STRICT_ALL_TABLES';
  INSERT INTO t (d) VALUES(DEFAULT);
  

  In this case, 0 should be accepted for the CREATE TABLE but rejected for the INSERT. However, previously the server did not evaluate DEFAULT values used for inserts or updates against the current sql_mode. In the example, the INSERT succeeds and inserts '0000-00-00' into the DATE column.

  As of MySQL 5.7.2, the server applies the proper sql_mode checks to generate a warning or error at insert or update time.

  A resulting incompatibility for replication if you use statement-based logging (binlog_format=STATEMENT) is that if a slave is upgraded, a nonupgraded master will execute the preceding example without error, whereas the INSERT will fail on the slave and replication will stop.

  To deal with this, stop all new statements on the master and wait until the slaves catch up. Then upgrade the slaves followed by the master. Alternatively, if you cannot stop new statements, temporarily change to row-based logging on the master (binlog_format=ROW) and wait until all slaves have processed all binary logs produced up to the point of this change. Then upgrade the slaves followed by the master and change the master back to statement-based logging.

• Incompatible change: Several changes were made to the audit log plugin for better compatibility with Oracle Audit Vault. For upgrading purpose, the main issue is that the default format of the audit log file has changed: Information within <AUDIT_RECORD> elements previously written using attributes now is written using subelements.

  Example of old <AUDIT_RECORD> format:

  <AUDIT_RECORD
   TIMESTAMP="2013-04-15T15:27:27"
   NAME="Query"
   CONNECTION_ID="3"
   STATUS="0"
   SQLTEXT="SELECT 1"
  />
  

  Example of new format:

  <AUDIT_RECORD>
   <TIMESTAMP>2013-04-15T15:27:27 UTC</TIMESTAMP>
   <RECORD_ID>3998_2013-04-15T15:27:27</RECORD_ID>
   <NAME>Query</NAME>
   <CONNECTION_ID>3</CONNECTION_ID>
   <STATUS>0</STATUS>
   <STATUS_CODE>0</STATUS_CODE>
   <USER>root[root] @ localhost [127.0.0.1]</USER>
   <OS_LOGIN></OS_LOGIN>
   <HOST>localhost</HOST>
   <IP>127.0.0.1</IP>
   <COMMAND_CLASS>select</COMMAND_CLASS>
   <SQLTEXT>SELECT 1</SQLTEXT>
  </AUDIT_RECORD>
  

  If you previously used an older version of the audit log plugin, use this procedure to avoid writing new-format log entries to an existing log file that contains old-format entries:

  1. Stop the server.

  2. Rename the current audit log file manually. This file will contain only old-format log entries.

  3. Update the server and restart it. The audit log plugin will create a new log file, which will contain only new-format log entries.

  For information about the audit log plugin, see Section 7.5.5, “MySQL Enterprise Audit”.

InnoDB Changes

• Incompatible change: To simplify InnoDB tablespace discovery during crash recovery, new redo log record types were introduced in MySQL 5.7.5. This enhancement changes the redo log format. Before performing an in-place upgrade, perform a clean shutdown using an innodb_fast_shutdown setting of 0 or 1. A slow shutdown using innodb_fast_shutdown=0 is a recommended step in Performing an In-Place Upgrade.

• Incompatible change: MySQL 5.7.8 and 5.7.9 undo logs may contain insufficient information about spatial columns, which could result in a upgrade failure (Bug #21508582). Before performing an in-place upgrade from MySQL 5.7.8 or 5.7.9 to 5.7.10 or higher, perform a slow shutdown using innodb_fast_shutdown=0 to clear the undo logs. A slow shutdown using innodb_fast_shutdown=0 is a recommended step in Performing an In-Place Upgrade.

• Incompatible change: MySQL 5.7.8 undo logs may contain insufficient information about virtual columns and virtual column indexes, which could result in a upgrade failure (Bug #21869656). Before performing an in-place upgrade from MySQL 5.7.8 to MySQL 5.7.9 or higher, perform a slow shutdown using innodb_fast_shutdown=0 to clear the undo logs. A slow shutdown using innodb_fast_shutdown=0 is a recommended step in Performing an In-Place Upgrade.

• Incompatible change: As of MySQL 5.7.9, the redo log header of the first redo log file (ib_logfile0) includes a format version identifier and a text string that identifies the MySQL version that created the redo log files. This enhancement changes the redo log format, requiring that MySQL be shutdown cleanly using an innodb_fast_shutdown setting of 0 or 1 before performing an in-place upgrade to MySQL 5.7.9 or higher. A slow shutdown using innodb_fast_shutdown=0 is a recommended step in Performing an In-Place Upgrade.

• In MySQL 5.7.9, DYNAMIC replaces COMPACT as the implicit default row format for InnoDB tables. A new configuration option, innodb_default_row_format, specifies the default InnoDB row format. Permitted values include DYNAMIC (the default), COMPACT, and REDUNDANT.

  After upgrading to 5.7.9, any new tables that you create will use the row format defined by innodb_default_row_format unless you explicitly define a row format (ROW_FORMAT).

  For existing tables that do not explicitly define a ROW_FORMAT option or that use ROW_FORMAT=DEFAULT, any operation that rebuilds a table also silently changes the row format of the table to the format defined by innodb_default_row_format. Otherwise, existing tables retain their current row format setting. For more information, see Section 15.11.2, “Specifying the Row Format for a Table”.

SQL Changes

• Incompatible change: The GET_LOCK() function was reimplemented in MySQL 5.7.5 using the metadata locking (MDL) subsystem and its capabilities have been extended:

  • Previously, GET_LOCK() permitted acquisition of only one named lock at a time, and a second GET_LOCK() call released any existing lock. Now GET_LOCK() permits acquisition of more than one simultaneous named lock and does not release existing locks.

    Applications that rely on the behavior of GET_LOCK() releasing any previous lock must be modified for the new behavior.

  • The capability of acquiring multiple locks introduces the possibility of deadlock among clients. The MDL subsystem detects deadlock and returns an ER_USER_LOCK_DEADLOCK error when this occurs.

  • The MDL subsystem imposes a limit of 64 characters on lock names, so this limit now also applies to named locks. Previously, no length limit was enforced.

  • Locks acquired with GET_LOCK() now appear in the Performance Schema metadata_locks table. The OBJECT_TYPE column says USER LEVEL LOCK and the OBJECT_NAME column indicates the lock name.

  • A new function, RELEASE_ALL_LOCKS() permits release of all acquired named locks at once.

  For more information, see Section 13.20, “Miscellaneous Functions”.

• The optimizer now handles derived tables and views in the FROM clause in consistent fashion to better avoid unnecessary materialization and to enable use of pushed-down conditions that produce more efficient execution plans. However, for statements such as DELETE or UPDATE that modify tables, using the merge strategy for a derived table that previously was materialized can result in an ER_UPDATE_TABLE_USED error:

  mysql> DELETE FROM t1
      -> WHERE id IN (SELECT id
      ->              FROM (SELECT t1.id
      ->                    FROM t1 INNER JOIN t2 USING (id)
      ->                    WHERE t2.status = 0) AS t);
  ERROR 1093 (HY000): You can't specify target table 't1'
  for update in FROM clause
  

  The error occurs when merging a derived table into the outer query block results in a statement that both selects from and modifies a table. (Materialization does not cause the problem because, in effect, it converts the derived table to a separate table.) To avoid this error, disable the derived_merge flag of the optimizer_switch system variable before executing the statement:

  SET optimizer_switch = 'derived_merge=off';
  

  The derived_merge flag controls whether the optimizer attempts to merge subqueries and views in the FROM clause into the outer query block, assuming that no other rule prevents merging. By default, the flag is on to enable merging. Setting the flag to off prevents merging and avoids the error just described. For more information, see Section 9.2.2.3, “Optimizing Derived Tables and View References”.

• Some keywords may be reserved in MySQL 5.7 that were not reserved in MySQL 5.6. See Section 10.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 10.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.

• In UNION statements, to apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT:

  (SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
  UNION
  (SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);
  

  Previous versions of MySQL may permit such statements without parentheses. In MySQL 5.7, the requirement for parentheses is enforced.