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 MySQL 4.1 and 5.0. See section C 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 recent version of MySQL listed here that you can't find on our download page (http://dev.mysql.com/downloads/), it means that the version has not yet been released.
The date mentioned with a release version is the date of the last BitKeeper ChangeSet on which the release was based, not the date when the packages were made available. The binaries are usually made available a few days after the date of the tagged ChangeSet, because building and testing all packages takes some time.
The following changelog shows what has already been done in the 5.0 tree:
CREATE VIEW Syntax.
SELECT INTO list_of_vars, which can be of mixed
(that is, global and local) types.
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.
VARCHAR fields now remembers end space. A VARCHAR() field can
now contain up to 65535 bytes.
CREATE ... SELECT, MySQL now creates the result field based
on the max_length of the string/expression:
| max_length | Column type |
| = 0 | CHAR(0)
|
| < 512 | VARCHAR(max_length)
|
| >= 512 | TEXT
|
For a full list of changes, please refer to the changelog sections for each individual 5.0.x release.
Functionality added or changed:
cp932 (SJIS for Windows Japanese) and
eucjpms (UJIS for Windows Japanese) character sets.
InnoDB status variables.
See section 5.2.4 Server Status Variables.
FEDERATED storage engine.
See section 14.6 The FEDERATED Storage Engine.
SHOW CREATE TABLE now uses USING index_type rather than
TYPE index_type to specify an index type. (Bug #7233)
error member to the MYSQL_BIND data structure that
is used in the C API for prepared statements. This member is used for
reporting data truncation errors. Truncation reporting is enabled via the
new MYSQL_REPORT_DATA_TRUNCATION option for the mysql_options()
C API function.
reconnect flag in the MYSQL structure is now set
to 0 by mysql_real_connect(). Only those client programs which didn't
explicitely set this flag to 0 or 1 after mysql_real_connect() will
experience a change. Having automatic reconnection enabled by default was
considered too dangerous (after reconnection, table locks, temporary tables,
user and session variables are lost).
FLUSH TABLES WITH READ LOCK is now killable while it's waiting for
running COMMIT statements to finish.
VARCHAR columns now remember end space. A VARCHAR() column can
now contain up to 65535 bytes. For more details, see section D.1 Changes in release 5.0.x (Development).
If the table handler doesn't support the new VARCHAR type, then
it's converted to a CHAR column. Currently this happens for NDB
and InnoDB tables.
ROW_FORMAT=REDUNDANT.
The new format (ROW_FORMAT=COMPACT) is the default.
AUTO_INCREMENT value for the table
using CREATE TABLE...AUTO_INCREMENT = works now also for the
InnoDB table.
Seconds_Behind_Master will be NULL (which means ``unknown'') if
the slave SQL thread is not running, or if the slave I/O thread is not running
or not connected to master. It will be zero if SQL thread has caught up on I/O
thread. It no longer grows indefinitely if the master is idle.
--log-bin option but cannot initialize the
binary log at startup (that is, an error occurs when writing to the binary
log file or binary log index file).
MyISAM tables when there is a ``disk full'' or ``quota exceeded''
error.
See section A.4.3 How MySQL Handles a Full Disk.
--log-bin-index and without --log-bin, and when started with
--log-slave-updates and without --log-bin.
--log-bin and
without --log-bin-index, thus not specifying a name for the binary log
index file, a warning will be issued because MySQL will fall back to
using the hostname for that name, and this is prone to replication issues if
the server's hostname's gets changed later.
MAX_USER_CONNECTIONS limit, which allows to
specify maximum number of concurrent connections for the account. Also now
all limited resources are counted per account (instead of being counted per
user + host pair as it was before). One can use --old-style-user-limits
option to get old behavior.
Bugs fixed:
max_heap_table_size handling, that resulted in
Table is full error when the table was still smaller than the limit.
(Bug #7791).
mysqlaccess script.
Reported by Javier Fernandez-Sanguino Pena
and Debian Security Audit Team.
( CAN-2005-0004)
CONVERT_TZ()
function with constant arguments was prepared. (Bug #6849)
CREATE TABLE .. SELECT query to the binary log when the
insertion of new records partially failed. (Bug #6682)
mysqld a SIGHUP caused it to crash.
CREATE/ALTER/DROP DATABASE statements so
that replication of CREATE DATABASE is possible when using
--binlog-do-db and --binlog-ignore-db. (Bug #6391)
BEGIN (or SET AUTOCOMMIT=0), FLUSH TABLES
WITH READ LOCK, transactional update, COMMIT, FLUSH
TABLES WITH READ LOCK could hang the connection forever and possibly the MySQL
server itself. This happened for example when running the innobackup
script several times. (Bug #6732)
mysqlbinlog did not print SET PSEUDO_THREAD_ID statements in
front of LOAD DATA INFILE statements inserting into temporary tables,
thus causing potential problems when rolling forward these statements after
restoring a backup. (Bug #6671)
InnoDB now supports ALTER
TABLE...AUTO_INCREMENT = x query to set auto increment value for a
table.
SHOW CREATE DATABASE even if the
connection has an open transaction or locked tables; refusing it made
mysqldump --single-transaction sometimes fail to print a complete
CREATE DATABASE statement for some dumped databases. (Bug #7358)
MyISAM sometimes didn't sleep and retry the write, thus
resulting in a corrupted table. (Bug #7714)
--expire-log-days was not honored if using only transactions.
(Bug #7236)
ANALYZE
TABLE, OPTIMIZE TABLE, or REPAIR TABLE statements from the
master. (Bug #6461, Bug #7658)
Functionality added or changed:
NOT operator has changed so that expressions such as
NOT a BETWEEN b AND c are parsed correctly as
NOT (a BETWEEN b AND c) rather than as
(NOT a) BETWEEN b AND c.
The pre-5.0 higher-precedence behavior can be obtained by enabling the new
HIGH_NOT_PRECEDENCE SQL mode.
IS [NOT] boolean_value syntax, where boolean_value is
TRUE, FALSE, or UNKNOWN.
InnoDB status variables.
See section 5.2.4 Server Status Variables.
WITH CHECK OPTION clause for CREATE VIEW.
CHECK TABLE now works for views.
SCHEMA and SCHEMAS keywords are now accepted as synonyms
for DATABASE and DATABASES.
CREATE TRIGGER and
DROP TRIGGER statements).
mysqldump --single-transaction --master-data is now able to take an
online (non-blocking) dump of InnoDB and report the corresponding binary log
coordinates, which makes a backup suitable for point-in-time recovery,
roll-forward or replication slave creation. See section 8.8 The mysqldump Database Backup Program.
--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).
--auto-increment-increment and --auto-increment-offset
startup options. These allow you to set up a server to generate
auto-increment values that don't conflict with another server.
ALLOW_INVALID_DATES SQL mode.
STRICT_TRANS_TABLES, STRICT_ALL_TABLES,
NO_ZERO_IN_DATE, NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO, and TRADITIONAL SQL modes.
The TRADITIONAL mode is shorthand for all the preceding modes.
When using mode TRADITIONAL, MySQL generates an error if you try
to insert a wrong value in a column. It does not adjust the value to the
closest possible legal value.
STRICT_TRANS_TABLES/STRICT_ALL_TABLES mode, you now get
an error if you do an INSERT without specifying all columns that
don't have a default value. A side effect of this is that when you do
SHOW CREATE for a new table, you will no longer see a DEFAULT
value for a column for which you didn't specify a default value.
DONT_USE_DEFAULT_FIELDS was removed because you
can get the same behavior by setting the sql_mode system variable to
STRICT_TRANS_TABLES.
NO_AUTO_CREATE_USER SQL mode to prevent GRANT
from automatically creating new users if it would otherwise do so,
unless a password also is specified.
sql_updatable_view_key system variable to
updatable_views_with_limit. This variable now can have only two values:
1 or YES: Don't issue an error message (warning only) if a
VIEW without presence of a key in the underlying table is used in queries
with a LIMIT clause for updating. (This is the default value.)
0 or NO: Prohibit update of a VIEW, which does not contain a
key in the underlying table and the query uses a LIMIT clause (usually
get from GUI tools).
SHOW TABLES to old pre-5.0.1 format that did
not include a table type column. To get the additional column that lists the
table type, use SHOW FULL TABLES now.
mysql_fix_privilege_tables script now initializes the global
CREATE VIEW and SHOW VIEW privileges in the user
table to the value of the CREATE privilege in that table.
user table has not been upgraded to
include the view-related privilege columns, it treats each account as having
view privileges that are the same as its CREATE privilege.
innodb_locks_unsafe_for_binlog
in `my.cnf', InnoDB in an UPDATE or a DELETE only
locks the rows that it updates or deletes. This greatly reduces the
probability of deadlocks.
State column of SHOW PROCESSLIST.
mysqlbinlog now prints an informative commented line (thread id,
timestamp, server id, etc) before each LOAD DATA INFILE, like it already
does for other queries; unless --short-form is used.
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 can be
trusted 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)
SET COLLATION_SERVER... statements it
replicates don't advance its position (so that if it gets interrupted before
the actual update query, it will later redo the SET). (Bug #5705)
FLUSH TABLES WITH READ LOCK, then COMMIT,
would cause replication slaves to stop (complaining about error 1223). Bug
surfaced when using the InnoDB innobackup script. (Bug #5949)
OPTIMIZE TABLE, REPAIR TABLE, and ANALYZE TABLE are now replicated
without any error code in the binary log. (Bug #5551)
SELECT FOR UPDATE
then executed a non-transactional update, that update automatically committed
the transaction (thus releasing InnoDB's row-level locks etc). (Bug #5714)
BEGIN and ROLLBACK binary
log events; that caused superfluous slave stops. (Bug #6522)
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 that 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.52 mysql_shutdown().
db.p()
USE db_name is
in effect.
USE db_name statements no longer are 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 option 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 binary log cache
is allocated for connections. This should save binlog_cache_size
bytes of memory (32KB 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 now are written to the binary log
even if they changed nothing on the master (for example, even if a
DELETE matched no rows). 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 statement does not result in the table being
marked as ``corrupted'' any more; the table remains as if CHECK TABLE
had not even started.
See section 13.5.5.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_stmt_prepare()) was terminated
by garbage characters. This can happen if you pass a wrong length
parameter to these functions. 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 %, as 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)
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
the master server, the error code was not written to the binary log.
(Bug #4553)
LOAD DATA INFILE
statement, it didn't show the statement in the output of SHOW
PROCESSLIST. (Bug #4326)
Functionality added or changed:
KILL statement now takes CONNECTION and QUERY
modifiers. 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 the DATE_ADD() and DATE_SUB() functions.
sql_mode, SQL_AUTO_IS_NULL, FOREIGN_KEY_CHECKS
(which was already replicated since 4.0.14, but here it's done more
efficiently and takes less space in the binary logs), UNIQUE_CHECKS.
Other variables (like character sets, SQL_SELECT_LIMIT, ...) will be
replicated in upcoming 5.0.x releases.
OR clauses.
See section 7.2.6 Index Merge Optimization.
SELECT INTO list_of_vars, which can be of mixed
(that is, global and local) types.
See section 19.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 13.1.4 INSERT Syntax.
GROUP_CONCAT() aggregate function.
See section 12.9 Functions and Modifiers for Use with GROUP BY Clauses.
UTF8. This makes MySQL
more flexible, but might cause some problems upgrading if you have table
or column names that use characters outside of the standard 7-bit US-ASCII
range.
See section 2.10.2 Upgrading from Version 4.0 to 4.1.
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 13.5.4.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:
Bugs fixed:
max_heap_table_size handling, that resulted in
Table is full error when the table was still smaller than the limit.
(Bug #7791).
mysqlaccess script.
Reported by Javier Fernandez-Sanguino Pena
and Debian Security Audit Team.
( CAN-2005-0004)
Functionality added or changed:
-win to -win32
-noinstall and -essential have been
moved in front of the version number
Seconds_Behind_Master will be NULL (which means ``unknown'') if
the slave SQL thread is not running, or if the slave I/O thread is not running
or not connected to master. It will be zero if the SQL thread has caught up
with the I/O thread. It no longer grows indefinitely if the master is idle.
InnoDB table lock in LOCK
TABLES if AUTOCOMMIT=1. This helps in porting old MyISAM
applications to InnoDB. InnoDB table locks in that case
caused deadlocks very easily.
InnoDB
if the size of `ibdata' files is smaller than what is stored in the
tablespace header; innodb_force_recovery overrides this.
--log-bin option but cannot initialize the
binary log at startup (that is, an error occurs when writing to the binary
log file or binary log index file).
MyISAM
when there is a "disk full" or "quota exceeded" error. See section A.4.3 How MySQL Handles a Full Disk.
Bugs fixed:
innodb_file_per_table in `my.cnf'.
If you shut down mysqld, records could disappear from
the secondary indexes of a table. (Bug #7496)
mysqld binaries built on HP-UX-11 did
not work with InnoDB files greater than 2 GB in size. (Bug #6189)
DISCARD TABLESPACE
if it fails because the table is referenced by a FOREIGN KEY.
InnoDB failed to drop a table in the background
drop queue if the table was referenced by a FOREIGN KEY constraint.
INSERT was waiting
for a lock to check a FOREIGN KEY constraint, then an assertion would
fail in lock_reset_all_on_table().
NULL value from a wrong field in the index; this has probably
caused no bugs visible to the user. It caused only some extra space to be
used in some rare cases.
fcntl() file flush method on Mac OS X versions 10.3
and up. Apple had disabled fsync() in Mac OS X for internal disk
drives, which caused corruption at power outages.
mysqladmin password now checks whether the server has
--old-passwords turned on or predates 4.1 and uses the old-format
password if so. (Bug #7451)
--default-character-set option to mysqladmin to avoid
problems when the default character set is not latin1. (Bug #7524)
FLOAT values. (Bug #7361)
PROCEDURE ANALYSE(), which did not quote some
ENUM values properly. (Bug #2813)
TIME or DATE values.
(Bug #6914)
mysql_config --lib_r.
(Bug #7021)
mysql_config not producing all relevant
flags from CFLAGS. (Bug #6964)
mysqld_safe not properly capturing
output from ps. (Bug #5878)
STR_TO_DATE function, if there is some other specifier in
the format string following %f. (Bugs #7458)
SHOW CREATE DATABASE even if the
connection has an open transaction or locked tables. Refusing it made
mysqldump --single-transaction sometimes fail to print a complete
CREATE DATABASE statement for some dumped databases. (Bug #7358)
MyISAM sometimes didn't sleep and retry the write, thus
resulting in a corrupted table. (Bug #7714)
--expire-log-days was not honored if using only transactions.
(Bug #7236)
ANALYZE
TABLE, OPTIMIZE TABLE, or REPAIR TABLE statements from the
master. (Bug #6461, Bug #7658)
Note: Due to a libtool-related bug in the source distribution,
the creation of shared libmysqlclient libraries was not possible
(the resulting files were missing the .so file name extension).
The file `ltmain.sh' was updated to fix this problem and the resulting
source distribution was released as `mysql-4.1.8a.tar.gz'. This
modification did not affect the binary packages. (Bug #7401)
Functionality added or changed:
ALTER DATABASE, the database name now can be omitted to apply the
change to the default database.
WITH CONSISTENT SNAPSHOT clause to START TRANSACTION to
begin a transaction with a consistent read.
--order-by-primary to mysqldump,
to sort each table's data in a dump file.
This may be useful when dumping a MyISAM table which
will be loaded into an InnoDB table.
Dumping a MyISAM table with this option is considerably
slower than without.
SHOW INNODB STATUS information
to a temporary file unless the configuration option
innodb_status_file=1 is set.
ALTER TABLE. This makes it much faster to recover from an
aborted ALTER TABLE or OPTIMIZE TABLE.
FULLTEXT index block size is changed to be 1024 instead of 2048.
--disable-log-bin option to mysqlbinlog. Using
this option you can disable binary logging for the statements produced
by mysqlbinlog. That is, mysqlbinlog --disable-log-bin
<file_name> | mysql won't write any statements to the MySQL server
binary log.
--master-data option for mysqldump now takes an optional
argument of 1 or 2 to produce a non-commented or commented CHANGE MASTER
TO statement. The default is 1 for backward compatibility.
mysqldump --single-transaction --master-data now is able to take an
online (non-blocking) dump of InnoDB and report the corresponding binary log
coordinates. This makes a backup suitable for point-in-time recovery,
roll-forward or replication slave creation. See section 8.8 The mysqldump Database Backup Program.
--lock-all-tables to mysqldump to lock all tables by
acquiring a global read lock.
--hex-blob option to mysqldump for dumping binary
string columns using hexadecimal notation.
mysql_hex_string() C API function that hex-encodes a string.
Binlog Dump
requests.
[mysql_cluster] section to `my.cnf' file for configuration
settings specific to MySQL Cluster. ndb-connectstring variable moved here.
State column of SHOW PROCESSLIST.
mysqlbinlog now prints an informative commented line (thread id,
timestamp, server id, etc) before each LOAD DATA INFILE, like it already
does for other queries; unless --short-form is used.
Bugs fixed:
WHERE clause. (Bug #7079)
LEFT, when this function
is part of the expression which is used as GROUP BY field.
(Bug #7101)
time_zone variable or used CONVERT_TZ() function) in case
when some table-level or column-level privileges existed. (Bug #6765)
mysql_stmt_data_seek(stmt,0) will now rewind a counter and enable
that buffered rows can be re-fetched on the client side.
(Bug #6996)
SHOW CREATE TABLE
command. (Bug #7043)
MATCH ... AGAINST on columns that use multi-byte character sets.
(Bug #6269)
CREATE TABLE .. SELECT query to the binary log when the
insertion of new records partially failed. (Bug #6682)
INSERT ... SELECT no longer reports spurious "column truncated" warnings
(Bug #6284)
HEAP tables that contain only one row.
(Bug #6748)
mysqld_safe was in many cases ignoring any
--no-defaults, --defaults-file, or
--defaults-extra-file arguments. Those arguments
are now honored, and this may change what options are
passed to mysqld in some installations.
CHAR BINARY and VARCHAR BINARY
columns from 4.0 tables as having the BINARY and VARBINARY
data types. Now they are interpreted as CHAR and VARCHAR
columns that have the binary collation of the column's character set. (This
is the same way that CHAR BINARY and VARCHAR BINARY are
handled for new tables created in 4.1.)
REPLACE or
INSERT ... ON DUPLICATE KEY UPDATE statements performing multiple-row
insert in the table that had unique and fulltext indexes.
(Bug #6784)
SET and DO statements
which caused wrong results to be returned from subsequent queries.
(Bug #6462)
MYSQL_TIME structure as for datetime values passed as strings.
(Bug #6266)
INSERT for a table with FULLTEXT indexes.
Under rare circumstances, this could result in a corrupted table if words of
different lengths may be considered equal. This is possible in some
collations, for example, in utf8_general_ci or latin1_german2_ci.
(Bug #6265)
FOREIGN KEY parser to remove the latin1
character 0xA0 from the end of an unquoted identifier. The EMS MySQL
Manager in ALTER TABLE adds that character after a table name, which
caused error 121 when we tried to add a new constraint.
rewind() when displaying
SHOW INNODB STATUS information on stderr.
INSERT IGNORE to insert several rows at a time,
and the first inserts were ignored because of a duplicate key collision,
then InnoDB in a replication slave assigned AUTO_INCREMENT values 1
bigger than in the master. This broke the MySQL replication. (Bug #6287)
innodb_data_file_path the
max specification in :autoextend:max:2000M.
This bug was introduced in 4.1.1.
innodb_locks_unsafe_for_binlog still uses
next-key locking (Bug #6747). InnoDB used next-key locking when
record matched completely to search tuple. This unnecessary
next-key locking is now removed when innodb_locks_unsafe_for_binlog
option is used.
FOREIGN KEY constraints treated table and
database names as case-insensitive. RENAME TABLE t TO T would hang
in an endless loop if t had a foreign key constraint defined on it.
Fix also a hang over the dictionary mutex that would occur if one tried
in ALTER TABLE or RENAME TABLE to create a foreign key
constraint name that collided with another existing name. (Bug #3478)
STMT_ATTR_UPDATE_MAX_LENGTH is set for a prepared statement,
mysql_stmt_store_result() will update field->max_length
for numeric columns as well. (Bug#6096)
ZEROFILL when converting integer
to string.
mysql_stmt_store_result() occurred without
a preceding call to mysql_stmt_bind_result().
SELECT * FROM t1 NATURAL JOIN t2....
SELECT ... PROCEDURE.
GROUP_CONCAT(...ORDER BY) when used with prepared statements gave
wrong sorting order.
CREATE TABLE created_table didn't signal when table was created.
This could cause a DROP TABLE created_table in another thread
to wait "forever".
OPTIMIZE TABLE, REPAIR TABLE, and ANALYZE TABLE are now replicated
without any error code in the binary log. (Bug #5551)
LOAD DATA INFILE now works with option replicate-rewrite-db.
(Bug #6353)
CREATE/ALTER/DROP DATABASE statements so
that replication of CREATE DATABASE is possible when using
--binlog-do-db and --binlog-ignore-db. (Bug #6391)
SELECT FOR UPDATE
then executed a non-transactional update, that update automatically committed
the transaction (thus releasing InnoDB's row-level locks etc). (Bug #5714)
BEGIN and ROLLBACK binary
log events; that caused superfluous slave stops. (Bug #6522)
BEGIN (or SET AUTOCOMMIT=0), FLUSH TABLES
WITH READ LOCK, transactional update, COMMIT, FLUSH
TABLES WITH READ LOCK could hang the connection forever and possibly the MySQL
server itself. This happened for example when running the innobackup
script several times. (Bug #6732)
mysqlbinlog did not print SET PSEUDO_THREAD_ID statements in
front of LOAD DATA INFILE statements inserting into temporary tables,
thus causing potential problems when rolling forward these statements after
restoring a backup. (Bug #6671)
Functionality added or changed:
MOD() no longer rounds arguments with a fractional part to integers.
Now it returns exact remainder after division. (Bug #6138)
innodb_table_locks
for making LOCK TABLE acquire also InnoDB locks.
The default value is 1, which means that
LOCK TABLES causes also InnoDB internally to take a table lock.
In applications using AUTOCOMMIT=1 and LOCK TABLES, InnoDB's
internal table locks that were added in 4.0.20 and 4.1.2
can cause deadlocks. You can set
innodb_table_locks=0 in `my.cnf' to remove that problem.
(Bug #3299, Bug #5998)
InnoDB Tables. (Bug #3299, Bug #5998)
InnoDB: SHOW TABLE STATUS now shows the creation time of the table
for InnoDB. Note that this timestamp might not be the correct time because,
e.g., ALTER TABLE changes this timestamp.
innodb_thread_concurrency would be exceeded, let a thread
sleep 10 ms before entering the FIFO queue; previously, the value was 50 ms.
Bugs fixed:
FOUND_ROWS() used together with LIMIT clause
in prepared statements. (Bug#6088)
NATURAL JOIN in prepared statements. (Bug#6046).
time_zone variable or use CONVERT_TZ() function)
does not require any privileges. (Bug #6116)
libmysqlclient
function mysql_create_db() was called. (Bug #6081)
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user so that all
privileges are revoked correctly. (Bug #5831). This corrects a case that
the fix in 4.1.6 could miss.
HEAP table with key_column IS
NOT NULL. This could also cause a crash if not all index parts where used.
(Bug #6082)
BLANK. This was caused by the
new key sort order in 4.1. (Bug #6151)
LOAD DATA INFILE…REPLACE
printing duplicate key error when executing the same
load query several times. (Bug #5835)
SELECT statements not returning a result set (such
as SELECT ... INTO OUTFILE). (Bug #6059)
libmysqlclient with wrong conversion of negative time
values to strings. (Bug #6049).
libmysqlclient with wrong conversion of zero date
values (0000-00-00) to strings. (Bug #6058)
RAND(?). (Bug #5985)
DATE, TIME, and DATETIME
columns in the binary protocol. The problem is compiler-specific and could
have been observed on HP-UX, AIX, Solaris9, when compiling with native
compiler. (Bug #6025)
TINYINT columns in the binary protocol.
The problem is specific to platforms where the C compiler has the char
data type unsigned by default. (Bug #6024)
FLUSH TABLES WITH READ LOCK,
then COMMIT, would cause replication slaves to stop (complaining
about error 1223). Bug surfaced when using the InnoDB innobackup
script. (Bug #5949)
FOREIGN KEY
operation, so that we do not starve other users doing CREATE TABLE
or other DDL operation. This caused a notorious 'Long semaphore wait' message
to be printed to the `.err' log. (Bug #5961)
Functionality added or changed:
--sigint-ignore to the mysql command line client
to make it ignore SIGINT signals (typically the result of the user
pressing Control-C).
innodb_max_purge_lag for delaying INSERT, UPDATE
and DELETE operations when the purge operations are lagging.
The default value of this parameter is zero, meaning that there will
not be any delays. See section 15.13 Implementation of Multi-Versioning.
innodb_autoextend_increment startup option that was
introduced in release 4.1.5 was made a settable global variable. (Bug #5736)
DROP TABLE is invoked on an InnoDB table for which the
.ibd file is missing, print to error log that the table was removed
from the InnoDB data dictionary, and allow MySQL to delete the
.frm file. Maybe DROP TABLE should issue a warning in this
case.
TIMESTAMP columns now can store NULL values. To create such
a column, you must explicitly specify the NULL attribute in the column
specification. (Unlike all other column types, TIMESTAMP columns
are NOT NULL by default.)
ALTER TABLE converts one AUTO_INCREMENT column to
another AUTO_INCREMENT column it will preserve zero values (this
includes the case that we don't change such column at all).
ALTER TABLE converts some column to TIMESTAMP NOT NULL
column it will convert NULL values to current timestamp value (One can
still get old behavior by setting system TIMESTAMP variable to zero).
Bugs fixed:
FOREIGN KEY parser that prevented
ALTER TABLE of tables containing `#' in their names. (Bug #5856)
ALTER TABLE t DISCARD TABLESPACE from working. (Bug #5851)
SHOW CREATE TABLE now obeys the SET SQL_MODE=ANSI
and SET SQL_QUOTE_SHOW_CREATE=0 settings. (Bug #5292)
CREATE TEMPORARY TABLE ... ENGINE=InnoDB to terminate
mysqld when running in innodb_file_per_table mode.
Per-table tablespaces for temporary tables will from now on be
created in the temporary directory of mysqld. (Bug #5137)
TEXT or BLOB) value, then
ANOTHER externally stored column would show up as 512 bytes of good
data + 20 bytes of garbage in a consistent read that fetched the old
version of the row. (Bug #5960)
HA_ERR_ROW_IS_REFERENCED if we cannot
DROP a parent table referenced by a FOREIGN KEY constraint;
this error number is less misleading than the previous number
HA_ERR_CANNOT_ADD_FOREIGN, but misleading still.
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user
so that all privileges are revoked correctly. (Bug #5831)
'abc' LIKE CONVERT('abc' as utf8). (Bug #5688)
mysql_change_user() C API function now frees all prepared statements
associated with the connection. (Bug #5315)
NULL into an AUTO_INCREMENT column
failed, when using prepared statements. (Bug #5510)
SET COLLATION_SERVER... statements it
replicates don't advance its position (so that if it gets interrupted before
the actual update query, it will later redo the SET). (Bug #5705)
MyISAM table fails because of a full disk or
an exceeded disk quota, it prints a message to the error log every 10 minutes,
and waits until disk space becomes available. (Bug #3248)
FROM
clause when it resolves GROUP BY clause (e.g. SELECT t1.a AS
c FROM t1, t2 ORDER BY a will produce an error if both t1 and
t2 tables contain a column). (Bug #4302)
ALTER TABLE converting column containing NULL
values to AUTO_INCREMENT column is no longer affected by
NO_AUTO_VALUE_ON_ZERO mode. (Bug #5915).
Functionality added or changed:
InnoDB: Added configuration option innodb_autoextend_increment
for setting the size in megabytes by which InnoDB tablespaces are
extended when they become full. The default value is 8, corresponding to
the fixed increment of 8MB in previous versions of MySQL.
InnoDB: The new Windows installation wizard of MySQL makes InnoDB as
the MySQL default table type on Windows, unless explicitly specified
otherwise. Note that it places the `my.ini' file in the
installation directory of the MySQL server.
Bugs fixed:
BETWEEN ? AND ? and a datetime column.
(Bug #5748)
max_sort_length, sql_mode, and
group_concat_max_len system variables now are stored in the query cache
with other query information to avoid returning an incorrect result from the
query cache. (Bug #5394)
(Bug #5515)
sql_mode=IGNORE_SPACE. It happened
to take phrases like default .07 as
identifier.identifier. (Bug #5318)
SELECT @var_name.
(Bug #4788)
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:
CSV storage engine.
TIMESTAMP values in InnoDB in
4.1 to be the same as in 4.0. This difference resulted in incorrect datetime
values in TIMESTAMP columns in InnoDB tables after an 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.3.9 Selecting a MySQL Server type.
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 statement does not result in the table being
marked as ``corrupted'' any more; the table remains as if CHECK TABLE
had not even started.
See section 13.5.5.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).
ORDER BY and GROUP BY was dropped. For example the following
query SELECT a AS b FROM t1 ORDER BY t1.b is not allowed. One should
use SELECT a AS b FROM t1 ORDER BY t1.a or SELECT a AS b FROM
t1 ORDER BY b instead. This was non-standard (since aliases are defined on
query level not on table level) and caused problems with some queries.
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
the master server, the error code was not written to the binary log.
(Bug #4553)
LOAD DATA INFILE
statement, it didn't show the statement 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)
ORDER BY
clause was present in two tables participating in SELECT even if the
second instance of column in select list was renamed. (Bug #4302)
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:
ARCHIVE storage engine.
ucs2 and utf8
Unicode character sets: Icelandic, Latvian, Romanian, Slovenian, Polish,
Estonian, Swedish, Turkish, Czech, Danish, Lithuanian, Slovak, Spanish,
Traditional Spanish.
@@time_zone system
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.9 Post-Installation Setup and Testing.
timezone system variable has been removed and 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 the equivalent datetime value in
the time zone specified by its third argument.
CHECK TABLE now can be killed. It will then mark the table as corrupted.
See section 13.5.5.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.52 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 account 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(?, col_name) 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=file_name, where the file was located somewhere
outside of the data directory, it created binary log files owned by
root in this directory that 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:
EXAMPLE storage engine.
mysqld Windows server was renamed to mysqld-debug.
See section 2.3.9 Selecting a MySQL Server type.
Handler_discover status variable.
MYSQL_TYPE_BLOB type
code in prepared statement protocol.
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_multi now creates the log in the directory named by
datadir (from the [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 12.3 String Functions.
mysql command-line client no longer stores in the history file
multiple copies of identical queries that are run consecutively.
mysql command-line client now are stored
in the history file as a single line.
UUID() function implemented. Note that it does not work with replication
yet.
See section 12.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 system 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 binary log cache
is allocated for connections. This should save binlog_cache_size
bytes of memory (32KB 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 the in-memory buffer. 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)
mysqld could crash when a table was altered and used at the same time.
This was a 4.1.2-specific bug. (Bug #3643).
WHERE with PROCEDURE ANALYSE().
(Bug #2238).
GRANT accounts until 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 were 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_stmt_prepare()) was terminated
by garbage characters. This can happen if you pass a wrong length
parameter to these functions. The result was that the
garbage characters were written into the binary log. (Bug #2703)
mysql_stmt_fetch and
mysql_stmt_store_result() to hang if they were called without
prior call of mysql_stmt_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 %, as 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)
mysqld that was started with binary
logging disabled, but with a non-zero value for the expire_logs_days
system 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:
lower_case_table_names is
non-zero.
--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 13.5.5.4 LOAD INDEX INTO CACHE Syntax.
Structured system
variables are introduced as a means of grouping related key cache parameters.
See section 9.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 system 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.