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_prepare_result()
function
to 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
mysql_fix_privilege_tables script.
Replication: Replication over SSL now works.
Replication:
ANALYZE TABLE
,
OPTIMIZE TABLE
,
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 LOGS
,
FLUSH MASTER
,
FLUSH SLAVE
,
and FLUSH TABLES WITH
READ LOCK
are not logged in any case. For a syntax
example, see Section 12.4.6.2, “FLUSH Syntax”.
Added SQLSTATE
codes for all server errors.
New CHECKSUM TABLE
statement
for reporting table checksum values.
Added new type
values
DAY_MICROSECOND
,
HOUR_MICROSECOND
,
MINUTE_MICROSECOND
,
SECOND_MICROSECOND
, and
MICROSECOND
for
DATE_ADD()
,
DATE_SUB()
, and
EXTRACT()
.
TIME
columns with hour values
greater than 24 were returned incorrectly to the client.
Added new syntax for ADDDATE()
and SUBDATE()
. The second
argument now may be a number representing the number of days
to be added to or subtracted from the first date argument.
Disabled the PURGE LOGS
statement that was
added in version 4.1.0. The statement now should be issued as
PURGE
MASTER LOGS
or PURGE BINARY
LOGS
.
Added the OLAP (On-Line Analytical Processing) function
ROLLUP
, which provides summary rows for
each GROUP BY
level.
You can revoke all privileges from a user with REVOKE
ALL PRIVILEGES, GRANT FROM user_list
.
LIMIT
no longer accepts negative arguments
(they used to be treated as very big positive numbers before).
New COERCIBILITY()
function to
return the collation coercibility of a string.
Added DROP USER
'
statement to drop an account that has no privileges.
user_name
'@'host_name
'
Added new COMPRESS()
,
UNCOMPRESS()
, and
UNCOMPRESSED_LENGTH()
functions.
New global system variable
relay_log_purge
to enable or
disable automatic relay log purging.
CREATE TABLE
now
generates a warning if the named storage engine is not
available. The table is still created as a
tbl_name
(...)
TYPE=storage_engine
MyISAM
table, as before.
Added new ADDTIME()
,
DATE()
,
DATEDIFF()
,
LAST_DAY()
,
MAKEDATE()
,
MAKETIME()
,
MICROSECOND()
,
SUBTIME()
,
TIME()
,
TIMEDIFF()
,
TIMESTAMP()
,
UTC_DATE()
,
UTC_TIME()
,
UTC_TIMESTAMP()
, and
WEEKOFYEAR()
functions.
It is now possible to create a MERGE
table
from MyISAM
tables in different databases.
Formerly, all the MyISAM
tables had to be
in the same database, and the MERGE
table
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.
Added PURGE BINARY LOGS
as an
alias for
PURGE
MASTER LOGS
.
Produce warnings even for single-row
INSERT
statements, not just for
multiple-row INSERT
statements.
Previously, it was necessary to set
sql_warnings = 1
to generate
warnings for single-row statements.
The --quote-names
option for
mysqldump now is enabled by default.
Table aliases are not case sensitive if
lower_case_table_names
is
nonzero.
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.
Added secure_auth
global
server system variable and
--secure-auth
server option
that disallow authentication for accounts that have old
(pre-4.1.1) passwords.
Added new %f
microseconds format specifier
for DATE_FORMAT()
and
TIME_FORMAT()
.
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.
Added delimiter
(\d
)
command to the mysql command-line client
for changing the statement delimiter (terminator). The default
delimiter is semicolon.
Added preload_buffer_size
system variable.
The interface to aggregate user-defined functions has changed
a bit. You must now declare a xxx_clear()
function for each aggregate function
XXX()
. xxx_clear()
is
used instead of xxx_reset()
.
Added MATCH ... AGAINST( ... WITH QUERY
EXPANSION)
and the
ft_query_expansion_limit
system variable.
Added
mysql_set_server_option()
C
API client function to enable multiple statement handling in
the server to be enabled or disabled.
Added Slave_IO_State
and
Seconds_Behind_Master
columns to the output
of SHOW SLAVE STATUS
.
Slave_IO_State
indicates the state of the
slave I/O thread, and Seconds_Behind_Master
indicates the number of seconds by which the slave is late
compared to the master.
MySQL source distributions now also include the MySQL
Internals Manual internals.texi
.
Most subqueries are now much faster than before.
The START SLAVE
statement now
supports an 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
CREATE TABLE
table2 (LIKE table1)
that creates an empty table
table2
with a definition that is exactly
the same as table1
, including any indexes.
LOAD DATA
now produces warnings
that can be fetched with SHOW
WARNINGS
.
The --lower-case-table-names=1
server option now also makes aliases case insensitive. (Bug
#534)
Renamed bdb_version
system
variable to version_bdb
.
Added mysql_sqlstate()
and
mysql_stmt_sqlstate()
C API
client functions that return the SQLSTATE
error code for the last error.
Added IGNORE
option for
DELETE
statement.
EXPLAIN
now supports an
EXTENDED
option. When given,
EXPLAIN
generates extra
information that may be viewed with the
SHOW WARNINGS
statement.
Full-text search now supports multi-byte character sets and
the Unicode utf8
character set. (The
Unicode ucs2
character set is not yet
supported.)
Added SHOW
MASTER LOGS
as an alias for
SHOW BINARY LOGS
. (In 4.1.0,
SHOW MASTER
LOGS
was renamed to SHOW BINARY
LOGS
. Now you can use either one.)
Added
--sql-mode=NO_AUTO_VALUE_ON_ZERO
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.
Require DEFAULT
before table and database
default character set. This enables us to use ALTER
TABLE
to change the character set for all
tbl_name
... CHARACTER
SET=...CHAR
,
VARCHAR
, and
TEXT
columns in a table.
Added aggregate function
BIT_XOR()
for bitwise XOR
operations.
Removed unused ft_max_word_len_for_sort
system variable.
Removed unused ft_max_word_len_for_sort
variable from myisamchk.
The DATABASE()
function now
returns NULL
rather than the empty string
if there is no database selected.
Added character_set_client
,
character_set_connection
,
character_set_database
,
character_set_results
,
character_set_server
,
character_set_system
,
collation_connection
,
collation_database
, and
collation_server
system
variables to provide information about character sets and
collations.
Added SHOW BDB LOGS
as an alias for
SHOW LOGS
.
It is now possible to create multiple key caches, assign table indexes to particular caches, and to preload indexes into caches. See Section 12.4.6.1, “CACHE INDEX Syntax”. See Section 12.4.6.4, “LOAD INDEX INTO CACHE Syntax”. Structured system variables are introduced as a means of grouping related key cache parameters. See Section 5.1.4.1, “Structured System Variables”.
The mysql_next_result()
C API
function now returns -1
if there are no
more result sets.
Added --secure-auth
option to
mysql command-line client. If this option
is set, the client refuses to send passwords in old
(pre-4.1.1) format.
Renamed CLIENT_MULTI_QUERIES
connect option
flag to CLIENT_MULTI_STATEMENTS
. To permit
a transition period, the old option continues to be recognized
for a while.
CHAR
,
VARCHAR
, and
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
CHAR(
column
for a multi-byte character set takes more storage than before.
Similarly, index values on such columns are measured in
characters, not bytes.
n
)
When using SET sql_mode='mode'
for a
complex mode (such as ANSI
),
we now update the sql_mode
variable to include all the individual options implied by the
complex mode.
The --old-protocol
option for
mysqld is no longer supported and has been
removed.
Bugs Fixed
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)
,
where @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)
Replication:
CONNECTION_ID()
now is properly
replicated. (Bug #177)
Replication: Replication
failed between a 3.23 master and a 4.0 slave. The slave lost
replicated temporary tables if
FLUSH LOGS
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
BEGIN
, in
the first relay log.) (Bug #53)
LAST_INSERT_ID()
now returns 0
if the last INSERT
statement
didn't insert any rows.
HASH
, BTREE
,
RTREE
, ERRORS
, and
WARNINGS
no longer are reserved words. (Bug
#724)
A memory overrun could occur due to in subqueries in the
SELECT
list with
WHERE
clause larger than that of the outer
query's WHERE
clause. (Bug #726)
Using the ?
prepared statement parameter as
the argument to certain functions or statement clauses caused
a server crash when mysql_prepare()
was
invoked. (Bug #1500)
Error-handling functions were not called properly when an
error resulted from [CREATE | REPLACE| INSERT] ...
SELECT
statements.
REPAIR TABLE ... USE_FRM
could cause data
loss when used with tables that contained
TIMESTAMP
columns and were
created in 4.0.x.
A SELECT
that required a
temporary table (marked by Using temporary
in EXPLAIN
output) and was used
as a derived table in EXPLAIN
statement caused the server to crash. (Bug #251)
Using EXPLAIN
on a derived
table with a join caused the server to crash.
The USER()
function
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.
The types and lengths of result set columns for
UNION
operations are now
determined taking into account values for all
SELECT
statements in the
UNION
, and not just the first
SELECT
.
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)
DELETE
with ORDER
BY
and LIMIT
could cause the
server to crash.
Subqueries in ORDER BY
and GROUP
BY
clauses were not processed correctly. (Bug #442)
Under certain, rare circumstances table corruption was caused
by a DELETE
from a large table
with a “new” (created by MySQL-4.1) full-text
index.
UNION
with an empty select list
and a nonexistent column being used in some of the individual
SELECT
statements could cause
the server to crash.
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.
Names of outer columns of subqueries in
INSERT
/REPLACE
statements were not resolved correctly. (Bug #446)
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 mysql_prepare()
,
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)
When 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 UNION
kept
NULL
values from being inserted into result
set columns where the first
SELECT
of the
UNION
retrieved NOT
NULL
columns. The type and maximum length of the
result column are now defined based on all parts of the
UNION
.
User Comments