Skip navigation links
5.4 Key changes in MySQL 5.5 releases »
Section Navigation      [Toggle]

5.3.2. Changes in MySQL 5.4.2 (01 September 2009)

Functionality added or changed:

  • Incompatible Change: The inclusion of InnoDB Plugin as the built-in version of InnoDB in MySQL Server results in an incompatibility for upgrades from MySQL 5.1. InnoDB Plugin is 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.4 or the server will fail to start.

  • Incompatible Change: If you upgrade to MySQL 5.4.2 from MySQL 5.1, the server may fail to start due to InnoDB log file configuration changes. MySQL 5.4 increases the default value of innodb_log_files_in_group from 2 to 3. It also increases the default value of innodb_log_file_size from 5MB to 128MB. (MySQL 5.4.0 increased the minimum value of innodb_log_file_size from 1MB to 32MB, but this has been reverted to 1MB in 5.4.2.)

    For an upgrade to MySQL 5.4.2, you should discard the old InnoDB log files and let MySQL 5.4.2 create new ones. It is necessary to shut down your current server cleanly so that no outstanding transaction information remains in the log. The procedure to use depends on the value of innodb_fast_shutdown used in your current server:

    • If innodb_fast_shutdown is not set to 2: Stop your current MySQL server and make sure that it shuts down without errors (to ensure that there is no information for outstanding transactions in the log). Copy the old log files into a safe place in case something went wrong during the shutdown and you need them to recover the tablespace. Delete the old log files and edit my.cnf if necessary to specify the desired log file configuration. Then upgrade to MySQL 5.4.2 and start the new server. mysqld sees that no InnoDB log files exist at startup and creates new ones.

    • If innodb_fast_shutdown is set to 2: Shut down your current server, set innodb_fast_shutdown to 1, and restart the server. The server should be allowed to recover. Then you should shut down the server again and follow the procedure described in the preceding item to change InnoDB log file size. Set innodb_fast_shutdown back to 2 and start the MySQL 5.4.2 server.

  • Incompatible Change: The following list indicates InnoDB configuration changes in MySQL 5.4.2, compared to previous versions of MySQL 5.4. For a description of cumulative changes in MySQL 5.4 compared to MySQL 5.1, see MySQL 4.1 in a Nutshell.

    These system variables were added:

    • innodb_adaptive_flushing: Controls adaptive flushing of dirty pages.

    • innodb_change_buffering: Controls insert buffering.

    • innodb_file_format: The format for new InnoDB tables.

    • innodb_file_format_check: Whether to perform file format compatibility checking.

    • innodb_read_ahead_threshold: Controls sensitivity of linear read-ahead.

    • innodb_replication_delay: The replication thread delay (in ms) on a slave server if innodb_thread_concurrency is reached.

    • innodb_spin_wait_delay: Maximum delay between polls for a spin lock.

    • innodb_stats_sample_pages: How many index pages to sample for statistics calculations.

    • innodb_strict_mode: Whether InnoDB returns errors rather than warnings for certain exceptional conditions (analogous to strict SQL mode).

    • innodb_use_sys_malloc: Whether InnoDB uses the OS (system) or its own memory allocator.

    • innodb_version: The current version of InnoDB.

    More information about the new system variables can be found in the InnoDB Plugin Manual at http://www.innodb.com/products/innodb_plugin/plugin-documentation.

    These system variables have been made dynamic and can be modified at runtime:

    • innodb_adaptive_hash_index (global value)

    • innodb_file_per_table (global value)

    • innodb_io_capacity (global value)

    • innodb_lock_wait_timeout (global and session values)

    These system variables were removed, along with the functionality that they represented:

    • innodb_extra_dirty_writes

    • innodb_max_merged_io

    • innodb_thread_concurrency_timer_based

    The minimum value of these system variables was changed:

    Variable Change in minimum value
    innodb_additional_mem_pool_size From 2MB to 512KB
    innodb_buffer_pool_size From 64MB to 5MB
    innodb_log_buffer_size From 2MB to 256KB
    innodb_log_file_size From 32MB to 1MB

    These minimum value changes in most cases revert changes made in MySQL 5.4.0.

    The default value of this system variable was changed:

    Variable Change in default value
    innodb_sync_spin_loops From 20 to 30

    These status variables were renamed or removed:

    • Innodb_have_sync_atomic: renamed to Innodb_have_atomic_builtins

    • Innodb_heap_enabled: Removed

    • Innodb_wake_ups: Removed

Bugs fixed:

  • Partitioning: Security Fix: Accessing a table having user-defined partitioning when the server SQL mode included ONLY_FULL_GROUP_BY caused the MySQL server to crash. For example, the following sequence of statements crashed the server: 

    DROP TABLE IF EXISTS t1;

SET SESSION SQL_MODE='ONLY_FULL_GROUP_BY';

CREATE TABLE t1 (id INT, KEY(id))
    PARTITION BY HASH(id) PARTITIONS 2;

    (Bug#45807)

  • Security Fix: The strxnmov() library function could write a null byte after the end of the destination buffer. (Bug#44834)

  • Security Fix: Four potential format string vulnerabilities were fixed (discovered by the Veracode code analysis). (Bug#44166)

  • Incompatible Change: The server can load plugins under the control of startup options. For example, many storage engines can be built in pluggable form and loaded when the server starts. In the following descriptions, plugin_name stands for a plugin name such as innodb.

    Previously, plugin options were handled like other boolean options (see Program Option Modifiers). That is, any of these options enabled the plugin: 

    --plugin_name
--plugin_name=1
--enable-plugin_name

    And these options disabled the plugin: 

    --plugin_name=0
--disable-plugin_name
--skip-plugin_name

    However, use of a boolean option for plugin loading did not provide control over what to do if the plugin failed to start properly: Should the server exit, or start with the plugin disabled? The actual behavior has been that the server starts with the plugin disabled, which can be problematic. For example, if InnoDB fails to start, existing InnoDB tables become inaccessible, and attempts to create new InnoDB tables result in tables that use the default storage engine unless the NO_ENGINE_SUBSTITUTION SQL mode has been enabled to cause an error to occur instead.

    Now, there is a change in the options used to control plugin loading, such that they have a tristate format:

    • --plugin_name=OFF

      Do not enable the plugin.

    • --plugin_name[=ON]

      Enable the plugin. If plugin initialization fails, start the server anyway, but with the plugin disabled. Specifying the option as --plugin_name without a value also enables the plugin.

    • --plugin_name=FORCE

      Enable the plugin. If plugin initialization fails, do not start the server. In other words, force the server to run with the plugin or not at all.

    The values OFF, ON, and FORCE are not case sensitive.

    Suppose that CSV and InnoDB have been built as pluggable storage engines and that you want the server to load them at startup, subject to these conditions: The server is allowed to run if CSV initialization fails, but must require that InnoDB initialization succeed. To accomplish that, use these lines in an option file: 

    [mysqld]
csv=ON
innodb=FORCE

    This change is incompatible with the previous implementation if you used options of the form --plugin_name=0 or --plugin_name=1, which should be changed to --plugin_name=OFF or --plugin_name=ON, respectively.

    --enable-plugin_name is still supported and is the same as --plugin_name=ON. --disable-plugin_name and --skip-plugin_name are still supported and are the same as --plugin_name=OFF. (Bug#19027)

    See also Bug#45336.