Functionality added or changed:
IGNORE option for
The MySQL source distribution now also includes the MySQL
mysql_set_server_option() C API
client function to allow multiple statement handling in the
server to be enabled or disabled.
mysql_next_result() C API function
-1 if there are no more
option flag to
To allow for a transition period, the old option will
continue to be recognized for a while.
DEFAULT before table and database
default character set. This enables us to use
TABLE table_name ... CHARACTER SET=... to change
the character set for all
columns in a table.
MATCH ... AGAINST( ... WITH QUERY
EXPANSION) and the
ft_query_expansion_limit server variable.
Full-text search now supports multi-byte character sets and
utf8 character set. (The
ucs2 character set is not yet
Phrase search in
MATCH ... AGAINST ( ... IN BOOLEAN
MODE) no longer matches partial words.
Added aggregate function
bitwise XOR operations.
Replication over SSL now works.
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.
Produce warnings even for single-row
INSERT statements, not just for
Previously, it was necessary to set
SQL_WARNINGS=1 to generate warnings for
command to the
mysql command-line client
for changing the statement delimiter (terminator). The
default delimiter is semicolon.
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
CHAR(n) column for a multi-byte
character set will take more storage than before. Similarly,
index values on such columns are measured in characters, not
LIMIT no longer accepts negative
arguments (they used to be treated as very big positive
DATABASE() function now returns
NULL rather than the empty string if
there is no database selected.
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.
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
Added MySQL Server option and global variable 'secure-auth' that disallows authentication for accounts that have old (pre-4.1.1) passwords.
Added MySQL command line client option 'secure-auth'. If this option is set, client will refuse to send password in old (pre-4.1.1) format.
change! Renamed the C API
mysql_prepare_result() function to
mysql_get_metadata() as the old name was
DROP USER 'username'@'hostname'
statement to drop an account that has no privileges.
The interface to aggregated UDF functions has changed a bit.
You must now declare a
function for each aggregate function
CONCAT_WS() function no longer skips
Added new syntax for
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.
%f microseconds format
All queries in which at least one
does not use indexes properly now are written to the slow
query log when long log format is used.
It is now possible to create a
MyISAM tables in different
databases. Formerly, all the
tables had to be in the same database, and the
MERGE table had to be created in that
database as well.
SET sql_mode='mode' for a
complex mode (like
ANSI), we now update
sql_mode variable to include all the
individual options implied by the complex mode.
Added the OLAP (On-Line Analytical Processing) function
ROLLUP, which provides summary rows for
GROUP BY level.
SQLSTATE codes for all server
mysql_stmt_sqlstate() C API client
functions that return the
code for the last error.
TIME columns with hour values greater
than 24 were returned incorrectly to the client.
statements are now stored in the binary log and thus
replicated to slaves. This logging does not occur if the
NO_WRITE_TO_BINLOG keyword (or
LOCAL) is given. Exceptions are
FLUSH SLAVE, and
FLUSH TABLES WITH READ LOCK are not
logged in any case. For a syntax example, see
New global variable
enable or disable automatic relay log purging.
LOAD DATA now produces warnings that can
be fetched with
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
CREATE TABLE table_name (...)
TYPE=storage_engine now generates a warning if the
named storage engine is not available. The table is still
created as a
MyISAM table, as before.
Most subqueries are now much faster than before.
PURGE BINARY LOGS as an alias for
PURGE MASTER LOGS.
PURGE LOGS statement that
was added in in version 4.1.0. The statement now should be
PURGE MASTER LOGS or
PURGE BINARY LOGS.
SHOW BDB LOGS as an alias for
SHOW MASTER LOGS (which had been
deleted in version 4.1.0) as an alias for
Seconds_Behind_Master columns to the
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
--lower-case-table-names=1 now also makes
aliases case insensitive. (Bug#534)
Fixed merging types and length of result set columns for
UNION operations. The types and lengths
now are determined taking into account values for all
SELECT statements in the
UNION, not just the first
Fixed a bug in privilege handling that caused connections from certain IP addresses to be 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 username and database name were different. (Bug#1636)
Error-handling functions were not called properly when an
error resulted from
[CREATE | REPLACE| INSERT] ...
WARNINGS no longer are reserved words.
Fix for bug in
ROLLUP when all tables
const tables. (Bug#714)
Fixed a bug in
UNION that prohibited
NULL values from being inserted into
result set columns where the first
Fixed name resolution of columns of reduced subqueries in unions. (Bug#745)
Fixed memory overrun in subqueries in select list with
WHERE clause bigger than outer query
WHERE clause. (Bug#726)
Fixed a bug that caused
FULLTEXT indexes created in 4.0.x to
be unreadable in 4.1.x.
Fixed a data loss bug in
REPAIR TABLE ...
USE_FRM when used with tables that contained
TIMESTAMP columns and were created in
Fixed reduced subquery processing in
GROUP BY clauses. (Bug#442)
Fixed name resolution of outer columns of subquery in
Fixed bug in marking columns of reduced subqueries. (Bug#679)
Fixed a bug that made
INDEX syntax illegal.
Fixed a crash when a
SELECT that required
a temporary table (marked by
was used as a derived table in
Fixed a rare table corruption bug in
DELETE from a big table with a
new (created by MySQL-4.1)
LAST_INSERT_ID() now returns 0 if the
INSERT statement didn't insert any
Fixed missing last character in function output. (Bug#447)
Fixed a rare replication bug 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. Then replication would resume at the
beginning of the second or later relay log, which was
incorrect. (It should resume at
the first relay log.) (Bug#53)
CONNECTION_ID() now is properly
PASSWORD() function in 4.1 is now
properly replicated. (Bug#344)
Fixed a bug with double freed memory.
Fixed a crashing bug in
that involved temporary tables.
Fixed a crashing bug in
EXPLAIN is used on a
DERIVED TABLES with a join.
Fixed a crashing bug in
ORDER BY and
caused by an uninitialized array of reference pointers.
Fixed a bug in the
USER() function caused
by an error in the size of the allocated string.
Fixed a crashing bug when attempting to create a table containing a spatial (GIS) column with a storage engine that does not support spatial types.
Fixed a crashing bug in
UNION caused by
the empty select list and a non-existent column being used
in some of the individual
Fixed a replication bug with a 3.23 master and a 4.0 slave:
The slave lost the replicated temporary tables if
FLUSH LOGS was issued on the master. (Bug#254)
Fixed a security bug: A server compiled without SSL support
still allowed connections by users that had the
REQUIRE SSL option specified for their
When an undefined user variable was used in a updating query
on the master (such as
INSERT INTO t
@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)
Fixed bug with prepared statements: Using the
? prepared statement parameter as the
argument to certain functions or statement clauses caused a
server crash when
Fixed bug with prepared statements: after call to mysql_stmt_prepare placeholders became allowed in all consequent statements, even if they are not prepared (Bug#1946)
This is a translation of the MySQL Reference Manual that can be found at dev.mysql.com. The original Reference Manual is in English, and this translation is not necessarily as up to date as the English version.