2.11.1.2 Changes Affecting Upgrades to MySQL 5.6

Before upgrading to MySQL 5.6, 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 13.7.2.5, “REPAIR TABLE Syntax”.

Note

Beginning with MySQL 5.6.6, several MySQL Server parameters have defaults that differ from previous releases. See the notes regarding these changes under Configuration Changes, particularly regarding overriding them to preserve backward compatibility if that is a concern.

• Configuration Changes

• Server Changes

• SQL Changes

Configuration Changes

• Beginning with MySQL 5.6.6, several MySQL Server parameters have defaults that differ from previous releases. The motivation for these changes is to provide better out-of-box performance and to reduce the need for the database administrator to change settings manually. These changes are subject to possible revision in future releases as we gain feedback.

  In some cases, a parameter has a different static default value. In other cases, the server autosizes a parameter at startup using a formula based on other related parameters or server host configuration, rather than using a static value. For example, the setting for back_log now is its previous default of 50, adjusted up by an amount proportional to the value of max_connections. The idea behind autosizing is that when the server has information available to make a decision about a parameter setting likely to be better than a fixed default, it will.

  The following table summarizes changes to defaults. Any of these can be overridden by specifying an explicit value at server startup.

  Parameter	Old Default	New Default
  back_log	50	Autosized using max_connections
  binlog_checksum	NONE	CRC32
  --binlog-row-event-max-size	1024	8192
  flush_time	1800 (on Windows)	0
  innodb_autoextend_increment	8	64
  innodb_buffer_pool_instances	1	8 (platform dependent)
  innodb_checksum_algorithm	INNODB	CRC32 (changed back to INNODB in MySQL 5.6.7)
  innodb_concurrency_tickets	500	5000
  innodb_file_per_table	0	1
  innodb_old_blocks_time	0	1000
  innodb_open_files	300	Autosized using innodb_file_per_table, table_open_cache
  innodb_stats_on_metadata	ON	OFF
  join_buffer_size	128KB	256KB
  max_allowed_packet	1MB	4MB
  max_connect_errors	10	100
  sync_master_info	0	10000
  sync_relay_log	0	10000
  sync_relay_log_info	0	10000

  With regard to compatibility with previous releases, the most important changes are:

  • innodb_file_per_table is enabled (previously disabled).

  • innodb_checksum_algorithm is CRC32 (previously INNODB and changed back to INNODB in MySQL 5.6.7).

  • binlog_checksum is CRC32 (previously NONE).

  Therefore, if you are upgrading an existing MySQL installation, have not already changed the values of these parameters from their previous defaults, and backward compatibility is a concern, you may want to explicitly set these parameters to their previous defaults. For example, put these lines in the server option file:

  [mysqld]
  innodb_file_per_table=0
  innodb_checksum_algorithm=INNODB
  binlog_checksum=NONE

  Those settings preserve compatibility as follows:

  • With the new default of innodb_file_per_table enabled, ALTER TABLE operations following an upgrade will move InnoDB tables that are in the system tablespace to individual .ibd files. Using innodb_file_per_table=0 will prevent this from happening.

  • Setting innodb_checksum_algorithm=INNODB permits binary downgrades after upgrading to this release. With a setting of CRC32, InnoDB would use checksumming that older MySQL versions cannot use.

  • With binlog_checksum=NONE, the server can be used as a replication master without causing failure of older slaves that do not understand binary log checksums.

