MySQL/InnoDB-5.0.3 has been released

Hi!

MySQL-5.0.3 is probably the most important new MySQL release in several
years. On the MySQL side, a vast number of new features, like stored
procedures and views, have been implemented in 5.0. On the InnoDB side, we
changed the table format to a more space-saving one, and implemented the
2-phase commit XA protocol.

Since 5.0.3 contains a lot of new features, we cannot yet recommend it for
production use. This is a beta release for testing and developing your
applications that take advantage of MySQL's new powerful features.

InnoDB is the MySQL table type that supports foreign key constraints,
transactions, row-level locking, non-locking consistent read (MVCC), all
four SQL-1992 isolation levels of transactions, multiple tablespaces,
asynchronous unbuffered disk I/O on Windows, and a non-free hot online
backup tool.

You can download mySQL-5.0.3 from
http://dev.mysql.com/downloads/mysql/5.0.html


Functionality added or changed:

* Introduced a compact record format that does not store the number of
columns or the lengths of fixed-size columns. The old format can be
requested by specifying ROW_FORMAT=REDUNDANT. The new format
(ROW_FORMAT=COMPACT) is the default. This typically saves 20 % of space
compared to the old InnoDB table format. Note that the old tables that you
have will still have the old table format. There is no automatic conversion
when you upgrade to 5.0.3. New tables will by default have the new table
format.

* MySQL/InnoDB now supports two-phase commit of transactions, and the
associated XA protocol.

* Upgrading from 4.1: The sorting order for end-space in TEXT columns for
InnoDB and MyISAM tables has changed. Starting from 5.0.3, InnoDB compares
TEXT columns as space-padded at the end. If you have a non-unique index on a
TEXT column, you should run CHECK TABLE on it, and run OPTIMIZE TABLE if the
check reports errors. If you have a UNIQUE INDEX on a TEXT column, you
should rebuild the table with OPTIMIZE TABLE.

* When MySQL/InnoDB is compiled on Mac OS X 10.2 or earlier, detect the
operating system version at run time and use the fcntl() file flush method
on Mac OS X versions 10.3 and later. Apple had disabled fsync() in Mac OS X
for internal disk drives, which caused corruption at power outages.

* Implemented fast TRUNCATE TABLE. The old approach (deleting rows one by
one) may be used if the table is being referenced by foreign keys. (Bug
#7150)

* Setting the initial AUTO_INCREMENT value for an InnoDB table using CREATE
TABLE ... AUTO_INCREMENT = n now works, and ALTER TABLE ... AUTO_INCREMENT =
n resets the current value.

* Commit after every 10,000 copied rows when executing ALTER TABLE, CREATE
INDEX, DROP INDEX or OPTIMIZE TABLE. This makes it much faster to recover
from an aborted operation.

* Added several InnoDB status variables.

* A shared record lock (LOCK_REC_NOT_GAP) is now taken for a matching record
in the foreign key check because inserts can be allowed into gaps.

* Relaxed locking in INSERT...SELECT, single table UPDATE...SELECT and
single table DELETE...SELECT clauses when innobase_locks_unsafe_for_binlog
is used and isolation level of the transaction is not serializable. InnoDB
uses consistent read in these cases for a selected table.

* Added a new global system variable slave_transaction_retries: if the
replication slave SQL thread fails to execute a transaction because of an
InnoDB deadlock or exceeded InnoDB's innodb_lock_wait_timeout, it
automatically retries slave_transaction_retries times before stopping with
an error. The default is 10.


Bugs fixed:

* All the bug fixes from the MySQL-4.0 and 4.1 branches.


Outstanding bugs:

* If an SQL statement fails because an error, MySQL may fail to roll back
the statement automatically. It should be rolled back or the whole
transaction rolled back according to the ANSI SQL standards. This bug that
was introduced in 5.0.3 and will probably be fixed in upcoming 5.0.4.

* Column prefix PRIMARY KEYs do not work properly for multi-byte character
sets.


Upgrading from 4.1:

* Starting from 5.0.3, a VARCHAR in MySQL is a 'true' VARCHAR. It remembers
the number of spaces that there were at the end of the string. Previously,
MySQL at storage trimmed the end spaces from a VARCHAR. Tables created with
< 5.0.3 will remain to have the old VARCHAR semantics, while new tables will
have the new semantics.

* The sorting order for end-space in TEXT columns for InnoDB and MyISAM
tables has changed. Starting from 5.0.3, InnoDB compares TEXT columns as
space-padded at the end. If you have a non-unique index on a TEXT column,
you should run CHECK TABLE on it, and run OPTIMIZE TABLE if the check
reports errors. If you have a UNIQUE INDEX on a TEXT column, you should
rebuild the table with OPTIMIZE TABLE.

* The sorting order of BINARY and VARBINARY may still change in some 5.0.x
version.

* There is a bug in the InnoDB sorting order of ENUMs if the collation of
the ENUM is not latin1 or if there are more than about 100 different values
for the ENUM. This bug is also present in 4.0 and 4.1.

* InnoDB Hot Backup 2.0.1 or earlier does not work with MySQL-5.0.3. A new
version 2.1 will.


Best regards,

Heikki Tuuri
Innobase Oy
http://www.innodb.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org