2009.10.10

MySQL

MySQL Server 5.4.3ベータ版がリリースされました

オリジナル版:http://dev.mysql.com/doc/refman/5.4/en/news-5-4-3.html


MySQLデータベース管理システムの新バージョンであるMySQL Server 5.4.3ベータ版がリリースされました。


MySQL 5.4.3はベータ版であり、他の試作版と同じであることに注意してください。製品レベルのシステムあるいは、重要なデータを持つシステムにインストールする場合は慎重にしてください。MySQL 5.1を使用する製品レベルシステムでは、下記のMySQL Enterpriseの製品説明をよく読んでください。

   http://mysql.com/products/enterprise/


MySQL 5.4はMySQL 5.1に基づいていますが、MySQLサーバのアドレススケーラビリティおよびパフォーマンス問題に対処する影響の大きな変更を含んでいます。この変更は、ハードウェアおよびCPU設計の進歩における詳細な性能を引き出し、既存のハードウェアをより有効に利用できます。MySQL 5.4は現在ベータ版です。

MySQL 5.4の新機能の概要については、以下を参照してください。

   http://dev.mysql.com/doc/refman/5.4/en/news-5-4-x.html

   http://lists.mysql.com/internals/


新しいサーバでMySQL 5.4.3をインストールする情報として、以下のMySQL 5.1のインストールドキュメントを参照してください。

  http://dev.mysql.com/doc/refman/5.1/en/installing.html


前回のMySQLリリースからアップグレードするには、以下のアップグレードについての注意事項を参照してください。

   http://dev.mysql.com/doc/mysql-5.4-features/en/index.html

 

その他助けになるものとしては下記のものがあります。

  http://dev.mysql.com/doc/refman/5.4/en/mysql-nutshell.html
  http://dev.mysql.com/doc/refman/5.4/en/configuration-changes-5-4.html

  http://dev.mysql.com/doc/refman/5.4/en/upgrading-from-previous-series.html

MySQLサーバは、ダウンロード・ページからソースコード及び多くのプラットフォームのためのバイナリで現在利用可能です。

  http://dev.mysql.com/downloads/

すべてのミラーサイトが現在、最新であるとは限らないことに注意してください。
あるミラーサイトでこのバージョンを見つけることができない場合は、再度確認を行うか、あるいは別のダウンロード・サイトを選択してください。

現時点では全ミラーサイトが最新というわけではありませんので、いくつかのミラーサイトではこのバージョンを見つけられないかもしれません。後日ダウンロードするか、別のダウンロードサイトを利用してください。

バグレポート、バグ修正、パッチ等の情報をお待ちしております。
http://forge.mysql.com/wiki/Contributing

以下はMySQL 5.4の前回のバージョンからのMySQLソースコード内の変更をリストアップしています。これらはオンラインでも閲覧可能です。

  http://dev.mysql.com/doc/refman/5.4/en/news-5-4-2.html

==========

Functionality added or changed:

* Incompatible Change: If you upgrade to MySQL 5.4.3 from an
earlier 5.4 release, the server may fail to start due to
changes in the default values of innodb_log_files_in_group and
innodb_log_file_size, which differ from those in earlier 5.4
releases. You should discard the old InnoDB log files and let
MySQL 5.4.3 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.3 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.3 server.

* Incompatible Change: The following list indicates InnoDB
configuration changes in MySQL 5.4.3, compared to previous
versions of MySQL 5.4. For a description of cumulative changes
in MySQL 5.4 compared to MySQL 5.1, see Section 1.4.1, "What
Is New in MySQL 5.4."

The default value of these system variables was changed:

Variable Change in default value
innodb_autoextend_increment From 64MB to 8MB
innodb_buffer_pool_size From 1GB to 128MB
innodb_log_buffer_size From 16MB to 8MB
innodb_log_file_size From 128MB to 5MB
innodb_log_files_in_group From 3 to 2
innodb_read_io_threads From 8 to 4
innodb_write_io_threads From 8 to 4

These default value changes in most cases revert changes made
in earlier MySQL 5.4 releases. The result is that the defaults
are more similar to those in MySQL 5.1.

* On Linux (and perhaps other systems), the performance of MySQL
Server can be improved by using a different malloc()
implementation, developed by Google and called tcmalloc. The
gain is noticeable with a higher number of simultaneous users.
MySQL 5.4.1 was linked against this library, which improved
performance but imposed some restrictions. As a result of
these restrictions, MySQL 5.4.3 uses a different approach:

