Skip navigation links
5.4.2 Changes in MySQL 5.5.2 (12 February 2010) »
Section Navigation      [Toggle]

5.4.1. Changes in MySQL 5.5.3 (24 March 2010)

Functionality added or changed:

  • Incompatible Change: The following obsolete constructs have been removed. Where alternatives are shown, applications should be updated to use them.

    • The log_bin_trust_routine_creators system variable (use log_bin_trust_function_creators).

    • The myisam_max_extra_sort_file_size system variable.

    • The record_buffer system variable (use read_buffer_size).

    • The sql_log_update system variable.

    • The table_type system variable (use storage_engine).

    • The FRAC_SECOND modifier for the TIMESTAMPADD() function.

    • The TYPE table option to specify the storage engine for CREATE TABLE or ALTER TABLE (use ENGINE).

    • The SHOW TABLE TYPES SQL statement (use SHOW ENGINES).

    • The SHOW INNODB STATUS and SHOW MUTEX STATUS SQL statements (use SHOW ENGINE INNODB STATUS SHOW ENGINE INNODB MUTEX).

    • The SHOW PLUGIN SQL statement (use SHOW PLUGINS).

    • The LOAD TABLE ... FROM MASTER and LOAD DATA FROM MASTER SQL statements (use mysqldump or mysqlhotcopy to dump tables and mysql to reload dump files).

    • The BACKUP TABLE and RESTORE TABLE SQL statements (use mysqldump or mysqlhotcopy to dump tables and mysql to reload dump files).

    • TIMESTAMP(N) data type: The ability to specify a display width of N (use without N).

    • The --default-character-set and --default-collation server options (use --character-set-server and --collation-server).

    • The --delay-key-write-for-all-tables server option (use --delay-key-write=ALL).

    • The --enable-locking and --skip-locking server options (use --external-locking and --skip-external-locking).

    • The --log-bin-trust-routine-creators server option (use --log-bin-trust-function-creators).

    • The --log-long-format server option.

    • The --log-update server option.

    • The --master-xxx server options to set replication parameters (use the CHANGE MASTER TO statement instead): --master-host, --master-user, --master-password , --master-port, --master-connect-retry, --master-ssl, --master-ssl-ca, --master-ssl-capath, --master-ssl-cert, --master-ssl-cipher, --master-ssl-key.

    • The --safe-show-database server option.

    • The --skip-symlink and --use-symbolic-links server options (use --skip-symbolic-links and --symbolic-links).

    • The --sql-bin-update-same server option.

    • The --warnings server option (use --log-warnings).

    • The --no-named-commands option for mysql (use --skip-named-commands

    • The --no-pager option for mysql (use --skip-pager).

    • The --no-tee option for mysql (use --skip-tee).

    • The --position option for mysqlbinlog (use --start-position).

    • The --all option for mysqldump (use --create-options).

    • The --first-slave option for mysqldump (use --lock-all-tables).

    • The --config-file option for mysqld_multi (use --defaults-extra-file).

    • The --set-variable=var_name=value and -O var_name=value general-purpose options for setting program variables (use --var_name=value).

    (Bug#48048)

    See also Bug#47974.

  • Incompatible Change: Aliases for wildcards (as in SELECT t.* AS 'alias' FROM t) are no longer accepted and result in an error. Previously, such aliases were ignored silently. (Bug#27249)

  • Incompatible Change: FLUSH TABLES has a new variant, FLUSH TABLES tbl_list WITH READ LOCK. This variant enables tables to be flushed and locked in a single operation. It provides a workaround for the restriction (due to work done for Bug#989) that FLUSH TABLES is disallowed when there is an active LOCK TABLES ... READ.

    See also Bug#42465.

  • Incompatible Change: The server now 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/DOUBLE) numbers:

    • Consistent conversion results across platforms, which eliminates, for example, Unix versus Windows conversion differences.

    • Accurate representation of values in cases where results previously did not provide sufficient precision, such as for values close to IEEE limits.

    • Conversion of numbers to string format with the best possible precision. The precision of dtoa is always the same or better than that of the standard C library functions.

    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 Type Conversion in Expression Evaluation.

    See also Bug#12860, Bug#21497, Bug#26788, Bug#24541, Bug#34015.

  • Incompatible Change: 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 Unicode Support, and Upgrading from Previous to Current Unicode Support.

  • Incompatible Change: Several columns were added to the INFORMATION_SCHEMA.ROUTINES table to provide information about the RETURNS clause data type for stored functions: DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, CHARACTER_SET_NAME, and COLLATION_NAME.

    This change produces an incompatibility for applications that depend on column order in the ROUTINES table because the new columns appear between the ROUTINE_TYPE and DTD_IDENTIFIER columns. Such applications may need to be adjusted to account for the new columns.

Bugs fixed:

  • Security Fix: The server crashed if an account with the CREATE ROUTINE privilege but not the EXECUTE privilege attempted to create a stored procedure. (Bug#44798)

  • Incompatible Change: Replication: The --binlog_format system variable can no longer be set inside a transaction. In other words, the binary logging format can no longer be changed while a transaction is in progress. (Bug#47863)

  • Incompatible Change: For debug builds, wttempts to execute RESET statements within a transaction that had acquired metadata locks led to an assertion failure.

    As a result of this bug fix, RESET statements now cause an implicit commit. (Bug#51336)

  • Incompatible Change: A deadlock occurred for this sequence of events: Session 1 locked a table using LOCK TABLES; Session 2 dropped the database containing the table; Session 1 created any database.

    A consequence of this bug fix is that CREATE DATABASE is disallowed within a session that has an active LOCK TABLES statement. (Bug#49988)

  • Incompatible Change: For application compatibility reasons, when sql_auto_is_null is 1, MySQL converts auto_inc_col IS NULL to auto_inc_col = LAST_INSERT_ID(). However, this was being done regardless of whether the predicate was alone or at the top level. Now it occurs only when it is a single top-level predicate.

    In conjunction with this bug fix, the default value of the sql_auto_is_null system variable has been changed from 1 to 0, which may cause incompatibilities with existing applications. (Bug#41371)

  • Incompatible Change: The parser accepted illegal syntax in a FOREIGN KEY clause:

    • Multiple MATCH clauses.

    • Multiple ON DELETE clauses.

    • Multiple ON UPDATE clauses.

    • MATCH clauses specified after ON UPDATE or ON DELETE. In case of multiple redundant clauses, this leads to confusion, and implementation-dependent results.

    These illegal syntaxes are now properly rejected. Existing applications that used them will require adjustment. (Bug#34455)

  • Incompatible Change: The Locked thread state was equivalent to the Table lock state and has been removed. It no longer appears in SHOW PROCESSLIST output. (Bug#28870)

  • Incompatible Change: Several changes were made to the processing of multiple-table DELETE statements:

    • Statements could not perform cross-database deletes unless the tables were referred to without using aliases. This limitation has been lifted and table aliases now are allowed.

    • Previously, alias declarations could be given for tables elsewhere than in the table_references part of the syntax. This could lead to ambiguous statements that have unexpected results such as deleting rows from the wrong table. Example: 

      DELETE FROM t1 AS a2 USING t1 AS a1 INNER JOIN t2 AS a2;

      Now alias declarations can be declared only in the table_references part. Elsewhere in the statement, alias references are allowed but not alias declarations.

    • Alias resolution was improved so that it is no longer possible to have inconsistent or ambiguous aliases for tables.

    Statements containing alias constructs that are no longer allowed must be rewritten. (Bug#27525)

    See also Bug#30234.

  • Incompatible Change: DROP TABLE now is allowed only if you have acquired a WRITE lock with LOCK TABLES, or if you hold no locks, or if the table is a TEMPORARY table.

    Previously, if other tables were locked, you could drop a table with a read lock or no lock, which could lead to deadlocks between clients. The new stricter behavior means that some usage scenarios will fail when previously they did not. (Bug#25858)

  • Incompatible Change: If a data definition language (DDL) statement occurred for a table that was being used by another session in an active transaction, statements could be written to the binary log in the wrong order. For example, this could happen if DROP TABLE occurred for a table being used in a transaction. This is now prevented by deferring release of metadata locks on tables used within a transaction until the transaction ends.

    This bug fix results in some incompatibilities with previous versions:

    • A table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends.

    • FLUSH TABLES is disallowed when there is an active LOCK TABLES ... READ. Use FLUSH TABLES tbl_list WITH READ LOCK instead. This causes a problem with mysqlhotcopy, fixed in Bug#42465.

    (Bug#989, Bug#39675)