Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.4Mb
PDF (A4) - 35.5Mb
PDF (RPM) - 34.5Mb
EPUB - 8.7Mb
HTML Download (TGZ) - 8.5Mb
HTML Download (Zip) - 8.5Mb
HTML Download (RPM) - 7.3Mb
Eclipse Doc Plugin (TGZ) - 9.3Mb
Eclipse Doc Plugin (Zip) - 11.5Mb
Man Pages (TGZ) - 201.4Kb
Man Pages (Zip) - 306.7Kb
Info (Gzip) - 3.3Mb
Info (Zip) - 3.3Mb
Excerpts from this Manual

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

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.4, “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
  • 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:

    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.

  • 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.4, “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.19, “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:

    mysql> 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.1.18.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”.

  • 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
  Posted by Luke Matthews on August 13, 2015
Defaults for performance_schema=off changed to 'on' at 5.6.6.
As result, this leads to higher memory consumption by mysqld (+400Mb RSS used).

http://dev.mysql.com/doc/refman/5.6/en/performance-schema-system-variables.html#sysvar_performance_schema

Also see http://stackoverflow.com/questions/19570181/percona-5-6-high-memory-usage
  Posted by Morgan Tocker on December 3, 2015
Just to clarify on Luke Matthews comment:

This advice applies to the MySQL 5.6 series specifically. In MySQL 5.7 performance_schema was changed to use scalable memory allocation, and does not allocate all resources in advance. See: http://mysqlserverteam.com/new-in-mysql-5-7-performance-schema-scalable-memory-allocation/
Sign Up Login You must be logged in to post a comment.