+ The server is linked against the default malloc()
provided by the respective platform.

+ Binary distributions for Linux include
libtcmalloc_minimal.so (a shared library that can be
linked against at runtime) in pkglibdir (that is, the
same directory within the package where server plugins
and similar object files are located). The version of
tcmalloc included with MySQL 5.4.3 comes from
google-perftools 1.4.

If you want to try tcmalloc but are using a binary
distribution for a non-Linux platform or a source
distribution, you can install Google's tcmalloc. Some
distributions provide it in a google-perftools package or
with a similar name, or you can download it from Google at
http://code.google.com/p/google-perftools/ and compile it
yourself.

+ mysqld_safe now supports a --malloc-lib option that
enables administrators to specify that mysqld should use
tcmalloc.

The --malloc-lib option works by modifying the LD_PRELOAD
environment value to affect dynamic linking to enable the
loader to find the memory-allocation library when mysqld runs:

+ If the option is not given, or is given without a value
(--malloc-lib=), LD_PRELOAD is not modified and no
attempt is made to use tcmalloc.

+ If the option is given as --malloc-lib=tcmalloc,
mysqld_safe looks for a tcmalloc library in /usr/lib and
then in the MySQL pkglibdir location (for example,
/usr/local/mysql/lib or whatever is appropriate). If
tmalloc is found, its path name is added to the beginning
of the LD_PRELOAD value for mysqld. If tcmalloc is not
found, mysqld_safe aborts with an error.

+ If the option is given as
--malloc-lib=/path/to/some/library, that full path is
added to the beginning of the LD_PRELOAD value. If the
full path points to a nonexistent or unreadable file,
mysqld_safe aborts with an error.

+ For cases where mysqld_safe adds a path name to
LD_PRELOAD, it adds the path to the beginning of any
existing value the variable already has.

As a result of the preceding changes, Linux users can use the
libtcmalloc_minimal.so now included in binary packages by
adding these lines to the my.cnf file:

[mysqld_safe]
malloc-lib=tcmalloc

Those lines also suffice for users on any platform who have
installed a tcmalloc package in /usr/lib. To use a specific
tcmalloc library, specify its full path name. Example:

[mysqld_safe]
malloc-lib=/opt/lib/libtcmalloc_minimal.so

