Documentation Home
MySQL 5.5 Release Notes
Related Documentation Download these Release Notes
PDF (US Ltr) - 1.2Mb
PDF (A4) - 1.2Mb
EPUB - 317.0Kb

MySQL 5.5 Release Notes  /  Changes in MySQL 5.5.3 (2010-03-24, Milestone 3)

Changes in MySQL 5.5.3 (2010-03-24, Milestone 3)

InnoDB Notes

  • This release includes InnoDB 1.0.6. This version is considered of Release Candidate (RC) quality.

IPv6 Support

  • MySQL Server now can accept TCP/IP connections from clients connecting over IPv6. See IPv6 Support. For example, this command connects over IPv6 to the MySQL server on the local host:

    shell> mysql -h ::1

    To use this capability, two things must be true:

    • Your system must be configured to support IPv6.

    • The default MySQL server configuration permits only IPv4 connections, so the server must be configured for IPv6 connections. To permit IPv6 connections in addition to or instead of IPv4 connections, start the server with an appropriate --bind-address option.

    MySQL account names permit IPv6 addresses to enable DBAs to specify privileges for clients that connect to the server over IPv6. See Specifying Account Names. IPv6 addresses can be specified in account names in statements such as CREATE USER, GRANT, and REVOKE. For example:

    mysql> CREATE USER 'bill'@'::1' IDENTIFIED BY 'secret';
    mysql> GRANT SELECT ON mydb.* TO 'bill'@'::1';

    The default set of accounts created during MySQL installation now include an account for 'root'@'::1'. See Securing the Initial MySQL Accounts. This account can be used to make connections as root if the server is bound to ::1 and accepts only local IPv6 connections. (Bug #8836)

Performance Schema Notes

  • MySQL Server now includes the Performance Schema, a feature for monitoring server execution at a low level. The implementation uses the PERFORMANCE_SCHEMA storage engine and the performance_schema database. The Performance Schema focuses primarily on performance data. This differs from INFORMATION_SCHEMA, which serves for inspection of metadata. For more information, see MySQL Performance Schema.

    Performance Schema support is included in binary MySQL distributions but is disabled by default. To enable it, start the server with the --performance_schema option.

    To create the performance_schema database if you are upgrading from an earlier release, run mysql_upgrade and restart the server. See mysql_upgrade — Check and Upgrade MySQL Tables.

Functionality Added or Changed

  • Performance: The performance of internal functions that trim multiple spaces from strings when comparing them has been improved. (Bug #14637)

  • Incompatible Change: CREATE VIEW and DROP VIEW now are prohibited while a LOCK TABLES statement is in effect. (Bug #56571)

  • 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_lock_wait_timeout system variable.

    • The table_type system variable (use storage_engine).

    • The FRAC_SECOND modifier for the TIMESTAMPADD() function (use MICROSECOND).

    • 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 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)

    References: See also Bug #47974, Bug #56408.

  • 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: Several changes were made to processing of server system variables and command-line options to make their treatment more consistent.

    General changes:

    • The help message text displayed by mysqld --verbose --help now consistently uses dashes to show the names of options and system variables that can be set at server startup. Previously, the message used both dashes and underscores (generally with dashes for options and underscores for system variables). For example, the help message now displays --log-output and --general-log, whereas previously it displayed --log-output and --general_log.

      This is a display-only change. The permissible syntax for setting options and variables remains unchanged:

      • At server startup, you can specify options and variables on the command line or in option files using either dashes or underscores.

      • For those system variables that can be set at runtime (for example, using SET), you must specify them using underscores.

    • There are fewer session-only system variables. These variables now have a global value: autocommit, foreign_key_checks, profiling, sql_auto_is_null, sql_big_selects, sql_buffer_result, sql_log_bin, sql_log_off, sql_notes, sql_quote_show_create, sql_safe_updates, sql_warnings, unique_checks.

      For those variables, you can now set the global value to change the value from which the session value is initialized for new sessions.

      The following list shows the variables that remain session-only. They apply only in the context of a specific session so that a global value is of no use: debug_sync, error_count, identity, insert_id, last_insert_id, pseudo_thread_id, rand_seed1, rand_seed2, timestamp, warning_count.

    • All system variables are accessible at runtime using @@ syntax (@@GLOBAL.var_name, @@SESSION.var_name, @@var_name). Previously, this syntax produced an error for some variables.

    • All system variables are included as appropriate in the output from SHOW {GLOBAL, SESSION} VARIABLES and the INFORMATION_SCHEMA.GLOBAL_VARIABLES and INFORMATION_SCHEMA.SESSION_VARIABLES tables. Previously, some variables were not displayed.

    • As appropriate in the preceding item means that SHOW GLOBAL VARIABLES and INFORMATION_SCHEMA.GLOBAL_VARIABLES no longer include session-only system variables. Previously, these included the global value of a variable if it had one, and the session value if not. (SHOW SESSION VARIABLES still includes global-only variables.)

    • The server now enforces type checking for assignments to system variables, so it is more consistent and strict about rejecting invalid values.

    • For attempts to assign a negative value to an unsigned system variable, the server truncates the value to the minimum permitted value. Previously, there was sometimes wraparound to a large positive value.

    • Some system variables (typically those that control memory or disk allocation) are permitted to take only values that are a multiple of a given block size, and assigning a value not a block size multiple causes truncation to the nearest multiple. (For example, net_buffer_length must be a multiple of 1024. Assigning 16384 results in a value of 16384, whereas assigning 16383 results in a value of 15360.) A warning now occurs when adjustment of the specified value takes place. Previously, adjustment was silent.

    • More system variables can be assigned the value DEFAULT to set them to their default value. Previously, this syntax produced an error in some cases.

    • All variables that have a SET data type value can be set to an integer value that is treated like a bit mask. Previously, this did not work for some SET-type variables.

    • The default value for several system variables no longer differs between 32-bit and 64-bit builds. Previously, the values differed by about 100 bytes for some variables.

    • There are no longer any write-only system variables. For example, SELECT @@rand_seed1 returns 0, not Variable 'rand_seed1' can only be set, not read.

    Variable-specific changes:

    • The concurrent_insert system variable now is handled as an enumeration with the permissible values NEVER, AUTO, and ALWAYS. The corresponding integer values 0, 1, and 2 are still recognized.

    • The completion_type system variable now is handled as an enumeration with the permissible values NO_CHAIN, CHAIN, and RELEASE. The corresponding integer values 0, 1, and 2 are still recognized.

    • For concurrent_insert and completion_type, the string form of the value is displayed by SHOW VARIABLES and SELECT @@var_name.

    • The unused rpl_recovery_rank system variable is deprecated.

    • The storage_engine system variable is deprecated in favor of the new system variable default_storage_engine. This enables pairing of the --default-storage-engine command-line option with a system variable of a more closely corresponding name.

    • The --myisam-recover option is renamed to --myisam-recover-options to pair better with the name of the myisam_recover_options system variable. The old option name still works because it is recognized as an unambiguous prefix of the new name. (Option prefix recognition occurs as described in Specifying Program Options.)

    • --myisam-recover-options has a new permissible value OFF.

    • Attempts to drop the default key cache produce an error. Previously, it produced only a warning and status of success even though the attempt failed.

    References: See also Bug #34437, Bug #34635, Bug #11747961, Bug #34829, Bug #34878, Bug #25430.

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

  • 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: 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.

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

  • Incompatible Change: Implicit conversion of a number or temporal value to string now produces a value that has a character set and collation determined by the character_set_connection and collation_connection system variables. (These variables commonly are set with SET NAMES. For information about connection character sets, see Connection Character Sets and Collations.)

    This change means that such a conversion results in a character (nonbinary) string (a CHAR, VARCHAR, or LONGTEXT value), except when the connection character set is set to binary. In that case, the conversion result is a binary string (a BINARY, VARBINARY, or LONGBLOB value).

    Previously, an implicit conversion always produced a binary string, regardless of the connection character set. Such implicit conversions to string typically occur for functions that are passed numeric or temporal values when string values are more usual, and thus could have effects beyond the type of the converted value. Consider the expression CONCAT(1, 'abc'). The numeric argument 1 was converted to the binary string '1' and the concatenation of that value with the nonbinary string 'abc' produced the binary string '1abc'.

    This change in conversion behavior affects several functions that expect string arguments because a numeric or temporal argument converted to a string now results in a character rather than binary string argument:

    These functions remain unaffected:

    • CHAR() without a USING clause still returns VARBINARY.

    • Functions that previously returned utf8 strings still do so. Examples include CHARSET() and COLLATION().

    Encryption and compression functions that expect string arguments and previously returned binary strings are affected depending on the content of the return value:

    The INET_NTOA() return value contains only ASCII characters, and this function now returns a character string with the connection character set and collation rather than a binary string.

  • Important Change; Replication: RESET MASTER and RESET SLAVE now reset the values shown for Last_IO_Error, Last_IO_Errno, Last_SQL_Error, and Last_SQL_Errno in the output of SHOW SLAVE STATUS. (Bug #34654)

    References: See also Bug #44270.

  • Important Change: The --skip-thread-priority option is now deprecated such that the server will not change the thread priorities by default. Giving threads different priorities might yield marginal improvements in some platforms (where it actually works), but it might instead cause significant degradation depending on the thread count and number of processors. Meddling with the thread priorities is a not a safe bet as it is very dependent on the behavior of the CPU scheduler and system where MySQL is being run. (Bug #35164, Bug #37536)

  • Replication; Cluster Replication: MySQL Replication now supports attribute promotion and demotion for row-based replication between columns of different but similar types on the master and the slave. For example, it is possible to promote an INT column on the master to a BIGINT column on the slave, and to demote a TEXT column to a VARCHAR column.

    The implementation of type demotion distinguishes between lossy and non-lossy type conversions, and their use on the slave can be controlled by setting the slave_type_conversions global server system variable.

    For more information, see Row-based replication: attribute promotion and demotion. (Bug #47163, Bug #46584)

  • Replication: For replication based on row-based and mix-format binary logging, it is now safe to mix transactional and nontransactional statements within a transaction. The nontransactional statements are logged immediately rather than waiting until the transaction ends, ensuring that their results are logged and replicated correctly regardless of the result of the transaction.

  • mysqltest has a new --max-connections option to set a higher number of maximum permitted server connections than the default 128. This option can also be passed using (Bug #51135)

  • has a new --portbase option and a corresponding MTR_PORT_BASE environment variable for setting the port range, as an alternative to the existing --build-thread option. (Bug #50182)

  • SHOW PROFILE CPU has been ported to Windows. Thanks to Alex Budovski for the patch. (Bug #50057)

  • now has a --gprof option that runs the server through the gprof profiler, much the same way the currently supported --gcov option runs it through gcov. (Bug #49345)

  • mysqltest now has a lowercase_result command that converts the output of the next statement to lowercase. This is useful for test cases where the lettercase may vary between platforms. (Bug #48863)

  • mysqltest now has a remove_files_wildcard command that removes files matching a pattern from a directory. (Bug #39774)

  • MySQL support for adding collations using LDML specifications did not support the <i> identity rule that indicates one character sorts identically to another. The <i> rule now is supported. See LDML Syntax Supported in MySQL. (Bug #37129)

  • For boolean options, the option-processing library now prints additional information in the --help message: If the option is enabled by default, the message says so and indicates that the --skip form of the option disables the option. This affects all compiled MySQL programs that use the library. (Bug #35224)

  • The use of the SQL_CACHE and SQL_NO_CACHE options in SELECT statements now is checked more restrictively: 1) Previously, both options could be given in the same statement. This is no longer true; only one can be given. 2) Previously, these options could be given in SELECT statements that were not at the top-level. This is no longer true; the options are not permitted in subqueries (including subqueries in the FROM clause, and SELECT statements in unions other than the first SELECT. (Bug #35020)

  • The mysql client now has an --auto-vertical-output option, which causes result sets to be displayed vertically if they are too wide for the current window, and uses normal tabular format otherwise. (This applies to statements terminated by ; or \G.) (Bug #26780)

  • TRUNCATE TABLE now is permitted for a table for which a WRITE lock has been acquired with LOCK TABLES. (Bug #20667)

    References: See also Bug #46452.

  • FLUSH LOGS now takes an optional log_type value so that FLUSH log_type LOGS can be used to flush only a specified log type. These log_type options are permitted:

    • BINARY closes and reopens the binary log files.

    • ENGINE closes and reopens any flushable logs for installed storage engines.

    • ERROR closes and reopens the error log file.

    • GENERAL closes and reopens the general query log file.

    • RELAY closes and reopens the relay log files.

    • SLOW closes and reopens the slow query log file.

    Thanks to Eric Bergen for the patch to implement this feature. (Bug #14104)

  • Previously, prepared CALL statements could be used through the C API only for stored procedures that produce at most one result set, and applications could not use placeholders for OUT or INOUT parameters. For prepared CALL statements used using PREPARE and EXECUTE, placeholders could not be used for OUT or INOUT parameters.

    For the C API, prepared CALL support now is expanded in the following ways:

    • A stored procedure can produce any number of result sets. The number of columns and the data types of the columns need not be the same for all result sets.

    • The final values of OUT and INOUT parameters are available to the calling application after the procedure returns. These parameters are returned as an extra single-row result set following any result sets produced by the procedure itself. The row contains the values of the OUT and INOUT parameters in the order in which they are declared in the procedure parameter list.

    • A new C API function, mysql_stmt_next_result(), is available for processing stored procedure results. See C API Support for Prepared CALL Statements.

    • The CLIENT_MULTI_RESULTS flag now is enabled by default. It no longer needs to be enabled when you call mysql_real_connect(). (This flag is necessary for executing stored procedures because they can produce multiple result sets.)

    For PREPARE and EXECUTE, placeholder support for OUT and INOUT parameters is now available. See CALL Syntax. (Bug #11638, Bug #17898)

  • Three options were added to mysqldump make it easier to generate a dump from a slave server:

    (Bug #8368)

  • mysqladmin now permits the password value to be omitted following the password command. In this case, mysqladmin prompts for the password value, which enables you to avoid specifying the password on the command line. Omitting the password value should be done only if password is the final command on the mysqladmin command line. Otherwise, the next argument is taken as the password. (Bug #5724)

  • When the server detects MyISAM table corruption, it now writes additional information to the error log, such as the name and line number of the source file, and the list of threads accessing the table. Example: Got an error from thread_id=1, mi_dynrec.c:368. This is useful information to include in bug reports.

  • The maximum length of table comments was extended from 60 to 2048 characters. The maximum length of column comments was extended from 255 to 1024 characters. Index definitions now can include a comment of up to 1024 characters.

  • The optimizer_switch system variable now has an engine_condition_pushdown flag to control whether storage engine condition pushdown optimization is used. As a consequence, the engine_condition_pushdown system variable now is deprecated.

  • The server now provides a pluggable audit interface that enables information about server operations to be reported to interested parties. Audit plugins may register with the audit interface to receive notification about server operations. When an auditable event occurs within the server, the server determines whether notification is needed. For each registered audit plugin, the server checks the event against those event classes in which the plugin is interested and passes the event to the plugin if there is a match. For more information, see Audit Plugins.

  • Code that produces query IDs and updates the value of the Threads_running status variable no longer acquires a global lock that also protects the list of all connections. Instead, it relies on atomic increment and decrement instructions. This improves scalability and to a certain extent alleviates the problem described in Bug #11751904.

    References: See also Bug #42930.

  • The TABLESPACES table has been added to INFORMATION_SCHEMA for tracking tablespace details.

  • Added the PARAMETERS table to INFORMATION_SCHEMA. The PARAMETERS table provides information about stored procedure and function parameters, and about return values for stored functions.

  • Some conversions between Japanese character sets are more efficient.

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)

  • Security Enhancement: When the DATA DIRECTORY or INDEX DIRECTORY clause of a CREATE TABLE statement referred to a subdirectory of the data directory through a symlinked component of the data directory path, it was accepted, when for security reasons it should be rejected. (Bug #39277)

  • Performance; Replication: When writing events to the binary log, transactional events (that is, events that operate on transactional tables) are written to a thread-specific transaction cache, which is then written to the binary log on commit. To handle nontransactional events, there was a lock taken on the binary log (when entering the function MYSQL_BIN_LOG::write()), even when the event was written to the transaction cache instead of the binary log, causing a major bottleneck in replication performance. (Bug #42757)

  • 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; Replication: Concurrent statements using a stored function and DROP FUNCTION for that function could break statement-based replication.

    DDL statements for stored procedures and functions are now prohibited while a LOCK TABLES statement is in effect. (Bug #30977)

    References: See also Bug #57663.

  • Incompatible Change: For debug builds, attempts 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.

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

  • Incompatible Change: CREATE TABLE statements (including CREATE TABLE ... LIKE) are now prohibited whenever a LOCK TABLES statement is in effect.

    One consequence of this change is that CREATE TABLE ... LIKE makes the same checks as CREATE TABLE and does not just copy the .frm file. This means that if the current SQL mode is different from the mode in effect when the original table was created, the table definition might be considered invalid for the new mode and the statement will fail. (Bug #42546, Bug #11751609)

  • Incompatible Change: Due to work done for Bug #989, FLUSH TABLES is not permitted when there is an active LOCK TABLES ... READ. This caused a problem with mysqlhotcopy, which used that sequence of statements. mysqlhotcopy now uses FLUSH TABLES tbl_list WITH READ LOCK to flush and lock tables. If mysqlhotcopy is used with a server older than MySQL 5.5.3 that does not support this statement, it has a new option --old_server that causes it to use the previous statement sequence.

    To provide a workaround for the restriction that FLUSH TABLES is no longer permitted when there is an active LOCK TABLES ... READ, 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:


    (Bug #42465)

  • 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 parser accepted an INTO clause in nested SELECT statements, which is invalid because such statements must return their results to the outer context. This syntax is no longer permitted. (Bug #33204)

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

      Now alias declarations outside table_references are disallowed for all multiple-table DELETE statements. Alias declarations are permitted only in the table_references part.


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

      DELETE a1, a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2

      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

      Now 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. (Bug #27525)

    References: See also Bug #30234.

  • Incompatible Change: DROP TABLE now is permitted 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:

    (Bug #989, Bug #39675)

  • Important Change; Replication: For an engine that supported only row-based replication, replication stopped with an error when executing row events.

    For information about changes in how the binary logging format is determined in relation to statement type and storage engine logging capabilities, see Mixed Binary Logging Format.

    As part of the fix for this issue, the EXAMPLE storage engine is now changed so that it supports statement-based logging only. Previously, it supported row-based logging only. (Bug #39934, Bug #11749859)

  • Important Change: The IPv6 loopback address ::1 was interpreted as a hostname rather than a numeric IP address.

    In addition, the IPv6-enabled server on Windows interpreted the hostname localhost as ::1 only, which failed to match the default 'root'@'' account in the mysql.user privilege table.


    As a result of this fix, a 'root'@'::1' account is added to the mysql.user table as one of the default accounts created during MySQL installation.

    (Bug #43006)

    References: See also Bug #38247, Bug #11753779, Bug #45584, Bug #45606.

  • InnoDB; Replication: Column length information generated by InnoDB did not match that generated by MyISAM, which caused invalid metadata to be written to the binary log when trying to replicate BIT columns. (Bug #49618)

  • InnoDB: SHOW INNODB STATUS could display incorrect information about deadlocks, when the deadlock detection routine stops early (to avoid excessive CPU usage). (Bug #49001)

  • InnoDB: Concurrent execution of ALTER TABLE for InnoDB table and a transaction that tried to read and then update the table could result in a deadlock between table-level locks and InnoDB row locks, which was detected only after the innodb_lock_wait_timeout timeout occurred. (Bug #37346)

  • Partitioning: When using a debug build of MySQL, if a query against a partitioned table having an index on one or more DOUBLE columns used that index, the server failed with an assertion. (Bug #45816)

  • Partitioning: The first time that a query against the INFORMATION_SCHEMA.TABLES table for partitioned tables using the ARCHIVE engine was run, it returned invalid data. If the server had been restarted since such a table had been created, or if the table had never actually been opened, its DATA_LENGTH was reported as 0 bytes. (The second and subsequent attempts to issue the same query returned the expected result.) (Bug #44622)

  • Partitioning: ALTER TABLE on a partitioned table caused unnecessary deadlocks. (Bug #43867)

    References: See also Bug #46654. This bug is a regression of Bug #40181.

  • Partitioning: Attempting to drop a partitioned table from one connection while waiting for the completion of an ALTER TABLE that had been issued from a different connection, and that changed the storage engine used by the table, could cause the server to crash. (Bug #42438)

  • Partitioning: After attempting to create a duplicate index on a partitioned table (and having the attempt fail as expected), a subsequent attempt to create a new index on the table caused the server to hang. (Bug #40181)

  • Partitioning: When used on a partitioned table, ALTER TABLE produced the wrong error message when the name of a nonexistent storage engine was used in the ENGINE clause. (Bug #35765)

  • Partitioning: When one user was in the midst of a transaction on a partitioned table, a second user performing an ALTER TABLE on this table caused the server to hang. (Bug #34604)

  • Partitioning: Portions of the partitioning code were refactored in response to potential regression issues uncovered while working on the fix for Bug #31210. (Bug #32115)

    References: See also Bug #40281.

  • Replication: When using the row-based or mixed replication format with a debug build of the MySQL server, inserts into columns using the utf32 character set on the master caused the slave to crash. (Bug #51787)

    References: See also Bug #51716.

  • Replication: When using the row-based or mixed replication format, column values using the utf16 character set on the master were padded incorrectly on the slave. (Bug #51716)

    References: See also Bug #51787.

  • Replication: An issue internal to the code, first seen in Bug #49132 but not completely resolved in the fix for that bug, was removed. This should prevent similar issues to those in the previous bug with binlog_format changes following DDL statements.

    For developers working with the MySQL Server code: the public class variable THD::current_stmt_binlog_row_based was supposed to have been removed as part of the fix for Bug #39934, but was still present in the code. If a developer later tried to use this variable, it could cause the previous issues to re-occur, and possibly new ones to arise. The variable has now been removed; the previously added class functions THD::is_current_stmt_binlog_format_row(), THD::set_current_stmt_binlog_format_row(), and THD::clear_current_stmt_binlog_format_row() should be used instead. (Bug #51021)

    References: See also Bug #11749859.

  • Replication: Adding an index to a table on the master caused the slave to stop logging slow queries to the slow query log. (Bug #50620)

  • Replication: If a CHANGE MASTER TO statement set MASTER_HEARTBEAT_PERIOD to 30 or higher, Slave_received_heartbeats did not increase on the slave. This caused the slave to reconnect before the time indicated by slave_net_timeout had elapsed.

    This issue affected big-endian 64-bit platforms such as Solaris/SPARC. (Bug #50296)

  • Replication: The error message given when trying to replicate (using statement-based mode) insertions into an AUTO_INCREMENT column by a stored function or a trigger was improved. (Bug #50192)

  • Replication: The server could deadlock when FLUSH LOGS was executed concurrently with DML statements. To fix this problem, nontransactional changes are now always flushed before transactional changes. (Bug #50038)

  • Replication: Metadata for GEOMETRY fields was not properly stored by the slave in its definitions of tables. (Bug #49836)

    References: See also Bug #48776.

  • Replication: Statement-based replication of user variables having numeric data types did not always work correctly. (Bug #49562, Bug #11757508)

  • Replication: When using the semisynchronous replication plugin on Windows, the wait time calculated when the master was waiting for reply from the slave was incorrect. In addition, when the wait time was less than the current time, the master did not wait for a reply at all.

    This issue was caused by the fact that a different internal function was used to get current time by the plugin on Windows as opposed to other platforms, and this function was not correctly implemented. Now the Windows version of the plugin uses the same function as other platforms for this purpose. (Bug #49557)

  • Replication: Due to a change in the format of the information used by the slave to connect to the master, which could cause to reject connection attempts to older masters by newer slaves. (Bug #49259)

    References: This bug was introduced by Bug #13963.

  • Replication: When using row-based logging, a failing INSERT ... SELECT statement on a nontransactional table was not flagged correctly, such that, if a rollback was requested and no other nontransactional table had been updated, nothing was written to the binary log. (Bug #47175)

    References: See also Bug #40278.

  • Replication: When using row-based replication, the incomplete logging of a group of events involving both transaction and nontransactional tables could cause STOP SLAVE to hang. (Bug #45940)

    References: See also Bug #319, Bug #38205.

  • Replication: There were two related issues concerning handling of unsafe statements and setting of the binary logging format when there were open temporary tables on the master, and the existing replication format was row-based or mixed:

    1. When using binlog_format=ROW, and an unsafe statement was executed while there were open temporary tables on the master, the statement SET @@session.binlog_format = MIXED failed with the error Cannot switch out of the row-based binary log format when the session has open temporary tables.

    2. When using binlog_format=MIXED, and an unsafe statement was executed while there were open temporary tables on the master, the statement SET @@session.binlog_format = STATEMENT caused any subsequent DML statements to be written to the binary log using the row-based format instead of the statement-based format.

    (Bug #45855, Bug #45856)

  • Replication: Statements that updated AUTO_INCREMENT columns in multiple tables were logged using the row-based format when --binlog_format was set to MIXED, but did not cause an Unsafe statement warning to be generated when --binlog_format was set to STATEMENT. (Bug #45827)

    References: See also Bug #39934, Bug #11749859.

  • Replication: Even though INSERT DELAYED statements are unsafe for statement-based replication, they caused the statement only to be logged in row format when the binary logging format was MIXED, but did not cause a warning to be generated when the binary logging format was STATEMENT. (Bug #45825)

  • Replication: When using MIXED binary logging format, statements containing a LIMIT clause and occurring in stored routines were not written to the log as row events. (Bug #45785)

  • Replication: When using statement-based replication, database-level character sets were not always honored by the replication SQL thread. This could cause data inserted on the master using LOAD DATA to be replicated using the wrong character set.


    This was not an issue when using row-based replication.

    (Bug #45516)

  • Replication: STOP SLAVE did not flush the relay log or the or files, which could lead to corruption if the server crashed. (Bug #44188)

  • Replication: Large transactions and statements could corrupt the binary log if the size of the cache (as set by max_binlog_cache_size) was not large enough to store the changes.

    Now, for transactions that do not fit into the cache, the statement is not logged, and the statement generates an error instead.

    For nontransactional changes that do not fit into the cache, the statement is also not logged—an incident event is logged after committing or rolling back any pending transaction, and the statement then raises an error.


    If a failure occurs before the incident event is written the binary log, the slave does not stop, and the master does not report any errors.

    (Bug #43929, Bug #11752675)

    References: See also Bug #37148, Bug #11748696, Bug #46166, Bug #11754544.

  • Replication: On Windows, RESET MASTER failed in the event of a missing binary log file rather than issuing a warning and completing the rest of the statement. (Bug #42150, Bug #42218)

  • Replication: Executing the sequence of statements RESET SLAVE, RESET MASTER, and FLUSH LOGS, when binary log or relay log files listed in the index file could not be found, could cause the server to crash. This could happen, for example, when these files had been moved or deleted manually. (Bug #41902)

  • Replication: MySQL creates binary logs in a numbered sequence, with a maximum possible 4294967295 concurrent log files, 4294967295 being the maximum value for an unsigned long integer. However, binary log file extensions were turned into negative numbers once the variable used to hold the value reached the maximum value for a signed long integer (2147483647). Consequently, when the sequence value was incremented to the next (negative) number, MySQL tried to create the file using a .000000 extension, causing the server to fail since this file already existed.

    Negative file extensions are no longer permitted, and an error is returned when the limit is reached. In addition, FLUSH LOGS now also reports warnings to the user, if the extension number has reached the limit, and warnings are printed to the error log when the limit is approaching. (Bug #40611)

  • Replication: Issuing concurrent STOP SLAVE, START SLAVE, and RESET SLAVE statements using different connections caused the replication slave to crash. (Bug #38716)

    References: See also Bug #38715, Bug #44312.

  • Replication: A slave compiled using --with-libevent and run with --thread-handling=pool-of-threads could sometimes crash. (Bug #36929)

  • Replication: mysqlbinlog sometimes failed when trying to create temporary files; this was because it ignored the specified temp file directory and tried to use the system /tmp directory instead. (Bug #35546)

    References: See also Bug #35543.

  • Replication: A CHANGE MASTER TO statement with no MASTER_HEARTBEAT_PERIOD option failed to reset the heartbeat period to its default value. (Bug #34686)

  • Replication: Formerly, only slaves that had been started with the --report-hosts option were visible in the output of SHOW SLAVE HOSTS. Now, all slaves that are registered with the master appear in SHOW SLAVE HOSTS output.

    As part of the fix for this issue, the Rpl_recovery_rank column, which had appeared in the output of SHOW SLAVE HOSTS in some MySQL releases, was removed because the corresponding server variable rpl_recovery_rank (now deprecated) was never actually used. (Bug #13963)

    References: See also Bug #21132, Bug #21869.

  • For an IPv6-enabled MySQL server, privileges specified using standard IPv4 addresses for hosts were not matched (only IPv4-mapped addresses were handled correctly).

    As part of the fix for this bug, a new build option --disable-ipv6 has been introduced. Compiling MySQL with this option causes all IPv6-specific code in the server to be ignored.


    If the server has been compiled using --disable-ipv6, it is not able to resolve hostnames correctly when run in an IPv6 environment.

    (Bug #11754062, Bug #45606)

    References: See also Bug #38247, Bug #43006, Bug #45584.

  • mysqld_safe did not pass the correct default value of plugin_dir to mysqld. (Bug #51938)

  • mysqld_multi failed due to a syntax error in the script. (Bug #51468)

  • ALTER TABLE on a MERGE table that has been locked using LOCK TABLES ... WRITE incorrectly produced an ER_TABLE_NOT_LOCKED_FOR_WRITE error. (Bug #51240)

  • The mysql could default to the ascii character set, which is not a valid character set choice for MySQL. The latin1 character set will now be used when an ASCII environment has been identified. (Bug #51166)

  • On some Unix/Linux platforms, an error during build from source could be produced, referring to a missing LT_INIT program. This is due to versions of libtool 2.1 and earlier. (Bug #51009)

  • Referring to a subquery result in a HAVING clause could produce incorrect results. (Bug #50995)

  • Aggregate functions on TIMESTAMP columns could yield incorrect or undefined results. (Bug #50888)

  • The optimizer normally prefers use of filesort plus the join cache to a full index scan. But this combination was used even if the index is clustered, in which case, the clustered index scan can be faster. (Bug #50843)

  • For debug builds, SHOW BINARY LOGS raised an assertion if binary logging was not enabled. (Bug #50780)

  • The server did not recognize that the stored procedure cache became invalid if a view was created or modified within a procedure, resulting in a crash. (Bug #50624)

  • Incorrect handling of BIT columns in temporary tables could lead to spurious duplicate-key errors. (Bug #50591)

  • The second or subsequent invocation of a stored procedure containing DROP TRIGGER could cause a server crash. (Bug #50423)

  • The return values for calls to put information into the stored routine cache were not consistently checked, raising an assertion. (Bug #50412)

  • Full-text queries that used the truncation operator (*) could enter an infinite loop. (Bug #50351)

  • For debug builds, an assertion was incorrectly raised in the optimizer when matching ORDER BY expressions. (Bug #50335)

  • Queries optimized with GROUP_MIN_MAX did not clean up KEYREAD optimizations properly, causing subsequent queries to return incomplete rows. (Bug #49902)

  • mysql --show-warnings crashed if the server connection was lost. (Bug #49646)

  • For string-valued system variables containing multibyte characters, the byte length was used in contexts where the character length was more appropriate. (Bug #49645)

  • SHOW VARIABLES did not correctly display string-valued system variables that contained \0 characters. (Bug #49644)

  • MySQL program option-processing code incorrectly displayed some options when printing ambiguous-option errors. (Bug #49640)

  • For dynamic format MyISAM tables containing LONGTEXT columns, a bulk INSERT ... ON DUPLICATE KEY UPDATE or bulk REPLACE could cause corruption. (Bug #49628)

  • Setting binlog_format to DEFAULT assigned a value different from the default. (Bug #49540)

  • For debug builds, with sql_safe_updates enabled, a multiple-table UPDATE with the IGNORE modifier could raise an assertion. (Bug #49534)

  • EXPLAIN EXTENDED crashed trying to print column names for a subquery in the FROM clause when the table had gone out of scope. (Bug #49487)

  • For InnoDB tables, the test for using an index for ORDER BY sorting did not distinguish between primary keys and secondary indexes and expected primary key values to be concatenated to index values the way they are to secondary key values. (Bug #49324)

  • mysqltest no longer permits you to execute an SQL statement on a connection after doing a send command, unless you do a reap first. This was previously accepted but could produce unpredictable results. (Bug #49269)

  • Valgrind warnings for several logging messages were corrected. (Bug #49130)

  • For debug builds on Windows, warnings about incorrect use of debugging directives were written to the error log. The directives were rewritten to eliminate these messages. (Bug #49025)

  • Plugins in a binary release could not be installed into a debug version of the server. (Bug #49022)

  • On POSIX systems, calls to select() with a file descriptor set larger than FD_SETSIZE resulted in unpredictable I/O errors; for example, when a large number of tables required repair. (Bug #48929)

  • A dependent subquery containing COUNT(DISTINCT col_name)) could be evaluated incorrectly. (Bug #48920)

  • If a stored function contained a RETURN statement with an ENUM value in the ucs2 character set, SHOW CREATE FUNCTION and SELECT DTD_IDENTIFIER FROM INFORMATION_SCHEMA.ROUTINES returned incorrect values. (Bug #48766)

  • An .ARZ file missing from the database directory caused the server to crash. (Bug #48757)

  • Running SHOW CREATE TABLE on a view v1 that contained a function which accessed another view v2 could trigger a infinite loop if the view referenced within the function (v2) caused a warning to be raised while being opened. (Bug #48449)

  • Invalid memory reads could occur following a query that referenced a MyISAM table multiple times with a write lock. (Bug #48438)

  • For debug builds, creating a view containing a row constructor raised an assertion. (Bug #48294)

  • An aliasing violation in the C API could lead to a crash. (Bug #48284)

  • Slow CALL statements were not always logged to the slow query log because execution time for multiple-statement stored procedures was assessed incorrectly. (Bug #47905)

  • For debug builds, killing a SELECT retrieving from a view that was processing a function raised an assertion. (Bug #47736)

  • Failure to open a view with a nonexistent DEFINER was improperly handled and the server crashed later attempting to lock the view. (Bug #47734)

  • If a prepared statement used both a MERGE table and a stored function or trigger, execution sometimes failed with a No such table error. (Bug #47648)

  • CREATE VIEW raised an assertion if a temporary table existed with the same name as the view. (Bug #47635)

  • Renaming a column of an InnoDB table caused the server to go out of sync with the InnoDB data dictionary. To avoid this issue, renaming a column uses the older technique of copying all the table data rather than updating the table in-place. (Bug #47621)

  • If a temporary table was created with the same name as a view referenced in a stored routine, routine execution could raise an assertion. (Bug #47313)

  • Selecting from the process list in the embedded server caused a crash. (Bug #47304)

    References: See also Bug #43733.

  • Programs did not exit if the option file specified by --defaults-file was not found. (Bug #47216)

  • Attempts to print octal numbers with my_vsnprintf() could cause a crash. (Bug #47212)

  • Corrected a potential problem of unintended file overwriting when the MY_DONT_OVERWRITE_FILE flag was used. (Bug #47126)

  • Deadlock occurred if one session was running a multiple-statement transaction that involved a single partitioned table and another session attempted to alter the table. (Bug #46654)

  • Valgrind warnings about memory allocation overruns for handling CREATE FUNCTION statements for UDFs were corrected. (Bug #46570)

  • The server could crash attempting to flush privileges after receipt of a SIGHUP signal. (Bug #46495)

  • If INSERT INTO tbl_name invoked a stored function that modified tbl_name, the server crashed. (Bug #46374)

  • HANDLER statements within a transaction that already holds metadata locks could lead to deadlocks.

    Before this fix, all handlers for TEMPORARY tables were reset whenever any base table was opened. (Bug #46224)

  • For queries that used GROUP_CONCAT(DISTINCT ...), the value of max_heap_table_size was used for memory allocation, which could be excessive. Now the minimum of max_heap_table_size and tmp_table_size is used. (Bug #46018)

  • Improperly closing tables when INSERT DELAYED needed to reopen tables could cause an assertion failure. (Bug #45949)

    References: See also Bug #18484.

  • Grouping by a subquery in a query with a DISTINCT aggregate function led to incorrect and unordered grouping values. (Bug #45640)

  • The hostname cache failed to work correctly. (Bug #45584)

    References: See also Bug #38247, Bug #43006, Bug #11753779, Bug #45606.

  • A Windows Installation using the GUI installer failed with:

    MySQL Server 5.1 Setup Wizard ended prematurely
    The wizard was interrupted before MySQL Server 5.1. could be completely installed.
    Your system has not been modified. To complete installation at another time, please run
    setup again.
    Click Finish to exit the wizard

    This was due to a step in the MSI installer that could fail to execute correctly on some environments. (Bug #45418)

  • Propagation of a large unsigned numeric constant in WHERE expressions could lead to incorrect results. This also affected EXPLAIN EXTENDED, which printed incorrect numeric constants in such transformed WHERE expressions. (Bug #45360)

  • There was no timeout for attempts to acquire metadata locks (for example, a DROP TABLE attempt for a table that was open in another transaction would not time out).

    To handle such situations, there is now a lock_wait_timeout system variable that specifies the timeout in seconds for attempts to acquire metadata locks. The permitted values range from 1 to 31536000 (1 year). The default is 31536000.

    This timeout applies to all statements that use metadata locks. These include DML and DDL operations on tables, views, stored procedures, and stored functions, as well as LOCK TABLES, FLUSH TABLES WITH READ LOCK, and HANDLER statements.

    The timeout value applies separately for each metadata lock attempt. A given statement can require more than one lock, so it is possible for the statement to block for longer than the lock_wait_timeout value before reporting a timeout error. When lock timeout occurs, ER_LOCK_WAIT_TIMEOUT is reported.

    lock_wait_timeout does not apply to delayed inserts, which always execute with a timeout of 1 year. This is done to avoid unnecessary timeouts because a session that issues a delayed insert receives no notification of delayed insert timeouts.

    In addition: The unused table_lock_wait_timeout system variable was removed. The LOW_PRIORITY modifier for LOCK TABLES ... WRITE locks now has no effect. The meaning of LOW_PRIORITY remains as before in other contexts, such as for INSERT or DELETE statements. innodb_table_locks=0 no longer has an effect for tables locked explicitly with LOCK TABLES ... WRITE. It still has an effect for tables locked for read or write by LOCK TABLES ... WRITE implicitly (for example, through triggers) or by LOCK TABLES ... READ. (Bug #45225, Bug #56272)

  • Valgrind warnings about uninitialized variables in optimizer code were corrected. (Bug #45195)

  • Killing a delayed-insert thread could cause a server crash. (Bug #45067)

  • Execution of FLUSH TABLES or FLUSH TABLES WITH READ LOCK concurrently with LOCK TABLES resulted in deadlock. (Bug #45066)

  • The mysql_real_connect() C API function only attempted to connect to the first IP address returned for a hostname. This could be a problem if a hostname mapped to multiple IP address and the server was not bound to the first one returned. Now mysql_real_connect() attempts to connect to all IPv4 or IPv6 addresses that a domain name maps to. (Bug #45017)

    References: See also Bug #47757.

  • For plugins that did not have command-line options other than the ones to select the plugin itself, those options were not displayed in the mysqld help message. (Bug #44797)

  • Some plugins configured as mandatory could be disabled at server startup. (Bug #44691)

  • InnoDB took a shared row lock when executing SELECT statements inside a stored function as a part of a transaction using REPEATABLE READ. This prevented other transactions from updating the row. (Bug #44613)

  • MySQL Server permitted the creation of a merge table based on views but crashed when attempts were made to read from that table. The following example demonstrates this:

    #Create a test table
    CREATE TABLE tmp (id int, c char(2));                                           
    #Create two VIEWs upon it
    CREATE VIEW v1 AS SELECT * FROM tmp;                                            
    CREATE VIEW v2 AS SELECT * FROM tmp;                                            
    #Finally create a MERGE table upon the VIEWs
    CREATE TABLE merge (id int, c char(2))
    #Reading from the merge table lead to a crash
    SELECT * FROM merge;

    The final statement generated the crash. (Bug #44040)

  • A natural join of INFORMATION_SCHEMA tables could cause an assertion failure. (Bug #43834)

  • When used in conjunction with LOCK TABLES, FLUSH TABLE tbl_list waited for all tables with old versions to clear from the table definition list, rather than only the named tables. (Bug #43685)

  • HANDLER statements are now not permitted if a table lock has been acquired with LOCK TABLES. (Bug #43272)

  • In the embedded server, stack overflow checks for recursive stored procedure calls did not work and stack overflow could occur. (Bug #43201)

  • The server could crash if an attempt to open a MERGE table child MyISAM table failed. (Bug #42862)

  • Sign loss could occur in several contexts:

    • SEC_TO_TIME() could lose the sign of negative arguments.

    • MAKETIME() could lose the sign of negative arguments.

    • Comparison of TIME values could lose the sign of operands.

    (Bug #42661, Bug #42662, Bug #42664)

  • Setting key_buffer_size to a negative value could lead to very large allocations. Now an error occurs. (Bug #42103)

  • An assertion failure could occur if OPTIMIZE TABLE was started on an InnoDB table and the table was altered to a different storage engine during the optimization operation. (Bug #42074)

  • The state of a thread for the embedded server was always displayed as Writing to net, which is incorrect because there is no network connection for the embedded server. (Bug #41971)

  • The patch for Bug #10374 broke named-pipe and shared-memory connections on Windows. (Bug #41860)

  • Purging the stored-routine cache could take a long time and render the server unresponsive. (Bug #41804)

  • Command-line options for enumeration-type plugin variables were not honored. (Bug #41010)

  • System variables could be set to invalid values. (Bug #40988)

  • The CSV storage engine did not parse '\X' characters when they occurred in unquoted fields. (Bug #40814)

  • When archive tables were joined on their primary keys, a query returned no result if the optimizer chose to use this index. (Bug #40677)

  • mysqld_safe did not treat dashes and underscores as equivalent in option names. Thanks to Erik Ljungstrom for the patch to fix this bug. (Bug #40368)

  • SHOW CREATE VIEW returned invalid SQL if the definition contained a SELECT 'string' statement where the string was longer than the maximum length of a column name, due to the fact that this text was also used as an alias (in the AS clause).

    Because not all names retrieved from arbitrary SELECT statements can be used as view column names due to length and format restrictions, the server now checks the conformity of automatically generated column names and rewrites according to a predefined format any names that are not acceptable as view column names before storing the final view definition on disk.

    In such cases, the name is now rewritten as Name_exp_pos, where pos is the position of the column. To avoid this conversion scheme, define explicit, valid names for view columns using the column_list clause of the CREATE VIEW statement.

    As part of this fix, aliases are now generated only for top-level statements. (Bug #40277)

  • Threads were set to the Table lock state in such a way that use of this state by other threads to check for a lock wait was subject to a race condition. (Bug #39897)

  • Plugin shutdown could lead to an assertion failure caused by using an already destroyed mutex in the metadata locking subsystem. (Bug #39674)

  • Dropping a locked Maria table leads to an assertion failure. (Bug #39395)

  • Host name lookup failure could lead to a server crash. (Bug #39153)

  • flush_cache_records() did not correctly check for errors that should cause statement execution to stop, leading to a server crash. (Bug #39022)

  • InnoDB logged an error repeatedly trying to load a page into the buffer pool, filling the error log and using excessive disk space. Now the number of attempts is limited to 100, after which the operation aborts with a message. (Bug #38901)

  • Valgrind warnings that occurred for SHOW TABLE STATUS with InnoDB tables were silenced. (Bug #38479)

  • An IPv6-enabled MySQL server did not resolve the IP addresses of incoming connections correctly, with the result that a connection that attempted to match any privilege table entries using fully qualified domain names for hostnames or hostnames using wildcards were dropped. (Bug #38247)

    References: See also Bug #43006, Bug #11753779, Bug #45584, Bug #45606.

  • For CREATE TABLE ... LIKE with a MERGE source table that included a UNION clause, that clause was omitted from the definition of the destination table. (Bug #37371)

  • Previously, statements inside a stored program did not clear the warning list. For example, warnings or errors generated by statements within a trigger or stored function would be accumulated and added to the message list for the statement that activated the trigger or invoked the function, polluting the output of SHOW WARNINGS or SHOW ERRORS for the outer statement. Normally, messages for a statement that can generate messages replace messages from the previous such statement. The effect was that a statement could have a different effect on the message list depending on whether it executed inside or outside of a stored program.

    Now within a stored program, successive statements that can generate messages update the message list and replace messages from the previous such statement. Only messages from the last of these statements is copied to the message list for the outer statement. (Bug #36649)

  • myisampack --join did not create the destination table .frm file. (Bug #36573)

  • The parser incorrectly permitted MySQL error code 0 to be specified for a condition handler. (This is incorrect because the condition must be a failure condition and 0 indicates success.) (Bug #36510)

  • When parsing or formatting interval values of DAY_MICROSECOND type, fractional seconds were not handled correctly when more-significant fields were implied or omitted. (Bug #36466)

  • mysql_install_db failed if run as root and the root directory (/) was not writable. (Bug #36462)

  • mysql_stmt_prepare() did not reset the list of messages (those messages available using SHOW WARNINGS). (Bug #36004)

  • A global read lock obtained with FLUSH TABLES WITH READ LOCK did not prevent sessions from creating tables. (Bug #35935)

  • mysqlbinlog left temporary files on the disk after shutdown, leading to the pollution of the temporary directory, which eventually caused mysqlbinlog to fail. This caused problems in testing and other situations where mysqlbinlog might be invoked many times in a relatively short period of time. (Bug #35543)

  • When building MySQL when using a different target directory (for example using the VPATH environment variable), the build of the embedded readline component failed. (Bug #35250)

  • String-valued system variables could be assigned literal values, but could not be assigned values using expressions. Now expressions are legal. (Bug #34883, Bug #46314)

  • The sql_mode system variable could be assigned the illegal value of '?'. (Bug #34834)

  • Compiling MySQL on FreeBSD failed due to missing definitions for certain network constants. (Bug #34292)

  • Creation of a temporary BLOB or TEXT column could create a column with the wrong maximum length. (Bug #33969)

  • INSERT INTO ... VALUES(DEFAULT) failed to insert the correct value for ENUM columns. For MyISAM tables, an empty value was inserted. For CSV tables, the table became corrupt. (Bug #33717)

  • When read_only was enabled, the server incorrectly prevented data modifications to TEMPORARY tables belonging to transactional storage engines such as InnoDB. (Bug #33669)

  • Constant expressions in WHERE, HAVING, or ON clauses were not cached, but were evaluated for each row. This caused a slowdown of query execution, especially if constant user-defined functions or stored functions were used. (Bug #33546)

  • Plugins could find the unqualified form of their system variables but not the qualified form. For example, a plugin p with a system variable sv could find sv but not p_sv. (Bug #32902)

  • Killing a statement that invoked a stored function could return an incorrect error message indicating table corruption rather than that the statement had been interrupted. (Bug #32140)

  • Occurrence of an error within a stored routine did not always cause immediate statement termination. (Bug #31881)

  • For DROP FUNCTION db_name.func_name (that is, when the function name is qualified with the database name), the statement should apply only to a stored function named func_name in the given database. However, if a UDF with the same name existed, the statement dropped the UDF instead. (Bug #31767)

  • mysqld sometimes miscalculated the number of digits required when storing a floating-point number in a CHAR column. This caused the value to be truncated, or (when using a debug build) caused the server to crash. (Bug #26788)

    References: See also Bug #12860.

  • ALTER TABLE could not be used to add columns to a table if the table had an index on a utf8 column with a TEXT data type. (Bug #26180)

  • If an operation had an InnoDB table, and two triggers, AFTER UPDATE and AFTER INSERT, competing for different resources (such as two distinct MyISAM tables), the triggers were unable to execute concurrently. In addition, INSERT and UPDATE statements for the InnoDB table were unable to run concurrently. (Bug #26141)

  • Statements to create, alter, or drop a view were not waiting for completion of statements that were using the view, which led to incorrect sequences of statements in the binary log when statement-based logging was enabled. (Bug #25144)

  • Previously, the server handled character data types for a stored routine parameter, local routine variable created with DECLARE, or stored function return value as follows: If the CHARACTER SET attribute was present, the COLLATE attribute was not supported, so the character set's default collation was used. (This includes use of BINARY, which in this context specifies the binary collation of the character set.) If there was no CHARACTER SET attribute, the database character set and its default collation were used.

    Now for character data types, if there is a CHARACTER SET attribute in the declaration, the specified character set and its default collation is used. If the COLLATE is also present, that collation is used rather than the default collation. If there is no CHARACTER SET attribute, the database character set and collation in effect at routine creation time are used. (The database character set and collation are given by the value of the character_set_database and collation_database system variables.) (Bug #24690)

  • Data truncated for column col_num at row row_num warnings were generated for some (constant) values that did not have too high precision. (Bug #24541)

  • A statement that caused a circular wait among statements did not return a deadlock error. Now the server detects deadlock and returns ER_LOCK_DEADLOCK. (Bug #22876)

  • CREATE TABLE ... LIKE did not always produce an error is the source table column defaults were illegal for the current version of MySQL. (This could occur if the table was created using an older server that was less restrictive about legal default values.) (Bug #22090)

  • Several data-modification statements were not being counted toward the MAX_UPDATES_PER_HOUR user resource limit. (Bug #21793)

  • When inserting an extraordinarly large value into a DOUBLE column, the value could be truncated in such a way that the new value cannot be reloaded manually or from the output of mysqldump. (Bug #21497)

  • The value of sql_slave_skip_counter was empty when displayed by SHOW VARIABLES or INFORMATION_SCHEMA.GLOBAL_VARIABLES. (Bug #20413, Bug #37187)

  • For INSERT DELAYED statements issued for a table while an ALTER TABLE operation on the table was in progress, the server could return a spurious Server shutdown in progress error. (Bug #18484)

    References: See also Bug #45949.

  • Delayed-insert threads were counted as connected but not as created, incorrectly leading to a Threads_connected value greater than the Threads_created value. (Bug #17954)

  • The character set was not being properly initialized for CAST() with a type such as CHAR(2) BINARY, which resulted in incorrect results or a server crash. (Bug #17903)

  • Stored procedure exception handlers were catching fatal errors (such as out of memory errors), which could cause execution not to stop to due a continue handler. Now fatal errors are not caught by exception handlers and a fatal error is returned to the client. (Bug #15192)

  • Zero-padding of exponent values was not the same across platforms. (Bug #12860)

  • For CREATE TABLE, the parser did not enforce that parentheses were present in a CHECK (expr) clause; now it does. The parser did not enforce that CONSTRAINT [symbol] without a following CHECK clause was illegal; now it does. (Bug #11714, Bug #35578, Bug #38696)

  • If a connection was waiting for a GET_LOCK() lock or a SLEEP() call, and the connection aborted, the server did not detect this and thus did not close the connection. This caused a waste of system resources allocated to dead connections. Now the server checks such a connection every five seconds to see whether it has been aborted. If so, the connection is killed (and any lock request is aborted). (Bug #10374)

  • perror did not work for errors described in the sql/share/errmsg.txt file. (Bug #10143)

  • The grammar for GROUP BY, when used with WITH CUBE or WITH ROLLUP, caused a conflict with the grammar for view definitions that included WITH CHECK OPTION. (Bug #9801)

  • For the DIV operator, incorrect results could occur for noninteger operands that exceed BIGINT range. Now, if either operand has a noninteger type, the operands are converted to DECIMAL and divided using DECIMAL arithmetic before converting the result to BIGINT. If the result exceeds BIGINT range, an error occurs. (Bug #8457, Bug #11745058)

    References: See also Bug #59241.

  • Labels in stored routines did not work if the character set was not latin1. (Bug #7088)

  • Previously, for some Asian CJK character sets, the UPPER() and LOWER() functions worked only for basic Latin letters (A-Z, a-z). The affected character sets are ujis, sjis, gb2312, cp932, eucjpms, big5, euckr, and gbk.

    Now UPPER() and LOWER() perform case conversion correctly for all characters in these character sets, with the exception that if a character set contains a character in only one lettercase, conversion to the other lettercase cannot be done.

Download these Release Notes
PDF (US Ltr) - 1.2Mb
PDF (A4) - 1.2Mb
EPUB - 317.0Kb