MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Joerg Bruehe Date:December 15 2009 10:34pm
Subject:MySQL 5.5.0 has been released
View as plain text  
Dear MySQL users,

MySQL Server 5.5.0-m2, a new version of the popular Open Source
Database Management System, has been released.

The "-m2" suffix tells this is the second milestone according to our
"milestone" release model, also called "Betony".
You can read more about the release model and the planned milestones at

The new features in this release are of beta quality. As with any
other pre-production release, caution should be taken when installing on
production level systems or systems with critical data.
For production level systems using 5.1, we would like to direct your
attention to the product description of MySQL Enterprise at:

MySQL 5.5 is based on MySQL 5.4, which won't get any further updates.
So MySQL 5.5 includes several high-impact changes to address scalability
and performance issues in MySQL Server. These changes exploit advances
in hardware and CPU design and enable better utilization of existing

For an overview of what's new in MySQL 5.5, please see the
section "What Is New in MySQL 5.5" below, or view it online at

For information on installing MySQL 5.5.0 on new servers,
please see the MySQL installation documentation at

For upgrading from previous MySQL releases, please see the
important upgrade considerations at

Some other pointers you might like to follow are

MySQL Server is available in source and binary form for a
number of platforms from our download pages at

Not all mirror sites may be up to date at this point in
time, so if you can't find this version on some mirror,
please try again later or choose another download site.

We welcome and appreciate your feedback, bug reports, bug
fixes, patches, etc.:

Following the "What Is New" section, this mail lists the important
changes in the MySQL source code of MySQL 5.5.0.
The complete list of all "Bugs Fixed" is not included, because it would
exceed the length restrictions imposed on these mailing lists.
It may be viewed online at


On behalf of the MySQL Build Team at Sun Microsystems:
Jörg Brühe,
Senior Production Engineer


What Is New in MySQL 5.5

The following features have been added to MySQL 5.5:

 * Support for an interface for semisynchronous replication:
   A commit performed on the master side blocks before returning
   to the session that performed the transaction until at least
   one slave acknowledges that it has received and logged the events
   for the transaction.
   Semisynchronous replication is implemented through an optional
   plugin component. See Section 16.2.8, "Semisynchronous Replication"

 * Support for the SQL standard SIGNAL and RESIGNAL statements.
   See Section 12.8.8, "SIGNAL and RESIGNAL".

 * Enhancements to XML functionality, including a new LOAD XML

 * Two new types of user-defined partitioning:
   RANGE COLUMNS partitioning is an extension to RANGE partitioning;
   LIST COLUMNS partitioning is an extension to LIST partitioning.
   Each of these extensions provides two enhancements to MySQL
   partitioning capabilities:

   1. It is possible to define partitioning ranges or lists based on
      DATE, DATETIME, or string values (such as CHAR or VARCHAR).

      You can also define ranges or lists based on multiple column
      values when partitioning tables by RANGE COLUMNS or LIST COLUMNS,
      respectively. Such a range or list may refer to up to 16 columns.

   2. For tables defined using these partitioning types, partition
      pruning can now optimize queries with WHERE conditions that use
      multiple comparisons between (different) column values and
      constants, such as
          a = 10 AND b > 5 or a < "2005-11-25" AND b = 10 AND c = 50.

      For more information, see Section 17.2.1, "RANGE Partitioning",
      and Section 17.2.2, "LIST Partitioning".

 * It is now possible to delete all rows from one or more partitions
   of a partitioned table using the ALTER TABLE ... TRUNCATE
   PARTITION statement. Executing the statement deletes rows without
   affecting the structure of the table. The partitions named in the
   TRUNCATE PARTITION clause do not have to be contiguous.

 * Key caches are now supported for indexes on partitioned MyISAM
   tables, using the CACHE INDEX and LOAD INDEX INTO CACHE statements.
   In addition, a key cache can be defined for and loaded with indexes
   from an entire partitioned table, or for one or more partitions.

 * The TO_SECONDS() function is added. You may use this function in
   partitioning expressions, and partition pruning is supported for
   table defined using such expressions.