(Bug#47549: http://bugs.mysql.com/47549)


Bugs fixed:

* Performance: For MyISAM tables with bulk_insert_buffer_size
values larger than 256KB, the performance of bulk insert
operations such as multiple-row INSERT and INSERT ... SELECT
operations has been improved greatly when up to a hundred rows
are inserted at the same time.
(Bug#44723: http://bugs.mysql.com/44723)

* Partitioning: Replication: Attempting to replicate an INSERT
... SELECT statement on a partitioned table using row-based
replication caused the slave to crash.
(Bug#44108: http://bugs.mysql.com/44108)

* Partitioning: When reorganizing partitions, not all affected
subpartitions were removed prior to renaming. One way in which
the issue was visible was that attempting to reorganize two
partitions into a single partition having the same name as one
of the original partitions could lead to a crash of the
server. (Bug#47029: http://bugs.mysql.com/47029)
See also Bug#45961: http://bugs.mysql.com/45961,
Bug#43729: http://bugs.mysql.com/43729.

* Partitioning: An INSERT ... SELECT statement on an empty
partition of a partitioned table failed with ERROR 1030
(HY000): Got error 124 from storage engine. This issue also
caused queries run against a partitioned table while a LOAD
DATA CONCURRENT INFILE statement was in progress to fail with
the same error. (Bug#46639: http://bugs.mysql.com/46639)
See also Bug#35845: http://bugs.mysql.com/35845,
Bug#44657: http://bugs.mysql.com/44657,
Bug#45840: http://bugs.mysql.com/45840.

* Partitioning: An online or fast ALTER TABLE of a partitioned
table could leave behind temporary files in the database
directory. (Bug#46483: http://bugs.mysql.com/46483)

* Partitioning: A partitioned table having a TIMESTAMP column
with a default value of CURRENT_TIMESTAMP and this column was
not defined using an ON UPDATE option, an ALTER TABLE ...
REORGANIZE PARTITION statement on the table caused the
TIMESTAMP column value to be set to CURRENT_TIMESTAMP
regardless. (Bug#46478: http://bugs.mysql.com/46478)

* Partitioning: Partition pruning did not always work correctly
when the table's partitioning key used the TO_DAYS() function.
(Bug#46362: http://bugs.mysql.com/46362)

* Partitioning: Inserting negative values into an AUTO_INCREMENT
column of a partitioned table could lead to apparently
unrelated errors or a crash of the server.
(Bug#45823: http://bugs.mysql.com/45823)

* Partitioning: Attempting to access a partitioned table when
partitioning support was disabled in a MySQL server binary
that had been compiled with partitioning support caused the
server to crash. (Bug#39893: http://bugs.mysql.com/39893)

* Partitioning: Unnecessary calls were made in the server code
for performing bulk inserts on partitions for which no inserts
needed to be made. (Bug#35845: http://bugs.mysql.com/35845)
See also Bug#35843: http://bugs.mysql.com/35843.

* Partitioning: The use of TO_DAYS() in the partitioning
expression led to selection failures when the column having
the date value contained invalid dates. This occurred because
the function returns NULL in such cases, and the partition
containing NULL values was pruned away. For example, this
problem occurred if '2001-02-00' was inserted into a DATE
column of such a table, and a subsequent query on this table
used WHERE date_col < '2001-02-00' --- while '2001-01-01' is
less than '2001-02-00', TO_DAYS('2001-02-00') evaluates as
NULL, and so the row containing '2001-01-01' was not returned.
Now, for tables using RANGE or LIST partitioning and having
TO_DAYS() in the partitioning expression, the NULL partition
is also scanned instead of being ignored.

The fix for this issue also corrects misbehavior such that a
query of the form SELECT * FROM table WHERE date_col <
date_val on a table partitioned by RANGE or LIST was handled
as though the server SQL mode included ALLOW_INVALID_DATES
even if this was not actually part of the server SQL mode at
the time the query was issued.
(Bug#20577: http://bugs.mysql.com/20577)
See also Bug#18198: http://bugs.mysql.com/18198,
Bug#32021: http://bugs.mysql.com/32021,
Bug#46362: http://bugs.mysql.com/46362.

* Replication: Performing a multi-row update of the
AUTO_INCREMENT column of a transactional table could result in
an inconsistency between master and slave when there was a
trigger on the transactional table that updated a
non-transactional table. When such an update failed on the
master, no rows were updated on the master, but some rows
could (erroneously) be updated on the slave.
(Bug#46864: http://bugs.mysql.com/46864)

* Replication: When using the --replicate-rewrite-db option and
the database referenced by this option on the master was the
current database when the connection to the slave was closed,
any temporary tables existing in this database were not
properly dropped. (Bug#46861: http://bugs.mysql.com/46861)

* Replication: When a statement that changed both transactional
and non-transactional tables failed, the transactional changes
were automatically rolled back on the master but the slave
ignored the error and did not roll them back, thus leading to
inconsistencies between master and slave.

This issue is fixed by automatically rolling back a statement
that fails on the slave; however, the transaction is not
rolled back unless a corresponding ROLLBACK statement is found
in the relay log file.
(Bug#46130: http://bugs.mysql.com/46130)
See also Bug#33864: http://bugs.mysql.com/33864.

* Replication: When using row-based replication, importing a
dump made with mysqldump and replicating a row with an
AUTO_INCREMENT column set to 0, with NO_AUTO_VALUE_ON_ZERO
active on the master, the row was inserted successfully on the
master; however any setting for NO_AUTO_VALUE_ON_ZERO was
ignored on the slave. When the AUTO_INCREMENT column was
incremented, this caused replication to fail on the slave due
to a duplicate key error. In some cases it could also cause
the slave to crash. (Bug#45999: http://bugs.mysql.com/45999)

* Replication: When slave_transaction_retries is set, a
statement that replicates, but is then rolled back due to a
deadlock on the slave, should be retried. However, in certain
cases, replication was stopped with error 1213 (Deadlock found
when trying to get lock; try restarting transaction) instead,
even when this variable was set.
(Bug#45694: http://bugs.mysql.com/45694)

* Replication: The binary logging behavior (and thus, the
replication behavior) of CREATE DATABASE IF NOT EXISTS, CREATE
TABLE IF NOT EXISTS, and CREATE EVENT IF NOT EXISTS was not
consistent among these statements, nor with that of DROP
DATABASE IF EXISTS, DROP TABLE IF EXISTS, and DROP EVENT IF
EXISTS: A DROP ... IF EXISTS statement is always logged even
if the database object named in the statement does not exist.
However, of the CREATE ... IF NOT EXISTS statements, only the
CREATE EVENT IF NOT EXISTS statement was logged when the
database object named in the statement already existed.

Now, every CREATE ... IF NOT EXISTS statement is written to
the binary log (and thus replicated), whether the database
object named in the statement exists or not. For more
information, see Section 16.3.1.3, "Replication of CREATE ...
IF NOT EXISTS Statements."

Exception. Replication and logging of CREATE TABLE IF NOT
EXISTS ... SELECT continues to be handled according to
existing rules. See Section 16.3.1.4, "Replication of CREATE
TABLE ... SELECT Statements," for more information.
(Bug#45574: http://bugs.mysql.com/45574)

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

Note
This was not an issue when using row-based replication.
(Bug#45516: http://bugs.mysql.com/45516)

* Replication: In some cases, a STOP SLAVE statement could cause
the replication slave to crash. This issue was specific to
MySQL on Windows or Macintosh platforms.
(Bug#45238: http://bugs.mysql.com/45238,
Bug#45242: http://bugs.mysql.com/45242,
Bug#45243: http://bugs.mysql.com/45243,
Bug#46013: http://bugs.mysql.com/46013,
Bug#46014: http://bugs.mysql.com/46014,
Bug#46030: http://bugs.mysql.com/46030)
See also Bug#40796: http://bugs.mysql.com/40796.

* Replication: Creating a scheduled event whose DEFINER clause
was either set to CURRENT_USER or not set explicitly caused
the master and the slave to become inconsistent. This issue
stems from the fact that, in both cases, the DEFINER is set to
the CURRENT_USER of the current thread. (On the master, the
CURRENT_USER is the mysqld user; on the slave, the
CURRENT_USER is empty.)

This behavior has been modified as follows:

+ If CURRENT_USER is used as the DEFINER, it is replaced
with the value of CURRENT_USER before the CREATE EVENT
statement is written to the binary log.

+ If the definer is not set explicitly, a DEFINER clause
using the value of CURRENT_USER is added to the CREATE
EVENT statement before it is written to the binary log.
(Bug#44331: http://bugs.mysql.com/44331)
See also Bug#42217: http://bugs.mysql.com/42217.

* Replication: When using the statement-based logging format,
the only possible safe combination of transactional and
non-transactional statements within the same transaction is to
perform any updates on non-transactional tables (such as
MyISAM tables) first, before updating any transactional tables
(such as those using the InnoDB storage engine). This is due
to the fact that, although a modification made to a
non-transactional table is immediately visible to other
connections, the update is not immediately written to the
binary log, which can lead to inconsistencies between master
and slave. (Other combinations may hide a causal dependency,
thus making it impossible to write statements updating
non-transactional tables to the binary log in the correct
order.)

However, in some cases, this situation was not handled
properly, and the determination whether a given statement was
safe or not under these conditions was not always correct. In
particular, a multi-table update that affected both
transactional and non-transactional tables or a statement
modifying data in a non-transactional table having a trigger
that operated on a transactional table (or the reverse) was
not determined to be unsafe when it should have been.

With this fix, the following determinations regarding
replication safety are made when combining updates to
transactional and non-transactional tables within the same
transaction in statement-based logging mode:

1. Any statement modifying data in a non-transactional table
within a given transaction is considered safe if it is
issued prior to any data modification statement accessing
a transactional table within the same transaction.

2. A statement that updates transactional tables only is
always considered safe.

3. A statement affecting both transactional and
non-transactional tables within a transaction is always
considered unsafe. It is not necessary that both tables
be modified for this to be true; for example, a statement
such as INSERT INTO innodb_table SELECT * FROM
myisam_table is also considered unsafe.

Note
The current fix is valid only when using statement-based
logging mode; we plan to address similar issues occurring when
using the MIXED or ROW format in a future MySQL release.
(Bug#28976: http://bugs.mysql.com/28976)

* The ARCHIVE storage engine lost records during a bulk insert.
(Bug#46961: http://bugs.mysql.com/46961)

* CONCAT_WS() could return incorrect results due to an argument
buffer also being used as a result buffer.
(Bug#46815: http://bugs.mysql.com/46815)

* Stack overflow checking did not account for the size of the
structure stored in the heap.
(Bug#46807: http://bugs.mysql.com/46807)

* The server crashed when re-using outer column references in
correlated subqueries when the enclosing query used a temp
table. (Bug#46791: http://bugs.mysql.com/46791)

* The server could crash for queries with the following
elements: 1. An "impossible where" in the outermost SELECT; 2.
An aggregate in the outermost SELECT; 3. A correlated subquery
with a WHERE clause that includes an outer field reference as
a top-level WHERE sargable predicate;
(Bug#46749: http://bugs.mysql.com/46749)

* Assertion failure could result from repeated execution of a
stored procedure containing an incorrect query with a
subselect. (Bug#46629: http://bugs.mysql.com/46629)

* CREATE TABLE ... SELECT could cause assertion failure if a
table already existed with the same name and contained an
AUTO_INCREMENT column.
(Bug#46616: http://bugs.mysql.com/46616)

* SHOW CREATE TRIGGER for a MERGE table trigger caused an
assertion failure. (Bug#46614: http://bugs.mysql.com/46614)

* In queries for which the loose index scan access method was
chosen, using a condition of the form col_name rather than the
equivalent col_name <> 0 caused an assertion failure.
(Bug#46607: http://bugs.mysql.com/46607)

* The server ignored the setting of sync_frm for CREATE TABLE
... LIKE. (Bug#46591: http://bugs.mysql.com/46591)

* TRUNCATE TABLE for a table that was opened with HANDLER did
not close the handler and left it in an inconsistent state
that could lead to a server crash. Now TRUNCATE TABLE for a
table closes all open handlers for the table.
(Bug#46456: http://bugs.mysql.com/46456)

* An attempt to create a table with the same name as an existing
view could cause a server crash.
(Bug#46384: http://bugs.mysql.com/46384)

* A parser problem prevented properly stripping backquotes from
an argument to a user-defined function (UDF). If the UDF was
in an ORDER BY clause, its name would not be properly resolved
against an alias with the same name in the select list.
(Bug#46259: http://bugs.mysql.com/46259)

* A query containing a subquery in the FROM clause and PROCEDURE
ANALYSE() caused a server crash.
(Bug#46184: http://bugs.mysql.com/46184)

* Certain SELECT statements containing DISTINCT, GROUP BY, and
HAVING clauses could hang in an infinite loop.
(Bug#46159: http://bugs.mysql.com/46159)

* Killing a query that was performing a sort could result in a
memory leak. (Bug#45962: http://bugs.mysql.com/45962)

* A Valgrind error when opening the init_file file was
corrected. (Bug#45894: http://bugs.mysql.com/45894)

* CREATE TEMPORARY TABLE failed for InnoDB tables on systems
with case-insensitive file systems when lower_case_table_names
= 2 and the pathname of the temporary file directory contained
uppercase characters. (Bug#45638: http://bugs.mysql.com/45638)

* Truncation of DECIMAL values could lead to assertion failures;
for example, when deducing the type of a table column from a
literal DECIMAL value.
(Bug#45261: http://bugs.mysql.com/45261)

* Debug builds of the server on Windows crashed in debugging
code. (Bug#45118: http://bugs.mysql.com/45118)

* The default value of innodb_buffer_pool_size was set to 1GB in
MySQL 5.4.0. This was too large for many installations, so the
default has been lowered to 128MB.
(Bug#44408: http://bugs.mysql.com/44408)

* SHOW ERRORS returned an empty result set after an attempt to
drop a nonexistent table.
(Bug#42364: http://bugs.mysql.com/42364)

* A test for stack growth failed on some platforms, leading to
server crashes. (Bug#42213: http://bugs.mysql.com/42213)

* A buffer overflow could occur during handling of IS NULL
ranges. (Bug#37044: http://bugs.mysql.com/37044)

* mysqladmin --wait ping crashed on Windows systems.
(Bug#35132: http://bugs.mysql.com/35132)

* myisamchk performed parameter value casting at startup that
generated unnecessary warning messages.
(Bug#33785: http://bugs.mysql.com/33785)

* When using the ARCHIVE storage engine, SHOW TABLE STATUS
displayed incorrect information for Max_data_length,
Data_length and Avg_row_length.
(Bug#29203: http://bugs.mysql.com/29203)