End of Product Lifecycle. Active development and support for MySQL Database Server versions 3.23, 4.0, and 4.1 has ended. For details, see http://www.mysql.com/about/legal/lifecycle/#calendar. Please consider upgrading to a recent version. Further updates to the content of this manual will be minimal. All formats of this manual will continue to be available until 31 Dec 2010.
Functionality Added or Changed
Security Fix: The
--defaults-file= option now requires that the file name must exist. (Bug #3413)
Incompatible Change: The signature of the
mysql_stmt_prepare() function was changed to
int mysql_stmt_prepare(MYSQL_STMT *stmt, const char *query, unsigned long length). To create a
MYSQL_STMT handle, you should use the
mysql_stmt_init() function, not
Incompatible Change: Renamed prepared statements C API functions:
|Old Name||New Name|
Now all functions that operate with a
MYSQL_STMT structure begin with the prefix
Incompatible Change: Added support for
DEFAULT CURRENT_TIMESTAMP and for
ON UPDATE CURRENT_TIMESTAMP specifications for
TIMESTAMP columns. Now you can explicitly say that a
TIMESTAMP column should be set automatically to the current timestamp for
UPDATE statements, or even prevent the column from updating automatically. Only one column with such an auto-set feature per table is supported.
TIMESTAMP columns created with earlier versions of MySQL behave as before. Behavior of
TIMESTAMP columns that were created without explicit specification of default/on as earlier depends on its position in table: If it is the first
TIMESTAMP column, it be treated as having been specified as
TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. In other cases, it would be treated as a
TIMESTAMP DEFAULT 0 column.
NOW is supported as an alias for
Unlike in previous versions, explicit specification of default values for
TIMESTAMP column is never ignored and turns off the auto-set feature (unless you have
CURRENT_TIMESTAMP as the default).
Incompatible Change: String comparison now works according to the SQL standard. Because we have that
'a' = 'a ' then from it must follow that
'a' > 'a\t'. (The latter was not the case before MySQL 4.1.2.) To implement it, we had to change how storage engines compare strings internally. As a side effect, if you have a table where a
VARCHAR column in some row has a value with the last character less than
ASCII(32), you have to repair this table.
CHECK TABLES tells you if this problem exists. (Bug #3152)
Incompatible Change: Handling of the
DOUBLE floating-point data types is more strict to follow standard SQL. For example, a data type of
FLOAT(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 4.1.2 and that contain floating-point data not strictly legal for the column type, you should alter the data types of those columns. For example:
Incompatible Change: The
Type output column for
SHOW TABLE STATUS now is labeled
Replication: For replication of
HEAP) tables: Made the master automatically write a
DELETE FROM statement to its binary log when a
MEMORY table is opened for the first time since the master's startup. This is for the case where the slave has replicated a nonempty
MEMORY table, and then the master is shut down and restarted: the table is now empty on the master; the
DELETE FROM empties it on the slave as well.
Even with this fix, between the master's restart and the first use of the table on master, the slave still has out-of-date data in the table. However, if you use the
--init-file option to populate the
MEMORY table on the master at startup, it ensures that the failing time interval is zero. (Bug #2477)
Replication: Added option
UUID() function implemented. Note that it does not work with replication yet. See Section 11.14, “Miscellaneous Functions”.
MySQL now issues a warning when a
ENUM column with duplicate values in the list is created. (Bug #1427)
ft_boolean_syntax variable now can be changed while the server is running. See Section 5.1.3, “Server System Variables”.
MyISAM tables now support keys up to 1000 bytes long.
mysql command-line client now supports multiple
-e options. (Bug #591)
CHAR BYTE is an alias for the
BINARY data type. (Previously, it was an alias for
mysqlhotcopy now works on NetWare.
Prepared statements now work with all types of subqueries.
--default-storage-engine server option as a synonym for
InnoDB tables now support index prefix lengths up to 1000 bytes long.
The mysqld Windows server was renamed to mysqld-debug. See Section 2.3.8, “Selecting a MySQL Server Type”.
The MySQL server now returns an error if
SET sql_log_bin or
SET sql_log_update is issued by a user without the
SUPER privilege (in previous versions it just silently ignored the statement in this case).
init_slave system variables. The values should be SQL statements to be executed when each client connects or each time a slave's SQL thread starts, respectively.
mysql_set_local_infile_default() C API functions.
Handler_discover status variable.
C API enhancement:
SERVER_QUERY_NO_GOOD_INDEX_USED flags are now set in the
server_status field of the
MYSQL structure. It is these flags that make the query to be logged as slow if mysqld was started with
DECIMAL types now obey (precision,scale) settings. (Bug #10897)
The mysql command-line client no longer stores in the history file multiple copies of identical queries that are run consecutively.
If you try to create a key with a key part that is too long, and it is safe to auto-truncate it to a smaller length, MySQL now does so. A warning is generated, rather than an error.
The improved character set support introduced in MySQL 4.1.0 for the
HEAP storage engines is now available for
InnoDB as well.
latin1_spanish_ci (Modern Spanish) collation for the
latin1 character set.
Binlog_cache_disk_use status variables that count the number of transactions that used transaction binary log and that had to flush this temporary binary log to disk instead of using only the in-memory buffer. They can be used for tuning the
binlog_cache_size system variable.
storage_engine system variable as a synonym for
Added explanation of hidden
UNION in output of
EXPLAIN SELECT statement.
EXAMPLE storage engine.
Internal string-to-number conversion now supports only SQL:2003 compatible syntax for numbers. In particular,
'0x10'+0 does not work anymore. (Actually, it worked only on some systems before, such as Linux. It did not work on others, such as FreeBSD or Solaris. Making these queries OS-independent was the goal of this change.) Use
CONV() to convert hexadecimal numbers to decimal. Example:
--log-warnings server option now is enabled by default. Disable with
ENGINE table option as a synonym for the
TYPE option for
CREATE TABLE and
UNHEX() function implemented. See Section 11.5, “String Functions”.
Multi-line statements in the
mysql command-line client now are stored in the history file as a single line.
ALTER TABLE DROP PRIMARY KEY no longer drops the first
UNIQUE index if there is no primary index. (Bug #2361)
The Mac OS X Startup Item has been moved from the directory
/Library/StartupItems/MySQLCOM to avoid a file name collision with the MySQL Startup Item installed with Mac OS X Server. See Section 2.12.2, “Mac OS X Notes”.
MySQL now supports up to 64 indexes per table.
myisam_data_pointer_size system variable. See Section 5.1.3, “Server System Variables”.
A name of “Primary” no longer can be specified as an index name. (That name is reserved for the
PRIMARY KEY if the table has one.) (Bug #856)
sql_select_limit variable has no influence on subqueries. (Bug #2600)
REVOKE ALL PRIVILEGES, GRANT FROM user_list is changed to a more consistent
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_list. (Bug #2642)
--to-last-log to mysqlbinlog, for use in conjunction with
When a session having open temporary tables terminates, the statement automatically written to the binary log is now
DROP TEMPORARY TABLE IF EXISTS instead of
DROP TEMPORARY TABLE, for more robustness.
Added support for character set conversion and
MYSQL_TYPE_BLOB type code in prepared statement protocol.
Changed that when the MySQL server has binary logging disabled (that is, no
--log-bin option was used), then no transaction binary log cache is allocated for connections. This should save
binlog_cache_size bytes of memory (32KB by default) for every connection.
mysqld_multi now creates the log in the directory named by
datadir (from the
[mysqld] section in
my.cnf or compiled in), not in
/tmp. Thanks to Christian Hammers from Debian Security Team for reporting this. (CVE-2004-0388)
SHOW GRANTS with no
FOR clause or with
FOR CURRENT_USER() shows the privileges for the current session.
Packaging: In the Mac OS DMG
postinstall script, mysql_install_db was invoked with an invalid argument.
Replication: Replication: a rare race condition in the slave SQL thread that could lead to an incorrect complaint that the relay log is corrupted. (Bug #2011)
Replication: Removed a misleading "check permissions on master.info" from a replication error message, because the cause of the problem could be different from permissions. (Bug #2121)
Replication: A MySQL slave server built using
--with-debug, and replicating itself, crashed. (Bug #3568)
DELETE statements were never replicated by the slave if there were any
--replicate-*-table options. (Bug #2527)
server-id was not set using startup options but with
SET GLOBAL, the replication slave still complained that it was not set. (Bug #3829)
Replication: If a replication slave was unable to create the first relay log, it crashed. (Bug #2145)
Replication: In some replication error messages, a very long query caused the rest of the message to be invisible (truncated), by putting the query last in the message. (Bug #3357)
Replication: Memory could be corrupted by replicating a
LOAD DATA INFILE from a MySQL 3.23 master. Some less critical issues remain; see Section 14.7, “Replication Features and Issues”. (Bug #3422)
Replication: Replication: in the slave SQL thread, a multiple-table
UPDATE could produce an incorrect complaint that some record was not found in one table, if the
UPDATE was preceded by a
INSERT ... SELECT. (Bug #1701)
DELETE statements were always replicated by the slave if there were some
--replicate-*-ignore-table options and no
--replicate-*-do-table options. (Bug #3461)
Replication: Following a nonfatal error during the execution of a statement that later succeeded, the master failed to reset the error code to 0, so the error code was written into the binary log. This caused false Did not get the same error as on master errors on the slave. (Bug #2083)
Replication: Corrected the master's binary log position that
InnoDB reports when it is doing a crash recovery on a slave server. (Bug #3015)
Replication: Changed that when a thread handling
INSERT DELAYED (also known as a
delayed_insert thread) is killed, its statements are recorded with an error code of value zero (killing such a thread does not endanger replication, so we thus avoid a superfluous error on the slave). (Bug #3081)
CREATE TABLE ... LIKE ... statements were not always written to the binary log. (Bug #2557)
Replication: Replication: If a client connects to a slave server and issues an administrative statement for a table (for example,
OPTIMIZE TABLE or
REPAIR TABLE), this could sometimes stop the slave SQL thread. This does not lead to any corruption, but you must use
START SLAVE to get replication going again. (Bug #1858)
--replicate-wild-*-table rules now apply to
ALTER DATABASE when the table pattern is
%, as is the case for
CREATE DATABASE and
DROP DATABASE. (Bug #3000)
Replication: Statements did not raise errors on the slave, if the slave was excluded given the
--replicate-* options in use at the time. The effect of this problem was: when a statement was killed on the master, the slave stopped. (Bug #2983)
A rare error condition caused the slave SQL thread spuriously to print the message Binlog has bad magic number and stop when it was not necessary to do so. (Bug #3401)
Queries with subqueries in the
FROM clause now lock all tables at once.
EXPLAIN of subqueries in
FROM output was also not handled correctly. (Bug #2120)
Prepare statements parameter do not cause error message as fields used in select list but not included in
ORDER BY list.
Attempting to bind a negative value bind to unsigned caused an Unknown error. (Bug #3223)
UNION statements did not consult
sql_select_limit value when set. This is now fixed properly, which means that this limit is applied to the top level query, unless
LIMIT for entire
UNION is used.
GROUP_CONCAT() had a number of issues with
ORDER BY and
DISTINCT, and with
GROUP BY in subqueries. (Bug #2695, Bug #3319, Bug #3381)
Full-text indexing of strings in multi-byte (all besides
utf8) charsets could sometimes hang. (Bug #2065)
SHOW GRANTS and
EXPLAIN SELECT did not always perform character set conversion correctly. (Bug #3403)
REPAIR TABLE could corrupt a table containing
FULLTEXT indexes and many words of different lengths that are considered equal (which is possible in certain collations, such as
utf8_general_ci). (Bug #3835)
RAND() in subqueries with static tables was not always handled correctly. (Bug #2645)
Rotate event was found by the slave SQL thread in the middle of a transaction, the value of
SHOW SLAVE STATUS was incorrectly altered. (Bug #3017)
HEAP table status for
BTREE indexes was not calculated correctly. (Bug #2719)
Exec_master_log_pos column and its disk image in the
relay-log.info were not handled correctly if the master had version 3.23. (The value was too big by six bytes.) This bug does not exist in MySQL 5.0. (Bug #3400)
mysql_stmt_store_result() could hang if they were called without a prior call to
mysql_stmt_execute(). Now they give an error instead. (Bug #2248)
CHECK TABLE sometimes produced a spurious error Found key at page ... that points to record outside datafile for a table with a
FULLTEXT index. (Bug #2190)
CHECK TABLE that sometimes a spurious error Found key at page ... that points to record outside datafile for a table with a
FULLTEXT index. (Bug #1977)
SOME subqueries was not performed well (key field present in subquery). (Bug #3646)
Starting mysqld with binary logging disabled, but with a nonzero value for the
expire_logs_days system variable caused the server to crash. (Bug #3807)
Full-text search on multi-byte character sets (such as UTF8) that appeared when a search word was shorter than a matching word from the index (for example, searching for “Uppsala” when the table contains “Uppsa*la”). (Bug #3011)
On Linux platforms, setting the
character_set_results variable to
NULL and then attempting to retrieve it using
SELECT @@character_set_results caused the server to crash. (Bug #3296)
The output of mysqldump --tab was not correct. (Bug #2705)
Added support for unsigned integer types to prepared statement API . (Bug #3035)
An issue with the range optimizer caused a segmentation fault on some very rare queries. (Bug #2698)
INTERVAL() function did not work correctly when 8 or more comparison arguments were used. (Bug #1561)
Made clearer the error message that one gets when an update is refused because of the
--read-only option. (Bug #2757)
UTF8 charset breaks joins with mixed column/string constant. (Bug #2959)
Prepared statements are supported for
SHOW statements. All other statements are now prohibited by the prepared statement interface. (Bug #3406, Bug #3398, Bug #2811)
LOAD DATA FROM MASTER after
RESET SLAVE caused a segmentation fault. (Bug #2922)
EXPLAIN should now work correctly with
UNION queries. (Bug #3639)
UNION operations with the
InnoDB storage engine, when some columns from one table were used in one
SELECT statement and some were used in another
SELECT statement, were not handled correctly. (Bug #2552)
Incorrect error message when wrong table used in multiple-table
DELETE statement in prepared statements. (Bug #3411)
SHOW CREATE TABLE ... did not properly double quotation marks. (Bug #2593)
CREATE ... SELECT sometimes created a string column with a multi-byte character set (such as
UTF8) of insufficient length for holding the data.
The second execution of a prepared statement using
UNION caused the server to crash. (Bug #3577)
Removed try to check
NULL if index built on column where
NULL is impossible in
IN subquery optimization. (Bug #2393)
GROUP_CONCAT() function on an expression with
ORDER BY as well as an external
ORDER BY in a query caused the server to crash. (Bug #3752)
Changed that when a
DROP TEMPORARY TABLE statement is automatically written to the binary log when a session ends, the statement is recorded with an error code of value zero (this ensures that killing a
SELECT on the master does not result in a superfluous error on the slave). (Bug #3063)
Short-form IP addresses used as arguments to
INET_ATON() were not parsed correctly. (Bug #2310)
Results of aggregate functions used in subqueries with empty result sets were incorrect. (Bug #3505)
mysqlbinlog --read-from-remote-server now print the exact positions of events in lines beginning with
at # in the log. (Bug #3214)
Compiling the server using the
--with-pstack options did not work with binutils 2.13.90. (Bug #1661)
Changed the column
SHOW SLAVE STATUS to never show a value of -1. (Bug #2826)
Packaging: Added missing file
mysql_create_system_tables to the server RPM package. This bug was fixed for the 4.1.1 RPMs by updating the MySQL-server RPM from
MySQL-server-4.1.1-1. The other RPMs were not affected by this change.
A memory leak could occur with
INSERT ... ON DUPLICATE KEY UPDATE. (Bug #2438)
Privileges were not checked correctly for
ALTER TABLE RENAME. (Bug #3270)
The MySQL server did not report any error if a statement (submitted through
mysql_stmt_prepare()) was terminated by garbage characters. This can happen if you pass a wrong
length parameter to these functions. The result was that the garbage characters were written into the binary log. (Bug #2703)
Max_used_connections was less than the actual maximum number of connections in use simultaneously.
Aggregate functions could lead to server crashes when used in prepared statements. (Bug #3360)
mysqlbinlog --read-from-remote-server read all binary logs following the one that was requested. It now stops at the end of the requested file, the same as it does when reading a local binary log. There is an option
--to-last-log to get the old behavior. (Bug #3204)
Full-text indexing of UTF8 data did not work correctly. (Bug #2033)
--local-load option of mysqlbinlog now requires an argument.
CONCAT_WS() makes the server die in case of illegal mix of collations. (Bug #3087)
The mysql client program crashed when passed a database name that was longer then expected. (Bug #2221)
Table names in were quoted in mysqldump when using values for the server SQL mode where this was not appropriate. (Bug #2591)
ANALYZE TABLE on a
BDB table inside a transaction caused the server to hang. (Bug #2342)
DROP DATABASE now reports the number of tables deleted.
Using an impossible
PROCEDURE ANALYSE() caused the server to hang. (Bug #2238)
Parallel repair (myisamchk -p,
myisam_repair_threads) sometimes failed to repair a table. (Bug #1334)
Added optimization that enables prepared statements using a large number of tables or tables with a large number of columns to be re-executed significantly faster. (Bug #2050)
A memory leak occurred in the client library when a statement handle was freed on a closed connection (call to
mysql_close()). (Bug #3073)
Invalid results were returned when
CAST() was applied to
NULL to obtain a signed or unsigned integer value. (Bug #2219)
FLUSH TABLES sometimes corrupted table resolution for statements which were prepared before the
FLUSH TABLES but which were being executed repeatedly afterward. (Bug #3307)
GRANT did not handle table-level privileges correctly. (Bug #2178)
mysql_set_server_option() caused client/server communications to be broken. (Bug #2207)
UPDATE statement resulted in an error when one of the tables was not updated but was used in the nested query contained therein.
Subqueries in the
FROM clause were not always parsed correctly. (Bug #2421)
There was a symlink vulnerability in the
mysqlbug script. (Bug #3284)
Table and column privileges were not loaded on startup. (Bug #2546)
UNION operations did not handle
NULL columns properly, when a column in the first
SELECT node was
NOT NULL. (Bug #2508)
vio_timeout() virtual function was not set for all protocols. This lead to crashes on Windows. (Bug #2025)
You can now call
mysql_stmt_attr_set(..., STMT_ATTR_UPDATE_MAX_LENGTH) to tell the client library to update
MYSQL_FIELD->max_length when doing
mysql_stmt_store_result(). . (Bug #1647)
A deadlock ocurred when two
START SLAVE statements were run at the same time. (Bug #2921)
Comparison of table and database names when using the
--lower_case_table_names option was not always performed correctly. (Bug #2880)
A query that uses both
UNION [DISTINCT] and
UNION ALL now works correctly. (Bug #1428)
mysql_stmt_send_long_data() misbehaved on the second execution of a prepared statement when long data had zero length. (Bug #1664)
Table default character set affects
LONGBLOB columns. (Bug #2821)
UPDATE privilege for tables which are not updated in multiple-table
UPDATE statement in prepared statements.
Segmentation faults could occur when processing malformed prepared statements. (Bug #2795, Bug #2274)
mysqldump did not quote names containing backtick characters (
`) correctly. (Bug #2592)
The results of a query that used
ORDER BY by a column's real name, while the column had an alias specified in the
SELECT clause, were not returned in the correct order. (Bug #3681)
MySQL-client RPM package against
libreadline instead of
libedit. (Bug #2289)
A password was not checked for changes in
GRANT accounts until
FLUSH PRIVILEGES was executed. (Bug #3404)
AND did not always work correctly. (Bug #2838)
ALTER DATABASE caused the client to hang if the database did not exist. (Bug #2333)
ORDER BY did not always work correctly with
SMALLINT columns. (Bug #2147)
When a password was assigned to an account at the global level and then privileges were granted at the database level (without specifying any password), the existing password was replaced temporarily in memory until the next
FLUSH PRIVILEGES operation or the server was restarted. (Bug #2953)
FLOAT columns could store out of range values. (Bug #2082)
A prepared statement using parameters and having a subquery in the
FROM clause could cause the server to crash. (Bug #3020)
MATCH ... AGAINST() on a phrase search operator with a missing closing double quote caused the server to crash. (Bug #2708)
mysql_stmt_affected_rows() now always returns the number of rows affected by a given statement. (Bug #2247)
ALTER TABLE RENAME, was used to rename a table with the same name in another database, it silently dropped the destination table if it existed. (Bug #2628)
CONVERT( is now supported again.
mysqld could crash when a table was altered and used at the same time. This was a 4.1.2-specific bug. . (Bug #3643)
Write operations on a column hvaing a
FULLTEXT index could under some rare circumstances lead to table file corruption. (Bug #2417)
mysqlbinlog failed to print a
USE statement under those rare circumstances where the binary log contained a
LOAD DATA INFILE statement. (Bug #3415)