This appendix lists the changes from version to version in the MySQL source code.
We are now working actively on MySQL 4.1 & 5.0 and will only provide critical bug fixes for MySQL 4.0 and MySQL 3.23. We update this section as we add new features, so that everybody can follow the development.
Our TODO section contains what further plans we have for 4.1 & 5.0. See section 1.9 MySQL and The Future (The TODO).
Note that we tend to update the manual at the same time we make changes to MySQL. If you find a version listed here that you can't find on the MySQL download page (http://www.mysql.com/downloads/), this means that the version has not yet been released!
The date mentioned with a release version is the date of the last BitKeeper ChangeSet that this particular release has been based on, not the date when the packages have been made available. The binaries are usually made available a few days after the date of the tagged ChangeSet - building and testing all packages takes some time.
For the time being, version 5.0 is only available in source code. See section 2.3.4 Installing from the Development Source Tree.
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.
SET
@a=10; then SELECT @A; will now return 10. Of course,
the content of the variable is still case sensitive; only the name of
this variable is case insensitive.
Version 4.1 of the MySQL server includes many enhancements and new features. Binaries for this version are available for download at http://www.mysql.com/downloads/mysql-4.1.html.
SELECT * FROM t1 WHERE t1.a=(SELECT t2.b FROM t2); SELECT * FROM t1 WHERE (1,2,3) IN (SELECT a,b,c FROM t2);
SELECT t1.a FROM t1, (SELECT * FROM t2) t3 WHERE t1.a=t3.a;
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 6.4.3 INSERT Syntax.
GROUP_CONCAT() aggregate function.
See section 6.3.7 Functions and Modifiers for Use with GROUP BY Clauses.
BTREE index on HEAP tables.
SHOW WARNINGS shows warnings for the last command.
See section 4.5.7.9 SHOW WARNINGS | ERRORS.
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table LIKE table.
HELP command that can be used in the mysql
command line client (and other clients) to get help for SQL commands.
For a full list of changes, please refer to the changelog sections for each individual 4.1.x release.
Functionality added or changed:
MERGE tables with tables in different databases.
COMPRESS(), UNCOMPRESS(), and
UNCOMPRESSED_LENGTH().
SET sql_mode=mode for a complex mode (like ANSI), we
now update the sql_mode variable to include all options the mode
requires.
ROLLUP, which gives
you summary rows for each GROUP BY level.
SQLSTATE codes for all server errors.
mysql_sqlstate() and
mysql_stmt_sqlstate() that returns the SQLSTATE error code for the
last error.
--lower-case-table-names=1 now also makes aliases case
insensitive. (Bug #534)
TIME columns with hours > 24 (days) were returned incorrectly to the client.
ANALYZE, OPTIMIZE, REPAIR, FLUSH (and its
equivalents invoked from mysqladmin) commands
are now stored in the binary log (hence are replicated to the slave),
except FLUSH LOGS, FLUSH MASTER, FLUSH SLAVE,
FLUSH TABLES WITH READ LOCK, and unless the optional
NO_WRITE_TO_BINLOG keyword
(or its alias LOCAL) was used (for a syntax example,
see section 4.5.3 FLUSH Syntax).
RELAY_LOG_PURGE to enable/disable automatic
relay log purging.
LOAD DATA now produces warnings that can be fetched with
SHOW WARNINGS.
CREATE TABLE table_name (LIKE table_name2).
CREATE TABLE table_name (...) TYPE=storage_engine will give a
warning if storage engine is not honored.
Bugs fixed:
WHERE clause bigger then outer query WHERE clause (bug #726).
MyISAM tables with FULLTEXT indexes
created in 4.0.x unreadable in 4.1.x.
REPAIR TABLE ... USE_FRM when used
with tables
that contained TIMESTAMP fields 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) fulltext index.
LAST_INSERT_ID() will now return 0 if the last INSERT didn't
insert any rows.
BEGIN, in the
first relay log). (Bug #53)
CONNECTION_ID() is now properly replicated (bug #177).
PASSWORD() function in 4.1 is now properly replicated
(bug #344).
UNION operations.
DERIVED TABLES when EXPLAIN is
used on a DERIVED TABLES with a join.
DELETE with ORDER BY and
LIMIT caused by non initiated array of reference pointers.
USER() function caused by the error in the size of
the allocated string.
GEOMETRY column type with a storage engine that does not support
it.
UNION caused by the empty select list and
a non-existent field being used in some of the sub-selects.
FLUSH LOGS was
issued on the master. (Bug #254)
Functionality added or changed:
localhost.
REPAIR of MyISAM tables now uses less temporary disk space when
sorting char columns.
DATE/DATETIME checking is now a bit stricter to support the
ability to automatically distinguish between date, datetime, and time
with microseconds. For example, dates of type YYYYMMDD HHMMDD are no
longer supported; one 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().
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 4.5.6 KILL Syntax.
REPAIR TABLE now can be killed. See section 4.5.6 KILL Syntax.
USE|IGNORE|FORCE INDEX.
DROP TEMPORARY TABLE now only drops temporary tables and doesn't
end transactions.
UNION in derived tables.
TIMESTAMP is now returned as string of type 'YYYY-MM-DD HH:MM:DD'
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 NOT NULL AUTO_INCREMENT UNIQUE.
SELECT ... FROM DUAL is an alias for SELECT ....
(To be compatible with some other databases).
CHAR/VARCHAR it's now
automatically changed to TEXT or BLOB; One will get a
warning in this case.
BLOB/TEXT types with the
syntax BLOB(length) and TEXT(length). MySQL will
automatically change it to one of the internal BLOB/TEXT
types.
CHAR BYTE is an alias for CHAR BINARY.
VARCHARACTER is an alias for VARCHAR.
integer MOD integer and integer DIV integer.
SERIAL DEFAULT VALUE added as an alias for AUTO_INCREMENT.
TRUE and FALSE added as alias for 1 and 0, respectively.
SELECT .. LIMIT 0 to return proper row count for
SQL_CALC_FOUND_ROWS.
--tmpdir=dirname1:dirname2:dirname3.
SELECT * from t1 where t1.a=(SELECT t2.b FROM t2).
SELECT a.col1, b.col2
FROM (SELECT MAX(col1) AS col1 FROM root_table) a,
other_table b
WHERE a.col1=b.col1;
BTREE index on HEAP tables.
CREATE TABLE.
SHOW FULL COLUMNS FROM table_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) returns the variance of expr
CREATE
[TEMPORARY] TABLE [IF NOT EXISTS] table (LIKE table). The table can
be either normal or temporary.
--reconnect and disable-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 table type is now included in the standard binaries,
adding transactions, row-level locking, and foreign keys.
See section 7.5 InnoDB Tables.
MERGE tables, now supporting INSERT statements and
AUTO_INCREMENT.
See section 7.2 MERGE Tables.
UNION syntax in SELECT.
See section 6.4.1.2 UNION Syntax.
DELETE statements.
See section 6.4.6 DELETE Syntax.
libmysqld, the embedded server library.
See section 9.1.15 libmysqld, the Embedded MySQL Server Library.
GRANT privilege options for even tighter control and
security.
See section 4.3.1 GRANT and REVOKE Syntax.
GRANT system, particularly
useful for ISPs and other hosting providers.
See section 4.3.6 Limiting user 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:
Functionality added or changed:
INSERT from SELECT where the table to be inserted into
can also be in the table list from the SELECT.
CREATE and INSERT from any UNION.
SQL_CALC_FOUND_ROWS option always returns the total number
of rows for any UNION.
--table option from mysqlbinlog
to refrain from repetition of mysqldump functionality.
max_seeks_for_key which can
be used to force the optimiser to use keys instead of table scans
even if the cardinality of the index is low.
LEFT JOIN to normal join in
some cases.
--nice option to mysqld_safe to allow setting the
niceness of the mysqld process. (Thanks to Christian Hammers for
providing the initial patch.) (Bug #627)
--read-only option to mysqld to only allow updates from
slave threads or the SUPER user. (Original patch from Markus
Benning).
SHOW BINLOG EVENTS FROM x where x is strictly less than 4 now
silently converts x to 4 instead of printing an error. The same change
was done for CHANGE MASTER TO MASTER_LOG_POS=x and
CHANGE MASTER TO RELAY_LOG_POS=x.
mysqld now only adds an interrupt handler for the SIGINT signal
if you start it with the new --gdb option. This is because some
MySQL users got strange problems when they accidently sent SIGINT to
mysqld threads.
RESET SLAVE now clears the Last_errno and
Last_error fields in the output of SHOW SLAVE STATUS.
Bugs fixed:
UNION.
SHOW TABLES and
similar commands.
max_user_connections.
HANDLER without index now works properly when a table has deleted
rows. (Bug #787)
LOAD DATA in mysqlbinlog. (Bug #670).
SET CHARACTER SET DEFAULT works. (Bug #462)
MERGE tables behaviour in ORDER BY ... DESC queries.
(Bug #515)
PURGE MASTER LOGS or SHOW MASTER LOGS
when binlog is off. (Bug #733)
DATETIME column and an integer
constant. (Bug #504)
mysqlbinlog. (Bug #672)
ERROR 1105: Unknown error that happened for some SELECT
queries, where a column that was declared as NOT NULL was compared
with an expression that took NULL value.
INSERT and UPDATE queries.
mysql_real_connect() to use poll() instead of
select() to go around problem with many open files in the client.
MATCH ... AGAINST used with a
LEFT JOIN query.
mysqld variables
on command line to 4294967295.
HANDLER ... READ command, when table is referenced via an alias.
safe_malloc, which caused MySQL to
give "Freeing wrong aligned pointer" errors on SCO 3.2.
ALTER TABLE ... ENABLE/DISABLE KEYS could cause a core dump when
done on an INSERT DELAYED table.
mysqld. (Bug #549)
INSERT ... SELECT in an AUTO_INCREMENT column
not replicate well. This bug is in the master, not in the slave.
(Bug #490)
INSERT ... SELECT inserted rows into a
non-transactional table, but failed at some point (for example because
of a 'Duplicate key' error), the query was not written to the binlog;
now it is written to the binlog, with its error code, as all other
queries are. About the slave-skip-errors option for how to
handle partially completed queries in the slave, see section 4.10.6 Replication Options in `my.cnf'. (Bug #491)
SET FOREIGN_KEY_CHECKS=0 was not replicated properly in
the MySQL replication. The fix will probably not be backported to
3.23.
LOAD DATA INFILE which had no IGNORE or
REPLACE clause on the master, was replicated with IGNORE.
While this is not a problem if the master's and slave's data are identical
(the LOAD having produced no duplicate conflict on the master will
not produce any on the slave anyway), which is true in normal operation, it
is better for debugging to not silently add the IGNORE (because this
way one can get an error message on the slave and discover that for some
reason, the data on master and slave are different and investigate why).
(Bug #571)
LOAD DATA INFILE printed an incomplete ``Duplicate entry
'%-.64s' for key %d''' message (the key name and value were not mentioned)
in case of duplicate conflict (which does not happen in normal operation).
(Bug #573)
--debug, CHANGE MASTER
TO RELAY_LOG_POS could cause a debug assertion failure. (Bug #576)
LOCK TABLES WRITE on an InnoDB table, commit could
not happen, if the query was not written to the binary log (for example, if
--log-bin was not used, or binlog-ignore-db was
used). (Bug #578)
DROP TABLE statements to its binlog, then the 4.0.13
slave would not notice the temporary tables have to be dropped, until the
slave mysqld server is restarted. This minor inconvenience is
fixed in 3.23.57 and 4.0.14 (meaning the master must be upgraded to
3.23.57 and the slave to 4.0.14 to remove the inconvenience). (Bug #254)
MASTER_POS_WAIT() was waiting, and the slave was idle, and
the SQL slave thread terminated, MASTER_POS_WAIT() went on waiting
forever. Now when the SQL slave thread terminates,
MASTER_POS_WAIT() immediately returns NULL (``slave
stopped''). (Bug #651)
Functionality added or changed:
PRIMARY KEY now implies NOT NULL. (Bug #390)
--enable-local-infile to match the Unix build configuration.
mysql-test-run. time does not
accept all required parameters on many platforms (for example, QNX) and timing
the tests is not really required (it's not a benchmark anyway).
SHOW MASTER STATUS and SHOW SLAVE STATUS required the
SUPER privilege; now they accept REPLICATION CLIENT as well.
(Bug #343)
myisam_repair_threads variable to enable it.
See section 4.5.7.4 SHOW VARIABLES.
innodb_max_dirty_pages_pct variable which controls amount of
dirty pages allowed in InnoDB buffer pool.
CURRENT_USER() and Access denied error messages now report the
hostname exactly as it was specified in the GRANT command.
ANALYZE TABLE.
--new now changes binary items (0xFFDF) to be
treated as binary strings instead of numbers by default. This fixes some
problems with character sets where it's convenient to input the string
as a binary item. After this change you have to convert the binary
string to INTEGER with a CAST if you want to compare two
binary items with each other and know which one is bigger than the other.
SELECT CAST(0xfeff AS UNSIGNED) < CAST(0xff AS UNSIGNED).
This will be the default behaviour in MySQL 4.1. (Bug #152)
NATURAL LEFT JOIN, NATURAL RIGHT JOIN and
RIGHT JOIN when using many joined tables. The problem was that
the JOIN method was not always associated with the tables
surrounding the JOIN method. If you have a query that uses many
RIGHT JOIN or NATURAL ... JOINS you should check that they
work as you expected after upgrading MySQL to this version. (Bug #291)
delayed_insert_timeout on Linux (most modern glibc
libraries have a fixed pthread_cond_timedwait). (Bug #211)
max_insert_delayed_threads. (Bug #211)
UPDATE ... LIMIT to also count accepted, but not changed rows.
BIT_AND() and BIT_OR() now return an unsigned 64 bit value.
--log-warnings).
--skip-symlink and --use-symbolic-links and
replaced these with --symbolic-links.
innodb_flush_log_at_trx_commit was changed
from 0 to 1 to make InnoDB tables ACID by default.
See section 7.5.3 InnoDB Startup Options.
SHOW KEYS to display keys that are disabled by
ALTER TABLE DISABLE KEYS command.
CREATE TABLE, first
try if the default table type exists before falling back to MyISAM.
MEMORY as an alias for HEAP.
rnd to my_rnd as the name was too generic
and is an exported symbol in libmysqlclient (thanks to Dennis Haney
for the initial patch).
mysqldump no longer silently deletes the binlogs when called with
--master-data or --first-slave;
while this behaviour was convenient for some
users, others may suffer from it. Now one has to explicitely ask for
this deletion with the new --delete-master-logs option.
replicate-wild-ignore-table=mysql.%)
to exclude mysql.user, mysql.host, mysql.db,
mysql.tables_priv and mysql.columns_priv from
replication, then GRANT and REVOKE will not be replicated.
Bugs fixed:
Access denied error message had wrong Using password
value. (Bug #398)
mysql command line client no longer looks for \* commands
inside backtick-quoted strings.
Unknown error when using UPDATE ... LIMIT. (Bug #373)
GROUP BY with constants. (Bug #387)
UNION and OUTER JOIN. (Bug #386)
UPDATE and the query required a
temporary table bigger than tmp_table_size. (Bug #286)
mysql_install_db with the -IN-RPM option for the Mac OS X
installation to not fail on systems with improperly configured hostname
configurations.
LOAD DATA INFILE will now read 000000 as a zero date instead as
"2000-00-00".
DELETE FROM table WHERE const_expression
always to delete the whole table (even if expression result was false).
(Bug #355)
FORMAT('nan',#). (Bug #284)
HAVING ... COUNT(DISTINCT ...).
*) in
MATCH ... AGAINST() in some complex joins.
REPAIR ... USE_FRM command, when used on read-only,
nonexisting table or a table with a crashed index file.
--no-defaults, with a prompt
that contained hostname and connection to non-existing db was requested
LEFT, RIGHT and MID when used with
multi-byte character sets and some GROUP BY queries. (Bug #314)
ORDER BY being discarded for some
DISTINCT queries. (Bug #275)
SET SQL_BIG_SELECTS=1 works as documented (This corrects
a new bug introduced in 4.0)
UPDATE ... ORDER BY. (Bug #241)
WHERE clause with constant
expression like in WHERE 1 AND (a=1 AND b=1).
SET SQL_BIG_SELECTS=1 works again.
SHOW GRANTS.
FULLTEXT index stopped working after ALTER TABLE
that converts TEXT field to CHAR. (Bug #283)
SELECT and wildcarded select list,
when user only had partial column SELECT privileges on the table.
SET PASSWORD.
NATURAL JOINs in the query.
SUM() didn't return NULL when there was no rows in result
or when all values was NULL.
--open-files-limit in
mysqld_safe. (Bug #264)
SHOW PROCESSLIST.
NAN in FORMAT(...) function ...
ALTER TABLE ENABLE / DISABLE KEYS which failed to
force a refresh of table data in the cache.
LOAD DATA INFILE for custom parameters
(ENCLOSED, TERMINATED and so on) and temporary tables
(Bugs #183 and #222).
FLUSH LOGS was
issued on the master. (Bug #254)
LOAD DATA INFILE IGNORE : when reading
the binary log, mysqlbinlog and the replication code read REPLACE
instead of IGNORE. This could make the slave's table
become different from the master's table. (Bug #218)
relay_log_space_limit was set to a too
small value. (Bug #79)
MyISAM when a row is inserted into a table with a
large number of NULL columns. Bug was caused by wrong calculation
of the record length, as the space required for storage of NULL
bits was not added to the total record length.
SELECT @nonexistent_variable caused the
error in client - server protocol due to net_printf() being sent to
the client twice.
SQL_BIG_SELECTS option.
SHOW PROCESSLIST which only displayed a localhost
in the "Host" column. This was caused by a glitch that only used
current thread info instead info from the linked list of threads.
multi-table-update for InnoDB tables as well.
multi-table-updates that caused some rows to be
updated several times.
mysqldump when it was called with
--master-data: the CHANGE MASTER TO commands appended to
the SQL dump had wrong coordinates. (Bug #159)
USER() was replicated
on the slave ; this caused segfault on the slave. (Bug #178).
USER() is still badly replicated on the slave (it is
replicated to "").
Functionality added or changed:
mysqld no longer reads options from world-writeable config files.
SHOW PROCESSLIST will now include the client TCP port after the
hostname to make it easier to know from which client the request
originated.
Bugs fixed:
mysqld crash on extremely small values of
sort_buffer variable.
INSERT INTO u SELECT ... FROM t was written too late to the
binary log if t was very frequently updated during the execution of
this query. This could cause a problem with mysqlbinlog or
replication. The master must be upgraded, not the slave. (Bug #136)
WHERE clause. (Bug #142)
multi-table updates with InnoDB
tables. This bug occurred as, in many cases, InnoDB tables can not
be updated "on the fly", but offsets to the records have to be stored in
a temporary table.
mysql_secure_installation to the server
RPM subpackage. (Bug #141)
myisamchk) crash on artificially
corrupted .MYI files.
BACKUP TABLE to overwrite existing files.
UPDATE statements when user had all privileges
on the database where tables are located and there were any entries in
tables_priv table, that is, grant_option was true.
TRUNCATE any table in the same database.
LOCK TABLE followed by DROP
TABLE in the same thread. In this case one could still kill the thread
with KILL.
LOAD DATA LOCAL INFILE was not properly written to the binary
log (hence not properly replicated). (Bug #82)
RAND() entries were not read correctly by mysqlbinlog from
the binary log which caused problems when restoring a table that was
inserted with RAND(). INSERT INTO t1 VALUES(RAND()). In
replication this worked ok.
SET SQL_LOG_BIN=0 was ignored for INSERT DELAYED
queries. (Bug #104)
SHOW SLAVE STATUS reported too old positions
(columns Relay_Master_Log_File and Exec_master_log_pos)
for the last executed statement from the master, if this statement
was the COMMIT of a transaction. The master must be upgraded for that,
not the slave. (Bug #52)
LOAD DATA INFILE was not replicated by the slave if
replicate_*_table was set on the slave. (Bug #86)
RESET SLAVE, the coordinates displayed by SHOW
SLAVE STATUS looked un-reset (though they were, but only
internally). (Bug #70)
LOAD DATA.
ANALYZE procedure with error.
CHAR(0) columns that could cause wrong
results from the query.
AUTO_INCREMENT column,
as a secondary column in a multi-column key (see section 3.5.9 Using AUTO_INCREMENT), when
data was inserted with INSERT ... SELECT or LOAD DATA into
an empty table.
STOP SLAVE didn't stop the slave until the slave
got one new command from the master (this bug has been fixed for MySQL 4.0.11
by releasing updated 4.0.11a Windows packages, which include this individual
fix on top of the 4.0.11 sources). (Bug #69)
LOAD DATA command
was issued with full table name specified, including database prefix.
pthread_attr_getstacksize on
HP-UX 10.20 (Patch was also included in 4.0.11a sources).
bigint test to not fail on some platforms (for example, HP-UX and
Tru64) due to different return values of the atof() function.
rpl_rotate_logs test to not fail on certain platforms (e.g.
Mac OS X) due to a too long file name (changed slave-master-info.opt
to .slave-mi).
Functionality added or changed:
NULL is now sorted LAST if you use ORDER BY ... DESC
(as it was before before MySQL 4.0.2). This change was required to comply
with the SQL-99 standard. (The original change was made because we thought
that SQL-99 required NULL to be always sorted at the same position,
but this was incorrect).
START TRANSACTION (SQL-99 syntax) as alias for BEGIN.
This is recommended to use instead of BEGIN to start a transaction.
OLD_PASSWORD() as a synonym for PASSWORD().
ALL in group functions.
INNER JOIN and JOIN syntaxes.
For example, SELECT * FROM t1 INNER JOIN t2 didn't work before.
Bugs fixed:
multi-table-delete and InnoDB tables.
BLOB NOT NULL columns used with IS NULL.
CREATE TABLE (...)
AUTO_INCREMENT=#.
MIN(key_column) could in some cases return NULL on a column
with NULL and other values.
MIN(key_column) and MAX(key_column) could in some cases
return wrong values when used in OUTER JOIN.
MIN(key_column) and MAX(key_column) could return wrong
values if one of the tables was empty.
INTERVAL,
CASE, FIELD, CONCAT_WS, ELT and
MAKE_SET functions.
--lower-case-table-names (default on Windows)
and you had tables or databases with mixed case on disk, then
executing SHOW TABLE STATUS followed with DROP DATABASE
or DROP TABLE could fail with Errcode 13.
Functionality added or changed:
--log-error[=file_name] to mysqld_safe and
mysqld. This option will force all error messages to be put in a
log file if the option --console is not given. On Windows
--log-error is enabled as default, with a default name of
host_name.err if the name is not specified.
Warning: to Note: in the log files.
GROUP BY ... ORDER BY NULL
then result is not sorted.
mysqld to replace/disable
built-in stopword list, that is used in fulltext search.
See section 4.5.7.4 SHOW VARIABLES.
gethostbyaddr() to resolve a hostname. You can fix
this for earlier MySQL versions by starting mysqld with
--thread-stack=192K.
mysql_waitpid to the binary distribution and the
MySQL-client RPM subpackage (required for mysql-test-run).
MySQL RPM package to MySQL-server. When
updating from an older version, MySQL-server.rpm will simply replace
MySQL.rpm.
replicate_wild_do_table=db.% or
replicate_wild_ignore_table=db.%, these rules will be applied to
CREATE/DROP DATABASE too.
MASTER_POS_WAIT().
Bugs fixed:
rand() distribution from the first call.
mysqld to hang when a
table was opened with the HANDLER command and then
dropped without being closed.
NULL in an auto_increment field and also
uses LAST_INSERT_ID().
ORDER BY constant_expression.
myisamchk's parallel repair mode.
mysqladmin --relative.
show status reported a strange number for
Open_files and Open_streams.
EXPLAIN on empty table.
LEFT JOIN that caused zero rows to be returned in
the case the WHERE condition was evaluated as FALSE after
reading const tables. (Unlikely condition).
FLUSH PRIVILEGES didn't correctly flush table/column privileges
when mysql.tables_priv is empty.
LOAD DATA INFILE one a file
that updated and auto_increment field with NULL or 0. This
bug only affected MySQL 4.0 masters (not slaves or MySQL 3.23 masters).
NOTE: If you have a slave that has replicated a file with
generated auto_increment fields then the slave data is corrupted and you
should reinitialise the affected tables from the master.
NOT NULL field to an
expression that returned NULL.
str LIKE "%other_str%" where str or
other_str contained characters >= 128.
LOAD DATA and InnoDB failed
with table full error the binary log was corrupted.
Functionality added or changed:
OPTIMIZE TABLE will for MyISAM tables treat all NULL
values as different when calculating cardinality. This helps in
optimising joins between tables where one of the tables has a lot of
NULL values in a indexed column:
SELECT * from t1,t2 where t1.a=t2.key_with_a_lot_of_null;
FORCE INDEX (key_list). This acts likes
USE INDEX (key_list) but with the addition that a table scan is
assumed to be VERY expensive. One bad thing with this is that it makes
FORCE a reserved word.
Bugs fixed:
LOAD DATA INFILE statement that
caused log rotation.
Functionality added or changed:
max_packet_length for libmysqld.c is now 1024*1024*1024.
max_allowed_packet in a file ready by
mysql_options(MYSQL_READ_DEFAULT_FILE).
for clients.
ON UPDATE CASCADE in
FOREIGN KEY constraints. See the InnoDB section in the manual
for the InnoDB changelog.
Bugs fixed:
ALTER TABLE with BDB tables.
QUOTE() function.
GROUP BY when used on BLOB column with NULL values.
NULL values in CASE ... WHEN ...
Functionality added or changed:
mysqlbug now also reports the compiler version used for building
the binaries (if the compiler supports the option --version).
Bugs fixed:
-DBIG_TABLES
on a 32 bit system.
mysql_drop_db() didn't check permissions properly so anyone could
drop another users database. DROP DATABASE is checked properly.
Functionality added or changed:
CHARACTER SET xxx and CHARSET=xxx
table options (to be able to read table dumps from 4.1).
IFNULL(A,B) is now set to be the
more 'general' of the types of A and B. (The order is
STRING, REAL or INTEGER).
mysql.server startup script in the RPM packages from
`/etc/rc.d/init.d/mysql' to `/etc/init.d/mysql'
(which almost all current Linux distributions support for LSB compliance).
Qcache_lowmem_prunes status variable (number of queries that were
deleted from cache because of low memory).
mysqlcheck so it can deal with table names containing dashes.
SHOW VARIABLES)
is no longer used when inserting small (less than 100) number of rows.
SELECT ... FROM merge_table WHERE indexed_column=constant_expr.
LOCALTIME and LOCALTIMESTAMP as synonyms for
NOW().
CEIL is now an alias for CEILING.
CURRENT_USER() function can be used to get a user@host
value as it was matched in the GRANT system.
See section 6.3.6.2 Miscellaneous Functions.
CHECK constraints to be compatible with SQL-99. This made
CHECK a reserved word. (Checking of CHECK constraints is
still not implemented).
CAST(... as CHAR).
LIMIT syntax:
SELECT ... LIMIT # OFFSET #
mysql_change_user() will now reset the connection to the state
of a fresh connect (Ie, ROLLBACK any active transaction, close
all temporary tables, reset all user variables etc..)
Bugs fixed:
multi table updates
--lower-case-table-names default on Mac OS X as the default
file system (HFS+) is case insensitive.
See section 6.1.3 Case Sensitivity in Names.
AUTOCOMMIT=0 mode didn't rotate binary log.
SELECT with joined tables with
ORDER BY and LIMIT clause when filesort had to be used.
In that case LIMIT was applied to filesort of one of the tables,
although it could not be.
This fix solved problems with LEFT JOIN too.
mysql_server_init() now makes a copy of all arguments. This fixes
a problem when using the embedded server in C# program.
libmysqlclient library
that allowed a malicious MySQL server to crash the client
application.
mysql_change_user() handling.
All users are strongly recommended to upgrade to version 4.0.6.
--chroot command-line option of
mysqld from working.
"..." in boolean full-text search.
OPTIMIZE TABLE to corrupt the table
under some rare circumstances.
LOCK TABLES now works together with multi-table-update and
multi-table-delete.
--replicate-do=xxx didn't work for UPDATE commands.
(Bug introduced in 4.0.0)
REPLACE, AUTO_INCREMENT,
INSERT INTO ... SELECT ... were fixed. See the InnoDB changelog
in the InnoDB section of the manual.
Functionality added or changed:
SHOW PROCESSLIST
command
WEEK() so that one can get
week number according to the ISO 8601 specification.
(Old code should still work).
INSERT DELAYED threads doesn't hang on Waiting for
INSERT when one sends a SIGHUP to mysqld.
AND works according to SQL-99 when it comes to
NULL handling. In practice, this only affects queries where you
do something like WHERE ... NOT (NULL AND 0).
mysqld will now resolve basedir to its full path (with
realpath()). This enables one to use relative symlinks to the
MySQL installation directory. This will however cause show
variables to report different directories on systems where there is
a symbolic link in the path.
IGNORE INDEX or USE INDEX.
to be ignored.
--use-frm option to mysqlcheck. When used with
REPAIR, it gets the table structure from the .frm file, so the
table can be repaired even if the .MYI header is corrupted.
MAX() optimisation when used with JOIN and
ON expressions.
BETWEEN behaviour changed (see section 6.3.1.2 Comparison Operators).
Now datetime_col BETWEEN timestamp AND timestamp should work
as expected.
TEMPORARY MERGE tables now.
DELETE FROM myisam_table now shrinks not only the `.MYD' file but
also the `.MYI' file.
--open-files-limit=# option to mysqld_safe
it's now passed on to mysqld.
EXPLAIN from 'where used' to
'Using where' to make it more in line with other output.
safe_show_database as it was no longer used.
automake 1.5 and
libtool 1.4.
--ignore-space) back to the
original --ignore-spaces in mysqlclient. (Both syntaxes will
work).
UPDATE privilege when using REPLACE.
DROP TEMPORARY TABLE ..., to be used to make
replication safer.
BEGIN/COMMIT are now stored in the binary log on
COMMIT and not stored if one does ROLLBACK. This fixes
some problems with non-transactional temporary tables used inside
transactions.
SELECT * FROM (t2 LEFT JOIN t3 USING (a)), t1 worked, but
not SELECT * FROM t1, (t2 LEFT JOIN t3 USING (a)). Note that
braces are simply removed, they do not change the way the join is
executed.
READ UNCOMMITTED and READ COMMITTED.
For a detailed InnoDB changelog, see section 7.5.15 InnoDB Change History
in this manual.
Bugs fixed:
MAX() optimisation when used with JOIN and
ON expressions.
INSERT DELAY threads don't hang on Waiting for
INSERT when one sends a SIGHUP to mysqld.
IGNORE INDEX or USE INDEX.
root user in mysqld_safe.
CHECK
or REPAIR.
GROUP BY queries that
didn't return any result.
mysqlshow to work properly with wildcarded database names and
with database names that contain underscores.
MyISAM crash when using dynamic-row tables with huge numbers of
packed fields.
BDB transactions.
MATCH
relevance calculations.
IN BOOLEAN MODE that made MATCH
to return incorrect relevance value in some complex joins.
MyISAM key length to a value
slightly less that 500. It is exactly 500 now.
GROUP BY on columns that may have a NULL value
doesn't always use disk based temporary tables.
--des-key-file argument to mysqld
is interpreted relative to the data directory if given as a relative pathname.
NULL has to be MyISAM. This was okay for 3.23, but not
needed in 4.*. This resulted in slowdown in many queries since 4.0.2.
ORDER BY ... LIMIT #
to not return all rows.
REPAIR TABLE and myisamchk
to corrupt FULLTEXT indexes.
mysql grant table database. Now queries
in this database are not cached in the query cache.
mysqld_safe for some shells.
MyISAM MERGE table has more than 2 ^ 32 rows and
MySQL was not compiled with -DBIG_TABLES.
ORDER BY ... DESC problems with InnoDB tables.
GRANT/REVOKE failed if hostname was given in
non-matching case.
LOAD DATA INFILE when setting a
timestamp to a string value of '0'.
myisamchk -R mode.
mysqld to crash on REVOKE.
ORDER BY when there is a constant in the SELECT
statement.
mysqld couldn't open the
privilege tables.
SET PASSWORD FOR ... closed the connection in case of errors (bug
from 4.0.3).
max_allowed_packet in mysqld to 1 GB.
INSERT on a table with an
AUTO_INCREMENT key which was not in the first part of the key.
LOAD DATA INFILE to not recreate index if the table had
rows from before.
AES_DECRYPT() with incorrect arguments.
--skip-ssl can now be used to disable SSL in the MySQL clients,
even if one is using other SSL options in an option file or previously
on the command line.
MATCH ... AGAINST( ... IN BOOLEAN MODE)
used with ORDER BY.
LOCK TABLES and CREATE TEMPORARY TABLES privilege on
the database level. One must run the mysql_fix_privilege_tables
script on old installations to activate these.
SHOW TABLE ... STATUS, compressed tables sometimes showed up as
dynamic.
SELECT @@[global|session].var_name didn't report
global | session in the result column name.
FLUSH LOGS in a circular
replication setup created an infinite number of binary log files.
Now a rotate-binary-log command in the binary log will not cause slaves
to rotate logs.
STOP EVENT from binary log when doing FLUSH LOGS.
SHOW NEW MASTER FOR SLAVE as this needs to be
completely changed in 4.1.
UNIQUE key) appeared in ORDER BY
part of SELECT DISTINCT.
--log-binary=a.b.c now properly strips off .b.c.
FLUSH LOGS removed numerical extension for all future update logs.
GRANT ... REQUIRE didn't store the SSL information in the
mysql.user table if SSL was not enabled in the server.
GRANT ... REQUIRE NONE can now be used to remove SSL information.
AND is now optional between REQUIRE options.
REQUIRE option was not properly saved, which could cause strange
output in SHOW GRANTS.
mysqld --help reports correct values for --datadir
and --bind-address.
mysqld was started.
SHOW VARIABLES on some 64 bit systems
(like Solaris sparc).
my_getopt(); --set-variable syntax didn't work for
those options that didn't have a valid variable in the my_option struct.
This affected at least the default-table-type option.
REPAIR TABLE and
myisamchk --recover to fail on tables with duplicates in a unique
key.
CREATE TABLE table_name
SELECT expression(),...
SELECT * FROM table-list GROUP BY ... and
SELECT DISTINCT * FROM ....
--slow-log when logging an administrator command
(like FLUSH TABLES).
OPTIMIZE of locked and modified table,
reported table corruption.
my_getopt() in handling of special prefixes
(--skip-, --enable-). --skip-external-locking didn't
work and the bug may have affected other similar options.
tee option.
SELECT ... FROM many_tables .. ORDER BY key limit #
SHOW OPEN TABLES when a user didn't have access
permissions to one of the opened tables.
configure ... --localstatedir=....
mysql.server script.
mysqladmin shutdown when pid file was modified
while mysqladmin was still waiting for the previous one to
disappear. This could happen during a very quick restart and caused
mysqladmin to hang until shutdown_timeout seconds had
passed.
AUTO_INCREMENT columns to
NULL in LOAD DATA INFILE.
--lower-case-table-names,
--lower-case-table-names=1,
-O lower-case-table-names=1,
--set-variable=lower-case-table-names=1
SHOW MASTER STATUS now returns an empty set if binary log is not
enabled.
SHOW SLAVE STATUS now returns an empty set if slave is not initialised.
SELECT DISTINCT ... FROM many_tables ORDER BY
not-used-column.
BIGINT values and quoted strings.
QUOTE() function that performs SQL quoting to produce values
that can be used as data values in queries.
DELAY_KEY_WRITE to an enum to allow one set
DELAY_KEY_WRITE for all tables without taking down the server.
IF(condition,column,NULL) so that it returns
the value of the column type.
safe_mysqld a symlink to mysqld_safe in binary distribution.
user.db
table.
CREATE TABLE ... SELECT function().
mysqld now has the option --temp-pool enabled by default as this
gives better performance with some operating systems.
CHANGE MASTER TO if the slave thread died very quickly.
--code-file option is specified, the server calls
setrlimit() to set the maximum allowed core file size to unlimited,
so core files can be generated.
--count=N (-c) option to mysqladmin, to make the
program do only N iterations. To be used with --sleep (-i).
Useful in scripts.
UPDATE: when updating a table,
do_select() became confused about reading records from a cache.
UPDATE when several fields were referenced
from a single table
REVOKE that caused user resources to be randomly set.
GRANT for the new CREATE TEMPORARY TABLE privilege.
DELETE when tables are re-ordered in the
table initialisation method and ref_lengths are of different sizes.
SELECT DISTINCT with large tables.
DEFAULT with INSERT statement.
myisam_max_sort_file_size and
myisam_max_extra_sort_file_size are now given in bytes, not megabytes.
MyISAM/ISAM files is now turned
off by default. One can turn this on with --external-locking.
(For most users this is never needed).
INSERT ... SET db_name.table_name.colname=''.
DROP DATABASE
SET [GLOBAL | SESSION] syntax to change thread-specific and global
server variables at runtime.
slave_compressed_protocol.
query_cache_startup_type to query_cache_type,
myisam_bulk_insert_tree_size to bulk_insert_buffer_size,
record_buffer to read_buffer_size and
record_rnd_buffer to record_rnd_buffer_size.
--skip-locking to --skip-external-locking.
query_buffer_size.
mysql client
non-functional.
AUTO_INCREMENT support to MERGE tables.
LOG() function to accept an optional arbitrary base
parameter.
See section 6.3.3.2 Mathematical Functions.
LOG2() function (useful for finding out how many bits
a number would require for storage).
LN() natural logarithm function for compatibility with
other databases. It is synonymous with LOG(X).
NULL handling for default values in DESCRIBE
table_name.
truncate() to round up negative values to the nearest integer.
--chroot=path option to execute chroot() immediately
after all options have been parsed.
lower_case_table_names now also affects database names.
XOR operator (logical and bitwise XOR) with ^
as a synonym for bitwise XOR.
IS_FREE_LOCK("lock_name").
Based on code contributed by Hartmut Holzgraefe hartmut@six.de.
mysql_ssl_clear() from C API, as it was not needed.
DECIMAL and NUMERIC types can now read exponential numbers.
SHA1() function to calculate 160 bit hash value as described
in RFC 3174 (Secure Hash Algorithm). This function can be considered a
cryptographically more secure equivalent of MD5().
See section 6.3.6.2 Miscellaneous Functions.
AES_ENCRYPT() and AES_DECRYPT() functions to perform
encryption according to AES standard (Rijndael).
See section 6.3.6.2 Miscellaneous Functions.
--single-transaction option to mysqldump, allowing a
consistent dump of InnoDB tables.
See section 4.8.5 mysqldump, Dumping Table Structure and Data.
innodb_log_group_home_dir in SHOW VARIABLES.
FULLTEXT index is present and no tables are used.
CREATE TEMPORARY TABLES, EXECUTE,
LOCK TABLES, REPLICATION CLIENT, REPLICATION SLAVE,
SHOW DATABASES and SUPER. To use these, you must have
run the mysql_fix_privilege_tables script after upgrading.
TRUNCATE TABLE; This fixes some core
dump/hangup problems when using TRUNCATE TABLE.
DELETE when optimiser uses only indices.
ALTER TABLE table_name RENAME new_table_name is as fast
as RENAME TABLE.
GROUP BY with two or more fields, where at least one
field can contain NULL values.
Turbo Boyer-Moore algorithm to speed up LIKE "%keyword%"
searches.
DROP DATABASE with symlink.
REPAIR ... USE_FRM.
EXPLAIN with LIMIT offset != 0.
"..." in boolean full-text search.
* in boolean full-text search.
+word*s in the query).
MATCH expression that did not use an index appeared twice.
mysqldump.
ft_min_word_len characters.
--without-query-cache.
INET_NTOA() now returns NULL if you give it an argument that
is too large (greater than the value corresponding to 255.255.255.255).
SQL_CALC_FOUND_ROWS to work with UNIONs. It will work only
if the first SELECT has this option and if there is global LIMIT
for the entire statement. For the moment, this requires using parentheses for
individual SELECT queries within the statement.
SQL_CALC_FOUND_ROWS and LIMIT.
CREATE TABLE ...(... VARCHAR(0)).
SIGINT and SIGQUIT problems in `mysql.cc' on Linux
with some glibc versions.
net_store_length() linked in the CONVERT::store() method.
DOUBLE and FLOAT columns now honor the UNSIGNED flag
on storage.
InnoDB now retains foreign key constraints through ALTER TABLE
and CREATE/DROP INDEX.
InnoDB now allows foreign key constraints to be added through the
ALTER TABLE syntax.
InnoDB tables can now be set to automatically grow in size (autoextend).
--ignore-lines=n option to mysqlimport. This has the
same effect as the IGNORE n LINES clause for LOAD DATA.
UNION with last offset being transposed to total result
set.
REPAIR ... USE_FRM added.
DEFAULT_SELECT_LIMIT is always imposed on UNION
result set.
SELECT options can appear only in the first
SELECT.
LIMIT with UNION, where last select is in
the braces.
UNION operations.
SELECT with an empty
HEAP table.
ORDER BY column DESC now sorts NULL values first.
(In other words, NULL values sort first in all cases, whether or
not DESC is specified.). This is changed back in 4.0.10.
WHERE key_name='constant' ORDER BY key_name DESC.
SELECT DISTINCT ... ORDER BY DESC optimisation.
... HAVING 'GROUP_FUNCTION'(xxx) IS [NOT] NULL.
--user=# option for mysqld to be specified
as a numeric user ID.
SQL_CALC_ROWS returned an incorrect value when used
with one table and ORDER BY and with InnoDB tables.
SELECT 0 LIMIT 0 doesn't hang thread.
USE/IGNORE INDEX when using
many keys with the same start column.
BerkeleyDB and InnoDB tables when
we can use an index that covers the whole row.
InnoDB sort-buffer handling to take less memory.
DELETE and InnoDB tables.
TRUNCATE and InnoDB tables that produced the
error Can't execute the given command because you have active locked
tables or an active transaction.
NO_UNSIGNED_SUBTRACTION to the set of flags that may be
specified with the --sql-mode option for mysqld. It disables
unsigned arithmetic rules when it comes to subtraction. (This will make
MySQL 4.0 behave more like 3.23 with UNSIGNED columns).
|, <<, ...) is now of
type unsigned integer.
nan values in MyISAM to make it possible to
repair tables with nan in float or double columns.
myisamchk where it didn't correctly update number of
``parts'' in the MyISAM index file.
autoconf 2.52 (from autoconf 2.13).
const tables. This fix also
improves performance a bit when referring to another table from a
const table.
UPDATE statement.
DELETE.
SELECT CONCAT(argument_list) ... GROUP BY 1.
INSERT ... SELECT did a full rollback in case of an error. Fixed
so that we only roll back the last statement in the current transaction.
NULL.
BIT_LENGTH() function.
GROUP BY BINARY column.
NULL keys in HEAP tables.
ORDER BY in queries of type:
SELECT * FROM t WHERE key_part1=1 ORDER BY key_part1 DESC,key_part2 DESC
FLUSH QUERY CACHE.
CAST() and CONVERT() functions. The CAST and
CONVERT functions are nearly identical and mainly useful when you
want to create a column with a specific type in a CREATE ... SELECT
statement. For more information, read section 6.3.5 Cast Functions.
CREATE ... SELECT on DATE and TIME functions now
create columns of the expected type.
Null and Index_type to SHOW INDEX
output.
--no-beep and --prompt options to mysql command-line client.
GRANT ... WITH MAX_QUERIES_PER_HOUR N1
MAX_UPDATES_PER_HOUR N2
MAX_CONNECTIONS_PER_HOUR N3;
See section 4.3.6 Limiting user resources.
mysql_secure_installation to the `scripts/' directory.
system command to mysql.
HANDLER was used with some unsupported table type.
mysqldump now puts ALTER TABLE tbl_name DISABLE KEYS and
ALTER TABLE tbl_name ENABLE KEYS in the sql dump.
mysql_fix_extensions script.
LOAD DATA FROM MASTER on OSF/1.
DES_ENCRYPT() and DES_DECRYPT() functions.
FLUSH DES_KEY_FILE statement.
--des-key-file option to mysqld.
HEX(string) now returns the characters in string converted to
hexadecimal.
GRANT when using lower_case_table_names=1.
SELECT ... IN SHARE MODE to
SELECT ... LOCK IN SHARE MODE (as in MySQL 3.23).
SELECT queries.
MATCH ... AGAINST(... IN BOOLEAN MODE) can now work
without FULLTEXT index.
myisam/ft_dump utility for low-level inspection
of FULLTEXT indexes.
DELETE ... WHERE ... MATCH ....
MATCH ... AGAINST(... IN BOOLEAN MODE).
Note: you must rebuild your tables with
ALTER TABLE tablename TYPE=MyISAM to be
able to use boolean full-text search.
LOCATE() and INSTR() are now case-sensitive if either
argument is a binary string.
RAND() initialisation so that RAND(N) and
RAND(N+1) are more distinct.
UPDATE ... ORDER BY.
INSERT INTO ... SELECT always had IGNORE enabled.
Now MySQL will stop (and possibly roll back) by default in case of an error
unless you specify IGNORE.
DATA DIRECTORY and INDEX DIRECTORY directives on Windows.
MODIFY and CHANGE in ALTER TABLE to accept
the FIRST and AFTER keywords.
ORDER BY on a whole InnoDB table.
--xml option to mysql for producing XML output.
ft_min_word_len, ft_max_word_len, and
ft_max_word_len_for_sort.
libmysqld, the embedded MySQL server
library. Also added example programs (a mysql client and
mysqltest test program) which use libmysqld.
my_thread_init() and my_thread_end()
from `mysql_com.h', and added mysql_thread_init() and
mysql_thread_end() to `mysql.h'.
MyISAM to be able to handle these.
BIGINT constants now work. MIN() and MAX()
now handle signed and unsigned BIGINT numbers correctly.
latin1_de which provides correct German sorting.
STRCMP() now uses the current character set when doing comparisons,
which means that the default comparison behaviour now is case-insensitive.
TRUNCATE TABLE and DELETE FROM tbl_name are now separate
functions. One bonus is that DELETE FROM tbl_name now returns
the number of deleted rows, rather than zero.
DROP DATABASE now executes a DROP TABLE on all tables in
the database, which fixes a problem with InnoDB tables.
UNION.
DELETE operations.
HANDLER interface to MyISAM tables.
INSERT on MERGE tables. Patch from
Benjamin Pflugmann.
WEEK(#,0) to match the calendar in the USA.
COUNT(DISTINCT) is about 30% faster.
IS NULL, ISNULL() and some other internal primitives.
myisam_bulk_insert_tree_size variable.
CHAR/VARCHAR) keys is now much faster.
SELECT DISTINCT * from tbl_name ORDER by key_part1 LIMIT #.
SHOW CREATE TABLE now shows all table attributes.
ORDER BY ... DESC can now use keys.
LOAD DATA FROM MASTER ``automatically'' sets up a slave.
safe_mysqld to mysqld_safe to make this name more
in line with other MySQL scripts/commands.
MyISAM tables. Symlink handling is
now enabled by default for Windows.
SQL_CALC_FOUND_ROWS and FOUND_ROWS(). This makes it
possible to know how many rows a query would have returned
without a LIMIT clause.
SHOW OPEN TABLES.
SELECT expression LIMIT ....
IDENTITY as a synonym for AUTO_INCREMENT (like Sybase).
ORDER BY syntax to UPDATE and DELETE.
SHOW INDEXES is now a synonym for SHOW INDEX.
ALTER TABLE tbl_name DISABLE KEYS and
ALTER TABLE tbl_name ENABLE KEYS commands.
IN as a synonym for FROM in SHOW commands.
FULLTEXT indexes.
REPAIR TABLE, ALTER TABLE, and OPTIMIZE TABLE
for tables with FULLTEXT indexes are now up to 100 times faster.
X'hexadecimal-number'.
FLUSH TABLES WITH READ LOCK.
DATETIME = constant in WHERE optimisation.
--master-data and --no-autocommit options to
mysqldump. (Thanks to Brian Aker for this.)
mysql_explain_log.sh to distribution.
(Thanks to mobile.de).
Please note that since release 4.0 is now production level, only critical fixes are done in the 3.23 release series. You are recommended to upgrade when possible, to take advantage of all speed and feature improvements in 4.0. See section 2.5.2 Upgrading From Version 3.23 to 4.0.
The 3.23 release has several major features that are not present in previous versions. We have added three new table types:
MyISAM
InnoDB
BerkeleyDB or BDB
Note that only MyISAM is available in the standard binary distribution.
The 3.23 release also includes support for database replication between a master and many slaves, full-text indexing, and much more.
All new features are being developed in the 4.x version. Only bug fixes and minor enhancements to existing features will be added to 3.23.
The replication code and BerkeleyDB code is still not as tested and as the rest of the code, so we will probably need to do a couple of future releases of 3.23 with small fixes for this part of the code. As long as you don't use these features, you should be quite safe with MySQL 3.23!
Note that the above doesn't mean that replication or Berkeley DB don't
work. We have done a lot of testing of all code, including replication
and BDB without finding any problems. It only means that not as many
users use this code as the rest of the code and because of this we are
not yet 100% confident in this code.
Unknown error when doing ORDER BY on reference table which
was used with NULL value on NOT NULL column. (Bug #479)
mysqld.
kill pid-of-mysqld to work on Mac OS X.
SHOW TABLE STATUS displayed incorrect Row_format value for
tables that have been compressed with myisampack.
(Bug #427)
SHOW VARIABLES LIKE 'innodb_data_file_path' displayed only the
name of the first datafile (bug #468).
mysqld didn't allow one to UPDATE
rows in a table even if one had a global UPDATE privilege and a
database SELECT privilege.
SELECT and wildcarded select list,
when user only had partial column SELECT privileges on the table.
WHERE clause with a constant
expression such as in WHERE 1 AND (a=1 AND b=1).
mysqlbinlog to fail.
innodb_flush_log_at_trx_commit was changed
from 0 to 1 to make InnoDB tables ACID by default.
See section 7.5.3 InnoDB Startup Options.
LOAD DATA INFILE IGNORE: When reading
the binary log, mysqlbinlog and the replication code read REPLACE
instead of IGNORE. This could make the slave's table
become different from the master's table. (Bug #218)
MyISAM when a row is inserted into a table with a
large number of NULL columns. Bug was caused by incorrect calculation
of the record length, as the space required for storage of NULL
bits was not added to the total record length.
TRUNCATE table_name or
DELETE FROM table_name statements, which could cause an INSERT to
table_name to be written to the log before the
TRUNCATE or DELETE statements.
UPDATE of InnoDB tables where one row could be
updated multiple times.
PROCEDURE ANALYSE() to report DATE instead of
NEWDATE.
PROCEDURE ANALYSE(#) to restrict the number of values in an
ENUM column to # also for string values.
mysqldump no longer silently deletes the binary logs when invoked with
the --master-data or --first-slave option;
while this behaviour was convenient for some
users, others may suffer from it. Now one has to explicitly ask for binary
logs to be deleted by using the new --delete-master-logs option.
mysqldump when it was invoked with the
--master-data option: The CHANGE MASTER TO statements that were
appended to the SQL dump had incorrect coordinates. (Bug #159)
mysqld crash on extremely small values of
sort_buffer variable.
GRANT UPDATE on column level.
HAVING with GROUP BY.
WHERE clause. (Bug #142)
myisamchk) crash on artificially
corrupted `.MYI' files.
mysqld no longer reads options from world-writeable config files.
mysqld and safe_mysqld now only use the first --user
option specified on the command line. (Normally this comes from
`/etc/my.cnf')
BACKUP TABLE to overwrite existing files.
LOCK TABLE and
another thread did a DROP TABLE. In this case one could do
a KILL on one of the threads to resolve the deadlock.
LOAD DATA INFILE was not replicated by slave if
replicate_*_table was set on the slave.
CHAR(0) columns that could cause wrong
results from the query.
SHOW VARIABLES on 64-bit platforms. The bug was
caused by wrong declaration of variable server_id.
SHOW TABLE STATUS now reports that it can
contain NULL values (which is the case for a crashed `.frm' file).
rpl_rotate_logs test to not fail on certain platforms (e.g.
Mac OS X) due to a too long file name (changed slave-master-info.opt
to .slave-mi).
BLOB NOT NULL columns used with IS NULL.
MAX() optimisation in MERGE tables.
RAND() initialisation for new connections.
poll() system call, which resulted in timeout the value specified as
it was executed in both select() and poll().
SELECT * FROM table WHERE datetime1 IS NULL OR datetime2 IS NULL.
INTERVAL,
CASE, FIELD, CONCAT_WS, ELT and
MAKE_SET functions.
--lower-case-table-names (default on Windows)
and you had tables or databases with mixed case on disk, then
executing SHOW TABLE STATUS followed with DROP DATABASE
or DROP TABLE could fail with Errcode 13.
NULL in an auto_increment field and also
uses LAST_INSERT_ID().
mysqladmin --relative.
show status reported a strange number for
Open_files and Open_streams.
free'd pointer bug in mysql_change_user()
handling, that enabled a specially hacked version of MySQL client
to crash mysqld. Note, that one needs to login to the server
by using a valid user account to be able to exploit this bug.
--slow-log when logging an administrator command
(like FLUSH TABLES).
GROUP BY when used on BLOB column with NULL values.
NULL values in CASE ... WHEN ....
--chroot (see section D.4.5 Changes in release 3.23.54 (05 Dec 2002))
is reverted. Unfortunately, there is no way to make it to work, without
introducing backward-incompatible changes in `my.cnf'.
Those who need --chroot functionality, should upgrade to MySQL 4.0.
(The fix in the 4.0 branch did not break backward-compatibility).
--lower-case-table-names default on Mac OS X as the default
file system (HFS+) is case insensitive.
NOHUP_NICENESS
testing.
AUTOCOMMIT=0 mode didn't rotate binary log.
scripts/make_binary_distribution that resulted in
a remaining @HOSTNAME@ variable instead of replacing it with the
correct path to the hostname binary.
SHOW PROCESSLIST to core
dump in pthread_mutex_unlock() if a new thread was connecting.
SLAVE STOP if the thread executing the query has locked
tables. This removes a possible deadlock situation.
mysqld
with a specially crafted packet.
free'd pointer) when altering a
temporary table.
libmysqlclient library
that allowed malicious MySQL server to crash the client
application.
mysql_change_user() handling.
All users are strongly recommended to upgrade to the version 3.23.54.
--chroot command-line option of mysqld
from working.
OPTIMIZE TABLE to corrupt the table
under some rare circumstances.
mysqlcheck so it can deal with table names containing dashes.
NULL field with <=> NULL.
IGNORE INDEX and USE INDEX sometimes
to be ignored.
GROUP BY queries that
didn't return any result.
MATCH ... AGAINST () >=0 was treated as if it was
>.
SHOW PROCESSLIST when running with an
active slave (unlikely timing bug).
TEMPORARY MERGE tables now.
--core-file works on Linux (at least on kernel 2.4.18).
BDB and ALTER TABLE.
GROUP BY
... ORDER BY queries. Symptom was that mysqld died in function
send_fields.
BLOB values in internal
temporary tables used for some (unlikely) GROUP BY queries.
WHERE column_name = key_column_name was calculated as true
for NULL values.
LEFT JOIN ... WHERE key_column=NULL.
MyISAM crash when using dynamic-row tables with huge numbers of
packed fields.
automake 1.5 and
libtool 1.4.
SHOW INNODB STATUS was used and skip-innodb
was defined.
LOCK TABLES on Windows when one connects to a
database that contains upper case letters.
--skip-show-databases doesn't reset the --port option.
safe_mysqld for some shells.
FLUSH STATUS doesn't reset delayed_insert_threads.
BINARY cast on a NULL value.
GRANT at the same time a new
user logged in or did a USE DATABASE.
ALTER TABLE and RENAME TABLE when running with
-O lower_case_table_names=1 (typically on Windows) when giving the
table name in uppercase.
-O lower_case_table_names=1 also converts database
names to lower case.
SELECT ... ORDER BY ... LIMIT.
AND/OR to report that they can return NULL. This fixes a
bug in GROUP BY on AND/OR expressions that return
NULL.
OPTIMIZE of locked and modified MyISAM table,
reported table corruption.
BDB-related ALTER TABLE bug with dropping a column
and shutting down immediately thereafter.
configure ... --localstatedir=....
UNSIGNED BIGINT on AIX (again).
BEGIN/COMMIT around transaction in the binary log.
This makes replication honour transactions.
user.db
table.
RND() to make it less predicatable.
GROUP BY on result with expression that created a
BLOB field.
GROUP BY on columns that have NULL values.
To solve this we now create an MyISAM temporary table when doing a
GROUP BY on a possible NULL item.
From MySQL 4.0.5 we can use in memory HEAP tables for this case.
SLAVE START, SLAVE STOP and automatic repair
of MyISAM tables that could cause table cache to be corrupted.
OPTIMIZE TABLE and REPAIR TABLE.
UNIQUE() key where first part could contain NULL values.
MERGE tables and MAX() function.
ALTER TABLE with BDB tables.
LOAD DATA INFILE to binary log with no
active database.
DROP DATABASE on a
database with InnoDB tables.
mysql_info() returns 0 for 'Duplicates' when using
INSERT DELAYED IGNORE.
-DHAVE_BROKEN_REALPATH to the Mac OS X (darwin) compile
options in `configure.in' to fix a failure under high load.
mysqldump XML output.
ENUM values. (This fixed a problem with
SHOW CREATE TABLE.)
CONCAT_WS() that cut the result.
Com_show_master_stat to
Com_show_master_status and Com_show_slave_stat to
Com_show_slave_status.
gethostbyname() to make the client library
thread-safe even if gethostbyname_r doesn't exist.
GRANT.
DROP DATABASE with symlinked directory.
DATETIME and value outside
DATETIME range.
BDB doc files from the source tree, as they're not
needed (MySQL covers BDB in its own documentation).
glibc 2.2 (needed for
make dist).
FLOAT(X+1,X) is not converted to FLOAT(X+2,X).
(This also affected DECIMAL, DOUBLE and REAL types)
IF() is case in-sensitive if the second and
third arguments are case sensitive.
gethostbyname_r.
'+11111' for
DECIMAL(5,0) UNSIGNED columns, we will just drop the sign.
ISNULL(expression_which_cannot_be_null) and
ISNULL(constant_expression).
glibc library that we used with the 3.23.50
Linux-x86 binaries.
<row> tags for mysqldump XML output.
crash-me and gcc 3.0.4.
@@unknown_variable doesn't hang server.
@@VERSION as a synonym for VERSION().
SHOW VARIABLES LIKE 'xxx' is now case-insensitive.
GET_LOCK() on HP-UX with DCE threads.
SIGINT and SIGQUIT problems in mysql.
InnoDB now retains foreign key constraints through ALTER TABLE
and CREATE/DROP INDEX.
InnoDB now allows foreign key constraints to be added through the
ALTER TABLE syntax.
InnoDB tables can now be set to automatically grow in size (autoextend).
gcc 3.0.4, which
should make them a bit faster.
--enable-named-pipe.
WHERE key_column = 'J' or key_column='j'.
--log-bin with LOAD DATA
INFILE without an active database.
RENAME TABLE when used with
lower_case_table_names=1 (default on Windows).
DROP TABLE on a table
that was in use by a thread that also used queries on only temporary tables.
SHOW CREATE TABLE and PRIMARY KEY when using
32 indexes.
SET PASSWORD for the anonymous user.
mysql_options().
--enable-local-infile.
bison.
DATE_FORMAT() returned empty string when used
with GROUP BY.
mysqldump --disable-keys to work.
NULL.
LOAD DATA LOCAL INFILE more secure.
glibc library,
which has serious problems under high load and Red Hat 7.2. The 3.23.49 binary
release doesn't have this problem.
--xml option to mysqldump for producing XML output.
autoconf 2.52 (from autoconf 2.13)
const tables.
InnoDB.
InnoDB variables were always shown in SHOW VARIABLES as
OFF on high-byte-first systems (like SPARC).
InnoDB table and another
thread doing an ALTER TABLE on the same table. Before that,
mysqld could crash with an assertion failure in `row0row.c',
line 474.
InnoDB SQL optimiser to favor index searches more often
over table scans.
InnoDB tables when several large
SELECT queries are run concurrently on a multiprocessor Linux
computer. Large CPU-bound SELECT queries will now also generally
run faster on all platforms.
InnoDB now prints after crash recovery the
latest MySQL binlog name and the offset InnoDB was able to recover
to. This is useful, for example, when resynchronising a master and a
slave database in replication.
InnoDB
tables.
InnoDB tablespace.
InnoDB now prevents a FOREIGN KEY declaration where the
signedness is not the same in the referencing and referenced integer columns.
SHOW CREATE TABLE or SHOW TABLE STATUS could cause
memory corruption and make mysqld crash. Especially at risk was
mysqldump, because it frequently calls SHOW CREATE TABLE.
AUTO_INCREMENT column were
wrapped inside one LOCK TABLES, InnoDB asserted in
`lock0lock.c'.
NULL values in a UNIQUE secondary
index for an InnoDB table. But CHECK TABLE was not relaxed: it
reports the table as corrupt. CHECK TABLE no longer complains in
this situation.
SHOW GRANTS now shows REFERENCES instead of REFERENCE.
SELECT ... WHERE key=@var_name OR key=@var_name2
InnoDB keys to 500 bytes.
InnoDB now supports NULL in keys.
SELECT RELEASE_LOCK().
DO expression,[expression]
slave-skip-errors option.
SHOW STATUS is
now much longer.)
InnoDB tables.
GROUP BY expr DESC works.
t1 LEFT JOIN t2 ON t2.key=constant.
mysql_config now also works with binary (relocated) distributions.
InnoDB and BDB tables will now use index when doing an
ORDER BY on the whole table.
BDB tables.
ANALYZE, REPAIR, and OPTIMIZE TABLE when
the thread is waiting to get a lock on the table.
ANALYZE TABLE.
INSERT DELAYED
which could cause the binary log to have rows that were not yet written
to MyISAM tables.
(UPDATE|DELETE) ...WHERE MATCH bugfix.
MyISAM files.
--core-file now works on Solaris.
InnoDB to complain if it cannot find
free blocks from the buffer cache during recovery.
InnoDB insert buffer B-tree handling that could cause
crashes.
InnoDB lock timeout handling.
ALTER TABLE on a TEMPORARY InnoDB
table.
OPTIMIZE TABLE that reset index cardinality if it
was up to date.
t1 LEFT_JOIN t2 ... WHERE t2.date_column IS NULL when
date_column was declared as NOT NULL.
BDB tables and keys on BLOB columns.
MERGE tables on OS with 32-bit file pointers.
TIME_TO_SEC() when using negative values.
Rows_examined count in slow query log.
AVG() column in HAVING.
DAYOFYEAR(column), will return NULL for 0000-00-00 dates.
SELECT * FROM date_col="2001-01-01" and date_col=time_col)
Can't write, because of unique
constraint with some GROUP BY queries.
sjis character strings used within quoted table
names.
CREATE ... FULLTEXT keys with other
storage engines than MyISAM.
signal() on Windows because this appears to not be
100% reliable.
WHERE col_name=NULL on an indexed column
that had NULL values.
LEFT JOIN ... ON (col_name = constant) WHERE col_name = constant.
% could cause
a core dump.
TCP_NODELAY was not used on some systems. (Speed problem.)
The following changes are for InnoDB tables:
InnoDB variables to SHOW VARIABLES.
InnoDB tables.
DROP DATABASE now works also for InnoDB tables.
InnoDB now supports datafiles and raw disk partitions bigger
than 4 GB on those operating systems that have big files.
InnoDB calculates better table cardinality estimates for the
MySQL optimiser.
latin1 are ordered
according to the MySQL ordering.
Note: if you are using latin1 and have inserted characters whose
code is greater than 127 into an indexed CHAR column, you should
run CHECK TABLE on your table when you upgrade to 3.23.44, and
drop and reimport the table if CHECK TABLE reports an error!
innodb_thread_concurrency, helps in
performance tuning in heavily concurrent environments.
innodb_fast_shutdown, speeds up
server shutdown.
innodb_force_recovery, helps to save
your data in case the disk image of the database becomes corrupt.
innodb_monitor has been improved and a new
innodb_table_monitor added.
AUTO_INCREMENT columns with
multiple-line inserts.
MAX(col) is selected from an empty table, and
col is not the first column in a multi-column index.