Read all the items in these sections to see whether any of them might affect your 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 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 13.7.2.5, “REPAIR TABLE Syntax”.

Before upgrading to a new version of MySQL, Section 2.11.3, “Checking Whether Tables or Indexes Must Be Rebuilt”, to see whether changes to table formats or to character sets or collations were made between your current version of MySQL and the version to which you are upgrading. If so and these changes result in an incompatibility between MySQL versions, you will need to upgrade the affected tables using the instructions in Section 2.11.4, “Rebuilding or Repairing Tables or Indexes”.

After upgrading to a new version of MySQL, run mysql_upgrade (see Section 4.4.7, “mysql_upgrade — Check and Upgrade MySQL Tables”). This program checks your tables, and attempts to repair them if necessary. It also updates your grant tables to make sure that they have the current structure so that you can take advantage of any new capabilities. (Some releases of MySQL introduce changes to the structure of the grant tables to add new privileges or features.)

mysql_upgrade does not upgrade the contents of the help tables. For upgrade instructions, see Section 5.1.10, “Server-Side Help”.

If you run MySQL Server on Windows, see Section 2.3.8, “Upgrading MySQL on Windows”.

If you use replication, see Section 17.4.3, “Upgrading a Replication Setup”, for information on upgrading your replication setup.

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.

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

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:

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.3.8.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 'mypass';

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 'mypass';
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:

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. 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 Storage Requirements for Date and Time Types.

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.

Due to the temporal type changes described in the previous incompatible change item above, importing pre-MySQL 5.6.4 tables 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:

If table has foreign keys:

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)

Some keywords may be reserved in MySQL 5.6 that were not reserved in MySQL 5.5. See Section 9.3, “Reserved Words”.

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:

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:

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: