Before upgrading to MySQL 5.5, review the changes described in this section to identify upgrade issues that apply to your current MySQL installation and 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
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 126.96.36.199, “REPAIR TABLE Syntax”.
Incompatible change: The
InnoDB Pluginis included in MySQL 5.5 releases. It becomes the built-in version of
InnoDBin MySQL Server, replacing the version previously included as the built-in
InnoDB Pluginis also available in MySQL 5.1 as of 5.1.38, but it is an optional storage engine that must be enabled explicitly using two server options:
[mysqld] ignore-builtin-innodb plugin-load=innodb=ha_innodb_plugin.so
If you were using
InnoDB Pluginin MySQL 5.1 by means of those options, you must remove them after an upgrade to 5.5 or the server will fail to start.
In addition, in
InnoDB Plugin, the
innodb_file_io_threadssystem variable has been removed and replaced with
innodb_write_io_threads. If you upgrade from MySQL 5.1 to MySQL 5.5 and previously explicitly set
innodb_file_io_threadsat server startup, you must change your configuration. Either remove any reference to
innodb_file_io_threadsor replace it with references to
Incompatible change: In MySQL 5.5, the server includes a plugin services interface that complements the plugin API. The services interface enables server functionality to be exposed as a “service” that plugins can access through a function-call interface. The
libmysqlserviceslibrary provides access to the available services and dynamic plugins now must be linked against this library (use the
-lmysqlservicesflag). For an example showing how to configure for CMake, see Section 24.3, “MySQL Services for Plugins”.
Known issue: As of MySQL 5.5.32, for new installations, the
urlcolumns in the
mysqldatabase help tables are now created as type
TEXTto accommodate longer URLs. For upgrades, mysql_upgrade does not update the columns. Modify them manually using these statements:
ALTER TABLE mysql.help_category MODIFY url TEXT NOT NULL; ALTER TABLE mysql.help_topic MODIFY url TEXT NOT NULL;
Incompatible change: As of MySQL 5.5.3, due to work done for Bug #989,
FLUSH TABLESis not permitted when there is an active
LOCK TABLES ... READ. To provide a workaround for this restriction,
FLUSH TABLEShas a new variant,
FLUSH TABLES, that enables tables to be flushed and locked in a single operation. As a result of this change, applications that previously used this statement sequence to lock and flush tables will fail:
tbl_listWITH READ LOCK
LOCK TABLES tbl_list READ; FLUSH TABLES tbl_list;
Such applications should now use this statement instead:
FLUSH TABLES tbl_list WITH READ LOCK;
Incompatible change: As of MySQL 5.5.7, the server requires that a new grant table,
proxies_priv, be present in the
mysqldatabase. If you are upgrading to 5.5.7 from a previous MySQL release rather than performing a new installation, the server will find that this table is missing and exit during startup with the following message:
Table 'mysql.proxies_priv' doesn't exist
mysqld --skip-grant-tables & mysql_upgrade
Then stop the server and restart it normally.
You can specify other options on the mysqld command line if necessary. Alternatively, if your installation is configured so that the server normally reads options from an option file, use the
--defaults-fileoption to specify the file (enter each command on a single line):
mysqld --defaults-file=/usr/local/mysql/etc/my.cnf --skip-grant-tables & mysql_upgrade
--skip-grant-tablesoption, the server does no password or privilege checking, so any client can connect and effectively have all privileges. For additional security, use the
--skip-networkingoption as well to prevent remote clients from connecting.Note
This problem is fixed in MySQL 5.5.8; the server treats a missing
proxies_privtable as equivalent to an empty table. However, after starting the server, you should still run mysql_upgrade to create the table.
Incompatible change: As of MySQL 5.5.7,
InnoDBalways uses the fast truncation technique, equivalent to
CREATE TABLE. It no longer performs a row-by-row delete for tables with parent-child foreign key relationships.
TRUNCATE TABLEreturns an error for such tables. Modify your SQL to issue
DELETE FROMfor such tables instead.
Incompatible change: Prior to MySQL 5.5.7, if you flushed the logs using
FLUSH LOGSor mysqladmin flush-logs and mysqld was writing the error log to a file (for example, if it was started with the
--log-erroroption), it renames the current log file with the suffix
-old, then created a new empty log file. This had the problem that a second log-flushing operation thus caused the original error log file to be lost unless you saved it under a different name. For example, you could use the following commands to save the file:
mysqladmin flush-logs mv host_name.err-old backup-directory
To avoid the preceding file-loss problem, no renaming occurs as of MySQL 5.5.7; the server merely closes and reopens the log file. To rename the file, you can do so manually before flushing. Then flushing the logs reopens a new file with the original file name. For example, you can rename the file and create a new one using the following commands:
mv host_name.err host_name.err-old mysqladmin flush-logs mv host_name.err-old backup-directory
Incompatible change: As of MySQL 5.5.6, handling of
CREATE TABLE IF NOT EXISTS ... SELECTstatements has been changed for the case that the destination table already exists:
CREATE TABLE IF NOT EXISTS ... SELECT, MySQL produced a warning that the table exists, but inserted the rows and wrote the statement to the binary log anyway. By contrast,
CREATE TABLE ... SELECT(without
IF NOT EXISTS) failed with an error, but MySQL inserted no rows and did not write the statement to the binary log.
MySQL now handles both statements the same way when the destination table exists, in that neither statement inserts rows or is written to the binary log. The difference between them is that MySQL produces a warning when
IF NOT EXISTSis present and an error when it is not.
This change in handling of
IF NOT EXISTSresults in an incompatibility for statement-based replication from a MySQL 5.1 master with the original behavior and a MySQL 5.5 slave with the new behavior. Suppose that
CREATE TABLE IF NOT EXISTS ... SELECTis executed on the master and the destination table exists. The result is that rows are inserted on the master but not on the slave. (Row-based replication does not have this problem.)
To address this issue, statement-based binary logging for
CREATE TABLE IF NOT EXISTS ... SELECTis changed in MySQL 5.1 as of 5.1.51:
If the destination table does not exist, there is no change: The statement is logged as is.
If the destination table does exist, the statement is logged as the equivalent pair of
CREATE TABLE IF NOT EXISTSand
INSERT ... SELECTstatements. (If the
SELECTin the original statement is preceded by
This change provides forward compatibility for statement-based replication from MySQL 5.1 to 5.5 because when the destination table exists, the rows will be inserted on both the master and slave. To take advantage of this compatibility measure, the 5.1 server must be at least 5.1.51 and the 5.5 server must be at least 5.5.6.
To upgrade an existing 5.1-to-5.5 replication scenario, upgrade the master first to 5.1.51 or higher. Note that this differs from the usual replication upgrade advice of upgrading the slave first.
A workaround for applications that wish to achieve the original effect (rows inserted regardless of whether the destination table exists) is to use
CREATE TABLE IF NOT EXISTSand
INSERT ... SELECTstatements rather than
CREATE TABLE IF NOT EXISTS ... SELECTstatements.
Along with the change just described, the following related change was made: Previously, if an existing view was named as the destination table for
CREATE TABLE IF NOT EXISTS ... SELECT, rows were inserted into the underlying base table and the statement was written to the binary log. As of MySQL 5.1.51 and 5.5.6, nothing is inserted or logged.
Incompatible change: Prior to MySQL 5.5.6, if the server was started with
utf16, it crashed during full-text stopword initialization. Now the stopword file is loaded and searched using
utf32. If any table was created with
FULLTEXTindexes while the server character set was
utf32, it should be repaired using this statement:
REPAIR TABLE tbl_name QUICK;
Incompatible change: As of MySQL 5.5.5, all numeric operators and functions on integer, floating-point and
DECIMALvalues throw an “out of range” error (
ER_DATA_OUT_OF_RANGE) rather than returning an incorrect value or
NULL, when the result is out of the supported range for the corresponding data type. See Section 11.2.6, “Out-of-Range and Overflow Handling”.
Incompatible change: In very old versions of MySQL (prior to 4.1), the
TIMESTAMPdata type supported a display width, which was silently ignored beginning with MySQL 4.1. This is deprecated in MySQL 5.1, and removed altogether in MySQL 5.5. These changes in behavior can lead to two problem scenarios when trying to use
TIMESTAMP(columns with a MySQL 5.5 or later server:
When importing a dump file (for example, one created using mysqldump) created in a MySQL 5.0 or earlier server into a server from a newer release series, a
ALTER TABLEstatement containing
TIMESTAMP(causes the import to fail with a syntax error.
To fix this problem, edit the dump file in a text editor to replace any instances of
TIMESTAMPprior to importing the file. Be sure to use a plain text editor for this, and not a word processor; otherwise, the result is almost certain to be unusable for importing into the MySQL server.
When trying replicate any
ALTER TABLEstatement containing
TIMESTAMP(from a master MySQL server that supports the
TIMESTAMP(syntax to a MySQL 5.5.3 or higher slave, the statement causes replication to fail. Similarly, when you try to restore from a binary log written by a server that supports
TIMESTAMP(to a MySQL 5.5.3 or higher server, any
ALTER TABLEstatement containing
TIMESTAMP(causes the backup to fail. This holds true regardless of the logging format.
It may be possible to fix such issues using a hex editor, by replacing any width arguments used with
TIMESTAMP, and the parentheses containing them, with space characters (hexadecimal
20). Be sure to use a programmer's binary hex editor and not a regular text editor or word processor for this; otherwise, the result is almost certain to be a corrupted binary log file. To guard against accidental corruption of the binary log, you should always work on a copy of the file rather than the original.
You should try to handle potential issues of these types proactively by updating with
TIMESTAMP(N)columns in your databases so that they use
TIMESTAMPinstead, before performing any upgrades.
Incompatible change: As of MySQL 5.5.3, the Unicode implementation has been extended to provide support for supplementary characters that lie outside the Basic Multilingual Plane (BMP). Noteworthy features:
utf32character sets have been added. These correspond to the UTF-16 and UTF-32 encodings of the Unicode character set, and they both support supplementary characters.
utf8mb4character set has been added. This is similar to
utf8, but its encoding allows up to four bytes per character to enable support for supplementary characters.
ucs2character set is essentially unchanged except for the inclusion of some newer BMP characters.
In most respects, upgrading to MySQL 5.5 should present few problems with regard to Unicode usage, although there are some potential areas of incompatibility. These are the primary areas of concern:
Consequently, if you want to upgrade tables from
utf8mb4to take advantage of supplementary-character support, it may be necessary to change some column or index definitions.
For additional details about the new Unicode character sets and potential incompatibilities, see Section 10.9, “Unicode Support”, and Section 10.9.7, “Converting Between 3-Byte and 4-Byte Unicode Character Sets”.
Incompatible change: As of MySQL 5.5.3, the server includes
dtoa, a library for conversion between strings and numbers by David M. Gay. In MySQL, this library provides the basis for improved conversion between string or
DECIMALvalues and approximate-value (
Because the conversions produced by this library differ in some cases from previous results, the potential exists for incompatibilities in applications that rely on previous results. For example, applications that depend on a specific exact result from previous conversions might need adjustment to accommodate additional precision.
For additional information about the properties of
dtoaconversions, see Section 12.2, “Type Conversion in Expression Evaluation”.
Incompatible change: In MySQL 5.5, several changes were made regarding the language and character set of error messages:
--languageoption for specifying the directory for the error message file is now deprecated. The new
lc_messagessystem variables should be used instead, and the server treats
--languageas an alias for
languagesystem variable has been removed and replaced with the new
lc_messages_dirhas only a global value and is read only.
lc_messageshas global and session values and can be modified at runtime, so the error message language can be changed while the server is running, and individual clients each can have a different error message language by changing their session
lc_messagesvalue to a different locale name.
Error messages previously were constructed in a mix of character sets. This issue is resolved by constructing error messages internally within the server using UTF-8 and returning them to the client in the character set specified by the
character_set_resultssystem variable. The content of error messages therefore may in some cases differ from the messages returned previously.
For more information, see Section 10.11, “Setting the Error Message Language”, and Section 10.6, “Error Message Character Set”.
Incompatible change: MySQL 5.5 implements new functions used to calculate row placement for tables partitioned by
LINEAR KEY. Tables that were created using
LINEAR KEYpartitioning in MySQL 5.1 can be upgraded in MySQL 5.5.31 and later using
ALTER TABLE ... PARTITION BY ALGORITHM=2 [LINEAR] KEY (...). (Bug #14521864, Bug #66462)
Incompatible change: Previously, the parser accepted an
INTOclause in nested
SELECTstatements, which is invalid because such statements must return their results to the outer context. As of MySQL 5.5.3, this syntax is no longer permitted and statements that use it must be changed.
Incompatible change: In MySQL 5.5.3, several changes were made to alias resolution in multiple-table
DELETEstatements so that it is no longer possible to have inconsistent or ambiguous table aliases.
In MySQL 5.1.23, alias declarations outside the
table_referencespart of the statement were disallowed for the
USINGvariant of multiple-table
DELETEsyntax, to reduce the possibility of ambiguous aliases that could lead to ambiguous statements that have unexpected results such as deleting rows from the wrong table.
As of MySQL 5.5.3, alias declarations outside
table_referencesare disallowed for all multiple-table
DELETEstatements. Alias declarations are permitted only in the
DELETE FROM t1 AS a2 USING t1 AS a1 INNER JOIN t2 AS a2; DELETE t1 AS a2 FROM t1 AS a1 INNER JOIN t2 AS a2;
DELETE FROM t1 USING t1 AS a1 INNER JOIN t2 AS a2; DELETE t1 FROM t1 AS a1 INNER JOIN t2 AS a2;
Previously, for alias references in the list of tables from which to delete rows in a multiple-table delete, the default database is used unless one is specified explicitly. For example, if the default database is
db1, the following statement does not work because the unqualified alias reference
a2is interpreted as having a database of
DELETE a1, a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2 WHERE a1.id=a2.id;
To correctly match an alias that refers to a table outside the default database, you must explicitly qualify the reference with the name of the proper database:
DELETE a1, db2.a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2 WHERE a1.id=a2.id;
As of MySQL 5.5.3, alias resolution does not require qualification and alias references should not be qualified with the database name. Qualified names are interpreted as referring to tables, not aliases.
Statements containing alias constructs that are no longer permitted must be rewritten.
Some keywords may be reserved in MySQL 5.5 that were not reserved in MySQL 5.1. 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”.