2.11.1.1. Upgrading from MySQL 5.5 to 5.6

Read all the items in these sections to see whether any of them might affect your applications:

Note particularly any changes that are marked Known issue or Incompatible change. These incompatibilities with earlier versions of MySQL 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.7, "Upgrading MySQL on Windows".

If you use replication, see Section 16.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=0innodb_checksum_algorithm=INNODBbinlog_checksum=NONE

Those settings preserve compatibility as follows:

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: 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 ... */.

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 inadvertantly 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 (seedocumentation 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: