2.11.1.3 Changes in MySQL 5.5

Before upgrading to MySQL 5.5, review the changes described in this section to identify those that apply to your current MySQL installation and applications. Perform any recommended actions.

Changes marked as either Known issue or Incompatible change are incompatibilities with earlier versions of MySQL, and may require your attention before upgrading. 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”.

• Configuration Changes

• Server Changes

• InnoDB Changes

• SQL Changes

Configuration Changes

• Incompatible change: The InnoDB Plugin is included in MySQL 5.5 releases. It becomes the built-in version of InnoDB in MySQL Server, replacing the version previously included as the built-in InnoDB engine. InnoDB Plugin is 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 Plugin in 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_threads system variable has been removed and replaced with innodb_read_io_threads and innodb_write_io_threads. If you upgrade from MySQL 5.1 to MySQL 5.5 and previously explicitly set innodb_file_io_threads at server startup, you must change your configuration. Either remove any reference to innodb_file_io_threads or replace it with references to innodb_read_io_threads and innodb_write_io_threads.

• 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 libmysqlservices library provides access to the available services and dynamic plugins now must be linked against this library (use the -lmysqlservices flag). For an example showing how to configure for CMake, see Section 24.3, “MySQL Services for Plugins”.

Server Changes

• On Linux systems, the libaio library may be needed. Install it first, if it is not already present on your system.

• Known issue: As of MySQL 5.5.32, for new installations, the url columns in the mysql database help tables are now created as type TEXT to 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 TABLES is not permitted when there is an active LOCK TABLES ... READ. To provide a workaround for this restriction, FLUSH TABLES has a new variant, FLUSH TABLES tbl_list WITH READ LOCK, 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:

  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 mysql database. 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

  To create the proxies_priv table, start the server with the --skip-grant-tables option to cause it to skip the normal grant table checks, then run mysql_upgrade. For example:

  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-file option 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

  With the --skip-grant-tables option, the server does no password or privilege checking, so any client can connect and effectively have all privileges. For additional security, use the --skip-networking option as well to prevent remote clients from connecting.

  Note

  This problem is fixed in MySQL 5.5.8; the server treats a missing proxies_priv table 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, InnoDB always uses the fast truncation technique, equivalent to DROP TABLE and CREATE TABLE. It no longer performs a row-by-row delete for tables with parent-child foreign key relationships. TRUNCATE TABLE returns an error for such tables. Modify your SQL to issue DELETE FROM table_name for such tables instead.

• Incompatible change: Prior to MySQL 5.5.7, if you flushed the logs using FLUSH LOGS or mysqladmin flush-logs and mysqld was writing the error log to a file (for example, if it was started with the --log-error option), 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 ... SELECT statements has been changed for the case that the destination table already exists:

  • Previously, for 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 EXISTS is present and an error when it is not.

  This change in handling of IF NOT EXISTS results 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 ... SELECT is 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 ... SELECT is 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 EXISTS and INSERT ... SELECT statements. (If the SELECT in the original statement is preceded by IGNORE or REPLACE, the INSERT becomes INSERT IGNORE or REPLACE, respectively.)

  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 EXISTS and INSERT ... SELECT statements rather than CREATE TABLE IF NOT EXISTS ... SELECT statements.

  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 character_set_server set to utf16, it crashed during full-text stopword initialization. Now the stopword file is loaded and searched using latin1 if character_set_server is ucs2, utf16, or utf32. If any table was created with FULLTEXT indexes while the server character set was ucs2, utf16, or 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 DECIMAL values 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 TIMESTAMP data 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(N) 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 CREATE TABLE or ALTER TABLE statement containing TIMESTAMP(N) 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 TIMESTAMP(N) with TIMESTAMP prior 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 CREATE TABLE or ALTER TABLE statement containing TIMESTAMP(N) from a master MySQL server that supports the TIMESTAMP(N) 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(N) to a MySQL 5.5.3 or higher server, any CREATE TABLE or ALTER TABLE statement containing TIMESTAMP(N) 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 ALTER TABLE any TIMESTAMP(N) columns in your databases so that they use TIMESTAMP instead, 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:

  • utf16 and utf32 character 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.

  • The utf8mb4 character 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.

  • The ucs2 character 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:

  • For the variable-length character data types (VARCHAR and the TEXT types), the maximum length in characters is less for utf8mb4 columns than for utf8 columns.

  • For all character data types (CHAR, VARCHAR, and the TEXT types), the maximum number of characters that can be indexed is less for utf8mb4 columns than for utf8 columns.

  Consequently, if you want to upgrade tables from utf8 to utf8mb4 to 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 DECIMAL values and approximate-value (FLOAT or DOUBLE) numbers.

  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 dtoa conversions, 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:

  • The --language option for specifying the directory for the error message file is now deprecated. The new lc_messages_dir and lc_messages system variables should be used instead, and the server treats --language as an alias for lc_messages_dir.

  • The language system variable has been removed and replaced with the new lc_messages_dir and lc_messages system variables. lc_messages_dir has only a global value and is read only. lc_messages has 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_messages value 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_results system 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 KEY and LINEAR KEY. Tables that were created using KEY or LINEAR KEY partitioning 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)

InnoDB Changes

As of MySQL 5.5.62, the zlib library version bundled with MySQL was raised from version 1.2.3 to version 1.2.11.

The zlib compressBound() function in zlib 1.2.11 returns a slightly higher estimate of the buffer size required to compress a given length of bytes than it did in zlib version 1.2.3. The compressBound() function is called by InnoDB functions that determine the maximum row size permitted when creating compressed InnoDB tables or inserting rows into compressed InnoDB tables. As a result, CREATE TABLE ... ROW_FORMAT=COMPRESSED or INSERT operations with row sizes very close to the maximum row size that were successful in earlier releases could now fail.

If you have compressed InnoDB tables with large rows, it is recommended that you test compressed table CREATE TABLE statements on a MySQL 5.5 test instance prior to upgrading.

SQL Changes

• Incompatible change: Previously, the parser accepted an INTO clause in nested SELECT statements, 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 DELETE statements so that it is no longer possible to have inconsistent or ambiguous table aliases.

  • In MySQL 5.1.23, alias declarations outside the table_references part of the statement were disallowed for the USING variant of multiple-table DELETE syntax, 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_references are disallowed for all multiple-table DELETE statements. Alias declarations are permitted only in the table_references part.

    Incorrect:

    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;

    Correct:

    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 a2 is interpreted as having a database of db1:

    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”.