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.
A consequence of the change in handling of the
NUMERICfixed-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 as
DECIMALcolumn values were passed as
REAL_RESULTvalues 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
BIGINT. 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.
Incompatible Change: The C API
ER_WARN_DATA_TRUNCATEDwarning symbol was renamed to
MySQL Cluster; Replication: Added a new global system variable
slave_transaction_retries: If the replication slave SQL thread fails to execute a transaction because of an
InnoDBdeadlock or exceeded
TransactionInactiveTimeout, it automatically retries
slave_transaction_retriestimes before stopping with an error. The default is 10. (Bug #8325)
MySQL Cluster: When using this storage engine, the output of
SHOW TABLE STATUSnow displays properly calculated values in the
Data_lengthcolumns. (Note that
BLOBcolumns are not yet taken into account.) In addition, the number of replicas is now shown in the
Replication: If the MySQL server is started without an argument to
--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.
LOAD DATAstatement was extended to support user variables in the target column list, and an optional
SETclause. 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
LOAD DATAwas changed, so you can't replicate such statements from a 5.0.3 master to pre-5.0.3 slaves.
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.
index_mergequery execution plans were chosen on IRIX. (Bug #8578)
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
Ready for connectionsmessage that is written to the error log at startup. (Bug #8444)
CHECKSUM TABLEreturns a warning for nonexisting tables. The checksum value remains
NULLas before. (Bug #8256)
Setting the connection collation to a value different from the server collation followed by a
CREATE TABLEstatement that included a quoted default value resulted in a server crash. (Bug #8235)
When a client releases a user-level lock,
DO 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. (Bug #7998)
InnoDB: Corrected a bug in the crash recovery of
ROW_FORMAT=COMPACTtables that caused corruption. There may still be bugs in the crash recovery, especially in
COMPACTtables. (Bug #7973)
Permitted the service-installation command for Windows servers to specify a single option other than
--defaults-filefollowing the service name. This is for compatibility with MySQL 4.1. (Bug #7856)
Changed XML format for mysql from
<field name="to enable proper encoding of column names that are not legal as element names. (Bug #7811)
SHOW CREATE TABLEnow uses
TYPEto specify an index type. (Bug #7233)
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. (Bug #7150)
Out-of-order packets were sent (
EOF) following a
KILL QUERYstatement. (Bug #6804)
sql_notessession variable to cause
Note-level warnings not to be recorded. (Bug #6662)
mysql_library_end()as synonyms for the
mysql_server_end()C API functions.
#definesymbols, 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 uses
libmysqld. (Bug #6149)
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. (Bug #3190)
MyISAMstorage engine detects corruption of a
MyISAMtable, a message describing the problem now is written to the error log.
InnoDB: A commit is now performed after every 10,000 copied rows when executing
OPTIMIZE TABLE. This makes recovery from an aborted operations of these types much faster than previous to this change.
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 specifying
ROW_FORMAT=REDUNDANT. The new format (
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
INSERT ... SELECT, single table
UPDATE ... (SELECT)and single table
DELETE ... (SELECT)clauses when
innodb_locks_unsafe_for_binlogis used and isolation level of the transaction is not
InnoDBuses consistent read in these cases for a selected table.
my.cnfin 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_HOMEis used instead of the hard-coded path.
API change: the
reconnectflag in the
MYSQLstructure is now set to 0 by
mysql_real_connect(). Only those client programs which didn't explicitly set this flag to 0 or 1 after
mysql_real_connect()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).
engine_condition_pushdownsystem variable. For NDB, setting this variable to 1 permits processing of some
WHEREclause conditions to be processed in NDB nodes before rows are sent to the MySQL server, rather than having rows sent to the server for evaluation.
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 collationserrors.
COERCIBILITY()was modified to accommodate this new coercibility value. See Information Functions.
Security improvement: The server creates
.ISMtable 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. (CVE-2005-0711)
cp932(SJIS for Windows Japanese) and
eucjpms(UJIS for Windows Japanese) character sets.
Added support for
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
fcntl()does; however, the flush request is ignored by some external devices. Failure to flush the buffers may cause severe database corruption at power outages.
SHOW COLUMNS, and so forth, display information about the
INFORMATION_SCHEMAdatabase. Also, several
SHOWstatements now accept a
WHEREclause specifying which output rows to display. See INFORMATION_SCHEMA Tables.
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.
InnoDB: Upgrading from 4.1: The sorting order for end-space in
InnoDBtables has changed. Starting from 5.0.3,
TEXTcolumns as space-padded at the end. If you have a nonunique index on a
TEXTcolumn, you should run
CHECK TABLEon it, and run
OPTIMIZE TABLEif the check reports errors. If you have a
UNIQUE INDEXon a
TEXTcolumn, you should rebuild the table with
Support for the
ISAMstorage engine has been removed. If you have
ISAMtables, you should convert them before upgrading. See Changes Affecting Upgrades to 5.0.
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.
From the Windows distribution, predefined accounts without passwords for remote users (
''@'%') were removed (other distributions never had them).
errormember to the
MYSQL_BINDdata 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 the new
MYSQL_REPORT_DATA_TRUNCATIONoption for the
mysql_options()C API function.
MyISAMtables has been removed. If you have tables that use these options, you should convert them before upgrading. See Changes Affecting Upgrades to 5.0.
The MySQL server aborts immediately instead of simply issuing a warning if it is started with the
--log-binoption 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).
InnoDBstatus variables. See Server Status Variables.
BITin column definitions now is a distinct data type; it no longer is treated as a synonym for
User variable coercibility has been changed from “coercible” to “implicit.” That is, user variables have the same coercibility as column values.
HEAP) can have
MAX_USER_CONNECTIONSlimit, 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-limitsoption to get the old behavior.
Security improvement: User-defined functions should have at least one symbol defined in addition to the
xxxsymbol that corresponds to the main
xxx()function. These auxiliary symbols correspond to the
xxx_add()functions. mysqld by default no longer loads UDFs unless they have at least one auxiliary symbol defined in addition to the main symbol. The
--allow-suspicious-udfsoption controls whether UDFs that have only an
xxxsymbol can be loaded. By default, the option is off. mysqld also checks UDF file names when it reads them from the
mysql.functable and rejects those that contain directory path name separator characters. (It already checked names as given in
CREATE FUNCTIONstatements.) See UDF Calling Sequences for Simple Functions, UDF Calling Sequences for Aggregate Functions, and UDF Security Precautions. Thanks to Stefano Di Paola
<firstname.lastname@example.org>for finding and informing us about this issue. (CVE-2005-0709, CVE-2005-0710)
mysqlbinlog now prints a
ROLLBACKstatement 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 executed. The
ROLLBACKis harmless if the binary log file was written and closed normally.
The binary log file and binary log index file now are handled the same way as
MyISAMtables when there is a “disk full” or “quota exceeded” error. See How MySQL Handles a Full Disk.
CREATE USERprivilege was added.
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.
Implemented support for XA transactions. See XA Transactions. The implementation makes the
--innodb-safe-binlogoption obsolete, so it has been removed.
FEDERATEDstorage engine. See The FEDERATED Storage Engine.
Bit-field values can be written using
valueis a binary value written using 0s and 1s.
Additional control over transaction completion was implemented. The
AND [NO] CHAINand
RELEASEclauses. There is a new
RELEASE SAVEPOINTstatement. The
completion_typesystem variable was added for setting the global and session default completion type.
SHOW COLUMNSnow displays
NOrather than blank in the
Nulloutput column if the corresponding table column cannot be
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)
Replication: A replication master stamped a generated statement (such as a
SETstatement) 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. (Bug #8412)
Replication: If the slave was running with
--replicate-*-tableoptions which excluded one temporary table and included another, and the two tables were used in a single
DROP TEMPORARY TABLE IF EXISTSstatement, 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. (Bug #8055)
Replication: Multiple-table updates did not replicate properly to slave servers where
--replicate-*-tableoptions had been specified. (Bug #7011)
Replication: DDL statements for views were not being written to the binary log (and thus not subject to replication). (Bug #4838)
mysqldump misinterpreted “
_” and “
%” characters in the names of tables to be dumped as wildcard characters. (Bug #9123)
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
22001). (Bug #9029, Bug #6999)
The definition of the enumeration-valued
sql_modecolumn of the
mysql.proctable 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 routine definition. (Bug #8902)
TRUNCATE TABLEdid not work within stored procedures. Now, within stored procedures,
TRUNCATE TABLEis executed in the same way as
DELETE. This change was necessary because
TRUNCATE TABLEimplicitly locks tables. (Bug #8850)
A rare race condition could cause
FLUSH TABLES WITH READ LOCKto hang. (Bug #8682)
AES_DECRYPT(could fail to return
NULLfor invalid values in
col_namewas declared as
NOT NULL. (Bug #8669)
SELECT DISTINCTnamed 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. (Bug #8532)
MATCH ... AGAINSTin natural language mode could cause a server crash if the
FULLTEXTindex was not used in a join (that is,
EXPLAINdid not show
fulltextjoin mode) and the search query matched no rows in the table. (Bug #8522)
REPAIR TABLEdid not invalidate query results in the query cache that were generated from the table. (Bug #8480)
LOAD INDEXstatement now loads the index into memory. (Bug #8452)
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)
DELETE FROMwhen the
tbl_name... WHERE ... ORDER BY
ORDER BYcolumn was qualified with the table name caused the server to crash. (Bug #8392)
Stored functions that used cursors could return incorrect results. (Bug #8386)
The Cyrillic letters
SHORT I(Й) were treated as being the same character by the
utf8_general_cicollation. (Bug #8385)
When performing boolean full-text searches on
utf8columns, a double-quote character in the search string caused the server to crash. (Bug #8351)
--set-character-setoption 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. (Bug #8349)
MyISAMtable indexes could occur with
TRUNCATE TABLEif the table had already been opened. For example, this was possible if the table had been opened implicitly by selecting from a
MERGEtable that mapped to the
MyISAMtable. The server now issues an error message for
TRUNCATE TABLEunder these conditions. (Bug #8306)
Selecting from an
INFORMATION_SCHEMAtable combined with a subquery on an
INFORMATION_SCHEMAtable caused an error with the message
Table. (Bug #8164)
Matching of table names by mysqlhotcopy now accommodates
DBD::mysqlversions 2.9003 and up, which implement identifier quoting. (Bug #8136)
Re-execution of prepared statements containing subqueries caused the server to crash. (Bug #8125)
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.
Selecting from a view defined as a join caused a server crash if the query cache was enabled. (Bug #8054)
Creating a table using a name containing a character that is illegal in
character_set_clientresulted in the character being stripped from the name and no error. The character now is considered an error. (Bug #8041)
Certain correlated subqueries with forward references (referring to an alias defined later in the outer query) could crash the server. (Bug #8025)
Corrected a problem with references to
DUALwhere statements such as
SELECT 1 AS a FROM DUALwould succeed but statements such as
SELECT 1 AS a FROM DUAL LIMIT 1failed. (Bug #8023)
Comparing a nested row expression (such as
ROW(1,(2,3))) with a subquery caused the server to crash. (Bug #8022)
The number of columns in a row comparison against a subquery was calculated incorrectly. (Bug #8020)
mysqldump now avoids writing
SET NAMESto the dump output if the server is older than version 4.1 and would not understand that statement. (Bug #7997)
A deadlock could occur on an update followed by a
InnoDBtable without any explicit locks being taken.
InnoDBnow takes an exclusive lock when
INSERT ON DUPLICATE KEY UPDATEis checking duplicate keys. (Bug #7975)
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—
SELECT @@caused the server to hang—which was fixed in MySQL 3.23.50.) (Bug #7965)
Erroneous output resulted from
SELECT DISTINCTcombined with a subquery and
GROUP BY. (Bug #7946)
FOUND_ROWS()returned an incorrect value after a
SELECT SQL_CALC_FOUND_ROWS DISTINCTstatement that selected constants and included
LIMITclauses. (Bug #7945)
mysqld_safe now understands the
--helpoption. Previously, it ignored the option and attempted to start the server anyway. (Bug #7931)
Creating a user with grants failed when specifying a password but worked without one. (Bug #7905)
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)
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)
Ensured that mysqldump --single-transaction sets its transaction isolation level to
REPEATABLE READbefore proceeding (otherwise if the MySQL server was configured to run with a default isolation level lower than
REPEATABLE READit could give an inconsistent dump). (Bug #7850)
mysqlbinlog forgot to add backquotes around the collation of user variables (causing later parsing problems as
BINARYis a reserved word). (Bug #7793)
A Table is full error occurred when the table was still smaller than
max_heap_table_size. (Bug #7791)
HAVINGcaused the server to crash. (Bug #7769)
When encountering a disk full or quota exceeded write error,
MyISAMsometimes failed to sleep and retry the write, resulting in a corrupted table. (Bug #7714)
The output of the
\s) command in mysql had the values for the server and client character sets reversed. (Bug #7571)
LEFT OUTER JOINbetween an empty base table and a view on an empty base table caused a server crash. (Bug #7433)
Ordering by an unsigned expression (more complex than a column reference) was treating the value as signed, producing incorrectly sorted results.
HAVINGwas also treating unsigned columns as signed. (Bug #7425)
The server crashed when an error occurred during the filling of a temporary table created for handling a view or derived table. (Bug #7413)
Made the MySQL server accept executing
SHOW CREATE DATABASEeven if the connection has an open transaction or locked tables. Refusing it made mysqldump --single-transaction sometimes fail to print a complete
CREATE DATABASEstatement for some dumped databases. (Bug #7358)
Handling of trailing spaces was incorrect for the
ucs2character set. (Bug #7350)
--expire-logs-dayswas not honored if using only transactions. (Bug #7236)
INFORMATION_SCHEMAcolumns that contained catalog identifiers were of type
LONGTEXT. These were changed to
Nis the appropriate maximum identifier length. (Bug #7215)
GROUP_CONCAT()in the select list when selecting from a view caused a server crash. (Bug #7116)
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. (For example,
WHERE city='London' AND city='london'could fail.) (Bug #7098, Bug #8690)
Setting the initial
AUTO_INCREMENTvalue for an
CREATE TABLE ... AUTO_INCREMENT =did not work, and
ALTER TABLE ... AUTO_INCREMENT =did not reset the current value. (Bug #7061)
When setting integer system variables to a negative value with
SET VARIABLES, the value was treated as a positive value modulo 232. (Bug #6958)
Use of a view in a correlated subquery that contains
GROUP BYcaused a server crash. (Bug #6894)
Praparing a query using the
CONVERT_TZ()function with constant arguments caused the server to crash. (Bug #6849)
mysql_list_fields()of references to stored functions within views was incorrect and could result in a server crash. (Bug #6814)
A sequence of
SET autocommit = 0),
FLUSH TABLES WITH READ LOCK, transactional update,
FLUSH TABLES WITH READ LOCKcould hang the connection forever and possibly the MySQL server itself. This happened for example when running the
innobackupscript several times. (Bug #6732)
CREATE TABLE .. SELECTquery to the binary log when the insertion of new records partially failed. (Bug #6682)
mysqlbinlog did not print
SET PSEUDO_THREAD_IDstatements in front of
LOAD DATA INFILEstatements inserting into temporary tables, thus causing potential problems when rolling forward these statements after restoring a backup. (Bug #6671)
MyISAMtable on Windows had
DATA DIRECTORYtable 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 “
\” to “
/” in the path names on Windows. (Bug #6660)
Incorrectly ordered results were returned from a query using a
FULLTEXTindex to retrieve rows and there was another index that was usable for
ORDER BY. For such a query,
fulltextjoin type, but showed the other (not
FULLTEXT) index in the
Keycolumn. (Bug #6635)
CREATE TABLE ... LIKEfailed on Windows when the source or destination table was located in a symlinked database directory. (Bug #6607)
Selecting from a view that had an
NOT EXISTSsubquery did not always work properly, and selecting columns by name could cause a server crash. With
SELECT *, crashes did not occur, but columns in the outer query were not resolved properly. (Bug #6394)
Fixed a problem in
NO_BACKSLASH_ESCAPESSQL mode for strings that contained both the string quoting character and backslash. (Bug #6368)
CHAR()function was not ignoring
NULLarguments, contrary to the documentation. (Bug #6317)
Starting and stopping the slave thread (only) could in some circumstance cause the server to crash. (Bug #6148)
InnoDB: Honor the
--tmpdirstartup option when creating temporary files. Previously,
InnoDBtemporary files were always created in the temporary directory of the operating system. On Netware,
InnoDBwill continue to ignore
--tmpdir. (Bug #5822)
Platform and architecture information in version information produced for
--versionoption on Windows was always
Win95/Win98 (i32). More accurately determine platform as
Win64for 32-bit or 64-bit Windows, and architecture as
ia64for Itanium, and
axpfor Alpha. (Bug #4445)
When using the
RPAD()function (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) (Bug #4048)
Host name matching didn't work if a netmask was specified for table-specific privileges. (Bug #3309)
A symlink vulnerability in the mysqlaccess script was reported by Javier Fernandez-Sanguino Pena and Debian Security Audit Team. (CVE-2005-0004)
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 using
SUM(DISTINCT...)did not perform correctly.
Prepared statements did not work correctly with
mysql_fix_privilege_tablesnow makes it possible for
mysqlprivilege 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.
Prepared statements now gives warnings on prepare.
A number of portability issues relating to overflow in floating point values were corrected.
Giving mysqld a
SIGHUPcaused it to crash.
InnoDB: Use native
tmpfile()function on Netware. All
InnoDBtemporary files are created under
InnoDBtemporary files were never deleted on Netware.