This Beta release, as any other pre-production release, should not be installed on “production” level systems or systems with critical data. It is good practice to back up your data before installing any new version of software. Although MySQL worked very hard to ensure a high level of quality, protect your data by making a backup as you would for any software beta release.
Functionality Added or Changed
A consequence of the change in handling of the
NUMERIC fixed-point data types is
that the server is more strict to follow standard SQL. For
example, a data type of
DECIMAL(3,1) stores a
maximum value of 99.9. Previously, the server permitted larger
numbers to be stored. That is, it stored a value such as 100.0
as 100.0. Now the server clips 100.0 to the maximum permissible
value of 99.9. If you have tables that were created before MySQL
5.0.3 and that contain floating-point data not strictly legal
for the data type, you should alter the data types of those
columns. For example:
For user-defined functions, exact-value decimal arguments such
DECIMAL column values were passed
REAL_RESULT values prior to MySQL 5.0.3.
As of 5.0.3, they are passed as strings with a type of
DECIMAL_RESULT. If you upgrade to 5.0.3 and
find that your UDF now receives string values, use the
initialization function to coerce the arguments to numbers as
described in UDF Argument Processing.
CEILING() functions, the return
type is no longer always
For exact-value numeric arguments, the return value has an
exact-value numeric type. For string or floating-point
arguments, the return value has a floating-point type.
The C API
symbol was renamed to
MySQL Cluster; Replication:
Added a new global system variable
the replication slave SQL thread fails to execute a transaction
because of an
InnoDB deadlock or exceeded
it automatically retries
before stopping with an error. The default is 10.
When using this storage engine, the output of
SHOW TABLE STATUS now displays
properly calculated values in the
Data_length columns. (Note that
BLOB columns are not yet taken
into account.) In addition, the number of replicas is now shown
Comment column (as
If the MySQL server is started without an argument to
--log-bin and without
--log-bin-index, thus not
providing a name for the binary log index file, a warning is
issued because MySQL falls back to using the host name for that
name, and this is prone to replication issues if the server's
host name gets changed later.
Replication: The way the character set information is stored into the binary log was changed, so that it is now possible to have a replication master and slave running with different global character sets. A disadvantage is that replication from 5.0.3 masters to pre-5.0.3 slaves is impossible.
LOAD DATA statement was
extended to support user variables in the target column list,
and an optional
SET clause. Now one can
perform some transformations on data after they have been read
and before they are inserted into the table. For example:
LOAD DATA INFILE 'file.txt' INTO TABLE t1 (column1, @var1) SET column2 = @var1/100;
Also, replication of
was changed, so you can't replicate such statements from a 5.0.3
master to pre-5.0.3 slaves.
InnoDB: 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. In Mac OS X,
fsync() does not flush the write cache in
the disk drive, but the special
does; however, the flush request is ignored by some external
devices. Failure to flush the buffers may cause severe database
corruption at power outages.
The binary log file and binary log index file now are handled
the same way as
MyISAM tables when there is a
“disk full” or “quota exceeded” error.
See How MySQL Handles a Full Disk.
The MySQL server aborts immediately instead of simply issuing a
warning if it is started with the
--log-bin option but cannot
initialize the binary log at startup (that is, an error occurs
when writing to the binary log file or binary log index file).
NULL (which means “unknown”) if
the slave SQL thread is not running, or if the slave I/O thread
is not running or not connected to master. It is zero if the SQL
thread has caught up to the I/O thread. It no longer grows
indefinitely if the master is idle.
mysqlbinlog now prints a
statement at the end of its output, in case the server crashed
while it was in the process of writing the final entry into the
last binary log named on the command line. This causes any
half-written transaction to be rolled back when the output is
harmless if the binary log file was written and closed normally.
Setting the connection collation to a value different from the
server collation followed by a
TABLE statement that included a quoted default value
resulted in a server crash.
mysql_library_end() as synonyms
mysql_server_end() C API
#define symbols, but the names more clearly
indicate that they should be called when beginning and ending
use of a MySQL C API library no matter whether the application
Implemented support for XA transactions. See
XA Transactions. The implementation makes the
--innodb-safe-binlog option obsolete, so it has
limit, which enables you to specify the maximum number of
concurrent connections for the account. Also, all limited
resources now are counted per account (instead of being counted
per user + host pair as it was before). Use the
--old-style-user-limits option to
get the old behavior.
InnoDB status variables. See
Server Status Variables.
MyISAM storage engine detects
corruption of a
MyISAM table, a message
describing the problem now is written to the error log.
SHOW COLUMNS now displays
NO rather than blank in the
Null output column if the corresponding table
column cannot be
SHOW COLUMNS, and so forth,
display information about the
INFORMATION_SCHEMA database. Also, several
SHOW statements now accept a
WHERE clause specifying which output rows to
display. See INFORMATION_SCHEMA Tables.
HEAP) can have
execution plans were chosen on IRIX.
InnoDB: Corrected a bug in the crash recovery
ROW_FORMAT=COMPACT tables that caused
corruption. There may still be bugs in the crash recovery,
cp932 (SJIS for Windows Japanese) and
eucjpms (UJIS for Windows Japanese) character
BIT in column definitions now is
a distinct data type; it no longer is treated as a synonym for
Changed XML format for mysql from
to enable proper encoding of column names that are not legal as
Out-of-order packets were sent (
EOF) following a
Support for the
ISAM storage engine has been
removed. If you have
ISAM tables, you should
convert them before upgrading. See
Upgrading from MySQL 4.1 to 5.0.
system variable. For NDB, setting this variable to 1 permits
processing of some
WHERE clause conditions to
be processed in NDB nodes before rows are sent to the MySQL
server, rather than having rows sent to the server for
Security improvement: The server creates
files only if a file with the same name does not already exist.
Thanks to Stefano Di Paola
<email@example.com> for finding and
informing us about this issue.
Additional control over transaction completion was implemented.
AND [NO] CHAIN and
RELEASE clauses. There is a new
SAVEPOINT statement. The
completion_type system variable
was added for setting the global and session default completion
my.cnf in the compile-time datadir (usually
/usr/local/mysql/data/ in the binary
tarball distributions) is not being read anymore. The value of
the environment variable
MYSQL_HOME is used
instead of the hard-coded path.
variable to cause
Note-level warnings not to
Boolean full-text phrase searching now requires only that matches contain exactly the same words as the phrase and in the same order. Nonword characters no longer need match exactly.
InnoDB: Upgrading from
4.1: The sorting order for end-space in
TEXT columns for
InnoDB tables has changed. Starting from
TEXT columns as space-padded at
the end. If you have a nonunique 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
TEXT column, you should rebuild
the table with
The coercibility for the return value of functions such as
VERSION() now is “system
constant” rather than “implicit.” This makes
these functions more coercible than column values so that
comparisons of the two do not result in
Illegal mix of
COERCIBILITY() was modified to
accommodate this new coercibility value. See
CHECKSUM TABLE returns a warning
for nonexisting tables. The checksum value remains
NULL as before.
multi_range_count system variable.
error member to the
MYSQL_BIND data structure that is used in the
C API for prepared statements. This member is used for reporting
data truncation errors. Truncation reporting is enabled using
mysql_options() C API
Permitted the service-installation command for Windows servers
to specify a single option other than
--defaults-file following the
service name. This is for compatibility with MySQL 4.1.
SHOW CREATE TABLE now uses
to specify an
InnoDB: Implemented fast
TRUNCATE TABLE. The old approach
(deleting rows one by one) may be used if the table is being
referenced by foreign keys.
Security improvement: User-defined functions should have at
least one symbol defined in addition to the
xxx symbol that corresponds to the main
xxx() function. These auxiliary symbols
correspond to the
functions. mysqld by default no longer loads
UDFs unless they have at least one auxiliary symbol defined in
addition to the main symbol. The
controls whether UDFs that have only an
symbol can be loaded. By default, the option is off.
mysqld also checks UDF file names when it
reads them from the
mysql.func table and
rejects those that contain directory path name separator
characters. (It already checked names as given in
CREATE FUNCTION statements.) See
UDF Calling Sequences for Simple Functions,
UDF Calling Sequences for Aggregate Functions, and
User-Defined Function Security Precautions. Thanks to Stefano Di Paola
<firstname.lastname@example.org> for finding and
informing us about this issue.
User variable coercibility has been changed from “coercible” to “implicit.” That is, user variables have the same coercibility as column values.
When a client releases a user-level lock,
RELEASE_LOCK() will not be written to the binary log
anymore (this makes the binary log smaller); as a counterpart,
the slave does not actually take the lock when it executes
GET_LOCK(). This is mainly an
optimization and should not affect existing setups.
FEDERATED storage engine. See
The FEDERATED Storage Engine.
STDDEV_POP() as standard SQL
aliases for the
STDDEV() functions that compute
population variance and standard deviation. Added new
STDDEV_SAMP() functions to
compute sample variance and standard deviation.
From the Windows distribution, predefined accounts without
passwords for remote users (
''@'%') were removed (other distributions
never had them).
InnoDB: 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
ROW_FORMAT=REDUNDANT. The new
ROW_FORMAT=COMPACT) is the default.
The new format typically saves 20% of disk space and memory.
The presence of the new compact row format decreases row storage space by about 20% at the cost of increasing CPU use for some operations. If your workload is a typical one that is limited by cache hit rates and disk speed it is likely to be faster. If it is a rare case that is limited by CPU speed, it might be slower.
InnoDB: Relaxed locking in
SELECT, single table
(SELECT) and single table
(SELECT) clauses when
is used and isolation level of the transaction is not
InnoDB uses consistent read in these cases
for a selected table.
Bit-field values can be written using
value is a binary value written using
0s and 1s.
API change: the
reconnect flag in the
MYSQL structure is now set to 0 by
mysql_real_connect(). Only those
client programs which didn't explicitly set this flag to 0 or 1
experience a change. Having automatic reconnection enabled by
default was considered too dangerous (after reconnection, table
locks, temporary tables, user and session variables are lost).
InnoDB: A shared record lock
LOCK_REC_NOT_GAP) is now taken for a
matching record in the foreign key check because inserts can be
permitted into gaps.
RAID options in
MyISAM tables has been removed. If you have
tables that use these options, you should convert them before
upgrading. See Upgrading from MySQL 4.1 to 5.0.
CREATE USER privilege was
Added support for
InnoDB: A commit is now performed after every
10,000 copied rows when executing
DROP INDEX or
OPTIMIZE TABLE. This makes
recovery from an aborted operations of these types much faster
than previous to this change.
mysqld_safe will create the directory where the UNIX socket file is to be located if the directory does not exist. This applies only to the last component of the directory path name. (Bug #8513)
The server now includes a timestamp in the
connections message that is written to the error log
Replication: DDL statements for views were not being written to the binary log (and thus not subject to replication). (Bug #4838)
Replication: If multiple semicolon-separated statements were received in a single packet, they were written to the binary log as a single event rather than as separate per-statement events. For a server serving as a replication master, this caused replication to fail when the event was sent to slave servers. (Bug #8436)
A replication master stamped a generated statement (such as a
statement) with an error code intended only for another
statement. This could happen, for example, when a statement
generated a duplicate key error on the master but still had be
to replicated to the slave.
If the slave was running with
--replicate-*-table options which excluded one
temporary table and included another, and the two tables were
used in a single
DROP TEMPORARY TABLE IF
EXISTS statement, as the ones the master automatically
writes to its binary log upon client's disconnection when client
has not explicitly dropped these, the slave could forget to
delete the included replicated temporary table. Only the slave
needs to be upgraded.
Multiple-table updates did not replicate properly to slave
--replicate-*-table options had
Selecting from an
combined with a subquery on an
INFORMATION_SCHEMA table caused an error with
TABLE .. SELECT query to the binary log when the
insertion of new records partially failed.
FOUND_ROWS() returned an
incorrect value after a
DISTINCT statement that selected constants and
GROUP BY and
When encountering a disk full or
quota exceeded write error,
MyISAM sometimes failed to sleep and retry
the write, resulting in a corrupted table.
MATCH ... AGAINST in natural language mode
could cause a server crash if the
index was not used in a join (that is,
EXPLAIN did not show
fulltext join mode) and the
search query matched no rows in the table.
REPAIR TABLE did not invalidate
query results in the query cache that were generated from the
Made the MySQL server accept executing
CREATE DATABASE even if the connection has an open
transaction or locked tables. Refusing it made
mysqldump --single-transaction sometimes fail
to print a complete
DATABASE statement for some dumped databases.
Selecting from a view that had an
NOT EXISTS subquery did not always work
properly, and selecting columns by name could cause a server
SELECT *, crashes did not occur,
but columns in the outer query were not resolved properly.
MySQL permitted concurrent updates (including inserts and deletes) to a table if binary logging was enabled. Now, all updates are executed in a serialized fashion, because they are executed serialized when the binary log is replayed. (Bug #7879)
INFORMATION_SCHEMA columns that
contained catalog identifiers were of type
were changed to
N is the appropriate maximum
The number of columns in a row comparison against a subquery was calculated incorrectly. (Bug #8020)
GROUP_CONCAT() in the
select list when selecting from a view caused a server crash.
InnoDB: Honor the
--tmpdir startup option when
creating temporary files. Previously,
temporary files were always created in the temporary directory
of the operating system. On Netware,
will continue to ignore
--set-character-set option for
myisamchk was changed to
--set-collation. The value
needed for specifying how to sort indexes is a collation name,
not a character set name.
A rare race condition could cause
FLUSH TABLES WITH READ
LOCK to hang.
Prepared statements did not work correctly with
mysqlbinlog did not print
PSEUDO_THREAD_ID statements in front of
INFILE statements inserting into temporary tables,
thus causing potential problems when rolling forward these
statements after restoring a backup.
Selecting from a view defined as a join caused a server crash if the query cache was enabled. (Bug #8054)
Setting the initial
AUTO_INCREMENT value for
InnoDB table using
CREATE TABLE ...
AUTO_INCREMENT = did not
TABLE ... AUTO_INCREMENT =
did not reset the current
Matching of table names by mysqlhotcopy now
DBD::mysql versions 2.9003 and
up, which implement identifier quoting.
%” characters in the names of
tables to be dumped as wildcard characters.
A slave running MySQL 3.23.51 or newer hung while trying to
connect to a master running MySQL 3.23.50 or older. (This
occurred due to a bug in the old masters—
@@ caused the
server to hang—which was fixed in MySQL 3.23.50.)
mysqldump now avoids writing
NAMES to the dump output if the server is older than
version 4.1 and would not understand that statement.
Praparing a query using the
CONVERT_TZ() function with
constant arguments caused the server to crash.
Stored functions that used cursors could return incorrect results. (Bug #8386)
An expression that tested a case-insensitive character column
against string constants that differed in lettercase could fail
because the constants were treated as having a binary collation.
WHERE city='London' AND
city='london' could fail.)
(Bug #7098, Bug #8690)
LOAD INDEX statement now loads
the index into memory.
The server crashed when an error occurred during the filling of a temporary table created for handling a view or derived table. (Bug #7413)
Corrected a problem with references to
where statements such as
SELECT 1 AS a FROM
DUAL would succeed but statements such as
SELECT 1 AS a FROM DUAL LIMIT 1 failed.
Use of a view in a correlated subquery that contains
HAVING but no
caused a server crash.
When setting integer system variables to a negative value with
SET VARIABLES, the value was treated as a
positive value modulo 232.
The definition of the enumeration-valued
sql_mode column of the
mysql.proc table was missing some of the
current permissible SQL modes, so stored routines would not
necessarily execute with the SQL mode in effect at the time of
A deadlock could occur on an update followed by a
SELECT on an
InnoDB table without any explicit locks being
InnoDB now takes an exclusive lock
INSERT ON DUPLICATE KEY UPDATE is
checking duplicate keys.
Platform and architecture information in version information
--version option on Windows was
Win95/Win98 (i32). More accurately
determine platform as
Win64 for 32-bit or 64-bit Windows, and
ia32 for x86,
ia64 for Itanium, and
TRUNCATE TABLE did not work
within stored procedures. Now, within stored procedures,
TRUNCATE TABLE is executed in the
same way as
DELETE. This change
was necessary because
TABLE implicitly locks tables.
Starting and stopping the slave thread (only) could in some circumstance cause the server to crash. (Bug #6148)
mysqld_safe now understands the
--help option. Previously, it ignored the
option and attempted to start the server anyway.
The combination of
trunc* operators in a full-text search did
not work correctly. Using more than one truncated negative
search term caused the result to be empty.
Prepared statements now gives warnings on prepare.
Creating a table using a name containing a character that is
resulted in the character being stripped from the name and no
error. The character now is considered an error.
Host name matching didn't work if a netmask was specified for table-specific privileges. (Bug #3309)
DISTINCT named an index column multiple times in the
select list, the server tried to access different key fields for
each instance of the column, which could result in a crash.
LEFT OUTER JOIN between an empty base table
and a view on an empty base table caused a server crash.
CREATE TABLE ...
LIKE failed on Windows when the source or destination
table was located in a symlinked database directory.
Incorrectly ordered results were returned from a query using a
FULLTEXT index to retrieve rows and there was
another index that was usable for
For such a query,
fulltext join type, but
showed the other (not
FULLTEXT) index in the
--expire-logs-days was not
honored if using only transactions.
Fixed a problem in
NO_BACKSLASH_ESCAPES SQL mode
for strings that contained both the string quoting character and
In strict or traditional SQL mode, too-long string values
assigned to string columns (
BLOB) were correctly truncated,
but the server returned an SQLSTATE value of
01000 (should be
(Bug #9029, Bug #6999)
Re-execution of prepared statements containing subqueries caused the server to crash. (Bug #8125)
Handling of trailing spaces was incorrect for the
ucs2 character set.
Certain correlated subqueries with forward references (referring to an alias defined later in the outer query) could crash the server. (Bug #8025)
When performing boolean full-text searches on
utf8 columns, a double-quote character in the
search string caused the server to crash.
A number of portability issues relating to overflow in floating point values were corrected.
Comparing a nested row expression (such as
ROW(1,(2,3))) with a subquery caused the
server to crash.
mysqlbinlog forgot to add backquotes around
the collation of user variables (causing later parsing problems
BINARY is a reserved word).
A problem with equality propagation optimization for prepared statements and stored procedures caused a server crash upon re-execution of the prepared statement or stored procedure. (Bug #8115, Bug #8849)
References: See also Bug #51650.
A symlink vulnerability in the mysqlaccess script was reported by Javier Fernandez-Sanguino Pena and Debian Security Audit Team. (CVE-2005-0004)
Erroneous output resulted from
DISTINCT combined with a subquery and
Ordering by an unsigned expression (more complex than a column
reference) was treating the value as signed, producing
incorrectly sorted results.
HAVING was also
treating unsigned columns as signed.
The output of the
\s) command in mysql had
the values for the server and client character sets reversed.
InnoDB: Use native
tmpfile() function on Netware. All
InnoDB temporary files are created under
InnoDB temporary files were never deleted on
When using the
(or any function adding spaces to the right) in a query that had
to be resolved by using a temporary table, all resulting strings
had rightmost spaces removed (that is,
RPAD() did not work)
MyISAM table on Windows had
INDEX DIRECTORY or
DIRECTORY table options, mysqldump
dumped the directory path names with single-backslash path name
separators. This would cause syntax errors when importing the
dump file. mysqldump now changes
/” in the path names on
Prepared statements using
SUM(DISTINCT...) did not perform
DELETE FROM when the
WHERE ... ORDER BY
ORDER BY column was
qualified with the table name caused the server to crash.
A sequence of
SET autocommit = 0),
FLUSH TABLES WITH READ
LOCK, transactional update,
FLUSH TABLES WITH READ
LOCK could hang the connection forever and possibly
the MySQL server itself. This happened for example when running
innobackup script several times.
could fail to return
NULL for invalid values
col_name was declared as
A Table is full error occurred when the
table was still smaller than
For a stored function that refers to a given table, invoking the function while selecting from the same table resulted in a server crash. (Bug #8405)
MyISAM table indexes could
TRUNCATE TABLE if the
table had already been opened. For example, this was possible if
the table had been opened implicitly by selecting from a
MERGE table that mapped to the
MyISAM table. The server now issues an error
TRUNCATE TABLE under
of references to stored functions within views was incorrect and
could result in a server crash.
Giving mysqld a
caused it to crash.
The Cyrillic letters
(Й) were treated as being the same
character by the
Comparing the result of a subquery to a nonexistent column caused the server to crash. This issue affected MySQL on Windows platforms only. (Bug #7885)
HAVING caused the server to crash.
CHAR() function was not
NULL arguments, contrary to the
Ensured that mysqldump --single-transaction
sets its transaction isolation level to
REPEATABLE READ before
proceeding (otherwise if the MySQL server was configured to run
with a default isolation level lower than
REPEATABLE READ it could give
an inconsistent dump).
mysql_fix_privilege_tables now makes it
mysql privilege tables created
in MySQL 5.0 to be used with MySQL 4.1. This makes it possible
to downgrade from 5.0 to 4.1, or to run MySQL 4.1 and 5.0 using
the same privilege table files for testing purposes.
Creating a user with grants failed when specifying a password but worked without one. (Bug #7905)