This appendix lists the changes from version to version in the MySQL source code.
We are now working actively on MySQL 4.1 and 5.0, and will provide only critical bug fixes for MySQL 4.0 and MySQL 3.23. We update this section as we add new features, so that everybody can follow the development.
Our TODO section contains what further plans we have for 4.1 & 5.0. See section 1.6 MySQL and the Future (The TODO).
Note that we tend to update the manual at the same time we make changes to MySQL. If you find a version listed here that you can't find on the MySQL download page (http://www.mysql.com/downloads/), this means that the version has not yet been released!
The date mentioned with a release version is the date of the last BitKeeper ChangeSet that this particular release has been based on, not the date when the packages have been made available. The binaries are usually made available a few days after the date of the tagged ChangeSet - building and testing all packages takes some time.
The following changelog shows what has already been done in the 5.0 tree:
SELECT INTO list_of_vars, which can be of mixed,
that is, global and local type.
See section 19.1.6.3 SELECT ... INTO Statement.
--log-update, it will be
translated to --log-bin (or ignored if the server is explicitly
started with --log-bin), and a warning message will be written to
the error log. Setting SQL_LOG_UPDATE will silently set
SQL_LOG_BIN instead (or do nothing if the server is explicitly
started with --log-bin).
SET
@a=10; then SELECT @A; will now return 10.
Case sensitivity of a variable's value depends on the collation of the value.
For a full list of changes, please refer to the changelog sections for each individual 5.0.x release.
Functionality added or changed:
MEMORY (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 master's startup.
This is for the case where the slave has replicated a non-empty
MEMORY table, then the master is shut down and restarted: the table
is now empty on master; the DELETE FROM empties it on slave too. Note
that 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. But
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)
DROP TEMPORARY TABLE IF
EXISTS instead of DROP TEMPORARY TABLE, for more robustness.
SET SQL_LOG_BIN is issued by a
user without the SUPER privilege (in previous versions it just silently
ignored the statement in this case).
log-bin option was used) then no transaction binlog cache is allocated
for connections (this should save binlog_cache_size bytes of memory (32
kilobytes by default) for every connection).
Bugs fixed:
ALTER DATABASE caused the client to hang if the database did not
exist. (Bug #2333)
SLAVE START (which is a deprecated syntax, START SLAVE should be
used instead) could crash the slave. (Bug #2516)
DELETE statements were never replicated by the slave if
there were any replicate-*-table options. (Bug #2527)
mysql_real_query() or mysql_prepare()) was terminated by garbage
characters (which can happen if you pass a wrong length parameter to
mysql_real_query() or mysql_prepare()); the result was that the
garbage characters were written into the binary log. (Bug #2703)
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)
read-only option. (Bug #2757)
replicate-wild-*-table rules apply to ALTER DATABASE
when the table pattern is '%', like it is already the case for CREATE
DATABASE and DROP DATABASE. (Bug #3000)
Rotate event is found by the slave SQL thread in the
middle of a transaction, the value of Relay_Log_Pos in SHOW SLAVE
STATUS remains correct. (Bug #3017)
InnoDB reports when it is doing a
crash recovery on a slave server. (Bug #3015)
Seconds_Behind_Master in SHOW SLAVE STATUS to
never show a value of -1. (Bug #2826)
DROP TEMPORARY TABLE statement is automatically
written to the binlog 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)
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)
START SLAVE commands were run at the same
time. (Bug #2921)
replicate-* options. The bug was that if the
statement had been killed on the master, the slave would stop. (Bug #2983)
--local-load option of mysqlbinlog now requires an argument.
LOAD DATA FROM MASTER after
RESET SLAVE. (Bug #2922)
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 was it does when reading a local binary log. (Bug #3204)
mysqlbinlog --read-from-remote-server to print the exact
positions of events in the "at #" lines. (Bug #3214)
Binlog has bad magic number and stop when it
was not necessary to do so. (Bug #3401)
mysqlbinlog not to forget to print a USE statement
under rare circumstances where the binary log contained a LOAD DATA
INFILE statement. (Bug #3415)
LOAD DATA INFILE when the
master had version 3.23. (Bug #3422)
Functionality added or changed:
KILL statement now takes CONNECTION and QUERY
variants. The first is the same as KILL with no modifier (it
kills a given connection thread). The second kills only the statement
currently being executed by the connection.
TIMESTAMPADD() and TIMESTAMPDIFF() functions.
WEEK and QUARTER values as INTERVAL arguments
for DATE_ADD() and DATE_SUB() functions.
SQL_MODE, SQL_AUTO_IS_NULL, FOREIGN_KEY_CHECKS
(that one was already replicated since 4.0.14 but here it's done more
efficiently: takes less space in the binary logs), UNIQUE_CHECKS.
Other variables (like character sets, SQL_SELECT_LIMIT...) will be
replicated in next 5.0.x releases.
Merge Index optimization for OR clauses.
See section 7.2.5 How MySQL Optimizes OR Clauses.
SELECT INTO list_of_vars, which can be of mixed,
that is, global and local type.
See section 19.1.6.3 SELECT ... INTO Statement.
InnoDB are
not aware of multiple tablespaces.
Bugs fixed:
Version 4.1 of the MySQL server includes many enhancements and new features. Binaries for this version are available for download at http://www.mysql.com/downloads/mysql-4.1.html.
INSERT ... ON DUPLICATE KEY UPDATE ... syntax. This allows you to
UPDATE an existing row if the insert would cause a duplicate value
in a PRIMARY or UNIQUE key. (REPLACE allows you to
overwrite an existing row, which is something entirely different.)
See section 14.1.4 INSERT Syntax.
GROUP_CONCAT() aggregate function.
See section 13.9 Functions and Modifiers for Use with GROUP BY Clauses.
MyISAM tables with many tunable parameters. You can
have multiple key caches, preload index into caches for batches...
BTREE index on HEAP tables.
SHOW WARNINGS shows warnings for the last command.
See section 14.5.3.20 SHOW WARNINGS Syntax.
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table2 LIKE
table1.
HELP command that can be used in the mysql
command line client (and other clients) to get help for SQL statements.
For a full list of changes, please refer to the changelog sections for each individual 4.1.x release.
Functionality added or changed:
'a' = 'a ' then from it must follow that 'a' > 'a\t'.
(The latter was not the case before 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 CHAR or VARCHAR column in some row
has a value with the last character less than ASCII(32) you will have
to repair this table. CHECK TABLES will tell you if this problem
exists. (Bug #3152)
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 INSERT and/or 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 will
be treated as having been specified asTIMESTAMP 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 CURRENT_TIMESTAMP.
Warning: Incompatible change! 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).
| Old Name | New Name |
mysql_bind_param() | mysql_stmt_bind_param()
|
mysql_bind_result() | mysql_stmt_bind_result()
|
mysql_prepare() | mysql_stmt_prepare()
|
mysql_execute() | mysql_stmt_execute()
|
mysql_fetch() | mysql_stmt_fetch()
|
mysql_fetch_column() | mysql_stmt_fetch_column()
|
mysql_param_count() | mysql_stmt_param_count()
|
mysql_param_result() | mysql_stmt_param_metadata()
|
mysql_get_metadata() | mysql_stmt_result_metadata()
|
mysql_send_long_data() | mysql_stmt_send_long_data()
|
MYSQL_STMT structure begin with
the prefix mysql_stmt_.
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.
SHOW GRANTS with no FOR clause or with FOR
CURRENT_USER() shows the privileges for the current session.
MyISAM and HEAP storage engines is now available for
InnoDB as well.
SET or ENUM column with
duplicate values in the list is created. (Bug #1427)
SQL_SELECT_LIMIT variable has no influence on subqueries.
(Bug #2600)
UNHEX() function implemented.
See section 13.3 String Functions.
UUID() function implemented. Note that it does not work with replication
yet.
See section 13.8.4 Miscellaneous Functions.
MyISAM tables now support keys up to 1000 bytes long.
ft_boolean_syntax variable now can be changed while the server is
running.
See section 5.2.3 Server System Variables.
REVOKE ALL PRIVILEGES, GRANT FROM user_list
is changed to a more consistent
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_list. (Bug #2642)
'0x10'+0 will 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. E.g. CONV(MID('0x10',3),16,10)+0.
mysqlhotcopy now works on NetWare.
ALTER TABLE DROP PRIMARY KEY no longer drops the first UNIQUE
index if there is no primary index. (Bug #2361)
latin1_spanish_ci (Modern Spanish) collation for the latin1
character set.
ENGINE table option as a synonym for the TYPE option
for CREATE TABLE and ALTER TABLE.
--default-storage-engine server option as a synonym for
--default-table-type.
storage_engine system variable as a synonym for
table_type.
init_connect and init_slave server variables.
The values should be SQL statements to be executed when each client
connects or each time a slave's SQL thread starts, respectively.
SERVER_QUERY_NO_INDEX_USED and
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 --log-slow-queries
--log-queries-not-using-indexes.
MEMORY (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 master's startup.
This is for the case where the slave has replicated a non-empty
MEMORY table, then the master is shut down and restarted: the table
is now empty on master; the DELETE FROM empties it on slave too. Note
that 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. But
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)
DROP TEMPORARY TABLE IF
EXISTS instead of DROP TEMPORARY TABLE, for more robustness.
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).
log-bin option was used) then no transaction binlog cache is allocated
for connections (this should save binlog_cache_size bytes of memory (32
kilobytes by default) for every connection).
Binlog_cache_use and 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 buffer in memory. They can be used for tuning the
binlog_cache_size system variable.
Bugs fixed:
FROM
clause with parameter used. (Bug #3020)
multi-delete
statement in prepared statements. (Bug #3411)
UPDATE privilege for tables which will not be updated in
multi-update query in prepared statements.
INSERT, REPLACE,
CREATE, DELETE, SELECT, DO, SET and
SHOW. All other commands are prohibited via prepared statement
interface. (Bug ##3398, Bug #3406, Bug #2811)
GROUP_CONCAT(). (Bug #2695, #3381 and #3319)
character_set_results
variable to NULL.
(Bug #3296)
ANALYZE TABLE on a BDB table inside a transaction
that hangs server thread. (Bug #2342)
myisamchk -p, myisam_repair_threads);
sometimes the repair process failed to repair a table. (Bug #1334)
UNION [DISTINCT] and UNION ALL
now works correctly. (Bug #1428)
LONGBLOB columns. (Bug #2821)
CONCAT_WS() makes the server die in case of illegal mix of collations. (Bug #3087)
DROP DATABASE to report number of tables deleted.
mysql_stmt_close after
mysql_close). (Bug #3073)
OR and AND functions. (Bug #2838)
--lower_case_table_names option. (Bug #2880)
NULL if index built on column where NULL
is impossible in IN subquery optimization. (Bug #2393)
FROM clause (Bug #2421)
mysqldump for
various values of sql_mode of server (Bug #2591)
--with-pstack with binutils 2.13.90 (Bug #1661)
GRANT system. 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)
Max_used_connections to be less than
the actual maximum number of connections in use simultaneously.
Index_length in HEAP table status for
BTREE indexes. (Bug #2719)
mysql_stmt_affected_rows() call to always return number of rows
affected by given statement. (Bug #2247)
MATCH ... AGAINST() on a phrase search operator with
a missing closing double quote. (Bug #2708)
mysqldump --tab. (Bug #2705)
UNION operations that prevented proper handling of
NULL columns. This happened only if a column in the first
SELECT node was NOT NULL. (Bug #2508)
UNION operations with InnoDB storage engine,
when some columns from one table where used in one SELECT statement and
some were used in another SELECT statement. (Bug #2552)
SHOW CREATE TABLE ... which didn't properly double
quotes. (Bug #2593)
FROM clause locks all tables at once for now.
This also fixed bugs in EXPLAIN of subqueries
in FROM output. (Bug #2120)
mysqldump not quoting ``tricky'' names correctly.
(Bug #2592)
CREATE TABLE ... LIKE ... that resulted
in a statement not being written to the binary log. (Bug #2557)
INSERT ... ON DUPLICATE KEY UPDATE ....
(Bug #2438)
CONVERT(expression,type)
legal again.
INET_ATON(). (Bug #2310)
CREATE ... SELECT that sometimes caused a
string column with a multi-byte character set (such as utf8) to have
insufficient length to hold the data.
INSERT, REPLACE,
UPDATE, etc. but not DELETE) to a FULLTEXT index.
(Bug #2417)
MySQL-client RPM package against libreadline instead
of libedit. (Bug #2289)
vio_timeout() virtual function for all protocols. This bug occurred on
Windows. (Bug #2025)
mysql client program to erroneously
cache the value of the current database. (Bug #2025)
mysql_set_server_option() or
mysql_get_server_option() were invoked.
(Bug #2207)
CAST() was
applied on NULL to signed or unsigned integer column.
(Bug #2219)
mysql client program when
database name was longer then expected.
(Bug #2221)
CHECK TABLE that occasionally resulted in
spurious "Found key at page ... that points to record outside
datafile" error on a table with a FULLTEXT index. (Bug #2190)
GRANT with table-level privilege handling. (Bug #2178)
ORDER BY on a small column. (Bug #2147)
INTERVAL() function when 8 or more
comparison arguments are provided. (Bug #1561)
postinstall script
(mysql_install_db was called with an obsolete argument).
MySQL-server-4.1.1-0 to MySQL-server-4.1.1-1. The
other RPMs were not affected by this change.
CHECK TABLE that sometimes
resulted in a spurious error Found key at page ... that points to record
outside datafile for a table with a FULLTEXT index. (Bug #1977)
utf8) charsets. (Bug #2065)
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)
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)
MyISAM table first receives "no space left on device" but is able to finally
complete, see section A.4.3 How MySQL Handles a Full Disk); the bug was that the master forgot to reset the
error code to 0 after success, so the error code got into its binary log, thus
making the slave giving false alarms like "did not get the same error as on
master". (Bug #2083)
ALTER DATABASE caused the client to hang if the database did not
exist. (Bug #2333)
DELETE statements were never replicated by the slave if
there were any replicate-*-table options. (Bug #2527)
ALTER TABLE RENAME, when rename to the table with
the same name in another database silently dropped destination table if
it existed. (Bug #2628)
mysql_real_query() or mysql_prepare()) was terminated by garbage
characters (which can happen if you pass a wrong length parameter to
mysql_real_query() or mysql_prepare()); the result was that the
garbage characters were written into the binary log. (Bug #2703)
mysql_fetch and
mysql_stmt_store_result() to hang if they were called without
prior call of mysql_execute(). Now they give an error instead.
(Bug #2248)
read-only option. (Bug #2757)
replicate-wild-*-table rules apply to ALTER DATABASE
when the table pattern is '%', like it is already the case for CREATE
DATABASE and DROP DATABASE. (Bug #3000)
Rotate event is found by the slave SQL thread in the
middle of a transaction, the value of Relay_Log_Pos in SHOW SLAVE
STATUS remains correct. (Bug #3017)
InnoDB reports when it is doing a
crash recovery on a slave server. (Bug #3015)
Seconds_Behind_Master in SHOW SLAVE STATUS to
never show a value of -1. (Bug #2826)
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)
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)
START SLAVE commands were run at the same
time. (Bug #2921)
replicate-* options. The bug was that if the
statement had been killed on the master, the slave would stop. (Bug #2983)
--local-load option of mysqlbinlog now requires an argument.
LOAD DATA FROM MASTER after
RESET SLAVE. (Bug #2922)
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 was it does when reading a local binary log. (Bug #3204)
mysqlbinlog --read-from-remote-server to print the exact
positions of events in the "at #" lines. (Bug #3214)
Binlog has bad magic number and stop when it
was not necessary to do so. (Bug #3401)
Exec_master_log_pos column and its disk image in the
`relay-log.info' file to be correct 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)
mysqlbinlog not to forget to print a USE statement
under rare circumstances where the binary log contained a LOAD DATA
INFILE statement. (Bug #3415)
LOAD DATA INFILE when the
master had version 3.23. Some smaller problems remain in this setup,
See section 6.7 Replication Features and Known Problems. (Bug #3422)
This release includes all fixes in MySQL 4.0.16 and most of the fixes in MySQL 4.0.17.
Functionality added or changed:
CHECKSUM TABLE statement for reporting table checksum values.
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.
CACHE INDEX Syntax.
See section 14.5.4.4 LOAD INDEX INTO CACHE Syntax.
Structured system
variables are introduced as a means of grouping related key cache parameters.
See section 10.4.1 Structured System Variables.
COERCIBILITY() function to return the collation coercibility of a
string.
--quote-names option for mysqldump now is enabled
by default.
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.
InnoDB-4.1.1 or higher,
you cannot downgrade to a version lower than 4.1.1
any more! That is because earlier versions of InnoDB are not aware of
multiple tablespaces.
REVOKE ALL PRIVILEGES, GRANT FROM user_list.
IGNORE option for DELETE statement.
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 now returns -1 if there
are no more result sets.
CLIENT_MULTI_QUERIES connect option flag to
CLIENT_MULTI_STATEMENTS. 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 ALTER TABLE tbl_name ... CHARACTER SET=...
to change the character set for all CHAR, VARCHAR, and
TEXT columns in a table.
MATCH ... AGAINST( ... WITH QUERY EXPANSION) and the
ft_query_expansion_limit server variable.
ft_max_word_len_for_sort server variable.
utf8 character set. (The Unicode ucs2 character set is not
yet supported.)
MATCH ... AGAINST ( ... IN BOOLEAN MODE) no longer
matches partial words.
BIT_XOR() for bitwise XOR operations.
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.
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.
delimiter (\d) command to the mysql command-line
client for changing the statement delimiter (terminator).
The default delimiter is semicolon.
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(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 bytes.
LIMIT no longer accepts negative arguments
(they used to be treated as very big positive numbers before).
DATABASE() function now returns NULL rather than the empty
string if there is no database selected.
--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.
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.
secure_auth global server system variable and --secure-auth
server option that disallow authentication for accounts that have old
(pre-4.1.1) passwords.
--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.
mysql_prepare_result() function to
mysql_get_metadata() as the old name was confusing.
DROP USER 'username'@'hostname' statement to drop an account
that has no privileges.
xxx_clear() function for each aggregate function XXX().
ADDTIME(), DATE(), DATEDIFF(), LAST_DAY(),
MAKEDATE(), MAKETIME(), MICROSECOND(), SUBTIME(),
TIME(), TIMEDIFF(), TIMESTAMP(), UTC_DATE(),
UTC_TIME(), UTC_TIMESTAMP(), and WEEKOFYEAR()
functions.
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.
type values DAY_MICROSECOND,
HOUR_MICROSECOND, MINUTE_MICROSECOND,
SECOND_MICROSECOND, and MICROSECOND
for DATE_ADD(), DATE_SUB(), and EXTRACT().
%f microseconds format specifier for DATE_FORMAT() and
TIME_FORMAT().
SELECT does not use indexes properly
now are written to the slow query log when long log format is used.
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.
COMPRESS(), UNCOMPRESS(), and
UNCOMPRESSED_LENGTH() functions.
SET sql_mode='mode' for a complex mode (like ANSI), we
now update the sql_mode variable to include all the individual options
implied by the complex mode.
ROLLUP, which
provides summary rows for each GROUP BY level.
SQLSTATE codes for all server errors.
mysql_sqlstate() and mysql_stmt_sqlstate() C API client
functions that return the SQLSTATE error code for the last error.
TIME columns with hour values greater than 24 were returned incorrectly
to the client.
ANALYZE, OPTIMIZE, REPAIR, 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 14.5.4.2 FLUSH Syntax.
relay_log_purge to enable or disable automatic
relay log purging.
LOAD DATA now produces warnings that can be fetched with
SHOW WARNINGS.
CREATE TABLE table2 (LIKE table1)
that creates an empty table table2 with a definition that is
exactly the same as table1, including any indexes.
CREATE TABLE tbl_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.
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 issued as PURGE MASTER LOGS or
PURGE BINARY LOGS.
SHOW BDB LOGS as an alias for SHOW LOGS.
SHOW MASTER LOGS (which had been deleted in version
4.1.0) as an alias for SHOW BINARY LOGS.
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.
--lower-case-table-names=1 server option now also makes aliases case
insensitive. (Bug #534)
Bugs fixed:
mysql parser not to erroneously interpret `;' character
within /* ... */ comment as statement terminator.
UNION
operations. The types and lengths now are determined taking into
account values for all SELECT statements in the UNION,
not just the first SELECT.
[CREATE | REPLACE| INSERT] ... SELECT statements.
HASH, BTREE, RTREE, ERRORS, and
WARNINGS no longer are reserved words. (Bug #724)
ROLLUP when all tables were const tables.
(Bug #714)
UNION that prohibited NULL values from being
inserted into result set columns where the first SELECT of the
UNION retrieved NOT NULL columns. The type and max_length
of the result column is now defined based on all UNION parts.
WHERE clause
bigger than outer query WHERE clause. (Bug #726)
MyISAM tables with FULLTEXT indexes
created in 4.0.x to be unreadable in 4.1.x.
REPAIR TABLE ... USE_FRM when used
with tables
that contained TIMESTAMP columns and were created in 4.0.x.
ORDER BY/GROUP BY
clauses. (Bug #442)
INSERT/REPLACE statements. (Bug #446)
CREATE FULLTEXT INDEX syntax illegal.
SELECT that required a temporary table
(marked by Using temporary in EXPLAIN output)
was used as a derived table in EXPLAIN command. (Bug #251)
DELETE
from a big table with
a new (created by MySQL-4.1) full-text index.
LAST_INSERT_ID() now returns 0 if the last INSERT statement
didn't insert any rows.
BEGIN, in the
first relay log.) (Bug #53)
CONNECTION_ID() now is properly replicated. (Bug #177)
PASSWORD() function in 4.1 is now properly replicated.
(Bug #344)
UNION operations that involved temporary tables.
DERIVED TABLES when EXPLAIN is
used on a DERIVED TABLES with a join.
DELETE with ORDER BY and
LIMIT caused by an uninitialized array of reference pointers.
USER() function caused by an error in the size of
the allocated string.
UNION caused by the empty select list and
a non-existent column being used in some of the individual SELECT
statements.
FLUSH LOGS was
issued on the master. (Bug #254)
REQUIRE SSL option specified for
their accounts.
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)
? prepared statement
parameter as the argument to certain functions or statement clauses caused
a server crash when mysql_prepare() was invoked. (Bug #1500)
SLAVE START (which is a deprecated syntax, START SLAVE should be
used instead) could crash the slave. (Bug #2516)
ALTER TABLE RENAME, when rename to the table with
the same name in another database silently dropped destination table if
it existed. (Bug #2628)
Functionality added or changed:
--compatible option to mysqldump for producing output that
is compatible with other database systems or with older MySQL servers.
--opt option for mysqldump now is enabled by default,
as are all the options implied by --opt.
CHARSET() and COLLATION() functions to return the character
set and collation of a string.
USING type_name syntax in index definition.
IS_USED_LOCK() for determining the connection identifier
of the client that holds a given advisory lock.
user table.
CRC32() function to compute cyclic redundancy check value.
localhost.
REPAIR of MyISAM tables now uses less temporary disk space when
sorting char columns.
DATE/DATETIME checking is now a bit stricter to support the
ability to automatically distinguish between date, datetime, and time
with microseconds. For example, dates of type YYYYMMDD HHMMDD are no
longer supported; you must either have separators between each
DATE/TIME part or not at all.
help
week in the mysql client and get help for the week()
function.
mysql_get_server_version() C API client function.
libmysqlclient that fetched column defaults.
record_in_range() method to MERGE tables to be
able to choose the right index when there are many to choose from.
RAND() and user variables @var.
ANSI_QUOTES on the fly.
EXPLAIN SELECT now can be killed. See section 14.5.4.3 KILL Syntax.
REPAIR TABLE now can be killed. See section 14.5.4.3 KILL Syntax.
USE INDEX,
IGNORE INDEX, and FORCE INDEX.
DROP TEMPORARY TABLE now drops only temporary tables and doesn't
end transactions.
UNION in derived tables.
TIMESTAMP is now returned as a string of type
'YYYY-MM-DD HH:MM:SS' and different timestamp lengths are not supported.
This change was necessary for SQL standards compliance. In a future
version, a further change will be made (backward compatible with this
change), allowing the timestamp length to indicate the desired number
of digits of fractions of a second.
MYSQL_FIELD structure.
CREATE TABLE foo (a INT not null primary key) the
PRIMARY word is now optional.
CREATE TABLE the attribute SERIAL is now an alias for
BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.
SELECT ... FROM DUAL is an alias for SELECT ....
(To be compatible with some other databases).
CHAR/VARCHAR it's now
automatically changed to TEXT or BLOB; One will get a
warning in this case.
BLOB/TEXT types with the
syntax BLOB(length) and TEXT(length). MySQL will
automatically change it to one of the internal BLOB/TEXT
types.
CHAR BYTE is an alias for CHAR BINARY.
VARCHARACTER is an alias for VARCHAR.
integer MOD integer and integer DIV integer.
SERIAL DEFAULT VALUE added as an alias for AUTO_INCREMENT.
TRUE and FALSE added as alias for 1 and 0, respectively.
SELECT .. LIMIT 0 to return proper row count for
SQL_CALC_FOUND_ROWS.
--tmpdir=dirname1:dirname2:dirname3.
SELECT * from t1 where t1.a=(SELECT t2.b FROM t2).
SELECT a.col1, b.col2
FROM (SELECT MAX(col1) AS col1 FROM root_table) a,
other_table b
WHERE a.col1=b.col1;
CONVERT(... USING ...) syntax for converting string values between
character sets.
BTREE index on MEMORY (HEAP) tables.
CREATE TABLE.
SHOW FULL COLUMNS FROM tbl_name shows column comments.
ALTER DATABASE.
SHOW [COUNT(*)] WARNINGS shows warnings from the last command.
CREATE TABLE
... SELECT by defining the column in the CREATE part.
CREATE TABLE foo (a TINYINT NOT NULL) SELECT b+1 AS a FROM bar;
expr SOUNDS LIKE expr same as SOUNDEX(expr)=SOUNDEX(expr).
VARIANCE(expr) function returns the variance of expr
CREATE
[TEMPORARY] TABLE [IF NOT EXISTS] table (LIKE table). The table can
be either normal or temporary.
--reconnect and --skip-reconnect for the
mysql client, to reconnect automatically or not if the
connection is lost.
START SLAVE (STOP SLAVE) no longer returns an error
if the slave is already started (stopped); it returns a
warning instead.
SLAVE START and SLAVE STOP are no longer accepted by the query
parser; use START SLAVE and STOP SLAVE instead.
Version 4.0 of the MySQL server includes many enhancements and new features:
InnoDB storage engine is now included in the standard binaries,
adding transactions, row-level locking, and foreign keys.
See section 16 The InnoDB Storage Engine.
MERGE tables, now supporting INSERT statements and
AUTO_INCREMENT.
See section 15.2 The MERGE Storage Engine.
UNION syntax in SELECT.
See section 14.1.7.2 UNION Syntax.
DELETE statements.
See section 14.1.1 DELETE Syntax.
libmysqld, the embedded server library.
See section 20.2.15 libmysqld, the Embedded MySQL Server Library.
GRANT privilege options for even tighter control and
security.
See section 14.5.1.2 GRANT and REVOKE Syntax.
GRANT system, particularly
useful for ISPs and other hosting providers.
See section 5.5.5 Limiting Account Resources.
SET Syntax.
For a full list of changes, please refer to the changelog sections for each individual 4.0.x release.
Functionality added or changed:
FORCE INDEX clause as a candidate to resolve ORDER BY
as well.
UNION statements has changed to the standard
ones. So far, a table name in the ORDER BY clause was
tolerated. From now on a proper error message is issued (Bug #3064).
max_insert_delayed_threads system variable as a synonym for
max_delayed_threads.
query_cache_wlock_invalidate system variable. It allow
emulation of MyISAM table write-locking behavior, even for queries
in the query cache. (Bug #2693)
MASTER_SERVER_ID is not reserved anymore.
InnoDB in this case always stores file names in lower case:
One can now force lower_case_table_names to 0 from the command
line or a configuration file. This is useful with case-insensitive filesystems
when you have previously not used
lower_case_table_names=1 or lower_case_table_names=2 and
your have already created InnoDB tables. With
lower_case_table_names=0, InnoDB tables were stored in mixed case
while setting lower_case_table_names <> 0 will now force it to lower
case (to make the table names case insensitive).
Because it's possible to crash MyISAM tables by referring to them with
different case on a case-insensitive filesystem, we recommend that you
use lower_case_table_names or lower_case_table_names=2 on
such filesystems.
The easiest way to convert to use lower_case_table_names=2 is to
dump all your InnoDB tables with mysqldump, drop them and then
restore them.
UNION statements has changed to the standard
ones. So far, a table name in the ORDER BY clause was
tolerated. From now on a proper error message is issued (Bug #3064).
max_insert_delayed_threads system variable as a synonym for
max_delayed_threads.
query_cache_wlock_invalidate system variable. It allow
emulation of MyISAM table write-locking behavior, even for queries
in the query cache. (Bug #2693)
DROP TEMPORARY TABLE IF
EXISTS instead of DROP TEMPORARY TABLE, for more robustness.
Bugs fixed:
MATCH ... AGAINST() searches when another thread was
doing concurrent inserts into the MyISAM table in question. The first
--- full-text search -- query could return incorrect results in this case
(e.g. ``phantom'' rows or not all matching rows, even an empty result set).
The easiest way to check whether you are affected is to start `mysqld'
with --skip-concurrent-insert switch and see if it helps.
DROP DATABASE on a directory containing non-
MySQL files. Now a proper error message is returned.
ANALYZE TABLE on a BDB table inside a transaction
that hangs server thread. (Bug #2342)
SELECT DISTINCT where all selected parts
where constants and there were hidden columns in the created temporary table.
(Bug #3203)
COUNT(DISTINCT) when there was a lot of
values and one had a big value for max_heap_table_size.
RAID tables. (Bug #2882)
max_relay_log_size and the slave thread did a
flush_io_cache() at the same time.
SLAVE START from different
threads at the same time. (Bug #2921)
DROP DATABASE with lower_case_table_names=2.
UNION when using lower_case_table_names=2.
(Bug #2858)
DELETE and UPDATE statements if thread is killed.
(Bug #2422)
INSERT DELAYED statement is written at
once if binary logging is enabled. (Bug #2491).
myisamchk -p, myisam_repair_threads) -
sometimes repair process failed to repair a table. (Bug #1334)
UPDATE statements involving at least one constant
table. Bug was exhibited in allowing non matching row to be updated.
(Bug #2996).
--set-character-set and --character-sets-dir options in
`myisamchk' now work.
mysqlbinlog that caused one pointer to be free'd twice
in some cases.
+
operator (for example,
MATCH ... AGAINST('+(+(word1 word2)) +word3*' IN BOOLEAN MODE).
DELETE that was caused by foreign key
constraints. If the order of the tables established by MySQL optimizer
did not match parent-child order, no rows were deleted and no error
message was provided. (Bug #2799)
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)
The bug was accidentally not fixed in 4.0.17 as it was unfortunately
earlier said.
Rotate event is found by the slave SQL thread in the
middle of a transaction, the value of Relay_Log_Pos in SHOW SLAVE
STATUS remains correct. (Bug #3017)
InnoDB reports when it is doing a
crash recovery on a slave server. (Bug #3015)
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)
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)
START SLAVE commands were run at the same
time. (Bug #2921)
replicate-* options. The bug was that if the
statement had been killed on the master, the slave would stop. (Bug #2983)
--local-load option of mysqlbinlog now requires an argument.
LOAD DATA FROM MASTER after
RESET SLAVE. (Bug #2922)
Binlog has bad magic number and stop when it
was not necessary to do so. (Bug #3401)
Exec_master_log_pos (and its disk image in the
relay-log.info file) to be correct if the master had version 3.23 (it
was too big by 6 bytes). This bug does not exist in the 5.0 version.
(Bug #3400)
mysqlbinlog does not forget to print a USE command
under rare circumstances where the binary log contained a LOAD DATA
INFILE command. (Bug #3415)
LOAD DATA INFILE when the
master had version 3.23. Some smaller problems remain in this setup,
See section 6.7 Replication Features and Known Problems. (Bug #3422)
Functionality added or changed:
LOAD DATA by mysqlbinlog in remote mode.
(Bug #1378)
ENGINE is now a synonym for the TYPE option for
CREATE TABLE and ALTER TABLE.
lower_case_table_names system variable now can take a value of
2, to store table names in mixed case on case-insensitive filesystems.
It's forced to 2 if the database directory is located on a case-insensitive
filesystem.
MEMORY (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 master's startup.
This is for the case where the slave has replicated a non-empty
MEMORY table, then the master is shut down and restarted: the table
is now empty on master; the DELETE FROM empties it on slave too. Note
that 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. But
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)
--old-rpl-compat server option, which was a
holdover from the very first 4.0.x versions. (Bug #2428)
Bugs fixed:
mysqlhotcopy now works on NetWare.
DROP DATABASE could not drop databases with RAID tables that had
more than nine RAID_CHUNKS. (Bug #2627)
wait_timeout to 2147483 on Windows (OS limit). (Bug #2400)
--init-file crashes MySQL if it contains a large
SELECT. (Bug #2526)
SHOW KEYS now shows NULL in the Sub_part column for
FULLTEXT indexes.
mysqld
to run on Debian/IA-64 with a TLS-enabled glibc. (Bug #2599)
SELECT privilege is needed for tables that are only read in
multiple-table UPDATE statements. (Bug #2377)
LOCK TABLES ... ; INSERT
... SELECT and one used the same table in the INSERT and SELECT
part. (Bug #2296)
SELECT INTO ... DUMPFILE now deletes the generated file on error.
BDB tables. The
symptom was that data could be returned in the wrong lettercase. (Bug #2509)
TEXT columns if these columns happened to contain
values having trailing spaces. This bug was introduced in 4.0.17.
indexed_TEXT_column = expr
was present and the column contained values having trailing spaces.
This bug was introduced in 4.0.17.
TEXT columns
that happened to contain values having trailing spaces. This bug was
introduced in 4.0.17. (Bug #2295)
MyISAM tables for BLOB values longer
than 16MB. (Bug #2159)
mysqld_safe when running multiple instances of
MySQL. (Bug #2114)
HANDLER statement with tables not
from a current database. (Bug #2304)
UPDATE statements did not check that there was only one table
to be updated. (Bug #2103)
BLOB column
type index size being calculated incorrectly in MIN() and MAX()
optimizations.
(Bug #2189)
LOCK TABLES in
mysqldump. (Bug #2242)
mysqld_safe that caused mysqld to generate a
warning about duplicate user=xxx options if this option was specified in
the [mysqld] or [server] sections of `my.cnf'.
(Bug #2163)
INSERT DELAYED ... SELECT ... could cause table corruption because
tables were not locked properly. This is now fixed by ignoring DELAYED
in this context. (Bug #1983)
MyISAM table may first receive ``no space left
on device,'' but later complete when disk space becomes available.
See section A.4.3 How MySQL Handles a Full Disk.) The bug was that the master forgot to reset the error
code to 0 after success, so the error code got into its binary log, thus
causing the slave to issue false alarms such as ``did not get the same
error as on master.'' (Bug #2083)
LOAD DATA INFILE for an empty file from a 3.23 master to a
4.0 slave caused the slave to print an error. (Bug #2452)
lower_case_table_names to 1 if the file
system was case insensitive, mysqld could crash. This bug existed only
in MySQL 4.0.17. (Bug #2481)
TIMESTAMP columns
that was erroneously disabled in previous release. (Bug #2539) Fixed
SHOW CREATE TABLE to reflect these values. (Bug #1885) Note
that because of the auto-update feature for the first TIMESTAMP
column in a table, it makes no sense to specify a default value for
the column. Any such default will be silently ignored (unless another
TIMESTAMP column is added before this one). Also fixed the meaning
of the DEFAULT keyword when it is used to specify the value to be
inserted into a TIMESTAMP column other than the first. (Bug #2464)
UNIX_TIMESTAMP() to produce incorrect results or that caused
non-zero values to be inserted into TIMESTAMP columns. (Bug #2523)
Also, current time zone now is taken into account when checking if datetime
values satisfy both range boundaries for TIMESTAMP columns. The
range allowed for a TIMESTAMP column is time zone-dependant and
equivalent to a range of 1970-01-01 00:00:01 UTC to 2037-12-31
23:59:59 UTC.
DELETE statements were never replicated by the slave if
there were any replicate-*-table options. (Bug #2527)
query_prealloc_size,
query_alloc_block_size, trans_prealloc_size,
trans_alloc_block_size now
have an effect. (Bug #1948)
ALTER TABLE RENAME, when rename to the table with
the same name in another database silently dropped destination table if
it existed. (Bug #2628)
Functionality added or changed:
mysqldump no longer dumps data for MERGE tables. (Bug #1846)
lower_case_table_names is now forced to 1 if the database directory
is located on a case-insensitive filesystem. (Bug #1812)
realpath() doesn't
work. (Before one could use CREATE TABLE .. DATA DIRECTORY=.. even if
HAVE_BROKEN_REALPATH was defined. This is now disabled to avoid
problems when running ALTER TABLE).
AUTO_INCREMENT value in a MyISAM table
no longer updates the AUTO_INCREMENT counter to a big unsigned value.
(Bug #1366)
WEEK(..., mode) function.
See section 13.5 Date and Time Functions.
(Bug #1178)
UNION DISTINCT syntax.
mysql_server_init() now returns 1 if it can't initialize the
environment. (Previously mysql_server_init() called exit(1)
if it could not create a key with pthread_key_create(). (Bug #2062)
mysqld from MySql
to MySQL.
This should not affect usage, because service names are not case sensitive.
mysqld as a service on Windows systems, mysqld
will read startup options in option files from the option group with the same
name as the service name.
(Except when the service name is MySQL).
Bugs fixed:
SIGHUP to mysqld crashed the server if it was running with
--log-bin. (Bug #2045)
ON clauses
when lower_case_table_names is set. (Bug #1736)
IGNORE ... LINES option to LOAD DATA INFILE didn't work
when used with fixed length rows. (Bug #1704)
UNIX_TIMESTAMP() for timestamps close to 0.
(Bug #1998)
QUOTE()
function. (Bug #1868)
TEXT with endspace. (Bug #1651)
ENUM column that is set to the
empty string (for example, with REPLACE()). (Bug #2023)
mysql client program now correctly prints connection identifier
returned by mysql_thread_id() as unsigned integer rather than as signed
integer. (Bug #1951)
FOUND_ROWS() could return incorrect number of rows after a query
with an impossible WHERE condition. (Bug #1468)
SHOW DATABASES no longer shows .sym files (on Windows) that
do not point to a valid directory. (Bug #1385)
libmysql.so library. (from pthread_key_create()). (Bug #2061)
UNION statement with alias *. (Bug #1249)
DELETE ... ORDER BY ... LIMIT where the rows where not
deleted in the proper order. (Bug #1024, Bug #1697).
DECIMAL. (Bug #2128)
EXPLAIN if query contained an
ORDER BY NULL clause. (Bug #1335)
DROP DATABASE. (Bug #1898)
AND/OR queries. (Bug #1828)
ORDER BY when ordering by expression and identifier.
(Bug #1945)
HANDLER when an ALTER TABLE was
executed in a different connection. (Bug #1826)
trunc* operator of full-text search which
sometimes caused MySQL not to find all matched rows.
DECIMAL column values.
REF access plan was
preferred to more efficient RANGE on another column.
mysqld --install mysql --defaults-file=path-to-file.
(Bug #1643)
const tables
(such as one-row tables) and non-constant expression
(such as RAND()). (Bug #1271)
SQL_CALC_FOUND_ROWS into
account if LIMIT clause was present. (Bug #1274)
mysqlbinlog now asks for a password at the console when the -p
or --password option is used with no argument. This is consistent with
the way that other clients such mysqladmin and mysqldump
already behave. Note: A consequence of this change is that it is no
longer possible to invoke mysqlbinlog as mysqlbinlog -p
pass_val (with a space between the -p option and the following
password value).
(Bug #1595)
STOP SLAVE was
issued.
A->B->C, if 2 sessions on A
updated temporary tables of the same name at the same time, the binary log
of B became incorrect, resulting in C becoming confused.
(Bug #1686)
A->B->C, if STOP SLAVE
was issued on B while it was replicating a temporary table from
A, then when START SLAVE was issued on B, the binary log
of B became incorrect, resulting in C becoming confused.
(Bug #1240)
MASTER_LOG_FILE and MASTER_LOG_POS were not
specified, CHANGE MASTER used the coordinates of the slave I/O
thread to set up replication, which broke replication if the slave SQL
thread lagged behind the slave I/O thread. This caused the slave
SQL thread to lose some events. The new behavior is to use the
coordinates of the slave SQL thread instead.
See section 14.6.2.1 CHANGE MASTER TO Syntax.
(Bug #1870)
TIMESTAMP or
DATETIME value checks of year, month, day, hour, minute
and second ranges are performed and numbers representing illegal
timestamps are converted to 0 value. This behavior is consistent
with manual and with behavior of string to
TIMESTAMP/DATETIME conversion. (Bug #1448)
BIT_AND() and BIT_OR() group functions
returned incorrect value if SELECT used a temporary table and
no rows were found. (Bug #1790).
BIT_AND() is now unsigned in all contexts. This means that it will
now return 18446744073709551615 (= 0xffffffffffffffff) instead of -1 if
there were no rows in the result.
BIT_AND() still returning signed value for an
empty set in some cases. (Bug #1972)
^ (XOR) and >> (bit shift) still returning
signed value in some cases. (Bug #1993)
UPDATE could produce a
wrong complain that some record was not found in one table, if the
UPDATE was preceded by a INSERT ... SELECT. (Bug #1701)
SHOW TABLE STATUS to be
very slow when a database contained a large number of tables, even if a single
particular table were specified. (Bug #1952)
Functionality added or changed:
mysqladmin debug. This works only on systems that support the
mallinfo() call (like newer Linux systems).
range_alloc_block_size, query_alloc_block_size,
query_prealloc_size, transaction_alloc_block_size, and
transaction_prealloc_size.
mysqlbinlog now reads option files. To make this work, you must now
specify --read-from-remote-server when reading binary logs from
a MySQL server. (Note that using a remote server is deprecated and may
disappear in future mysqlbinlog versions).
SIGPIPE signals also for non-threaded programs. The blocking is moved
from mysql_init() to mysql_server_init(), which is
automatically called on the first call to mysql_init().
--libs_r and --include options to mysql_config.
`> prompt for mysql. This prompt is similar to the
'> and "> prompts, but indicates that an identifier
quoted with backticks was begun on an earlier line and the closing backtick
has not yet been seen.
mysql_install_db to be able to use the local machine's IP
address instead of the hostname when building the initial grant tables if
skip-name-resolve has been specified. This
option can be helpful on FreeBSD to avoid thread-safety problems with the
FreeBSD resolver libraries. (Thanks to Jeremy Zawodny for the patch.)
--slave-load-tmpdir option. All these files are
needed when the slave resumes replication after you restore the slave's data.
Bugs fixed:
ERROR 14: Can't change size of file (Errcode: 2)
on Windows in DELETE FROM tbl_name without a WHERE clause or
TRUNCATE TABLE tbl_name, when tbl_name is a MyISAM
table. (Bug #1397)
thr_alarm queue is full warnings after
increasing the max_connections variable with SET GLOBAL.
(Bug #1435)
LOCK TABLES to work when Lock_tables_priv is granted on the
database level and Select_priv is granted on the table level.
FLUSH QUERY CACHE on queries that use same table
several times (Bug #988).
SQL_WARNINGS) to NULL.
ERROR 2013: Lost connection
to MySQL server during query for queries that lasted longer than 30 seconds,
if the client didn't specify a limit with mysql_options(). Users of
4.0.15 on Windows should upgrade to avoid this problem.
SELECT statement. (Bug #1194).
BLOB values with compressed tables.
This was a bug introduced in 4.0.14. It caused MySQL to regard some
correct tables containing BLOB values as corrupted.
(Bug #770, Bug #1304, and maybe Bug #1295)
SHOW GRANTS showed USAGE instead of the real column-level
privileges when no table-level privileges were given.
LOAD DATA FROM
MASTER dropped the corresponding database on the slave, thus erroneously
dropping tables that had no counterpart on the master and tables that
may have been excluded from replication using replicate-*-table
rules. Now LOAD DATA FROM MASTER no longer drops the database.
Instead, it drops only the tables that have a counterpart on the master
and that match the replicate-*-table
rules. replicate-*-db rules can still be used to include or exclude a
database as a whole from LOAD DATA FROM MASTER. A database will
also be included or excluded as a whole if there are some rules like
replicate-wild-do-table=db1.% or
replicate-wild-ignore-table=db1.%, as is already the case for
CREATE DATABASE and DROP DATABASE in replication. (Bug
#1248)
mysqlbinlog crashed with a segmentation fault when used with the
-h or --host option. (Bug #1258)
mysqlbinlog crashed with a segmentation fault
when used on a binary log containing only final events for LOAD
DATA. (Bug #1340)
mysqlbinlog will not reuse temporary file names from previous runs.
Previously mysqlbinlog failed if was used several times on the same
binary log file that contained a LOAD DATA command.
OPENSSL_DISABLE_OLD_DES_SUPPORT
option was enabled).
LOAD DATA INFILE command from its master.
The bug was that one slave MySQL server sometimes
deleted the `SQL_LOAD-*' files (used for replication of
LOAD DATA INFILE and located in the slave-load-tmpdir directory,
which defaults to tmpdir) belonging to the other slave MySQL server of
this machine, if these slaves had the same slave-load-tmpdir directory.
When that happened, the other slave could not replicate
LOAD DATA INFILE and complained about not being able to open some
SQL_LOAD-* file. (Bug #1357)
LOAD DATA INFILE failed for a small file, the master forgot to
write a marker (a Delete_file event) in its binary log, so the slave
could not delete 2 files (`SQL_LOAD-*.info' and
`SQL_LOAD-*.data' from its tmpdir. (Bug #1391)
SQL_LOAD-*.info
file from tmpdir after successfully replicating a LOAD DATA
INFILE command. (Bug #1392)
DROP TEMPORARY TABLE
statements to the binary log for all temporary tables which the
connection had not explicitly dropped. MySQL forgot to backquote the
database and table names in the statement. (Bug #1345)
IMPORTANT:
If you are using this release on Windows, you should upgrade at least your
clients (any program that uses libmysql.lib) to 4.0.16 or above. This
is because the 4.0.15 release had a bug in the Windows client library that
causes Windows clients using the library to die with a Lost connection
to MySQL server during query error for queries that take more than 30
seconds. This problem is specific to Windows; clients on other platforms are
unaffected.
Functionality added or changed:
mysqldump now correctly quotes all identifiers when communicating
with the server. This assures that during the dump process, mysqldump
will never send queries to the server that result in a syntax error.
This problem is not related to the
mysqldump program's output, which was not changed. (Bug #1148)
MIN() and MAX()
report that they can return NULL (this is true because an
empty set will return NULL). (Bug #324)
mysqld server is
started on the same TCP/IP port as an already running mysqld server.
mysqld server variables wait_timeout,
net_read_timeout, and net_write_timeout now work on Windows.
One can now also set timeouts for read and writes in Windows clients with
mysql_options().
--sql-mode=NO_DIR_IN_CREATE to make it possible for
slaves to ignore INDEX DIRECTORY and
DATA DIRECTORY options given to CREATE TABLE.
When this is mode is on, SHOW CREATE TABLE will not show the
given directories.
SHOW CREATE TABLE now shows the INDEX DIRECTORY and
DATA DIRECTORY options, if they were specified when the table was
created.
open_files_limit server variable now shows the real open files limit.
MATCH ... AGAINST() in natural language mode
now treats words that are present
in more than 2,000,000 rows as stopwords.
.tar.gz) has been moved into a subdirectory docs.
See section 2.1.8 Installation Layouts.
info file in the binary
distributions. (Bug #1019)
libmysqld.a) by default. Due to a linking problem with non-gcc
compilers, it was not included in all packages of the initial 4.0.15
release. The affected packages were rebuilt and released as 4.0.15a.
See section 1.5.1.2 The Embedded MySQL Server.
BETWEEN with
non-constant limits. (Bug #991)
binlog-do-db and binlog-ignore-db options are tested
against the database on the master (see section 5.8.4 The Binary Log), and a
paragraph about how replicate-do-db, replicate-do-table
and analogous options are tested against the database and tables on the
slave (see section 6.8 Replication Startup Options).
SET PASSWORD
if it is configured to exclude the mysql database from
replication (using for example
replicate-wild-ignore-table=mysql.%). This was already the
case for GRANT and REVOKE since version 4.0.13 (though
there was Bug #980 in 4.0.13 & 4.0.14, which has been fixed in
4.0.15).
State column of SHOW
PROCESSLIST for replication threads and for MASTER_POS_WAIT()
and added the most common states for these threads to the
documentation, see section 6.3 Replication Implementation Details.
GRANT command that creates an anonymous user
(that is, an account with an empty username) no longer requires
FLUSH PRIVILEGES for the account to be recognized by the server.
(Bug #473)
CHANGE MASTER now flushes `relay-log.info'. Previously
this was deferred to the next run of START SLAVE, so if
mysqld was shutdown on the slave after CHANGE MASTER
without having run START SLAVE, the relay log's name and
position were lost. At restart they were reloaded from
`relay-log.info', thus reverting to their old (incorrect) values from
before CHANGE MASTER and leading to error messages
(as the old relay log did not exist any more) and the slave threads
refusing to start. (Bug #858)
Bugs fixed:
ALTER privilege on the
mysql.user table to execute random code or to gain shell access with
the UID of the mysqld process (thanks to Jedi/Sector One for spotting and
reporting this bug).
FORCE INDEX in a query that contained
"Range checked for each record" in the EXPLAIN output. (Bug #1172)
MYISAM introduced in 4.0.3 where the index file header
was not updated directly after an UPDATE of split dynamic rows.
The symptom was that the table had a corrupted delete-link if mysqld
was shut down or the table was checked directly after the update.
Can't unlock file error when running
myisamchk --sort-index on Windows. (Bug #1119)
key_buffer_size while
the key cache was actively used. (Bug #1088)
MyISAM and ISAM when a row is updated
in a table with a large number of columns and at least one BLOB/TEXT
column.
UNION and LIMIT #,# when
braces were not used around the SELECT parts.
UNION and ORDER BY .. LIMIT #
when one didn't use braces around the SELECT parts.
SELECT SQL_CALC_FOUND_ROWS ... UNION ALL ... LIMIT #
where FOUND_ROWS() returned incorrect number of rows.
1+1-1+1-1... in certain combinations. (Bug #871)
FULLTEXT index
from being marked as "analyzed".
SHOW CREATE TABLE is always larger than the data length.
The only known application that was affected by the old behavior was
Borland dbExpress, which truncated the output from the command.
(Bug #1064)
tis620 character
set. (Bug #1116)
ISAM bug in MAX() optimization.
myisamchk --sort-records=N no longer marks table as crashed if
sorting failed because of an inappropriate key. (Bug #892)
MyISAM compressed table handling that sometimes
made it impossible to repair compressed table in "Repair by sort" mode.
"Repair with keycache" (myisamchk --safe-recover) worked, though.
(Bug #1015)
PRIMARY key declared for a column
that is not explicitly marked NOT NULL was sorted after a
UNIQUE key for a NOT NULL column).
INTERVAL when applied to a DATE value.
(Bug #792)
XOR evaluation in WHERE clause. (Bug #992)
LEFT JOIN)
when ON condition is always false, and range search in used.
(Bug #926)
MATCH ... AGAINST() in some
joins. (Bug #942)
MERGE tables do not ignore "Using index" (from EXPLAIN output)
anymore.
myisamchk --sort-records crash when used on compressed table.
ALTER TABLE and related commands such as CREATE INDEX.
(Bug #712)
LOAD DATA FROM MASTER when
the master was running without the --log-bin option. (Bug #934)
REQUIRE SSL option specified for
their accounts.
GRANT
or REVOKE queries even if it was configured to exclude the
mysql database from replication (for example, using
replicate-wild-ignore-table=mysql.%). (Bug #980)
Last_Errno and Last_Error fields in the output of
SHOW SLAVE STATUS are now cleared by CHANGE MASTER and
when the slave SQL thread starts. (Bug #986)
RESET SLAVE does not change
connection information (master host, port, user, and
password), whereas it does. The statement resets these to the startup options
(master-host etc) if there were some. (Bug #985)
SHOW SLAVE STATUS now shows correct information (master host,
port, user, and password) after RESET SLAVE
(that is, it shows the new values, which are copied from the startup
options if there were some). (Bug #985)
Exec_Master_Log_Pos and problems
with MASTER_POS_WAIT() in A->B->C replication setup. (Bug #1086)
mysqlbinlog when
--position=x was used with x being between a
Create_file event and its fellow Append_block,
Exec_load or Delete_file events. (Bug #1091)
mysqlbinlog printed superfluous warnings when using
--database, which caused syntax errors when piped to
mysql. (Bug #1092)
mysqlbinlog --database filter LOAD DATA INFILE too
(previously, it filtered all queries except LOAD DATA
INFILE). (Bug #1093)
mysqlbinlog in some cases forgot to put a leading '#' in
front of the original LOAD DATA INFILE (this command is
displayed only for information, not to be run; it is later reworked to
LOAD DATA LOCAL with a different filename, for execution by
mysql). (Bug #1096)
binlog-do-db and binlog-ignore-db incorrectly filtered
LOAD DATA INFILE (it was half-written to the binary log). This
resulted in a corrupted binary log, which could cause the slave to
stop with an error. (Bug #1100)
InnoDB table)
was updated, and later in the same transaction a
non-transactional table (such as a MyISAM table) was
updated using the updated content of the transactional table
(with INSERT ... SELECT for example), the queries were written
to the binary log in an incorrect order. (Bug #873)
INSERT ... SELECT updated a
non-transactional table, and ROLLBACK was issued, no error was
returned to the client. Now the client is warned that some changes
could not be rolled back, as this was already the case for normal
INSERT. (Bug #1113)
STOP SLAVE was run while the slave
SQL thread was in the middle of a transaction, and then CHANGE
MASTER was used to point the slave to some non-transactional
statement, the slave SQL thread could get confused (because it would
still think, from the past, that it was in a transaction).
Functionality added or changed:
default_week_format system variable. The value is used as the
default mode for the WEEK() function.
mysqld now reads an additional option file group having a name
corresponding to the server's release series:
[mysqld-4.0] for 4.0.x servers, [mysqld-4.1] for 4.1.x servers,
and so forth. This allows options to be specified on a series-specific basis.
CONCAT_WS() function no longer skips empty strings. (Bug #586).
InnoDB now supports indexing a prefix of a column. This means, in
particular, that BLOB and TEXT columns can be indexed in
InnoDB tables, which was not possible before.
INTERVAL(NULL, ...) returns -1.
INSERT from SELECT when the table into which the records
are inserted is also a table listed in the SELECT.
CREATE TABLE and INSERT from any UNION.
SQL_CALC_FOUND_ROWS option now always returns the total number
of rows for any UNION.
--table option from mysqlbinlog
to avoid repeating mysqldump functionality.
basedir=c:\mysql # installation directory).
max_seeks_for_key variable that can
be used to force the optimizer to use keys instead of table scans
even if the cardinality of the index is low.
LEFT JOIN to normal join in
some cases.
CHANGE MASTER command. See section 14.6.2.1 CHANGE MASTER TO Syntax.
--nice option to mysqld_safe to allow setting the
niceness of the mysqld process. (Thanks to Christian Hammers for
providing the initial patch.) (Bug #627)
--read-only option to cause mysqld to allow no updates
except from slave threads or from users with the SUPER privilege.
(Original patch from Markus Benning).
SHOW BINLOG EVENTS FROM x where x is less than 4 now
silently converts x to 4 instead of printing an error. The same change
was done for CHANGE MASTER TO MASTER_LOG_POS=x and
CHANGE MASTER TO RELAY_LOG_POS=x.
mysqld now only adds an interrupt handler for the SIGINT signal
if you start it with the new --gdb option. This is because some
MySQL users encountered strange problems when they accidentally sent SIGINT to
mysqld threads.
RESET SLAVE now clears the Last_Errno and
Last_Error fields in the output of SHOW SLAVE STATUS.
max_relay_log_size variable; the relay log will be rotated
automatically when its size exceeds max_relay_log_size. But if
max_relay_log_size is 0 (the default), max_binlog_size
will be used (as in older versions). max_binlog_size still
applies to binary logs in any case.
FLUSH LOGS now rotates relay logs in addition to the other types of
logs it already rotated.
Bugs fixed:
latin1_de character set was rewritten.
The old algorithm could not handle cases like "sä" > "ßa".
See section 5.7.1.1 Using the German Character Set. In rare cases it resulted in table
corruption.
ALTER TABLE ... UNION=(...) for MERGE table is now allowed
even if some underlying MyISAM tables are read-only. (Bug #702)
CREATE TABLE t1 SELECT x'41'. (Bug #801)
REPAIR on a table with a multi-part
auto_increment key where one part was a packed CHAR.
INSERT statements not being replicated in the
event of a FLUSH LOGS command or when the binary log exceeds
max_binlog_size. (Bug #791)
INTERVAL and GROUP BY or
DISTINCT. (Bug #807)
mysqlhotcopy so it actually aborts for unsuccessful
table copying operations. Fixed another bug so that it succeeds
when there are thousands of tables to copy. (Bug #812)
mysqlhotcopy failing to read options from option
files. (Bug #808)
FULLTEXT indexes even though it was possible (for example, in
SELECT * FROM t1 WHERE MATCH a,b AGAINST("index") > 0).
UNION operations.
SHOW TABLES and
similar commands.
max_user_connections.
HANDLER without an index now works properly when a table has deleted
rows. (Bug #787)
LOAD DATA in mysqlbinlog. (Bug #670)
SET CHARACTER SET DEFAULT works. (Bug #462)
MERGE table behavior in ORDER BY ... DESC queries.
(Bug #515)
PURGE MASTER LOGS or SHOW MASTER LOGS
when the binary log is off. (Bug #733)
DATETIME column and an integer
constant. (Bug #504)
mysqlbinlog. (Bug #672)
ERROR 1105: Unknown error that occurred for some SELECT
queries, where a column that was declared as NOT NULL was compared
with an expression that took NULL value.
mysql_real_connect() to use poll() instead of
select() to work around problem with many open files in the client.
MATCH ... AGAINST used with a
LEFT JOIN query.
mysqld variables
to 4294967295 when they are specified on the command line.
HANDLER ... READ statements, when a table is referenced
via an alias.
safe_malloc, which caused MySQL to
give "Freeing wrong aligned pointer" errors on SCO 3.2.
ALTER TABLE ... ENABLE/DISABLE KEYS could cause a core dump when
done after an INSERT DELAYED statement on the same table.
mysqld. (Bug #549)
INSERT ... SELECT into an AUTO_INCREMENT column
not replicate well. This bug is in the master, not in the slave.
(Bug #490)
INSERT ... SELECT statement inserted rows into a
non-transactional table, but failed at some point (for example, due to
a ``Duplicate key'' error), the query was not written to the binary log.
Now it is written to the binary log, with its error code, as all other
queries are. About the slave-skip-errors option for how to
handle partially completed queries in the slave, see section 6.8 Replication Startup Options. (Bug #491)
SET FOREIGN_KEY_CHECKS=0 was not replicated properly.
The fix probably will not be backported to 3.23.
LOAD DATA INFILE which had no IGNORE or
REPLACE clause on the master, was replicated with IGNORE.
While this is not a problem if the master and slave data are identical
(a LOAD that produces no duplicate conflicts on the master will
produce none on the slave anyway), which is true in normal operation, it
is better for debugging not to silently add the IGNORE. That way,
you can get an error message on the slave and discover that for some
reason, the data on master and slave are different and investigate why.
(Bug #571)
LOAD DATA INFILE printed an incomplete ``Duplicate entry
'%-.64s' for key %d''' message (the key name and value were not mentioned)
in case of duplicate conflict (which does not happen in normal operation).
(Bug #573)
--debug, CHANGE MASTER
TO RELAY_LOG_POS could cause a debug assertion failure. (Bug #576)
LOCK TABLES WRITE on an InnoDB table, commit could
not happen, if the query was not written to the binary log (for example, if
--log-bin was not used, or binlog-ignore-db was
used). (Bug #578)
DROP TABLE statements to its binary log, then the 4.0.13
slave would not notice the temporary tables have to be dropped, until the
slave mysqld server is restarted. This minor inconvenience is
fixed in 3.23.57 and 4.0.14 (meaning the master must be upgraded to
3.23.57 and the slave to 4.0.14 to remove the inconvenience). (Bug #254)
MASTER_POS_WAIT() was waiting, and the slave was idle, and
the slave SQL thread terminated, MASTER_POS_WAIT() would wait
forever. Now when the slave SQL thread terminates,
MASTER_POS_WAIT() immediately returns NULL (``slave
stopped''). (Bug #651)
RESET SLAVE; START SLAVE;, the Relay_Log_Space value
displayed by SHOW SLAVE STATUS was too big by four bytes. (Bug #763)
replicate-ignore-table and other similar rules), the slave
still checked if the query got the same error code (0, no error) as on
the master. So if the master had an error on the query (for example,
``Duplicate entry'' in a multiple-row insert), then the slave stopped
and warned that the error codes didn't match. (Bug #797)
Functionality added or changed:
PRIMARY KEY now implies NOT NULL. (Bug #390)
--enable-local-infile to match the Unix build configuration.
mysql-test-run. time does not
accept all required parameters on many platforms (for example, QNX) and timing
the tests is not really required (it's not a benchmark anyway).
SHOW MASTER STATUS and SHOW SLAVE STATUS required the
SUPER privilege; now they accept REPLICATION CLIENT as well.
(Bug #343)
MyISAM repair optimization and
myisam_repair_threads variable to enable it.
See section 5.2.3 Server System Variables.
innodb_max_dirty_pages_pct variable which controls amount of
dirty pages allowed in InnoDB buffer pool.
CURRENT_USER() and Access denied error messages now report the
hostname exactly as it was specified in the GRANT command.
InnoDB tables now suppor