• As of MySQL 5.6.5, pre-4.1 passwords and the mysql_old_password authentication plugin are deprecated. Passwords stored in the older hash format used before MySQL 4.1 are less secure than passwords that use the native password hashing method and should be avoided. To prevent connections using accounts that have pre-4.1 password hashes, the secure_auth system variable is now enabled by default. (To permit connections for accounts that have such password hashes, start the server with --secure_auth=0.)

  DBAs are advised to convert accounts that use the mysql_old_password authentication plugin to use mysql_native_password instead. For account upgrade instructions, see Section 6.5.1.3, “Migrating Away from Pre-4.1 Password Hashing and the mysql_old_password Plugin”.

  Known issue: In some early development versions of MySQL 5.6 (5.6.6 to 5.6.10), the server could create accounts with a mismatched password hash and authentication plugin. For example, if the default authentication plugin is mysql_native_password, this sequence of statements results in an account with a plugin of mysql_native_password but a pre-4.1 password hash (the format used by mysql_old_password):

  SET old_passwords = 1;
  CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';

  The mismatch produces symptoms such as being unable to connect to the MySQL server and being unable to use SET PASSWORD with OLD_PASSWORD() or with old_passwords=1.

  As of MySQL 5.6.11, this mismatch no longer occurs. Instead, the server produces an error:

  mysql> SET old_passwords = 1;
  mysql> CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';
  ERROR 1827 (HY000): The password hash doesn't have the expected
  format. Check if the correct password algorithm is being used with
  the PASSWORD() function.

  To deal with an account affected by a mismatch, the DBA can modify either the plugin or Password column in the account's mysql.user table row to be consistent with the other column:

  • Set old_passwords to 0, then assign a new password to the account using SET PASSWORD and PASSWORD(). This sets the Password column to have a 4.1 password hash, consistent with the mysql_native_password plugin. This is the preferred method of fixing the account.

  • Alternatively, the DBA can change the plugin to mysql_old_password to make the plugin match the password hash format, then flush the privileges. This is not recommended because the mysql_old_password plugin and pre-4.1 password hashing are deprecated and support for them will be removed in a future version of MySQL.

Server Changes

