Beginning with MySQL 5.6.6, several MySQL Server parameters have defaults that differ from previous releases. See the notes regarding these changes later in this section, particularly regarding overriding them to preserve backward compatibility if that is a concern.
It is good practice to back up your data before installing any new version of software. Although MySQL works very hard to ensure a high level of quality, protect your data by making a backup.
To upgrade to 5.6 from any previous version,
MySQL recommends that you dump your tables with
mysqldump before upgrading and reload the
dump file after upgrading. Use the
--all-databases option to
include all databases in the dump. If your databases include
stored programs, use the
--events options as well.
In general, do the following when 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:
Section 2.11.1, “Upgrading MySQL”, has general update information.
The items in the change lists provided later in this section enable you to identify upgrade issues that apply to your current MySQL installation. Some incompatibilities discussed there require your attention before upgrading. Others should be dealt with after upgrading.
The MySQL 5.6 Release Notes describe significant new features you can use in 5.6 or that differ from those found in earlier MySQL releases. Some of these changes may result in incompatibilities.
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
For dump and reload instructions, see
Section 2.11.4, “Rebuilding or Repairing Tables or Indexes”. Any procedure that
REPAIR TABLE with
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 184.108.40.206, “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 ensure 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.)
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.
If your MySQL installation contains a large amount of data that
might take a long time to convert after an in-place upgrade, you
might find it useful to create a “dummy” database
instance for assessing what conversions might be needed and the
work involved to perform them. Make a copy of your MySQL
instance that contains a full copy of the
mysql database, plus all other databases
without data. Run your upgrade procedure on this dummy instance
to see what actions might be needed so that you can better
evaluate the work involved when performing actual data
conversion on your original database instance.
Read all the items in the following sections to see whether any of them might affect your applications:
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|
|50||Autosized using |
|1800 (on Windows)||0|
|1||8 (platform dependent)|
|300||Autosized using |
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:
With the new default of
operations following an upgrade will move
InnoDB tables that are in
the system tablespace to individual
.ibd files. Using
will prevent this from happening.
permits binary downgrades after upgrading to this
release. With a setting of
InnoDB would use checksumming that older MySQL versions
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
DBAs are advised to convert accounts that use the
mysql_old_password authentication plugin
mysql_native_password instead. For
account upgrade instructions, see
Section 220.127.116.11, “Migrating Away from Pre-4.1 Password Hashing and the mysql_old_password
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
SET old_passwords = 1; CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
As of MySQL 5.6.11, this mismatch no longer occurs. Instead, the server produces an error:
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
Password column in the account's
mysql.user table row to be consistent
with the other column:
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.
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
INSERT. However, the
server did not evaluate
used for inserts or updates against the current
sql_mode. In the example, the
INSERT succeeds and inserts
'0000-00-00' into the
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
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
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
Incompatible change: MySQL
5.6.11 and later supports
TABLE ... [SUB]PARTITION BY
ALGORITHM=, which can be used to create a table whose
n [LINEAR] KEY
KEY partitioning is compatible with a
MySQL 5.1 server (
#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
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
!50531 or replacing it with
!50611, wherever it occurs), or remove
This is not an issue with dump files made using MySQL 5.6.11
or later, where the
ALGORITHM option is
/*!50611 ... */.
Incompatible change: For
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
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,
TABLE upgrades old temporal columns to 5.6 format
ADD INDEX, and
operations. Hence, the following statement upgrades a table
containing columns in the old format:
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
ALTER TABLE does produce
a temporal-format conversion, it generates a message that
can be displayed with
of old format have been upgraded to the new
Due to the temporal type changes described in the previous
incompatible change item above, importing pre-MySQL 5.6.4
ALTER TABLE ...
IMPORT TABLESPACE) that contain
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
Memory storage engine
InnoDB, copying the
.frm file to the data directory of the
destination instance, and using
TABLE to change the table's storage engine type
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:
Copy the table's original
to the data directory on the server where you want to
import the tablespace.
Modify the table's
.frm file to use
Memory storage engine instead of
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
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.
.frm file for
InnoDB” to locate
the line with the other 6 bytes. The line appears as
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
TABLE ... ENGINE=INNODB to add the table
definition to the
dictionary. This creates the
table with the temporal data types in the new format.
operation to complete successfully, the
.frm file must correspond to the
Import the table using
... IMPORT TABLESPACE.
If table has foreign keys:
Recreate the tables with foreign keys using table
TABLE output. The incorrect temporal column
formats do not matter at this point.
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.
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
any table was created with
indexes while the server character set was
repair it using this statement:
Incompatible change: In
MySQL 5.6.20, the patch for Bug #69477 limits the size of
BLOB writes to 10%
of the redo log file size. As a result of this new limit,
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
setting is already 10 times the largest
BLOB data size or your tables
Some keywords may be reserved in MySQL 5.6 that were not reserved in MySQL 5.5. See Section 9.3, “Reserved Words”.
YEAR(2) data type has certain issues
that you should consider before choosing to use it. As of
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
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
DEFAULT to stored procedure or
function parameters or stored program local variables (for
example with a
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
var_name = DEFAULT
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
type differs in nonstandard ways from other data types:
TIMESTAMP columns not
explicitly declared with the
attribute are assigned the
attribute. (Columns of other data types, if not
explicitly declared as
NULL values.) Setting such a
NULL sets it to the current
column in a table, if not declared with the
NULL attribute or an explicit
UPDATE clause, is automatically assigned the
DEFAULT CURRENT_TIMESTAMP and
ON UPDATE CURRENT_TIMESTAMP
following the first one, if not declared with the
NULL attribute or an explicit
DEFAULT clause, are automatically
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
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
system variable at server startup. With this variable
enabled, the server handles
TIMESTAMP as follows instead:
TIMESTAMP columns not
explicitly declared as
NULL values. Setting such a
NULL sets it to
NULL, not the current timestamp.
TIMESTAMP column is
CURRENT_TIMESTAMP attributes automatically.
Those attributes must be explicitly specified.
NOT NULL and without an
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
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
Bring down the slaves, upgrade them, configure them with
the desired value of
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
and that operations on
TIMESTAMP columns coming
from the master use the old
Bring down the master, upgrade it, and configure it with
value used on the slaves, and bring it back up.
Copyright © 1997, 2015, Oracle and/or its affiliates. All rights reserved. Legal Notices