The following constructs are deprecated and will be removed in a future
MySQL release. Where alternatives are shown, applications should be
updated to use them.

 * The table_type system variable (use storage_engine).

   The TYPE table option to specify the storage engine for


 * The log_bin_trust_routine_creators variable
   (use log_bin_trust_function_creators).

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

   (use SHOW ENGINE INNODB STATUS for both of these).


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

 * The BACKUP TABLE and the RESTORE TABLE SQL statements.

 * 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,


Changes in MySQL 5.5.0

InnoDB Plugin Notes:

  * The InnoDB Plugin is included in MySQL 5.5 releases as the
    built-in version of InnoDB. The version of the InnoDB Plugin
    in this release is 1.0.5 and is considered of Release
    Candidate (RC) quality.
    The InnoDB Plugin offers new features, improved performance
    and scalability, enhanced reliability and new capabilities for
    flexibility and ease of use. Among the features of the InnoDB
    Plugin are "Fast index creation," table and index compression,
    file format management, new INFORMATION_SCHEMA tables,
    capacity tuning, multiple background I/O threads, and group
    For information about these features, see the InnoDB Plugin
    Manual at
    For general information about using InnoDB in MySQL, see
    Section 13.6, "The InnoDB Storage Engine."

Functionality added or changed:

  * Incompatible Change: A change has been made to the way that
    the server handles prepared statements. This affects prepared
    statements processed at the SQL level (using the PREPARE
    statement) and those processed using the binary client-server
    protocol (using the mysql_stmt_prepare() C API function).
    Previously, changes to metadata of tables or views referred to
    in a prepared statement could cause a server crash when the
    statement was next executed, or perhaps an error at execute
    time with a crash occurring later. For example, this could
    happen after dropping a table and recreating it with a
    different definition.
    Now metadata changes to tables or views referred to by
    prepared statements are detected and cause automatic
    repreparation of the statement when it is next executed.
    Metadata changes occur for DDL statements such as those that
    create, drop, alter, rename, or truncate tables, or that
    analyze, optimize, or repair tables. Repreparation also occurs
    after referenced tables or views are flushed from the table
    definition cache, either implicitly to make room for new
    entries in the cache, or explicitly due to FLUSH TABLES.
    Repreparation is automatic, but to the extent that it occurs,
    performance of prepared statements is diminished.
    Table content changes (for example, with INSERT or UPDATE) do
    not cause repreparation, nor do SELECT statements.
    An incompatibility with previous versions of MySQL is that a
    prepared statement may now return a different set of columns
    or different column types from one execution to the next. For
    example, if the prepared statement is SELECT * FROM t1,
    altering t1 to contain a different number of columns causes
    the next execution to return a number of columns different
    from the previous execution.
    Older versions of the client library cannot handle this change
    in behavior. For applications that use prepared statements
    with the new server, an upgrade to the new client library is
    strongly recommended.
    Along with this change to statement repreparation, the default
    value of the table_definition_cache system variable has been
    increased from 128 to 256. The purpose of this increase is to
    lessen the chance that prepared statements will need
    repreparation due to referred-to tables/views having been
    flushed from the cache to make room for new entries.
    A new status variable, Com_stmt_reprepare, has been introduced
    to track the number of repreparations.

  * Incompatible Change: Several changes have been made regarding
    the language and character set of error messages:

       + The --language option for specifying the directory for
         the error message file is now deprecated. The new
         --lc-messages-dir and --lc-messages options should be
         used instead, and --language is handled as an alias for

       + The language system variable has been removed and
         replaced with the new lc_messages_dir and lc_messages
         system variables. lc_messages_dir has only a global value
         and is read only. lc_messages has global and session
         values and can be modified at runtime, so the error
         message language can be changed while the server is
         running, and individual clients each can have a different
         error message language by changing their session
         lc_messages value to a different locale name.

       + Error messages previously were constructed in a mix of
         character sets. This issue is resolved by constructing
         error messages internally within the server using UTF-8
         and returning them to the client in the character set
         specified by the character_set_results system variable.
         The content of error messages therefore may in some cases
         differ from the messages returned previously.

    For more information, see Section 9.3, "Setting the Error
    Message Language," and Section 9.1.6, "Character Set for Error
    See also Bug#46218:,

  * Partitioning: New PARTITION BY RANGE COLUMNS(column_list) and
    PARTITION BY LIST COLUMNS(column_list) options are added for
    the CREATE TABLE and ALTER TABLE statements.
    A major benefit of RANGE COLUMNS and LIST COLUMNS partitioning
    is that they make it possible to define ranges or lists based
    on column values that use string, date, or datetime values.
    These new extensions also broaden the scope of partition
    pruning to provide better coverage for queries using
    comparisons on multiple columns in the WHERE clause, some
    examples being WHERE a = 1 AND b < 10 and WHERE a = 1 AND b =
    10 AND c < 10.
    For more information, see Section 17.2.1, "RANGE
    Partitioning," Section 17.2.2, "LIST Partitioning," and
    Section 17.4, "Partition Pruning."

  * Partitioning: A new ALTER TABLE option, TRUNCATE PARTITION,
    makes it possible to delete rows from one or more selected
    partitions only. Unlike the case with ALTER TABLE ... DROP
    all rows from the specified partition or partitions, and does
    not change the definition of the table.

  * Partitioning: It is now possible to assign indexes on
    partitioned MyISAM tables to key caches using the CACHE INDEX
    and to preload such indexes into the cache using LOAD INDEX
    INTO CACHE statements. Cache assignment and preloading of
    indexes for such tables can be performed for one, several, or
    all partitions of the table.
    This functionality is supported for only those partitioned
    tables that employ the MyISAM storage engine.

  * Cluster Replication: Replication: A replication heartbeat
    mechanism has been added to facilitate monitoring. This
    provides an alternative to checking log files, making it
    possible to detect in real time when a slave has failed.
    Configuration of heartbeats is done via a new
    MASTER_HEARTBEAT_PERIOD = interval clause for the CHANGE
    MASTER TO statement (see Section, "CHANGE MASTER TO
    Syntax"); monitoring can be done by checking the values of the
    status variables Slave_heartbeat_period and
    Slave_received_heartbeats (see Section 5.1.7, "Server Status
    The addition of replication heartbeats addresses a number of

       + Relay logs were rotated every slave_net_timeout seconds
         even if no statements were being replicated.

       + SHOW SLAVE STATUS displayed an incorrect value for
         Seconds_Behind_Master following a FLUSH LOGS statement.

       + Replication master-slave connections used
         slave_net_timeout for connection timeouts.


  * Replication: The global server variable sync_relay_log is
    introduced for use on replication slaves. Setting this
    variable to a nonzero integer value N causes the slave to
    synchronize the relay log to disk after every N events. Setting
    its value to 0 allows the operating system to handle
    synchronization of the file. The action of this variable, when
    enabled, is analogous to how the sync_binlog variable works
    with regard to binary logs on a replication master.
    The global server variables sync_master_info and
    sync_relay_log_info are introduced for use on replication
    slaves to control synchronization of, respectively, the and files.
    In each case, setting the variable to a nonzero integer value
    N causes the slave to synchronize the corresponding file to
    disk after every N events. Setting its value to 0 allows the
    operating system to handle synchronization of the file instead.
    The actions of these variables, when enabled, are analogous to
    how the sync_binlog variable works with regard to binary logs
    on a replication master.
    An additional system variable relay_log_recovery is also now
    available. When enabled, this variable causes a replication
    slave to discard relay log files obtained from the replication
    master following a crash.
    These variables can also be set in my.cnf, or by using the
    --sync-relay-log, --sync-master-info, --sync-relay-log-info,
    and --relay-log-recovery server options.
    For more information, see Section, "Replication Slave
    Options and Variables."

  * Replication: Because SHOW BINLOG EVENTS cannot be used to read
    events from relay log files, a new SHOW RELAYLOG EVENTS
    statement has been added for this purpose.

  * Replication: In circular replication, it was sometimes
    possible for an event to propagate such that it would be
    reapplied on all servers. This could occur when the
    originating server was removed from the replication circle and
    so could no longer act as the terminator of its own events, as
    normally happens in circular replication.
    In order to prevent this from occurring, a new
    IGNORE_SERVER_IDS option is introduced for the CHANGE MASTER
    TO statement. This option takes a list of replication server
    IDs; events having a server ID which appears in this list are
    ignored and not applied. For more information, see Section, "CHANGE MASTER TO Syntax."
    In conjunction with the introduction of IGNORE_SERVER_IDS,
    SHOW SLAVE STATUS has a new field Replicate_Ignore_Server_Ids
    that displays information about ignored servers.
    See also Bug#27808:

  * With semisynchronous replication, for each transaction, the
    master waits until timeout for acknowledgement of receipt from
    some semisynchronous slave. If no response occurs during this
    period, the master reverts to normal replication. A new system
    variable, rpl_semi_sync_master_wait_no_slave, controls whether
    the master waits for the timeout to expire before reverting to
    normal replication even if the slave count drops to zero
    during the timeout period.
    If the value is ON (the default), it is allowable for the
    slave count to drop to zero during the timeout period (for
    example, if slaves disconnect). The master still waits for the
    timeout, so as long as some slave reconnects and acknowledges
    the transaction within the timeout interval, semisynchronous
    replication continues.
    If the value is OFF, the master reverts to normal replication
    if the slave count drops to zero during the timeout period.

  * The InnoDB buffer pool is divided into two sublists: A new
    sublist containing blocks that are heavily used by queries,
    and an old sublist containing less-used blocks and from which
    candidates for eviction are taken. In the default operation of
    the buffer pool, a block when read in is loaded at the
    midpoint and then moved immediately to the head of the new
    sublist as soon as an access occurs. In the case of a table
    scan (such as performed for a mysqldump operation), each block
    read by the scan ends up moving to the head of the new sublist
    because multiple rows are accessed from each block. This
    occurs even for a one-time scan, where the blocks are not
    otherwise used by other queries. Blocks may also be loaded by
    the read-ahead background thread and then moved to the head of
    the new sublist by a single access. These effects can be
    disadvantageous because they push blocks that are in heavy use
    by other queries out of the new sublist to the old sublist
    where they become subject to eviction.
    InnoDB Plugin now provides two system variables that enable
    LRU algorithm tuning:

       + innodb_old_blocks_pct
         Specifies the approximate percentage of the buffer pool
         used for the old block sublist. The range of values is 5
         to 95. The default value is 37 (that is, 3/8 of the

       + innodb_old_blocks_time
         Specifies how long in milliseconds (ms) a block inserted
         into the old sublist must stay there after its first
         access before it can be moved to the new sublist. The
         default value is 0: A block inserted into the old sublist
         moves immediately to the new sublist the first time it is
         accessed, no matter how soon after insertion the access
         occurs. If the value is greater than 0, blocks remain in
         the old sublist until an access occurs at least that many
         ms after the first access. For example, a value of 1000
         causes blocks to stay in the old sublist for 1 second
         after the first access before they become eligible to
         move to the new sublist. See Section 7.4.6, "The InnoDB
         Buffer Pool"

    For additional information, see Section 7.4.6, "The InnoDB
    Buffer Pool."

  * Two new status variables have been added to SHOW STATUS
    output. Innodb_buffer_pool_read_ahead and
    Innodb_buffer_pool_read_ahead_evicted indicate the number of
    pages read in by the InnoDB read-ahead background thread, and
    the number of such pages evicted without ever being accessed,
    respectively. Also, the status variables

    and Innodb_buffer_pool_read_ahead_seq

    have been removed.

  * Columns that provide a catalog value in INFORMATION_SCHEMA
    tables (for example, TABLES.TABLE_CATALOG) now have a value of
    def rather than NULL.

  * The deprecated --default-table-type
    tion_mysqld_default-table-type) server option has been
    removed. (Bug#34818:

  * Previously, mysqldump would not dump the INFORMATION_SCHEMA
    database and ignored it if it was named on the command line.
    Now, mysqldump will dump INFORMATION_SCHEMA if it is named on
    the command line. Currently, this requires that the
    --skip-lock-tables (or --skip-opt) option be given.

  * Several undocumented C API functions were removed:
    mysql_manager_close(), mysql_manager_command(),
    mysql_manager_connect(), mysql_manager_fetch_line(),
    mysql_manager_init(), mysql_disable_reads_from_master(),
    mysql_disable_rpl_parse(), mysql_enable_reads_from_master(),
    mysql_enable_rpl_parse(), mysql_master_query(),
    mysql_master_send_query(), mysql_reads_from_master_enabled(),
    mysql_rpl_parse_enabled(), mysql_rpl_probe(),
    mysql_rpl_query_type(), mysql_set_master(),
    mysql_slave_query(), and mysql_slave_send_query().

  * Sinhala collations utf8_sinhala_ci and ucs2_sinhala_ci were
    added for the utf8 and ucs2 character sets.

  * If the value of the --log-warnings option is greater than 1,
    the server now writes access-denied errors for new connection
    attempts to the error log (for example, if a client user name
    or password is incorrect).

  * On Windows, use of POSIX I/O interfaces in mysys was replaced
    with Win32 API calls (CreateFile(), WriteFile(), and so forth)
    and the default maximum number of open files has been
    increased to 16384. The maximum can be increased further by
    using the --open-files-limit=N option at server startup.


  * MySQL now implements the SQL standard SIGNAL and RESIGNAL
    statements. See Section 12.8.8, "SIGNAL and RESIGNAL."

  * The undocumented, deprecated, and not useful SHOW COLUMN TYPES
    statement has been removed.

  * Dynamic plugins now need to be linked with the
    libmysqlservices.a library. For an example showing what should look like, see Section, "Creating
    a Plugin Library."

  * The FORMAT() function now supports an optional third parameter
    that enables a locale to be specified to be used for the
    result number's decimal point, thousands separator, and
    grouping between separators. Allowable locale values are the
    same as the legal values for the lc_time_names system variable
    (see Section 9.8, "MySQL Server Locale Support"). For example,
    the result from FORMAT(1234567.89,2,'de_DE') is 1.234.567,89.
    If no locale is specified, the default is 'en_US'.

  * The Greek locale 'el_GR' is now an allowable value for the
    lc_time_names system variable.

  * Previously, in the absence of other information, the MySQL
    client programs mysql, mysqladmin, mysqlcheck, mysqlimport,
    and mysqlshow use the compiled-in default character set,
    usually latin1.
    Now these clients can autodetect which character set to use
    based on the operating system setting, such as the value of
    the LANG or LC_ALL locale environment language on Unix system
    or the code page setting on Windows systems. For systems on
    which the locale is available from the OS, the client uses it
    to set the default character set rather than using the
    compiled-in default. Thus, users can configure the locale in
    their environment for use by MySQL clients. For example,
    setting LANG to ru_RU.KOI8-R causes the koi8r character set to
    be used. The OS character set is mapped to the closest MySQL
    character set if there is no exact match. If the client does
    not support the matching character set, it uses the
    compiled-in default. (For example, ucs2 is not supported as a
    connection character set.)
    Third-party applications that wish to use character set
    autodetection based on the OS setting can use the following
    mysql_options() call before connecting to the server:
    See Section 9.1.4, "Connection Character Sets and Collations."

  * mysql_upgrade now has an --upgrade-system-tables option that
    causes only the system tables to be upgraded. With this
    option, data upgrades are not performed.

  * MySQL now supports an interface for semisynchronous
    replication: A commit performed on the master side blocks
    before returning to the session that performed the transaction
    until at least one slave acknowledges that it has received and
    logged the events for the transaction. Semisynchronous
    replication is implemented through an optional plugin
    component. See Section 16.2.8, "Semisynchronous Replication."

  * The CREATE TABLESPACE privilege has been introduced. This
    privilege exists at the global (superuser) level and enables
    you to create, alter, and drop tablespaces and logfile groups.

  * The server now supports a Debug Sync facility for thread
    synchronization during testing and debugging. To compile in
    this facility, configure MySQL with the --enable-debug-sync
    option. The debug_sync system variable provides the user
    interface Debug Sync. mysqld and support a
    --debug-sync-timeout option to enable the facility and set the
    default synchronization point timeout.

  * Added the TO_SECONDS() function, which converts a date or
    datetime value to a number of seconds since the year 0.

  * Parser performance was improved for identifier scanning and
    conversion of ASCII string literals.

  * The LOAD XML INFILE statement was added. This statement makes
    it possible to read data directly from XML files into database
    tables. For more information, see Section 12.2.7, "LOAD XML

Joerg Bruehe,  MySQL Build Team,  Joerg.Bruehe@stripped
Sun Microsystems GmbH,   Komturstraße 18a,   D-12099 Berlin
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel
Vorsitzender des Aufsichtsrates: Martin Haering     Muenchen: HRB161028

MySQL 5.5.0 has been releasedJoerg Bruehe15 Dec