• 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, 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.6.13, 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: MySQL 5.6.11 and later supports CREATE TABLE ... [SUB]PARTITION BY ALGORITHM=n [LINEAR] KEY (...), which can be used to create a table whose KEY partitioning is compatible with a MySQL 5.1 server (n=1). (Bug #14521864, Bug #66462) This syntax is not accepted by MySQL 5.6.10 and earlier, although it is supported in MySQL 5.5 beginning with MySQL 5.5.31. mysqldump in MySQL 5.5.31 and later MySQL 5.5 releases includes the ALGORITHM option when dumping tables using this option, but surrounds it with conditional comments, like this:

  CREATE TABLE t1 (a INT)
  /*!50100 PARTITION BY KEY */ /*!50531 ALGORITHM = 1 */ /*!50100 ()
        PARTITIONS 3 */

  When importing a dump containing such CREATE TABLE statements into a MySQL 5.6.10 or earlier MySQL 5.6 server, the versioned comment is not ignored, which causes a syntax error. Therefore, prior to importing such a dump file, you must either change the comments so that the MySQL 5.6 server ignores them (by removing the string !50531 or replacing it with !50611, wherever it occurs), or remove them.

  This is not an issue with dump files made using MySQL 5.6.11 or later, where the ALGORITHM option is written using /*!50611 ... */.

• Incompatible change: For TIME, DATETIME, and TIMESTAMP columns, the storage required for tables created before MySQL 5.6.4 differs from storage required for tables created in 5.6.4 and later. This is due to a change in 5.6.4 that permits these temporal types to have a fractional part. This change can affect the output of statements that depend on the row format, such as CHECKSUM TABLE. After upgrading from MySQL 5.5 to MySQL 5.6.4 or later, it is recommended that you also upgrade from MySQL 5.5 to MySQL 5.6 TIME, DATETIME, and TIMESTAMP types. ALTER TABLE currently allows the creation of tables containing temporal columns in both MySQL 5.5 and MySQL 5.6.4 (or later) binary format but this makes it more difficult to recreate tables in cases where .frm files are not available. Additionally, as of MySQL 5.6.4, the aforementioned temporal types are more space efficient. For more information about changes to temporal types in MySQL 5.6.4, see Date and Time Type Storage Requirements.

  As of MySQL 5.6.16, ALTER TABLE upgrades old temporal columns to 5.6 format for ADD COLUMN, CHANGE COLUMN, MODIFY COLUMN, ADD INDEX, and FORCE operations. Hence, the following statement upgrades a table containing columns in the old format:

  ALTER TABLE tbl_name FORCE;

  This conversion cannot be done using the INPLACE algorithm because the table must be rebuilt, so specifying ALGORITHM=INPLACE in these cases results in an error. Specify ALGORITHM=COPY if necessary.

  When ALTER TABLE does produce a temporal-format conversion, it generates a message that can be displayed with SHOW WARNINGS: TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format.

  When upgrading to MySQL 5.6.4 or later, be aware that CHECK TABLE ... FOR UPGRADE does not report temporal columns that use the pre-MySQL 5.6.4 format (Bug #73008, Bug #18985579). In MySQL 5.6.24, two new system variables, avoid_temporal_upgrade and show_old_temporals, were added to provide control over temporal column upgrades (Bug #72997, Bug #18985760).

• Due to the temporal type changes described in the previous incompatible change item above, importing pre-MySQL 5.6.4 tables (using ALTER TABLE ... IMPORT TABLESPACE) that contain DATETIME and TIMESTAMP types into MySQL 5.6.4 (or later) fails. Importing a MySQL 5.5 table with these temporal types into MySQL 5.6.4 (or later) is the mostly likely scenario for this problem to occur.

  The following procedures describe workarounds that use the original pre-MySQL 5.6.4 .frm file to recreate a table with a row structure that is compatible with 5.6.4 (or later). The procedures involve changing the original pre-MySQL 5.6.4 .frm file to use the Memory storage engine instead of InnoDB, copying the .frm file to the data directory of the destination instance, and using ALTER TABLE to change the table's storage engine type back to InnoDB. Use the first procedure if your tables do not have foreign keys. Use the second procedure, which has additional steps, if your table includes foreign keys.

  If the table does not have foreign keys:

  1. Copy the table's original .frm file to the data directory on the server where you want to import the tablespace.

  2. Modify the table's .frm file to use the Memory storage engine instead of the InnoDB storage engine. This modification requires changing 7 bytes in the .frm file that define the table's storage engine type. Using a hexidecimal editing tool:

     • Change the byte at offset position 0003, which is the legacy_db_type, from 0c (for InnoDB) to 06 (for Memory), as shown below:

       00000000  fe 01 09 06 03 00 00 10  01 00 00 30 00 00 10 00

     • The remaining 6 bytes do not have a fixed offset. Search the .frm file for “InnoDB” to locate the line with the other 6 bytes. The line appears as shown below:

       00001010  ff 00 00 00 00 00 00 06  00 49 6e 6e 6f 44 42 00  |.........InnoDB.|

     • Modify the bytes so that the line appears as follows:

       00001010  ff 00 00 00 00 00 00 06 00 4d 45 4d 4f 52 59 00

  3. Run ALTER TABLE ... ENGINE=INNODB to add the table definition to the InnoDB data dictionary. This creates the InnoDB table with the temporal data types in the new format. For the ALTER TABLE operation to complete successfully, the .frm file must correspond to the tablespace.

  4. Import the table using ALTER TABLE ... IMPORT TABLESPACE.

  If table has foreign keys:

  1. Recreate the tables with foreign keys using table definitions from SHOW CREATE TABLE output. The incorrect temporal column formats do not matter at this point.

  2. Dump all foreign key definitions to a text file by selecting the foreign key information from INFORMATION_SCHEMA.TABLE_CONSTRAINTS and INFORMATION_SCHEMA.KEY_COLUMN_USAGE.

  3. Drop all tables and complete the table import process described in steps 1 to 4 in the procedure described above for tables without foreign keys.

  4. After the import operation is complete, add the foreign keys from foreign key definitions that you saved to a text file.

• Incompatible change: As of MySQL 5.6, the full-text stopword file is loaded and searched using latin1 if character_set_server is ucs2, utf16, utf16le, or utf32. If any table was created with FULLTEXT indexes while the server character set was ucs2, utf16, utf16le, or utf32, repair it using this statement:

  REPAIR TABLE tbl_name QUICK;

• Incompatible change: In MySQL 5.6.20, the patch for Bug #69477 limits the size of redo log BLOB writes to 10% of the redo log file size. As a result of this new limit, innodb_log_file_size should be set to a value greater than 10 times the largest BLOB data size found in the rows of your tables. No action is required if your innodb_log_file_size setting is already 10 times the largest BLOB data size or your tables contain no BLOB data.

  In MySQL 5.6.22, the redo log BLOB write limit is relaxed to 10% of the total redo log size (innodb_log_file_size * innodb_log_files_in_group). (Bug #19498877)

SQL Changes

• Some keywords may be reserved in MySQL 5.6 that were not reserved in MySQL 5.5. 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”.

• The YEAR(2) data type has certain issues that you should consider before choosing to use it. As of MySQL 5.6.6, YEAR(2) is deprecated. YEAR(2) columns in existing tables are treated as before, but YEAR(2) in new or altered tables are converted to YEAR(4). For more information, see Section 11.3.4, “YEAR(2) Limitations and Migrating to YEAR(4)”.

• As of MySQL 5.6.6, it is explicitly disallowed to assign the value DEFAULT to stored procedure or function parameters or stored program local variables (for example with a SET var_name = DEFAULT statement). This was not previously supported, or documented as permitted, but is flagged as an incompatible change in case existing code inadvertently used this construct. It remains permissible to assign DEFAULT to system variables, as before, but assigning DEFAULT to parameters or local variables now results in a syntax error.

  After an upgrade to MySQL 5.6.6 or later, existing stored programs that use this construct produce a syntax error when invoked. If a mysqldump file from 5.6.5 or earlier is loaded into 5.6.6 or later, the load operation fails and affected stored program definitions must be changed.

• In MySQL, the TIMESTAMP data type differs in nonstandard ways from other data types:

  • TIMESTAMP columns not explicitly declared with the NULL attribute are assigned the NOT NULL attribute. (Columns of other data types, if not explicitly declared as NOT NULL, permit NULL values.) Setting such a column to NULL sets it to the current timestamp.

  • The first TIMESTAMP column in a table, if not declared with the NULL attribute or an explicit DEFAULT or ON UPDATE clause, is automatically assigned the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attributes.

  • TIMESTAMP columns following the first one, if not declared with the NULL attribute or an explicit DEFAULT clause, are automatically assigned DEFAULT '0000-00-00 00:00:00' (the “zero” timestamp). For inserted rows that specify no explicit value for such a column, the column is assigned '0000-00-00 00:00:00' and no warning occurs.

  Those nonstandard behaviors remain the default for TIMESTAMP but as of MySQL 5.6.6 are deprecated and this warning appears at startup:

  [Warning] TIMESTAMP with implicit DEFAULT value is deprecated.
  Please use --explicit_defaults_for_timestamp server option (see
  documentation for more details).

  As indicated by the warning, to turn off the nonstandard behaviors, enable the new explicit_defaults_for_timestamp system variable at server startup. With this variable enabled, the server handles TIMESTAMP as follows instead:

  • TIMESTAMP columns not explicitly declared as NOT NULL permit NULL values. Setting such a column to NULL sets it to NULL, not the current timestamp.

  • No TIMESTAMP column is assigned the DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP attributes automatically. Those attributes must be explicitly specified.

  • TIMESTAMP columns declared as NOT NULL and without an explicit DEFAULT clause are treated as having no default value. For inserted rows that specify no explicit value for such a column, the result depends on the SQL mode. If strict SQL mode is enabled, an error occurs. If strict SQL mode is not enabled, the column is assigned the implicit default of '0000-00-00 00:00:00' and a warning occurs. This is similar to how MySQL treats other temporal types such as DATETIME.

  To upgrade servers used for replication, upgrade the slaves first, then the master. Replication between the master and its slaves should work provided that all use the same value of explicit_defaults_for_timestamp:

  1. Bring down the slaves, upgrade them, configure them with the desired value of explicit_defaults_for_timestamp, and bring them back up.

     The slaves will recognize from the format of the binary logs received from the master that the master is older (predates the introduction of explicit_defaults_for_timestamp) and that operations on TIMESTAMP columns coming from the master use the old TIMESTAMP behavior.

  2. Bring down the master, upgrade it, and configure it with the same explicit_defaults_for_timestamp value used on the slaves, and bring it back up.