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
http://forge.mysql.com/wiki/Development_Cycle
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:
http://mysql.com/products/enterprise/
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
hardware.
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
http://dev.mysql.com/doc/refman/5.5/en/mysql-nutshell.html
For information on installing MySQL 5.5.0 on new servers,
please see the MySQL installation documentation at
http://dev.mysql.com/doc/refman/5.5/en/installing.html
For upgrading from previous MySQL releases, please see the
important upgrade considerations at
http://dev.mysql.com/doc/mysql-5.5-features/en/index.html
Some other pointers you might like to follow are
http://dev.mysql.com/doc/refman/5.5/en/configuration-changes-5-5.html
http://dev.mysql.com/doc/refman/5.5/en/upgrading-from-previous-series.html
MySQL Server is available in source and binary form for a
number of platforms from our download pages at
http://dev.mysql.com/downloads/
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.:
http://forge.mysql.com/wiki/Contributing
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
http://dev.mysql.com/doc/refman/5.5/en/news-5-5-0.html
Enjoy!
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
statement.
* 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
CREATE TABLE or ALTER TABLE (use ENGINE).
The SHOW TABLE TYPES SQL statement (use SHOW ENGINES).
* 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).
* The SHOW INNODB STATUS and SHOW MUTEX STATUS SQL statements
(use SHOW ENGINE INNODB STATUS for both of these).
* The LOAD TABLE ... FROM MASTER and LOAD DATA FROM MASTER SQL
statements.
* 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,
--master-ssl-key.
-----
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
commit.
For information about these features, see the InnoDB Plugin
Manual at
http://www.innodb.com/products/innodb_plugin/plugin-documentation
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.
(Bug#27420: http://bugs.mysql.com/bug.php?id=27420,
Bug#27430: http://bugs.mysql.com/bug.php?id=27430,
Bug#27690: http://bugs.mysql.com/bug.php?id=27690)
* 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
--lc-messages-dir.
+ The language system variable has been removed and
replaced with the new lc_messages_dir and lc_messages
system variables. lc_messages_dir has only a global value
and is read only. lc_messages has global and session
values and can be modified at runtime, so the error
message language can be changed while the server is
running, and individual clients each can have a different
error message language by changing their session
lc_messages value to a different locale name.
+ Error messages previously were constructed in a mix of
character sets. This issue is resolved by constructing
error messages internally within the server using UTF-8
and returning them to the client in the character set
specified by the character_set_results system variable.
The content of error messages therefore may in some cases
differ from the messages returned previously.
For more information, see Section 9.3, "Setting the Error
Message Language," and Section 9.1.6, "Character Set for Error
Messages."
See also Bug#46218: http://bugs.mysql.com/bug.php?id=46218,
Bug#46236: http://bugs.mysql.com/bug.php?id=46236.
* 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
PARTITION, ALTER TABLE ... TRUNCATE PARTITION merely deletes
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 12.6.2.1, "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
Variables").
The addition of replication heartbeats addresses a number of
issues:
+ 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.
(Bug#20435: http://bugs.mysql.com/bug.php?id=20435,
Bug#29309: http://bugs.mysql.com/bug.php?id=29309,
Bug#30932: http://bugs.mysql.com/bug.php?id=30932)
* 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
master.info and relay.info 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 16.1.3.3, "Replication Slave
Options and Variables."
(Bug#31665: http://bugs.mysql.com/bug.php?id=31665,
Bug#35542: http://bugs.mysql.com/bug.php?id=35542,
Bug#40337: http://bugs.mysql.com/bug.php?id=40337)
* 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.
(Bug#28777: http://bugs.mysql.com/bug.php?id=28777)
* 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
12.6.2.1, "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.
(Bug#25998: http://bugs.mysql.com/bug.php?id=25998)
See also Bug#27808: http://bugs.mysql.com/bug.php?id=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.
(Bug#47298: http://bugs.mysql.com/bug.php?id=47298)
* 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
pool).
+ 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."
(Bug#45015: http://bugs.mysql.com/bug.php?id=45015)
* 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
Innodb_buffer_pool_read_ahead_rnd
(http://dev.mysql.com/doc/refman/5.4/en/server-status-variables.html#statvar_Innodb_buffer_pool_read_ahead_rnd)
and Innodb_buffer_pool_read_ahead_seq
(http://dev.mysql.com/doc/refman/5.4/en/server-status-variables.html#statvar_Innodb_buffer_pool_read_ahead_seq)
have been removed.
(Bug#42885: http://bugs.mysql.com/bug.php?id=42885)
* Columns that provide a catalog value in INFORMATION_SCHEMA
tables (for example, TABLES.TABLE_CATALOG) now have a value of
def rather than NULL.
(Bug#35427: http://bugs.mysql.com/bug.php?id=35427)
* The deprecated --default-table-type
(http://dev.mysql.com/doc/refman/5.4/en/server-options.html#op
tion_mysqld_default-table-type) server option has been
removed. (Bug#34818: http://bugs.mysql.com/bug.php?id=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.
(Bug#33762: http://bugs.mysql.com/bug.php?id=33762)
* 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().
(Bug#31952: http://bugs.mysql.com/bug.php?id=31952,
Bug#31954: http://bugs.mysql.com/bug.php?id=31954)
* Sinhala collations utf8_sinhala_ci and ucs2_sinhala_ci were
added for the utf8 and ucs2 character sets.
(Bug#26474: http://bugs.mysql.com/bug.php?id=26474)
* 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).
(Bug#25822: http://bugs.mysql.com/bug.php?id=25822)
* 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.
(Bug#24509: http://bugs.mysql.com/bug.php?id=24509)
* The TRADITIONAL SQL mode now includes NO_ENGINE_SUBSTITUTION.
(Bug#21099: http://bugs.mysql.com/bug.php?id=21099)
* MySQL now implements the SQL standard SIGNAL and RESIGNAL
statements. See Section 12.8.8, "SIGNAL and RESIGNAL."
(Bug#11661: http://bugs.mysql.com/bug.php?id=11661)
* The undocumented, deprecated, and not useful SHOW COLUMN TYPES
statement has been removed.
(Bug#5299: http://bugs.mysql.com/bug.php?id=5299)
* Dynamic plugins now need to be linked with the
libmysqlservices.a library. For an example showing what
Makefile.am should look like, see Section 21.2.3.3, "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:
mysql_options(mysql,
MYSQL_SET_CHARSET_NAME,
MYSQL_AUTODETECT_CHARSET_NAME);
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 mysql-test-run.pl 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
Syntax."
--
Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com
Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel
Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
