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.
This release includes all fixes in MySQL 4.0.16 and most of the fixes in MySQL 4.0.17.
Functionality Added or Changed
Incompatible Change: Renamed
the C API
mysql_get_metadata() because the old
name was confusing.
Incompatible Change: Client
authentication now is based on 41-byte passwords in the
user table, not 45-byte passwords as in
4.1.0. Any 45-byte passwords created for 4.1.0 must be reset
after running the
Replication: Replication over SSL now works.
REPAIR TABLE, and
FLUSH statements are now stored
in the binary log and thus replicated to slaves. This logging
does not occur if the optional
NO_WRITE_TO_BINLOG keyword (or its alias
LOCAL) is given. Exceptions are that
FLUSH TABLES WITH
READ LOCK are not logged in any case. For a syntax
example, see Section 126.96.36.199, “FLUSH Syntax”.
SQLSTATE codes for all server errors.
CHECKSUM TABLE statement
for reporting table checksum values.
TIME columns with hour values
greater than 24 were returned incorrectly to the client.
Added the OLAP (On-Line Analytical Processing) function
ROLLUP, which provides summary rows for
GROUP BY level.
You can revoke all privileges from a user with
ALL PRIVILEGES, GRANT FROM user_list.
LIMIT no longer accepts negative arguments
(they used to be treated as very big positive numbers before).
COERCIBILITY() function to
return the collation coercibility of a string.
statement to drop an account that has no privileges.
New global system variable
relay_log_purge to enable or
disable automatic relay log purging.
generates a warning if the named storage engine is not
available. The table is still created as a
MyISAM table, as before.
It is now possible to create a
MyISAM tables in different databases.
Formerly, all the
MyISAM tables had to be
in the same database, and the
had to be created in that database as well.
All queries in which at least one
SELECT does not use indexes
properly now are written to the slow query log when long log
format is used.
MyISAM tables now use a better checksum
algorithm (if checksum is enabled with
CREATE TABLE ...
CHECKSUM = 1). Old tables will appear to have
incorrect checksum, and should be repaired.
Produce warnings even for single-row
INSERT statements, not just for
Previously, it was necessary to set
sql_warnings = 1 to generate
warnings for single-row statements.
Table aliases are not case sensitive if
Changed that the relay log is flushed to disk by the slave I/O thread every time it reads a relay log event. This reduces the risk of losing some part of the relay log in case of brutal crash.
Phrase search in
MATCH ... AGAINST ( ... IN BOOLEAN
MODE) no longer matches partial words.
mysqldump now includes a statement in the
dump output to set
foreign_key_checks to 0 to
avoid problems with tables having to be reloaded in a
particular order when the dump is reloaded. The existing
foreign_key_checks value is
saved and restored.
command to the mysql command-line client
for changing the statement delimiter (terminator). The default
delimiter is semicolon.
The interface to aggregate user-defined functions has changed
a bit. You must now declare a
function for each aggregate function
used instead of
MATCH ... AGAINST( ... WITH QUERY
EXPANSION) and the
API client function to enable multiple statement handling in
the server to be enabled or disabled.
Seconds_Behind_Master columns to the output
SHOW SLAVE STATUS.
Slave_IO_State indicates the state of the
slave I/O thread, and
indicates the number of seconds by which the slave is late
compared to the master.
MySQL source distributions now also include the MySQL
Most subqueries are now much faster than before.
START SLAVE statement now
UNTIL clause for specifying
that the slave SQL thread should be started but run only until
it reaches a given position in the master's binary logs or in
the slave's relay logs.
Added support for syntax
table2 (LIKE table1) that creates an empty table
table2 with a definition that is exactly
the same as
table1, including any indexes.
server option now also makes aliases case insensitive. (Bug
IGNORE option for
Full-text search now supports multi-byte character sets and
utf8 character set. (The
ucs2 character set is not yet
option to suppress the usual behavior of generating the next
sequence number when zero is stored in an
AUTO_INCREMENT column. With this mode
enabled, zero is stored as zero; only storing
NULL generates a sequence number.
DEFAULT before table and database
default character set. This enables us to use
TABLE to change the character set for all
tbl_name ... CHARACTER
TEXT columns in a table.
Added aggregate function
BIT_XOR() for bitwise XOR
variable from myisamchk.
DATABASE() function now
NULL rather than the empty string
if there is no database selected.
variables to provide information about character sets and
SHOW BDB LOGS as an alias for
It is now possible to create multiple key caches, assign table indexes to particular caches, and to preload indexes into caches. See Section 188.8.131.52, “CACHE INDEX Syntax”. See Section 184.108.40.206, “LOAD INDEX INTO CACHE Syntax”. Structured system variables are introduced as a means of grouping related key cache parameters. See Section 220.127.116.11, “Structured System Variables”.
mysql_next_result() C API
function now returns
-1 if there are no
more result sets.
--secure-auth option to
mysql command-line client. If this option
is set, the client refuses to send passwords in old
CLIENT_MULTI_QUERIES connect option
CLIENT_MULTI_STATEMENTS. To permit
a transition period, the old option continues to be recognized
for a while.
TEXT columns now have lengths
measured in characters rather than in bytes. The character
size depends on the column's character set. This means, for
example, that a
for a multi-byte character set takes more storage than before.
Similarly, index values on such columns are measured in
characters, not bytes.
Security Fix: A server
compiled without SSL support still permitted connections by
users who had the
REQUIRE SSL option
specified for their accounts.
Security Fix: Connections from some IP addresses were assigned incorrect database-level privileges. A connection could be assigned the database privileges of the previous successful authentication from one of those IP addresses, even if the IP address user name and database name were different. (Bug #1636)
Replication: The new
PASSWORD() function in 4.1 is
now properly replicated. (Bug #344)
Replication: When an
undefined user variable was used in a updating query on the
master (such as
INSERT INTO t VALUES(@a),
@a had never been set by this
connection before), the slave could replicate the query
incorrectly if a previous transaction on the master used a
user variable of the same name. (Bug #1331)
CONNECTION_ID() now is properly
replicated. (Bug #177)
failed between a 3.23 master and a 4.0 slave. The slave lost
replicated temporary tables if
was issued on the master. (Bug #254)
Replication: When a
transaction spanned two or more relay logs, and the slave was
stopped while executing the part of the transaction that was
in the second or later relay log, replication resumed at the
beginning of the second or later relay log, which was
incorrect. (It should resume at
the first relay log.) (Bug #53)
WARNINGS no longer are reserved words. (Bug
A memory overrun could occur due to in subqueries in the
SELECT list with
WHERE clause larger than that of the outer
WHERE clause. (Bug #726)
? prepared statement parameter as
the argument to certain functions or statement clauses caused
a server crash when
invoked. (Bug #1500)
Error-handling functions were not called properly when an
error resulted from
[CREATE | REPLACE| INSERT] ...
REPAIR TABLE ... USE_FRM could cause data
loss when used with tables that contained
TIMESTAMP columns and were
created in 4.0.x.
EXPLAIN on a derived
table with a join caused the server to crash.
occasionally failed due an error in the size of the string
allocated to it.
mysql parser erroneously interpreted a
; character within a multi-line comment
/* ... */) as a statement terminator.
ROLLUP did not work correctly when all
tables in the join were
const tables. (Bug #714)
The final character was omitted from the output of
USER(). (Bug #447)
LIMIT could cause the
server to crash.
ORDER BY and
BY clauses were not processed correctly. (Bug #442)
Under certain, rare circumstances table corruption was caused
DELETE from a large table
with a “new” (created by MySQL-4.1) full-text
SLAVE START (which is a deprecated syntax,
START SLAVE should be used
instead) could crash the slave. (Bug #2516)
UNION operations that involved
temporary tables could cause the server to crash.
Attempting to create a table containing a spatial (GIS) column using a storage engine that does not support spatial types cause the server to crash.
When no host name is specified in
SET PASSWORD FOR
user, it now defaults to
instead of the current host.
MyISAM tables with
FULLTEXT indexes created in MySQL 4.0 were
unreadable by MySQL 4.1.
CREATE FULLTEXT INDEX was not supported.
Double the required amount of memory was freed by the server.
Name resolution of columns of reduced subqueries in
UNION statements was not always
performed correctly. (Bug #745)
Columns of reduced subqueries were not always handled correctly. (Bug #679)
Following a call to
placeholders were permitted in all consequent statements, even
if they were not prepared. (Bug #1946)
Privileges could be escalation using database wildcards in
GRANT statements. (Bug #3924)
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)
A problem with
NULL values from being inserted into result
set columns where the first
SELECT of the
NULL columns. The type and maximum length of the
result column are now defined based on all parts of the