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 bugfixes 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://dev.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 20.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:
--start-datetime, --stop-datetime,
--start-position, --stop-position options to
mysqlbinlog (makes point-in-time recovery easier).
SIGHUP and SIGQUIT on
Mac OS X 10.3. This is needed because under this OS, the MySQL server receives
lots of these signals (reported as Bug #2030).
Bugs fixed:
mysqlbinlog --read-from-remote-server sometimes
couldn't accept two binary logfiles on the command line. (Bug #4507)
mysqlbinlog --position --read-from-remote-server
had incorrect # at lines. (Bug #4506)
CREATE TABLE ... TYPE=HEAP ... AS SELECT... caused
replication slave to stop. (Bug #4971)
mysql_options(...,MYSQL_OPT_LOCAL_INFILE,...) failed to
disable LOAD DATA LOCAL INFILE. (Bug #5038)
disable-local-infile option had no effect if client read it
from a configuration file using
mysql_options(...,MYSQL_READ_DEFAULT,...). (Bug #5073)
SET GLOBAL SYNC_BINLOG did not work on some platforms (Mac OS
X). (Bug #5064)
mysql-test-run failed on the rpl_trunc_binlog test if
running test from the installed (the target of 'make install') directory. (Bug
#5050)
mysql-test-run failed on the grant_cache test when run
as Unix user 'root'. (Bug #4678)
KILL. (Bug
#4810)
KILLed while it was doing
START SLAVE. (Bug #4827)
FLUSH TABLES WITH READ LOCK block COMMIT if server is
running with binary logging; this ensures that the binary log position is
trustable when doing a full backup of tables and the binary log. (Bug #4953)
auto_increment column was not reset by
TRUNCATE TABLE is the table was a temporary one. (Bug #5033)
Note: This build passes our test suite and fixes a lot of reported bugs found in the previous 5.0.0 release. However, please be aware that this is not a 'standard MYSQL build' in the sense as there are still some open critical bugs in our bugs database at http://bugs.mysql.com/ that affect this release as well. We are actively fixing these and will make a new release where these are fixed as soon as possible. However, this binary should be a good candidate for testing new MySQL 5.0 features for future products.
Functionality added or changed:
SELECT is slow, but even using
EXPLAIN for it takes a noticeable amount of time.) Two new system
variables, optimizer_search_depth and optimizer_prune_level, can
be used to fine-tune optimizer behavior.
mysql_shutdown() now requires a second argument.
This is a source-level incompatibility that affects how you compile client
programs; it does not affect the ability of compiled clients to communicate
with older servers.
See section 21.2.3.51 mysql_shutdown().
db.p()
USE db_name is
in effect.
USE db_name is no longer allowed in a stored procedure.
SHOW TABLES output field name and values according to standard.
Field name changed from Type to table_type, values are
BASE TABLE, VIEW and ERROR. (Bug #4603)
sql_updatable_view_key system variable.
--replicate-same-server-id server option.
Last_query_cost status variable that reports optimizer cost
for last compiled query.
--to-last-log to mysqlbinlog, for use in conjunction
with --read-from-remote-server.
--innodb-safe-binlog server option, which adds consistency
guarantees between the content of InnoDB tables and the binary log.
See section 5.9.4 The Binary Log.
OPTIMIZE TABLE for InnoDB tables is now mapped to ALTER
TABLE instead of ANALYZE TABLE.
sync_frm is now a settable global variable (not only a startup option).
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).
sync_binlog=N global variable and startup option, which makes
the MySQL server synchronize its binary log to disk (fdatasync()) after
every Nth write to the binary log.
slave-skip-errors).
DROP DATABASE IF EXISTS, DROP TABLE IF EXISTS, single-table
DELETE and single-table UPDATE are now written to the binary log
even if they changed nothing on the master (for example, even if the
DELETE matched no row). The old behavior sometimes caused bad surprises
in replication setups.
mysqlbinlog now have better support for the case that
the session character set and collation variables are changed within a given
session.
See section 6.7 Replication Features and Known Problems.
CHECK TABLE does not result in the table being marked as
"corrupted" any more; table stays like if CHECK TABLE had not even
started. See section 14.5.4.3 KILL Syntax.
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 as it does when reading a local binary log. There is an option
--to-last-log to get the old behavior. (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)
DELETE statements were always replicated by the slave if
there were some replicate-*-ignore-table options and no
replicate-*-do-table options. (Bug #3461)
--with-debug and replicating itself. (BUG #3568)
server-id was not set using startup options but with SET
GLOBAL, the replication slave still complained that it was not set.
(Bug #3829)
mysql_fix_privilege_tables didn't correctly handle the argument of its
--password=# option. (Bug #4240)
mysql_real_connect() (which
required a compromised DNS server and certain operating systems). (Bug #4017)
mysqld
was run as the root system user, and if you had
--log-bin=<somewhere_out_of_var_lib_mysql> it created binary log
files owned by root in this directory, which remained owned by
root after the installation. This is now fixed by starting
mysqld as the mysql system user instead. (Bug #4038)
DROP DATABASE honor the value of
lower_case_table_names. (Bug #4066)
INSERT ... SELECT if it
examined more than 4 billion rows. (Bug #3871)
mysqlbinlog didn't escape the string content of user variables, and did
not deal well when these variables were in non-ASCII character sets; this is
now fixed by always printing the string content of user variables in
hexadecimal. The character set and collation of the string is now also
printed. (Bug #3875)
AND/OR expressions if query was ignored (either by a
replication server because of replicate-*-table rules, or by any MySQL
server because of a syntax error). (Bug #3969, Bug #4494)
CREATE TEMPORARY TABLE t SELECT failed while loading the data, the
temporary table was not dropped. (Bug #4551)
DROP TABLE failed to drop a table on
master, the error code was not written to the binary log. (Bug #4553)
LOAD DATA INFILE it didn't
show it in the output of SHOW PROCESSLIST. (Bug #4326)
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.
Index Merge 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 20.1.6.3 SELECT ... INTO Statement.
InnoDB is 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://dev.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.21 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:
Bugs fixed:
LIKE used with arguments
in different character sets crashed server on first execute. (Bug #4368)
OPTIMIZE TABLE that could cause table corruption
on FULLTEXT indexes. (Bug #5327)
Note: To fix a compile problem on systems that do not have automake-1.7 installed, an updated 4.1.4a source tarball has been published. In addition to resolving this automake dependency (BUG #5319), it also fixes some reported libedit compile errors when using a non-gcc compiler (BUG #5353).
Functionality added or changed:
TIMESTAMP values in InnoDB in
4.1 to be the same as in 4.0. This difference resulted in wrong datetime
values in TIMESTAMP columns in InnoDB tables after upgrade
from 4.0 to 4.1. (Bug #4492)
Warning: extra steps during upgrade required!
Unfortunately this means that if you are upgrading from 4.1.x,
where x <= 3, to 4.1.4
you should use mysqldump for saving and then restoring your
InnoDB tables with TIMESTAMP columns.
mysqld-opt Windows server was renamed to mysqld.
This completes the Windows server renaming begun in MySQL 4.1.2.
See section 2.2.1.4 Selecting a Windows Server.
ucs2 and utf8 Unicode
character sets. These are called ucs2_roman_ci and
utf8_roman_ci.
MySQLCOM in MySQL 4.1.2).
Thanks to Bryan McCormack for reporting this.
--start-datetime, --stop-datetime, --start-position,
and --stop-position options to mysqlbinlog. These make
point-in-time recovery easier.
CHECK TABLE no longer results in the table being marked as
"corrupted"; the table status remains the same as it was before
CHECK TABLE started.
See section 14.5.4.3 KILL Syntax.
SIGHUP and SIGQUIT on Mac OS X 10.3.
This is needed because under this OS, the MySQL server receives lots of these
signals (reported as Bug #2030).
Bugs fixed:
MEDIUMINT column. (Bug #5126)
SELECT ... INTO @var for a second time.
(Bug #5034)
IN subqueries that use compound
indexes. (Bug #4435)
SLAVE STOP if the IO thread was in a special
state. (Bug #4629)
MERGE tables
(even one MERGE table and MyISAM tables), that could have
resulted in a crash or hang of the server. (Bug #2408)
NOT in WHERE or ON
clauses. (Bug #4912)
MATCH ... AGAINST now works in a subquery. (Bug #4769)
--log-error) when the hostname contained a domain name.
The domain name is now replaced by the extension. (BUG #4997)
myisamchk. (Bug #4901)
CONVERT_TZ()
function with time zone described in database as parameter and this time
zone was not used before. (Bug #4508)
%T, %r, %V, %v and %X, %x format specifiers was
added to STR_TO_DATE() function. (Bug #4756)
NATURAL JOIN where joined table had
no common column. (Bug #4807)
UNHEX(NULL). (Bug #4441)
mysql_fix_privilege_tables didn't correctly handle the argument of its
--password=# option. (Bug #4240, Bug #4543)
mysqlbinlog --read-from-remote-server sometimes
couldn't accept 2 binary logs on command line. (Bug #4507)
mysqlbinlog --position --read-from-remote-server
had wrong # at lines. (Bug #4506)
CREATE TEMPORARY TABLE t SELECT failed while loading the data, the
temporary table was not dropped. (Bug #4551)
DROP TABLE failed to drop a table on
master, the error code was not written to the binary log. (Bug #4553)
LOAD DATA INFILE it didn't
show it in the output of SHOW PROCESSLIST. (Bug #4326)
CREATE TABLE ... TYPE=HEAP ... AS SELECT... caused
replication slave to stop. (Bug #4971)
mysql_options(...,MYSQL_OPT_LOCAL_INFILE,...) failed to
disable LOAD DATA LOCAL INFILE. (Bug #5038)
disable-local-infile option had no effect if client read it
from a configuration file using
mysql_options(...,MYSQL_READ_DEFAULT,...). (Bug #5073)
SET GLOBAL SYNC_BINLOG did not work on some platforms (Mac OS
X). (Bug #5064)
mysql-test-run failed on the rpl_trunc_binlog test
if running test from the installed (the target of 'make install') directory.
(Bug #5050)
mysql-test-run failed on the grant_cache test when
run as Unix user 'root'. (Bug #4678)
KILL.
(Bug #4810)
KILLed while it was doing
START SLAVE. (Bug #4827)
FLUSH TABLES WITH READ LOCK block COMMIT if server is
running with binary logging; this ensures that the binary log position is
trustable when doing a full backup of tables and the binary log. (Bug #4953)
auto_increment column was not reset by
TRUNCATE TABLE if the table was a temporary table. (Bug #5033)
Note: The initial release of MySQL 4.1.3 for Windows accidentally was not compiled with support for the Spatial Extensions (OpenGIS). This was fixed by rebuilding from the same 4.1 code snapshot with the missing option and releasing those packages as version 4.1.3a.
To enable compiling the newly released PHP 5 against MySQL 4.1.3 on Windows, the Windows packages had to be rebuilt once more to add a few missing symbols to the MySQL client library. These packages were released as MySQL 4.1.3b.
Functionality added or changed:
ucs2 and utf8
Unicode character sets: Icelandic, Latvian, Romanian, Slovenian, Polish,
Estonian, Swedish, Turkish, Czech, Danish, Lithuanian, Slovak, Spanish,
Traditional Spanish.
@@time_zone variable to
a value such as '+10:00' or 'Europe/Moscow' (where
'Europe/Moscow' is the name of one of the time zones described in the
system tables). Functions like CURRENT_TIMESTAMP,
UNIX_TIMESTAMP, and so forth
honor this time zone. Values of TIMESTAMP type are also
interpreted as values in this time zone (so now our TIMESTAMP type
behaves similar to Oracle's TIMESTAMP WITH LOCAL TIME ZONE, that is,
values stored in such a column are normalized towards UTC and converted back
to the current connection time zone when they are retrieved from such a
column).
To set up the tables that store time zone information, see
section 2.4 Post-Installation Setup and Testing.
timezone system variable has been removed. It is replaced by
system_time_zone.
See section 5.2.3 Server System Variables.
CONVERT_TZ() was
added. It assumes that its first argument is a datetime value in the time zone
specified by its second argument and returns equivalent datetime value in
time zone specified by its third argument.
CHECK TABLE now can be killed. It will then mark the table as corrupted.
See section 14.5.4.3 KILL Syntax.
mysql_shutdown() now requires a second argument.
This is a source-level incompatibility that affects how you compile client
programs; it does not affect the ability of compiled clients to communicate
with older servers.
See section 21.2.3.51 mysql_shutdown().
OPTIMIZE TABLE for InnoDB tables is now mapped to ALTER
TABLE instead of ANALYZE TABLE.
sync_frm is now a settable global variable (not only a startup option).
sync_binlog=N global variable and startup option, which makes
the MySQL server synchronize its binary log to disk (fdatasync()) after
every Nth write to the binary log.
slave-skip-errors).
DROP DATABASE IF EXISTS, DROP TABLE IF EXISTS, single-table
DELETE and single-table UPDATE are now written to the binary log
even if they changed nothing on the master (for example, even if the
DELETE matched no row). The old behavior sometimes caused bad surprises
in replication setups.
mysqlbinlog now have better support for the case that
the session character set and collation variables are changed within a given
session.
See section 6.7 Replication Features and Known Problems.
--innodb-safe-binlog server option, which adds consistency
guarantees between the content of InnoDB tables and the binary log.
See section 5.9.4 The Binary Log.
LIKE now supports the use of a prepared statement parameter or
delimited constant expression as the argument to ESCAPE (Bug #4200).
Bugs fixed:
CREATE DATABASE IF NOT EXISTS for Win32 which caused an
error if database existed. (Bug #4378)
root user to Windows version of mysqld. (Bug #4242)
EXPLAIN statement which led to server crash.
(Bug #4271)
MERGE tables created with INSERT_METHOD=LAST, that
were not able to report a key number that caused "Duplicate entry"
error for UNIQUE key in INSERT. As a result, error message was
not precise enough (error 1022 instead of error 1062) and
INSERT ... ON DUPLICATE KEY UPDATE did not work.
(Bug #4008)
DELETE from a table with FULLTEXT indexes
which under rare circumstances could result in a corrupted table, if words of
different lengths may be considered equal (which is possible in some
collations, for example, in utf8_general_ci or latin1_german2_ci.)
(Bug #3808)
HAVING
clause. (Bug #3984)
mysqldump when it didn't return an error if
the output device was filled (Bug #1851)
MYSQL_TIME
application buffer (prepared statements API). (Bug #4030)
DATETIME
or TIMESTAMP column. (Bug #2336)
IN() queries on
different key parts. (Bug #4157)
NULL and derived tables. (Bug #4097)
UNION results if display length of fields for numeric
types was set less then real length of values in them. (Bug #4067)
mysql_stmt_close(), which hung up when attempting
to close statement after failed mysql_stmt_fetch(). (Bug #4079)
COUNT(*), MAX() and
MIN() functions in prepared statements. (Bug #2687)
COUNT(DISTINCT) performance degradation in cases
like COUNT(DISTINCT a TEXT, b CHAR(1)) (no index used). (Bug #3904)
MATCH ... AGAINST(... IN BOOLEAN MODE) that
under rare circumstances could cause wrong results
if in the data's collation one byte could match many
(like in utf8_general_ci or latin1_german2_ci.)
(Bug #3964)
MYSQL_TYPE_TIME/MYSQL_TYPE_DATETIME columns was not sent to
the client. (Bug #4026)
--with-charset with configure didn't
affect the MySQL client library. (Bug #3990)
CONCAT(?, column) in prepared
statements. (Bug #3796)
mysql_real_connect() (which
required a compromised DNS server and certain operating systems). (Bug #4017)
mysqld
was run as the root system user, and if you had
--log-bin=<somewhere_out_of_var_lib_mysql> it created binary log
files owned by root in this directory, which remained owned by
root after the installation. This is now fixed by starting
mysqld as the mysql system user instead. (Bug #4038)
DROP DATABASE honor the value of
lower_case_table_names. (Bug #4066)
INSERT ... SELECT if it
examined more than 4 billion rows. (Bug #3871)
mysqlbinlog didn't escape the string content of user variables, and did
not deal well when these variables were in non-ASCII character sets; this is
now fixed by always printing the string content of user variables in
hexadecimal. The character set and collation of the string is now also
printed. (Bug #3875)
AND/OR expressions if query was ignored (either by a
replication server because of replicate-*-table rules, or by any MySQL
server because of a syntax error). (Bug #3969, Bug #4494)
Functionality added or changed:
mysqld Windows server was renamed to mysqld-debug.
See section 2.2.1.4 Selecting a Windows Server.
Handler_discover status variable.
SELECT of UNION in output of
EXPLAIN SELECT statement.
mysql command-line client now supports multiple -e options.
(Bug #591)
myisam_data_pointer_size system variable.
See section 5.2.3 Server System Variables.
--log-warnings server option now is enabled by default.
Disable with --skip-log-warnings.
--defaults-file=file_name option now requires that the filename
must exist (safety fix). (Bug #3413)
[mysqld]
section in `my.cnf' or compiled in), not in `/tmp' -
vulnerability id CAN-2004-0388. Thanks to Christian Hammers from
Debian Security Team for reporting this!
'a' = 'a ' then from it must follow that 'a' > 'a\t'.
(The latter was not the case before MySQL 4.1.2.) To implement it, we had to
change how storage engines compare strings internally. As a side effect, if
you have a table where a 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 as TIMESTAMP DEFAULT
CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.
In other cases, it would be treated as a TIMESTAMP DEFAULT 0 column.
NOW is supported as an alias for 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, not
mysql_stmt_prepare().
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.
PRIMARY KEY if the table has one.)
(Bug #856)
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.
MyISAM and InnoDB tables now support index prefix lengths 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.
Type output column for SHOW TABLE STATUS now is labeled
Engine.
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.
--replicate-same-server-id.
--to-last-log to mysqlbinlog, for use in conjunction
with --read-from-remote-server.
Bugs fixed:
EXPLAIN of UNION. (Bug #3639)
DISTINCT and ORDER BY
by column's real name, while the column had an alias, specified in
SELECT clause. (Bug #3681)
WHERE with PROCEDURE analyze().
(Bug #2238).
FLUSH PRIVILEGES was executed.
(Bug #3404)
GROUP_CONCAT() on expression with ORDER BY
and external ORDER BY in a query. (Bug #3752)
ALL/SOME subqueries in case of optimization (key
field present in subquery). (Bug #3646)
SHOW GRANTS and EXPLAIN SELECT character
set conversion. (Bug #3403)
ORDER BY list.
UNION statements did not consult SQL_SELECT_LIMIT value when
set. This is now fixed properly, which means that this limit is applied
to the top level query, unless LIMIT for entire UNION is
used.
UPDATE statements that resulted in an
error when one of the tables was not updated but was used in the nested query,
contained therein.
mysql_stmt_send_long_data() behavior on second execution of
prepared statement and in case when long data had zero length. (Bug #1664)
UNION.
(Bug #3577)
mysql_stmt_attr_set(..., STMT_ATTR_UPDATE_MAX_LENGTH)
to tell the client library to update MYSQL_FIELD->max_length when
doing mysql_stmt_store_result(). (Bug #1647).
FROM
clause with parameter used. (Bug #3020)
DELETE
statement in prepared statements. (Bug #3411)
UPDATE privilege for tables which will not be updated in
multiple-table UPDATE statement 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, Bug #3381, Bug #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)
RAND() in subqueries with static tables. (bug #2645)
mysqldump for
various values of sql_mode of server. (Bug #2591)
DOUBLE and
FLOAT columns.
(Bug #2082)
--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(expr,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 sometimes
resulted in a spurious error Found key at page ... that points to record
outside datafile for 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.
myisamchk and 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 as it does when reading a local binary log. There is an option
--to-last-log to get the old behavior. (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)
DELETE statements were always replicated by the slave if
there were some replicate-*-ignore-table options and no
replicate-*-do-table options. (Bug #3461)
--with-debug and replicating itself. (BUG #3568)
REPAIR TABLE that resulted sometimes in a corrupted
table, if the table contained FULLTEXT indexes and many words
of different lengths that are considered equal (which is possible in
certain collations, such as latin1_german2_ci
or utf8_general_ci).
(Bug #3835)
expire_logs_days variable.
(Bug #3807)
server-id was not set using startup options but with SET
GLOBAL, the replication slave still complained that it was not set. (Bug
#3829)
This release includes all fixes in MySQL 4.0.16 and most of the fixes in MySQL 4.0.17.
Functionality added or changed:
--old-protocol option for mysqld is no longer supported
and has been removed.
bdb_version system variable to version_bdb.
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.
preload_buffer_size system variable.
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 system variable.
ft_max_word_len_for_sort variable from myisamchk.
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 'user_name'@'host_name' 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 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 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 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)
INSERT DELAYED now works with InnoDB tables.
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:
DEFAULT(col_name) in expressions; it produces the
column's default value.
--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 TABLE 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 and OPTIMIZE 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.
DIV is now a reserved word.
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 21.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.6.4 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:
Bugs fixed:
mysqladmin now returns a status of 0 even when the server denies
access; such an error means the server is running. (Bug #3120)
Functionality added or changed:
./configure --comment during
compilation) when starting the server. E.g.:
Version: '4.0.21-debug' socket: '/tmp/mysql.sock' port: 0 Official MySQL Binary
SIGHUP and SIGQUIT on
Mac OS X 10.3. This is needed because under this OS, the MySQL server receives
lots of these signals (reported as Bug #2030).
mysqld-nt and mysqld-max-nt servers now
write error messages to the Windows event log in addition to the MySQL error
log.
Bugs fixed:
MERGE tables
(even one MERGE table and MyISAM tables), that could've
resulted in a crash or hang of the server. (Bug #2408)
GROUP BY queries
with expression in HAVING clause that refers to a BLOB
(TEXT, TINYBLOB, etc) fields. (Bug #4358)
HEAP table is
dropped.
It could only happen on Windows when a symlink file (.sym) is used and
if that symlink file contained double backslashes (\\). (Bug #4973)
TIMESTAMP(19) fields from being created.
(Bug #4491)
NULL values in BLOB (TINYBLOB, TEXT,
TINYTEXT, etc) columns of MyISAM tables. (Bug #4816)
ROUND() reporting incorrect metadata
(number of digits after the decimal point). It can be seen, for example,
in CREATE TABLE t1 SELECT ROUND(1, 34). (Bug #4393)
SQRT() and LOG(). (Bug #4356)
LOAD DATA with the LOCAL
option. The problem occurs when an error happens during the LOAD DATA
operation. Previously, the connection was broken. Now the error message
is returned and connection stays open.
col IN (val) the same way it does
for col = val.
net_buffer_length when building the
DBD::mysql Perl module. (Bug #4206)
lower_case_table_names=2 (keep case for table names) was not honored
with ALTER TABLE and CREATE/DROP INDEX. (Bug #3109)
DECIMAL(0,...) column. (Bug #4046)
IF() function incorrectly determining the result type
if aggregate functions were involved. (Bug #3987)
MATCH ... AGAINST() on a phrase search operator with
a missing closing double quote. (Bug #3870)
server-id was not set using startup options but with SET
GLOBAL, the replication slave still complained that it was not set.
(Bug #3829)
mysql_real_connect() (which
required a compromised DNS server and certain operating systems). (Bug #4017)
mysqld
was run as the root system user, and if you had
--log-bin=<somewhere_out_of_var_lib_mysql> it created binary log
files owned by root in this directory, which remained owned by
root after the installation. This is now fixed by starting
mysqld as the mysql system user instead. (Bug #4038)
DROP DATABASE honor the value of
lower_case_table_names. (Bug #4066)
INSERT ... SELECT if it
examined more than 4 billion rows. (Bug #3871)
AND/OR expressions if query was ignored (either by a
replication server because of replicate-*-table rules, or by any MySQL
server because of a syntax error). (Bug #3969, Bug #4494)
mysqlbinlog --position --read-from-remote-server
had wrong # at lines. (Bug #4506)
CREATE TEMPORARY TABLE t SELECT failed while loading the data, the
temporary table was not dropped. (Bug #4551)
DROP TABLE failed to drop a table on
master, the error code was not written to the binary log. (Bug #4553)
LOAD DATA INFILE it didn't
show it in the output of SHOW PROCESSLIST. (Bug #4326)
CREATE TABLE ... TYPE=HEAP ... AS SELECT... caused
replication slave to stop. (Bug #4971)
disable-local-infile option had no effect if client read it
from a configuration file using
mysql_options(...,MYSQL_READ_DEFAULT,...). (Bug #5073)
mysql-test-run failed on the rpl_trunc_binlog test if
running test from the installed (the target of 'make install') directory. (Bug
#5050)
KILL. (Bug
#4810)
KILLed while it was doing
START SLAVE. (Bug #4827)
FLUSH TABLES WITH READ LOCK block COMMIT if server is
running with binary logging; this ensures that the binary log position is
trustable when doing a full backup of tables and the binary log. (Bug #4953)
auto_increment column was not reset by
TRUNCATE TABLE is the table was a temporary one. (Bug #5033)
database.table.column) when
lower_case_table_names=1. (Bug #4792)
SET CHARACTER SET was not replicated correctly. MySQL 4.1
does not have that bug. (Bug #4500)
Note: The windows packages had to be repackaged and re-released several times to resolve packaging issues (such as missing files). This did not affect the binaries included (they have not been recompiled), therefore the installation packages are of version 4.0.20d, while the binaries included still identify themselves as version 4.0.20b.
Functionality added or changed:
MATCH ... AGAINST ( ... IN BOOLEAN MODE) no longer
matches partial words.
Bugs fixed:
/ reporting incorrect metadata
(number of digits after the decimal point). It can be seen, for example,
in CREATE TABLE t1 SELECT "0.01"/"3". (Bug #3612)
DROP DATABASE on some configurations
(in particular, Linux 2.6.5 with ext3 are known to expose this bug).
(Bug #3594)
Note: The MySQL 4.0.19 binaries were uploaded to the download mirrors on May, 10th. However, a potential crashing bug was found just before the 4.0.19 release was publicly announced and published from the 4.0 download pages at http://dev.mysql.com/.
A fix for the bug was pushed into the MySQL source tree shortly after it could be reproduced and is included in MySQL 4.0.20. Users upgrading from MySQL 4.0.18 should upgrade directly to MySQL 4.0.20 or later.
See (Bug #3596) for details (it was reported against MySQL-4.1, but was confirmed to affect 4.0.19 as well).
Functionality added or changed:
"YYYY-MM-DD HH:MM:SS. This is done to make it easier
to use tables created in MySQL 4.1 to be used in MySQL 4.0.
RAID_CHUNKS with a value > 255 it will be set to 255.
This was made to ensure that all raid directories are always 2 hex bytes.
(Bug #3182)
FORCE INDEX clause as a candidate to resolve ORDER BY
as well.
--log-warnings server option now is enabled by default.
Disable with --skip-log-warnings.
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 allows
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 filenames in lower case:
You 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
you have already created InnoDB tables. With
lower_case_table_names=0, InnoDB tables were stored in mixed case
while setting lower_case_table_names to a non-zero value now will 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.
DROP TEMPORARY TABLE IF
EXISTS instead of DROP TEMPORARY TABLE, for more robustness.
--replicate-same-server-id.
Bugs fixed:
ft_stopword_file to myisamchk.
',' at the end of field specifications. (Bug #3481)
INTERVAL now can handle big values for seconds, minutes and hours.
(Bug #3498)
'%'. (Bug #3473)
SOUNDEX() to ignore non-alphabetic characters also
in the beginning of the string. (Bug #3556)
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
(for example, ``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)
DELETE statements were always replicated by the slave if
there were some replicate-*-ignore-table options and no
replicate-*-do-table options. (Bug #3461)
--with-debug and replicating itself. (BUG #3568)
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)
--sync-frm. It's on by default, to
instruct MySQL to sync to disk each time `.frm' file is created.
Use --disable-sync-frm to disable.
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-dependent 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