This appendix lists the changes from version to version in the MySQL source code.
We are working actively on MySQL 4.1 and 5.0, and 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.
InnoDB Change History
MySQL Cluster Change History
The following changelog shows what has 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 is
translated to --log-bin (or ignored if the server is explicitly
started with --log-bin), and a warning message is written to
the error log. Setting SQL_LOG_UPDATE silently sets
SQL_LOG_BIN instead (or do nothing if the server is explicitly
started with --log-bin).
SET
@a=10; then SELECT @A; now returns 10.
Case sensitivity of a variable's value depends on the collation of the value.
VARCHAR fields remembers end space. A VARCHAR() field can
contain up to 65535 bytes.
MEMORY (HEAP) tables can have VARCHAR() fields.
CREATE ... SELECT, MySQL 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:
Bugs fixed:
Functionality added or changed:
Ready for connections message
that is written to the error log at server startup. (Bug #8444)
SQL_NOTES session variable to cause Note-level warnings
not to be recorded. (Bug #6662)
--defaults-file following the service name. This is
for compatibility with MySQL 4.1. (Bug #7856)
InnoDB: Commit after every 10,000 copied rows when executing
ALTER TABLE, CREATE INDEX or DROP INDEX. This makes it
much faster to recover from an aborted operation.
VAR_POP() and STDDEV_POP() as standard SQL aliases for
the VARIANCE() and STDDEV() functions that compute population
variance and standard deviation. Added new VAR_SAMP() and
STDDEV_SAMP() functions to compute sample variance and standard
deviation. (Bug #3190)
ERROR after
OK or EOF) following a KILL QUERY statement.
(Bug #6804)
SELECT that mixed UNION ALL
and UNION DISTINCT resulted in a different result than retrieving from
the original SELECT. (Bug #6565)
index_merge query execution plans
being chosen on IRIX. (Bug #8578)
BIT in column definitions now is a distinct data type; it no longer is
treated as a synonym for TINYINT(1).
b'value' notation.
value is a binary value written using 0s and 1s.
mysql_library_init() and mysql_library_end() as
synonyms for the mysql_server_init() and mysql_server_end()
C API functions. mysql_library_init() and mysql_library_end()
are #define symbols, but the names more clearly indicate that they
should be called when beginning and ending use of a MySQL C API
library no matter whether the application uses libmysqlclient
or libmysqld. (Bug #6149)
SHOW COLUMNS now displays NO rather than blank in the
Null output column if the corresponding table column cannot be
NULL.
mysql from
<col_name>col_value</col_name> to
<field name="col_name">col_value</field> to allow for
proper encoding of column names that are not legal as element names.
(Bug #7811)
--innodb-checksums and --innodb-doublewrite options for
mysqld.
--large-pages option for mysqld.
multi_read_range system variable.
SHOW DATABASES, SHOW TABLES, SHOW COLUMNS, and so forth
display information about the INFORMATION_SCHEMA database. Also,
several SHOW statements now accept a WHERE clause specifying
which output rows to display.
See section 21 The INFORMATION_SCHEMA Information Database.
CREATE ROUTINE and ALTER ROUTINE privileges, and made
the EXECUTE privilege operational.
ROW_FORMAT=COMPACT
tables that caused corruption. (Bug #7973) There may still be bugs in the
crash recovery, especially in COMPACT tables.
MyISAM storage engine detects corruption of a MyISAM
table, a message describing the problem now is written to the error log.
fcntl() file flush method on Mac OS X versions 10.3 and later.
Apple had disabled fsync() in Mac OS X for internal disk
drives, which caused corruption at power outages.
TRUNCATE TABLE. The old approach
(deleting rows one by one) may be used if the table is being
referenced by foreign keys. (Bug #7150)
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)
TRUNCATE TABLE. One visible change from this
is that auto-increment values for this table are reset on TRUNCATE.
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()
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.
MEMORY (HEAP) can have VARCHAR() fields.
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 is 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 is zero if the SQL thread has caught up to the 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 providing a name for the binary log
index file, a warning is issued because MySQL falls back to
using the hostname for that name, and this is prone to replication issues if
the server's hostname's gets changed later. See section 1.5.7.3 Open Bugs and Design Deficiencies in MySQL.
MAX_USER_CONNECTIONS limit, which allows you to
specify the maximum number of concurrent connections for the account. Also,
all limited resources now are counted per account (instead of being counted per
user + host pair as it was before). Use the --old-style-user-limits
option to get the old behavior.
LOCK_REC_NOT_GAP) is now taken for a
matching record in the foreign key check because inserts can be
allowed into gaps.
InnoDB uses consistent read in these cases for a selected table.
Bugs fixed:
LEFT OUTER JOIN between an empty base table and a view on an empty
base table caused a server crash. (Bug #7433)
GROUP_CONCAT() in the select list when selecting from a view
caused a server crash. (Bug #7116)
HAVING but no
GROUP BY caused a server crash. (Bug #6894)
mysql_list_fields() of references to stored functions
within views was incorrect and could result in a server crash. (Bug #6814)
mysqldump now avoids writing SET NAMES to the dump output
if the server is older than version 4.1 and would not understand that
statement. (Bug #7997)
EXISTS
or NOT EXISTS subquery. Selecting columns by name caused a server
crash. With SELECT *, a crash did not occur, but columns in outer
query were not resolved properly. (Bug #6394)
CHAR() function was not ignoring NULL arguments, contrary
to the documentation. (Bug #6317)
character_set_client resulted in the character being stripped from the
name and no error. The character now is considered an error. (Bug #8041)
utf8_general_ci collation. (Bug #8385)
INFORMATION_SCHEMA columns that contained catalog identifiers
were of type LONGTEXT. These were changed to VARCHAR(N,
where N is the appropriate maximum identifier length. (Bug #7215)
INFORMATION_SCHEMA columns that contained timestamp values
were of type VARBINARY. These were changed to TIMESTAMP.
(Bug #7217)
WHERE
city='London' AND city='london' could fail.)
(Bug #7098, Bug #8690)
STATUS (\s) command in mysql had
the values for the server and client character sets reversed. (Bug #7571)
--replicate-*-table options which
excluded one temporary table and included another, and the two tables were used
in a single DROP TEMPORARY TABLE IF EXISTS statement, as the ones the
master automatically writes to its binlog upon client's disconnection when
client has not explicitely dropped these, the slave could forget to delete the
included replicated temporary table. Only slave needs being upgraded.
(Bug #8055)
SET
VARIABLES, the value was treated as a positive value modulo 2^32.
(Bug #6958)
DUAL where statements such as
SELECT 1 AS a FROM DUAL would succeed but statements such as
SELECT 1 AS a FROM DUAL LIMIT 1 would fail. (Bug #8023)
DELETE FROM tbl_name ...
WHERE ... ORDER BY tbl_name.col_name when the ORDER BY
column was qualified with the table name. (Bug #8392)
MATCH ... AGAINST in natural language mode that could
cause a server crash if the FULLTEXT index was not used in a join
(EXPLAIN did not show fulltext join mode) and the search query
matched no rows in the table (Bug #8522).
InnoDB: Honor the --tmpdir startup option when creating
temporary files. Previously, InnoDB temporary files were always
created in the temporary directory of the operating system. On Netware,
InnoDB will continue to ignore --tmpdir. (Bug #5822)
--version option on Windows was always Win95/Win98 (i32).
More accurately determine platform as Win32 or Win64 for 32-bit
or 64-bit Windows, and architecture as ia32 for x86, ia64 for
Itanium, and axp for Alpha. (Bug #4445)
LOAD INDEX statement to actually load index in memory. (Bug #8452)
--replicate-*-table options had been specified.
(Bug #7011)
CREATE TABLE ... LIKE Windows when the source or
destination table was located in a symlinked database directory. (Bug #6607)
lower_case_table_names set to 1, mysqldump on Windows
could write the same table name in different lettercase for different SQL
statements. Fixed so that consistent lettercase is used. (Bug #5185)
mysqld_safe now understands the --help option.
Previously, it ignored the option and attempted to start the server
anyway. (Bug #7931)
NO_BACKSLASH_ESCAPES SQL mode for strings that
contained both the string quoting character and backslash. (Bug #6368)
SUM(DISTINCT...).
OUTER JOIN.
CONV() function returning unsigned BIGINT
number (third argument is positive, and return value does not fit in
32 bits). (Bug #7751)
IN() operator to return correct result if all
values in the list were constants and some of them were using substring
functions, for example, LEFT(), RIGHT(), or MID().
(Bug #7716)
CONVERT_TZ() function when its second or third argument
was from a const table (see section 7.2.1 EXPLAIN Syntax (Get Information About a SELECT)). (Bug #7705)
SELECT DISTINCT combined with a
subquery and GROUP BY. (Bug #7946)
row(1,(2,3))) with a subquery. (Bug #8022)
ALTER TABLE improperly would accept an index on a
TIMESTAMP column that CREATE TABLE would reject. (Bug #7884)
SHOW CREATE TABLE now reports ENGINE=MEMORY rather than
ENGINE=HEAP for a MEMORY table (unless the MYSQL323 SQL
mode is enabled). (Bug #6659)
GROUP_CONCAT() with HAVING
caused a server crash. (Bug #7769)
-not and trunc* operators of
full-text search. Using more than one truncated negative search term, was
causing empty result set.
ucs2
character set. (Bug #7350, Bug #8771)
tmpfile() function on Netware. All InnoDB
temporary files are created under sys:\tmp. Previously,
InnoDB temporary files were never deleted on Netware.
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)
mysqlbinlog forgot to add backquotes around the collation of user
variables (causing later parsing problems as BINARY is a reserved
word). (Bug #7793)
mysqldump --single-transaction sets its transaction
isolation level to REPEATABLE READ before proceeding (otherwise if the
MySQL server was configured to run with a default isolation level lower than
REPEATABLE READ it could give an inconsistent dump). (Bug #7850)
RPAD() function (or any function adding spaces
to the right) in a query that had to be resolved by using a temporary table,
all resulting strings had rightmost spaces removed (i.e. RPAD() did not
work) (Bug #4048)
SELECT
@@unknown_var hangs them -- which was fixed in MySQL 3.23.50). (Bug #7965)
InnoDB now takes an exclusive lock when INSERT
ON DUPLICATE KEY UPDATE is checking duplicate keys.
FLUSH TABLES WITH READ
LOCK hanging. (Bug #8682)
Functionality added or changed:
INFORMATION_SCHEMA ``information database'' that
provides database metadata.
See section 21 The INFORMATION_SCHEMA Information Database.
HAVING clause in a SELECT statement now can refer to columns
in the GROUP BY clause, as required by standard SQL.
CREATE USER and RENAME USER statements.
DROP USER so that it drops the account, including all its
privileges. Formerly, it removed the account record only for an account that
had had all privileges revoked.
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 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
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 later redoes 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)
mysqlbinlog from being able to read
from stdin, for example, when piping the output from zcat to
mysqlbinlog. (Bug #7853)
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:
--local-service option following the service
name to cause the server to run using the LocalService Windows
account that has limited privileges. This is in addition to the
--defaults-file option that also can be given following the service
name.
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 22.2.3.54 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 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 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:
SQL_NOTES session variable to cause Note-level warnings
not to be recorded. (Bug #6662)
InnoDB: Commit after every 10,000 copied rows when executing
CREATE INDEX or DROP INDEX, which are internally implemented
as ALTER TABLE. This makes it much faster to recover from an
aborted operation.
Bugs fixed:
mysql_real_escape_string() C API function, when a multi-byte
character is encountered that is illegal in the current character set,
escape only the first byte, not each byte. This avoids creating a valid
character from an invalid one. (Bug #8378)
cp1250_czech_cs collation that caused empty
literal strings not to compare equal to empty character columns. (Bug #8840)
USE INDEX or
FORCE INDEX hint not to be used properly for a LEFT JOIN across
indexed BLOB columns. (Bug #7520)
MAX(datetime_col) was returned as
VARCHAR rather than DATETIME if the query included a
GROUP BY clause. (Bug #5615)
FOUND_ROWS() returned an incorrect value for preceding SELECT
statements that used no table or view. (Bug #6089)
InnoDB: Do not try to space-pad BLOB columns containing
ucs2 characters. This avoids an assertion failure that was
introduced when fixing Bug #7350. (Bug #8771)
mysqldump now avoids writing SET NAMES to the dump output
if the server is older than version 4.1 and would not understand that
statement. (Bug #7997)
my_print_defaults that made it ignore the
--defaults-extra-file and --defaults-file options.
SELECT that mixed UNION ALL
and UNION DISTINCT resulted in a different result than retrieving from
the original SELECT. (Bug #6565)
glibc when static
linking is used and LDAP is one of the NSS sources. The workaround is to
detect when the bug causes a segfault and issue a diagnostic message with
information about the problem. (Bug #3037, Bug #4872)
mysql prompt was configured to display the default
database name, and that database was dropped, mysql did not
update the prompt. (Bug #4802)
perror was printing a spurious extra line of output
("Error code ###: Unknown error ###" printed directly before the
correct line with the error message). (Bug #8517)
CHAR() function was not ignoring NULL arguments, contrary
to the documentation. (Bug #6317)
SHOW ERRORS nor SHOW WARNINGS were displaying
Error-level messages. (Bug #6572)
character_set_client resulted in the character being stripped from the
name and no error. The character now is considered an error. (Bug #8041)
utf8_general_ci collation. (Bug #8385)
MAX_CONNECTIONS_PER_HOUR resource limit was not being reset hourly
and thus imposed an absolute limit on number of connections per account until
the server is restarted or the limits flushed. (Bug #8350)
lower_case_table_names=2, tables in
the database also were dropped but not being flushed properly from the table
cache. If the database was recreated, the tables also would appear to have
been recreated. (Bug #8355)
mysql_server_end() C API function to restore more variables
to their initial state so that a subsequent call to
mysql_server_init() would not cause a client program crash.
(Bug #7344)
utf_general_ci collation. (Bug #7878)
ENUM and SET columns in privilege tables incorrectly had a
case-sensitive collation, resulting in failure of assignments of values that
did not have the same lettercase as given in the column definitions.
The collation was changed to be case insensitive. (Bug #7617)
WHERE
city='London' AND city='london' could fail.)
(Bug #7098, Bug #8690)
STATUS (\s) command in mysql had
the values for the server and client character sets reversed. (Bug #7571)
--replicate-*-table options which
excluded one temporary table and included another, and the two tables were used
in a single DROP TEMPORARY TABLE IF EXISTS statement, as the ones the
master automatically writes to its binlog upon client's disconnection when
client has not explicitely dropped these, the slave could forget to delete the
included replicated temporary table. Only slave needs being upgraded.
(Bug #8055)
IMPLICIT derivation (coercibility) to
avoid ``Illegal mix of collations'' errors when replicating user variables.
(Bug #6676)
SET
VARIABLES, the value was treated as a positive value modulo 2^32.
(Bug #6958)
readline library that caused segmentation fault
in mysql when user entered Shift+Enter. (Bug #5672)
123.45E+02 (Bug #7840).
MATCH ... AGAINST in natural language mode that could
cause a server crash if the FULLTEXT index was not used in a join
(EXPLAIN did not show fulltext join mode) and the search query
matched no rows in the table (Bug #8522).
--version option on Windows was always Win95/Win98 (i32).
More accurately determine platform as Win32 or Win64 for 32-bit
or 64-bit Windows, and architecture as ia32 for x86, ia64 for
Itanium, and axp for Alpha. (Bug #4445)
InnoDB: Honor the --tmpdir startup option when creating
temporary files. Previously, InnoDB temporary files were always
created in the temporary directory of the operating system. On Netware,
InnoDB will continue to ignore --tmpdir. (Bug #5822)
FLUSH TABLES WITH READ
LOCK hanging. (Bug #8682)
Functionality added or changed:
mysql_library_init() and mysql_library_end() as
synonyms for the mysql_server_init() and mysql_server_end()
C API functions. mysql_library_init() and mysql_library_end()
are #define symbols, but the names more clearly indicate that they
should be called when beginning and ending use of a MySQL C API
library no matter whether the application uses libmysqlclient
or libmysqld. (Bug #6149)
lower_case_table_names=2 and the
data directory is on a case-sensitive filesystem, just as when
lower_case_table_names=0 on a case-insensitive filesystem.
(Bug #7887)
fcntl() file flush method on Mac OS X versions 10.3 and later.
Apple had disabled fsync() in Mac OS X for internal disk
drives, which caused corruption at power outages.
LOCK_REC_NOT_GAP) is now taken for a
matching record in the foreign key check because inserts can be
allowed into gaps.
InnoDB uses consistent read in these cases for a selected table.
Bugs fixed:
HASH indexes of
TEMPORARY tables created using MEMORY storage engine.
As a result queries that were using this index (as shown by EXPLAIN)
could have returned incorrect results. (Bug #8371)
DUAL where statements such as
SELECT 1 AS a FROM DUAL would succeed but statements such as
SELECT 1 AS a FROM DUAL LIMIT 1 would fail. (Bug #8023)
DELETE FROM tbl_name ...
WHERE ... ORDER BY tbl_name.col_name when the ORDER BY
column was qualified with the table name. (Bug #8392)
mysqld had problems finding its language files if the basedir
value was specified as a very long pathname. (Bug #8015)
binlog-do-db or binlog-ignore-db options even when there was
no current database, contrary to section 5.9.4 The Binary Log. (Bug #6749)
LOAD INDEX statement to actually load index in memory. (Bug #8452)
YEAR column were being stored as
2000 rather than as 0000. (Bug #6067)
--replicate-*-table options had been specified.
(Bug #7011)
mysql_stmt_close() C API function was not clearing an error indicator
when a previous prepare call failed, causing subsequent invocations of
error-retrieving calls to indicate spurious error values. (Bug #7990)
CREATE TABLE ... LIKE Windows when the source or
destination table was located in a symlinked database directory. (Bug #6607)
lower_case_table_names set to 1, mysqldump on Windows
could write the same table name in different lettercase for different SQL
statements. Fixed so that consistent lettercase is used. (Bug #5185)
HAVING that referred to RAND() or a user-defined function in
the SELECT part through an alias could cause a crash or wrong value.
(Bug #8216)
CONVERT_TZ() function in SELECT, which in its turn
was used in CREATE TABLE statements, then system time
zone tables were added to list of tables joined in SELECT and thus erroneous
result was produced. (Bug #7899)
CONV() function returning unsigned BIGINT
number (third argument is positive, and return value does not fit in
32 bits). (Bug #7751)
IN() operator to return correct result if all
values in the list were constants and some of them were using substring
functions, for example, LEFT(), RIGHT(), or MID().
SHOW INDEX reporting Sub_part values in bytes
rather than characters for columns with a multi-byte character set.
(Bug #7943)
CONVERT_TZ() function when its second or third argument
was from a const table (see section 7.2.1 EXPLAIN Syntax (Get Information About a SELECT)). (Bug #7705)
mysql_config, which was failing to produce
proper zlib option for linking under some circumstances. (Bug #6273)
SELECT DISTINCT combined with a
subquery and GROUP BY. (Bug #7946)
row(1,(2,3))) with a subquery. (Bug #8022)
--default-character-set option. (Bug #8159)
CHAR(31)
were considered equal to the empty string. (Bug #8134)
debug command to mysqladmin help output.
(Bug #8207)
perror.exe was always returning ``Unknown error'' on Windows.
See section 8.12 perror, Explaining Error Codes.
(Bug #7390)
SET statements produced by mysqldump to write quoted
strings using single quotes rather than double quotes. This avoids problems if
the dump file is reloaded while the ANSI_QUOTES SQL mode is in effect.
(Bug #8148)
ALTER TABLE improperly would accept an index on a
TIMESTAMP column that CREATE TABLE would reject. (Bug #7884)
UPDATE statements that
could cause spurious Table '#sql_....' is full errors if the number
of rows to update is big enough. (Bug #7788)
SHOW INDEX on a MERGE table could crash a
debugging version of the server. (Bug #7377)
ORDER BY clause for an indexed column
would cause a SELECT to return an empty result. (Bug #7331)
ALTER TABLE on a TEMPORARY table with a
mixed-lettercase name could cause the table to disappear when
lower_case_table_names was set to 2. (Bug #7261)
SIGHUP signal. (Bug #4285)
mysql_stmt_prepare() to be very slow when
used in client programs on Windows. (Bug #5787)
SHOW CREATE TABLE now displays the index type even if it
is the default, for storage engines that support multiple index types.
(Bug #7235)
GROUP_CONCAT() with HAVING
caused a server crash. (Bug #7769)
TIMEDIFF() function to return wrong results
if one of its arguments had non-zero microsecond part (Bug #7586).
TIMESTAMP columns with display width
specified to be not identical to DATETIME columns when server
was run in MAXDB mode (Bug #7418).
UNION statements that resulted in the wrong number of the
examined rows reported in the slow query log.
-not and trunc* operators of
full-text search. Using more than one truncated negative search term, was
causing empty result set.
innodb_file_per_table. mysqld would stop
and complain about Windows error number 87 in a file operation.
(See the Bugs database or the 4.1.9 change notes about a workaround for that
bug in 4.1.9). (Bug #8021)
ucs2
character set. (Bug #7350)
tmpfile() function on Netware. All InnoDB
temporary files are created under sys:\tmp. Previously,
InnoDB temporary files were never deleted on Netware.
space->n_pending_flushes == 0 to fail in
`fil0fil.c', in fil_space_free(),
in DROP TABLE or in ALTER TABLE.
ALTER TABLE ... ADD CONSTRAINT PRIMARY KEY ... complained
about bad foreign key definition. (Bug #7831)
INSERT ... SELECT ... (binlog not enabled),
or a multi-table UPDATE or DELETE, and only the read
tables are InnoDB type, the rest are MyISAM. (Bug #7879)
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)
mysqlbinlog forgot to add backquotes around the collation of user
variables (causing later parsing problems as BINARY is a reserved
word). (Bug #7793)
mysqldump --single-transaction sets its transaction
isolation level to REPEATABLE READ before proceeding (otherwise if the
MySQL server was configured to run with a default isolation level lower than
REPEATABLE READ it could give an inconsistent dump). (Bug #7850)
mysql client so that including \p as part of a
prompt command uses the name of the shared memory connection when the
connection is using shared memory. (Bug #7922)
SELECT
@@unknown_var hangs them -- which was fixed in MySQL 3.23.50). (Bug #7965)
Functionality added or changed:
mysqld_safe no longer tests for the presence of the data
directory when using a relatively-located server binary. It just assumes
the directory is there, and fails to start up if it is not. This allows
the data directory location to be specified on the command line, and avoids
running a server binary that was not intended. (Bug #7249)
-win to -win32
-noinstall and -essential have been
moved in front of the version number
Seconds_Behind_Master is 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 is 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:
mysql_fix_privilege_tables could result
in grant table columns with too-short lengths if the server character set had
been set to a multi-byte character set first. (Bug #7539)
innodb_file_per_table in `my.cnf'.
If you shut down mysqld, records could disappear from
the secondary indexes of a table. Unfortunately, on Windows a new Bug #8021
was introduced. Windows users of innodb_file_per_table
should put a line innodb_flush_method=unbuffered to their
`my.cnf' or `my.ini' to work around 8021. (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)
INSERT DELAYED with prepared
statements.
STR_TO_DATE function, if there is some other specifier in
the format string following %f. (Bug #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
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) now rewinds a counter and enables
buffered rows to 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() updates 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)
MyISAM index corruption when key values
start with character codes below 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
are no 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 preserves 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 converts 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 from now on are
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. (Bug #6202)
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 later redoes 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 produces 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 toward 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 then marks 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 22.2.3.54 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 have
to repair this table. CHECK TABLES tells 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
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 does 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 are not 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 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 continues 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 takes more storage than before.
Similarly, index values on such columns are measured in characters, not bytes.
LIMIT no longer accepts negative arguments
(they used to be treated as very big positive numbers before).
DATABASE() function now returns NULL rather than the empty
string if there is no database selected.
--sql-mode=NO_AUTO_VALUE_ON_ZERO option to suppress the usual
behavior of generating the next sequence number when zero is stored in
an AUTO_INCREMENT column. With this mode enabled, zero is stored as
zero; only storing NULL generates a sequence number.
user table, not 45-byte passwords as in 4.1.0.
Any 45-byte passwords created for 4.1.0 must be reset after running the
mysql_fix_privilege_tables script.
secure_auth global server system variable and --secure-auth
server option that disallow authentication for accounts that have old
(pre-4.1.1) passwords.
--secure-auth option to mysql command-line client. If this
option is set, the client refuses to send passwords in old (pre-4.1.1) format.
mysql_prepare_result() function to
mysql_get_metadata() as the old name was confusing.
DROP USER 'user_name'@'host_name' statement to drop an account
that has no privileges.
xxx_clear() function for each aggregate function XXX().
ADDTIME(), DATE(), DATEDIFF(), LAST_DAY(),
MAKEDATE(), MAKETIME(), MICROSECOND(), SUBTIME(),
TIME(), TIMEDIFF(), TIMESTAMP(), UTC_DATE(),
UTC_TIME(), UTC_TIMESTAMP(), and WEEKOFYEAR()
functions.
ADDDATE() and SUBDATE().
The second argument now may be a number representing the number of days to
be added to or subtracted from the first date argument.
type values DAY_MICROSECOND,
HOUR_MICROSECOND, MINUTE_MICROSECOND,
SECOND_MICROSECOND, and MICROSECOND
for DATE_ADD(), DATE_SUB(), and EXTRACT().
%f microseconds format specifier for DATE_FORMAT() and
TIME_FORMAT().
SELECT does not use indexes properly
now are written to the slow query log when long log format is used.
MERGE table from MyISAM tables in
different databases. Formerly, all the MyISAM tables had to be in the
same database, and the MERGE table had to be created in that database
as well.
COMPRESS(), UNCOMPRESS(), and
UNCOMPRESSED_LENGTH() functions.
SET sql_mode='mode' for a complex mode (like ANSI), we
now update the sql_mode variable to include all the individual options
implied by the complex mode.
ROLLUP, which
provides summary rows for each GROUP BY level.
SQLSTATE codes for all server errors.
mysql_sqlstate() and mysql_stmt_sqlstate() C API client
functions that return the SQLSTATE error code for the last error.
TIME columns with hour values greater than 24 were returned incorrectly
to the client.
ANALYZE TABLE, OPTIMIZE TABLE, REPAIR TABLE, and FLUSH statements
are now stored in the binary log and thus replicated to slaves.
This logging does not occur if the optional NO_WRITE_TO_BINLOG keyword
(or its alias LOCAL) is given. Exceptions are that
FLUSH LOGS, FLUSH MASTER, FLUSH SLAVE, and
FLUSH TABLES WITH READ LOCK are not logged in any case.
For a syntax example, see section 13.5.5.2 FLUSH Syntax.
relay_log_purge to enable or disable automatic
relay log purging.
LOAD DATA now produces warnings that can be fetched with
SHOW WARNINGS.
CREATE TABLE table2 (LIKE table1)
that creates an empty table table2 with a definition that is
exactly the same as table1, including any indexes.
CREATE TABLE tbl_name (...) TYPE=storage_engine now generates a
warning if the named storage engine is not available. The table is still
created as a MyISAM table, as before.
PURGE BINARY LOGS as an alias for PURGE MASTER LOGS.
PURGE LOGS statement that was added in version 4.1.0.
The statement now should be issued as PURGE MASTER LOGS or
PURGE BINARY LOGS.
SHOW BDB LOGS as an alias for SHOW LOGS.
SHOW MASTER LOGS as an alias for SHOW BINARY LOGS.
(In 4.1.0, SHOW MASTER LOGS was renamed to SHOW BINARY LOGS.
Now you can use either one.)
Slave_IO_State and Seconds_Behind_Master columns
to the output of SHOW SLAVE STATUS.
Slave_IO_State indicates the state of the slave I/O thread, and
Seconds_Behind_Master indicates the number of seconds by
which the slave is late compared to the master.
--lower-case-table-names=1 server option now also makes aliases case
insensitive. (Bug #534)
Bugs fixed:
mysql parser not to erroneously interpret `;' character
within /* ... */ comment as statement terminator.
UNION
operations. The types and lengths now are determined taking into
account values for all SELECT statements in the UNION,
not just the first SELECT.
[CREATE | REPLACE| INSERT] ... SELECT statements.
HASH, BTREE, RTREE, ERRORS, and
WARNINGS no longer are reserved words. (Bug #724)
ROLLUP when all tables were const tables.
(Bug #714)
UNION that prohibited NULL values from being
inserted into result set columns where the first SELECT of the
UNION retrieved NOT NULL columns. The type and max_length
of the result column is now defined based on all UNION parts.
WHERE clause
bigger than outer query WHERE clause. (Bug #726)
MyISAM tables with FULLTEXT indexes
created in 4.0.x to be unreadable in 4.1.x.
REPAIR TABLE ... USE_FRM when used
with tables
that contained TIMESTAMP columns and were created in 4.0.x.
ORDER BY/GROUP BY
clauses. (Bug #442)
INSERT/REPLACE statements. (Bug #446)
CREATE FULLTEXT INDEX syntax illegal.
SELECT that required a temporary table
(marked by Using temporary in EXPLAIN output)
was used as a derived table in EXPLAIN command. (Bug #251)
DELETE
from a big table with
a new (created by MySQL-4.1) full-text index.
LAST_INSERT_ID() now returns 0 if the last INSERT statement
didn't insert any rows.
BEGIN, in the
first relay log.) (Bug #53)
CONNECTION_ID() now is properly replicated. (Bug #177)
PASSWORD() function in 4.1 is now properly replicated.
(Bug #344)
UNION operations that involved temporary tables.
DERIVED TABLES when EXPLAIN is
used on a DERIVED TABLES with a join.
DELETE with ORDER BY and
LIMIT caused by an uninitialized array of reference pointers.
USER() function caused by an error in the size of
the allocated string.
UNION caused by the empty select list and
a non-existent column being used in some of the individual SELECT
statements.
FLUSH LOGS was
issued on the master. (Bug #254)
REQUIRE SSL option specified for
their accounts.
INSERT INTO t VALUES(@a), where @a had never
been set by this connection before), the slave could replicate the
query incorrectly if a previous transaction on the master used a user
variable of the same name. (Bug #1331)
? prepared statement
parameter as the argument to certain functions or statement clauses caused
a server crash when mysql_prepare() was invoked. (Bug #1500)
SLAVE START (which is a deprecated syntax, START SLAVE should be
used instead) could crash the slave. (Bug #2516)
ALTER TABLE RENAME, when rename to the table with
the same name in another database silently dropped destination table if
it existed. (Bug #2628)
SET PASSWORD FOR user it's now
defaulted to % instead of the current host.
Functionality added or changed:
SHOW MASTER LOGS statement to SHOW BINARY LOGS.
DEFAULT(col_name) in expressions; it produces the
column's default value.
--compatible option to mysqldump for producing output that
is compatible with other database systems or with older MySQL servers.
--opt option for mysqldump now is enabled by default,
as are all the options implied by --opt.
CHARSET() and COLLATION() functions to return the character
set and collation of a string.
USING type_name syntax in index definition.
IS_USED_LOCK() for determining the connection identifier
of the client that holds a given advisory lock.
user table.
old-password command to mysqladmin for changing
password but storing it using the old password-hashing format.
CRC32() function to compute cyclic redundancy check value.
localhost.
REPAIR TABLE of MyISAM tables now uses less temporary disk space when
sorting char columns.
DATE/DATETIME checking is now a bit stricter to support the
ability to automatically distinguish between date, datetime, and time
with microseconds. For example, dates of type YYYYMMDD HHMMDD are no
longer supported; you must either have separators between each
DATE/TIME part or not at all.
help
week in the mysql client and get help for the week()
function.
mysql_get_server_version() C API client function.
libmysqlclient that fetched column defaults.
record_in_range() method to MERGE tables to be
able to choose the right index when there are many to choose from.
RAND() and user variables @var.
ANSI_QUOTES on the fly.
EXPLAIN SELECT now can be killed. See section 13.5.5.3 KILL Syntax.
REPAIR TABLE and OPTIMIZE TABLE
now can be killed. See section 13.5.5.3 KILL Syntax.
USE INDEX,
IGNORE INDEX, and FORCE INDEX.
DROP TEMPORARY TABLE now drops only temporary tables and doesn't
end transactions.
UNION in derived tables.
TIMESTAMP is now returned as a string of type
'YYYY-MM-DD HH:MM:SS' and different timestamp lengths are not supported.
This change was necessary for SQL standards compliance. In a future
version, a further change will be made (backward compatible with this
change), allowing the timestamp length to indicate the desired number
of digits of fractions of a second.
MYSQL_FIELD structure.
CREATE TABLE foo (a INT not null primary key) the
PRIMARY word is now optional.
CREATE TABLE the attribute SERIAL is now an alias for
BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.
SELECT ... FROM DUAL is an alias for SELECT ....
(To be compatible with some other databases).
CHAR/VARCHAR it's now
automatically changed to TEXT or BLOB; One get a
warning in this case.
BLOB/TEXT types with the
syntax BLOB(length) and TEXT(length). MySQL
automatically changes it to one of the internal BLOB/TEXT
types.
CHAR BYTE is an alias for CHAR BINARY.
VARCHARACTER is an alias for VARCHAR.
integer MOD integer and integer DIV integer.
DIV is now a reserved word.
SERIAL DEFAULT VALUE added as an alias for AUTO_INCREMENT.
TRUE and FALSE added as alias for 1 and 0, respectively.
SELECT .. LIMIT 0 to return proper row count for
SQL_CALC_FOUND_ROWS.
--tmpdir=dirname1:dirname2:dirname3.
SELECT * from t1 where t1.a=(SELECT t2.b FROM t2).
SELECT a.col1, b.col2
FROM (SELECT MAX(col1) AS col1 FROM root_table) a,
other_table b
WHERE a.col1=b.col1;
CONVERT(... USING ...) syntax for converting string values between
character sets.
BTREE index on MEMORY (HEAP) tables.
CREATE TABLE.
SHOW FULL COLUMNS FROM tbl_name shows column comments.
ALTER DATABASE.
SHOW [COUNT(*)] WARNINGS shows warnings from the last command.
CREATE TABLE
... SELECT by defining the column in the CREATE part.
CREATE TABLE foo (a TINYINT NOT NULL) SELECT b+1 AS a FROM bar;
expr SOUNDS LIKE expr same as SOUNDEX(expr)=SOUNDEX(expr).
VARIANCE(expr) function returns the variance of expr
CREATE
[TEMPORARY] TABLE [IF NOT EXISTS] table (LIKE table). The table can
be either normal or temporary.
--reconnect and --skip-reconnect for the
mysql client, to reconnect automatically or not if the
connection is lost.
START SLAVE (STOP SLAVE) no longer returns an error
if the slave is started (stopped); it returns a
warning instead.
SLAVE START and SLAVE STOP are no longer accepted by the query
parser; use START SLAVE and STOP SLAVE instead.
Version 4.0 of the MySQL server includes many enhancements and new features:
InnoDB storage engine is now included in the standard binaries,
adding transactions, row-level locking, and foreign keys.
See section 15 The InnoDB Storage Engine.
MERGE tables, now supporting INSERT statements and
AUTO_INCREMENT.
See section 14.2 The MERGE Storage Engine.
UNION syntax in SELECT.
See section 13.1.7.2 UNION Syntax.
DELETE statements.
See section 13.1.1 DELETE Syntax.
libmysqld, the embedded server library.
See section 22.2.16 libmysqld, the Embedded MySQL Server Library.
GRANT privilege options for even tighter control and
security.
See section 13.5.1.3 GRANT and REVOKE Syntax.
GRANT system, particularly
useful for ISPs and other hosting providers.
See section 5.6.4 Limiting Account Resources.
SET Syntax.
For a full list of changes, please refer to the changelog sections for each individual 4.0.x release.
Functionality added or changed:
InnoDB: Added configuration option and settable global variable
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 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.
Bugs fixed:
TEMPOARY tables
under some circumstances, such as CREATE TABLE ... SELECT followed by
ANALYZE TABLE. (Bug #7519)
DELETE FROM tbl_name ...
WHERE ... ORDER BY tbl_name.col_name when the ORDER BY
column was qualified with the table name. (Bug #8392)
MATCH ... AGAINST in natural language mode that could
cause a server crash if the FULLTEXT index was not used in a join
(EXPLAIN did not show fulltext join mode) and the search query
matched no rows in the table (Bug #8522).
--version option on Windows was always Win95/Win98 (i32).
More accurately determine platform as Win32 or Win64 for 32-bit
or 64-bit Windows, and architecture as ia32 for x86, ia64 for
Itanium, and axp for Alpha. (Bug #4445)
InnoDB: Honor the --tmpdir startup option when creating
temporary files. Previously, InnoDB temporary files were always
created in the temporary directory of the operating system. On Netware,
InnoDB will continue to ignore --tmpdir. (Bug #5822)
DOUBLE UNSIGNED column when it was assigned a value from a signed
DOUBLE column. (Bug #7700)
--replicate-*-table options had been specified.
(Bug #7011)
set_bit() and clear_bit() functions in source code
to avoid a conflict with functions of the same names in Linux kernel
header files. (Bug #7971)
QUOTE() function returning bad results.
(Bug #8248)
INSERT INTO ...SELECT failed when the source and
target table were the same. (Bug #6034)
tmpfile() function on Netware. All InnoDB
temporary files are created under sys:\tmp. Previously,
InnoDB temporary files were never deleted on Netware.
mysqlaccess script.
Reported by Javier Fernandez-Sanguino Pena
and Debian Security Audit Team.
( CAN-2005-0004)
mysql_list_fields(), which was
accidentally broken in 4.0.22 (Bug#6761)
query_cache_wlock_invalidate system variable visible in
SHOW VARIABLES output. (Bug #7594)
FROM_UNIXTIME() function to return
NULL for zero argument instead of the Epoch. (Bug #7515)
QUOTE function when used in conjunction with some
other string functions. This lead to severe buffer overflow and server
crashing. (Bug #7495)
mysqld binaries built on HP-UX-11 did
not work with InnoDB files greater than 2 GB in size. (Bug #6189)
MyISAM sometimes didn't sleep and retry the write, thus
resulting in a corrupted table. (Bug #7714)
ANALYZE
TABLE, OPTIMIZE TABLE, or REPAIR TABLE statements from the
master. (Bug #6461, Bug #7658)
mysqldump in 4.0.x to dump
TIMESTAMP(2) and TIMESTAMP(4) column types. (Bug #6530)
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.0.23a.tar.gz'. This
modification did not affect the binary packages. (Bug #7401)
Functionality added or changed:
--hex-blob option to mysqldump for dumping binary
string columns using hexadecimal notation.
mysql_hex_string() C API function that hex-encodes a string.
SHOW INNODB STATUS information
to a temporary file unless the configuration option
innodb_status_file=1 is set.
mysqlbinlog now prints an informative commented line (thread id,
timestamp, server id, etc) before each LOAD DATA INFILE, like it
does for other queries; unless --short-form is used.
Bugs fixed:
mysql.user table in Windows distributions
that had been created with a Host value of build rather than
%. (Bug #6000)
CREATE TABLE .. SELECT query to the binary log when the
insertion of new records partially failed. (Bug #6682)
FROM_UNIXTIME() function to return wrong result
if the argument was too big. (Bug #6439)
TIMESTAMP
columns and give wrong results for UNIX_TIMESTAMP() function if it
was run in time zone with leap seconds. (Bug #6387)
LOAD DATA INFILE…REPLACE
printing duplicate key error when executing the same
load query several times. (Bug #5835)
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)
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)
0xA0 as space in InnoDB's
FOREIGN KEY parser if MySQL treats it as space in
the default charset. EMS MySQL Manager inserts character
0xA0 after the table name in an ALTER, which
confused InnoDB's parser.
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)
Functionality added or changed:
--with-openssl option for configure now accepts a path
prefix as an argument. --with-openssl-includes and
--with-openssl-libs are still supported, but are needed only to
override the default values. (Bug #5494)
--without-man option to configure to suppress
building/installing the manual pages. (Bug #5379)
mysqld option --innodb-table-locks and session
variable innodb_table_locks (on by default).
In applications using AUTOCOMMIT=1 and MySQL's LOCK TABLES
command, InnoDB's internal table locks that were added in 4.0.20
can cause deadlocks. You can set
innodb_table_locks=0 in `my.cnf' to remove that problem.
See section 15.17 Restrictions on InnoDB Tables.
(Bug #3299, Bug #5998)
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
are no delays. See section 15.13 Implementation of Multi-Versioning.
HA_ERR_ROW_IS_REFERENCED if we cannot
DROP a parent table because it is referenced by a
FOREIGN KEY constraint.
Bugs fixed:
libmysqlclient functions mysql_create_db(), mysql_rm_db()
were called and were going to return error. (Bug #6081)
ENABLE KEYS, which failed if tmpdir ran out of
space. Now, a full repair is done in this case. (Bug #5625)
FOREIGN KEY constraint. (Bug #5784)
FLUSH TABLE(S) to close HANDLER
tables. HANDLER tables are now reopened after a FLUSH
TABLE(S) the next time they are used. However, they lose their file position
if this happens. (Bug #4286)
HANDLER tables with the same alias to be
opened multiple times. HANDLER aliases must now be unique, even though
it is syntactically correct in versions below 4.1, to qualify them with their
base table's database name (e.g. test_db.handler_tbl, but this now
conflicts with e.g. another_db.handler_tbl). (Bug #4335)
mysqlimport now reads input files locally from the client host only
if the --local option is given. Previously, it assumed incorrectly
in some cases that files were local even without --local.
(Bug #5829)
FOREIGN KEY parser that prevented
ALTER TABLE of tables containing `#' in their names. (Bug #5856)
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)
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)
FOREIGN KEY
operation, so that we do not starve other users doing CREATE TABLE
or other DDL operations. This caused a notorious 'Long semaphore wait' message
to be printed to the `.err' log. (Bug #5961)
LOCK TABLES,
also over plain consistent read SELECTs.
mysqldump with the option -l or --opt,
or if one used LOCK TABLES ... LOCAL. (Workaround in 4.0.21: use
--quick and --single-transaction. (Bug #5538)
UNIONs. This value is printed in the slow query log.
(Bug #5879)
read_rnd_buffer_size
(Bug #5492)
TIMESTAMP fields if --new mode was enabled. (Bug #4131)
mysqladmin now returns a status of 0 even when the server denies
access; such an error means the server is running. (Bug #3120)
SHOW
DATABASES even if the database name doesn't match the given wildcard (Bug #5539)
Functionality added or changed:
./configure --comment during
compilation) when starting the server. E.g.:
Version: '4.0.21-debug' socket: '/tmp/mysql.sock' port: 0 Official MySQL Binary
SIGHUP and SIGQUIT on
Mac OS X 10.3. This is needed because under this OS, the MySQL server receives
lots of these signals (reported as Bug #2030).
mysqld-nt and mysqld-max-nt servers now
write error messages to the Windows event log in addition to the MySQL error
log.
Bugs fixed:
MERGE tables
(even one MERGE table and MyISAM tables), that could've
resulted in a crash or hang of the server. (Bug #2408)
GROUP BY queries
with expression in HAVING clause that refers to a BLOB
(TEXT, TINYBLOB, etc) fields. (Bug #4358)
HEAP table is
dropped.
It could only happen on Windows when a symlink file (.sym) is used and
if that symlink file contained double backslashes (\\). (Bug #4973)
TIMESTAMP(19) fields from being created.
(Bug #4491)
NULL values in BLOB (TINYBLOB, TEXT,
TINYTEXT, etc) columns of MyISAM tables. (Bug #4816)
ROUND() reporting incorrect metadata
(number of digits after the decimal point). It can be seen, for example,
in CREATE TABLE t1 SELECT ROUND(1, 34). (Bug #4393)
SQRT() and LOG(). (Bug #4356)
LOAD DATA with the LOCAL
option. The problem occurs when an error happens during the LOAD DATA
operation. Previously, the connection was broken. Now the error message
is returned and connection stays open.
col IN (val) the same way it does
for col = val.
net_buffer_length when building the
DBD::mysql Perl module. (Bug #4206)
lower_case_table_names=2 (keep case for table names) was not honored
with ALTER TABLE and CREATE/DROP INDEX. (Bug #3109)
DECIMAL(0,...) column. (Bug #4046)
IF() function incorrectly determining the result type
if aggregate functions were involved. (Bug #3987)
MATCH ... AGAINST() on a phrase search operator with
a missing closing double quote. (Bug #3870)
server-id was not set using startup options but with SET
GLOBAL, the replication slave still complained that it was not set.
(Bug #3829)
mysql_real_connect() (which
required a compromised DNS server and certain operating systems). (Bug #4017)
mysqld
was run as the root system user, and if you had
--log-bin=<somewhere_out_of_var_lib_mysql> it created binary log
files owned by root in this directory, which remained owned by
root after the installation. This is now fixed by starting
mysqld as the mysql system user instead. (Bug #4038)
DROP DATABASE honor the value of
lower_case_table_names. (Bug #4066)
INSERT ... SELECT if it
examined more than 4 billion rows. (Bug #3871)
AND/OR expressions if query was ignored (either by a
replication server because of --replicate-*-table rules, or by any MySQL
server because of a syntax error). (Bug #3969, Bug #4494)
mysqlbinlog --position --read-from-remote-server
had wrong # at lines. (Bug #4506)
CREATE TEMPORARY TABLE t SELECT failed while loading the data, the
temporary table was not dropped. (Bug #4551)
DROP TABLE failed to drop a table on
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)
disable-local-infile option had no effect if client read it
from a configuration file using
mysql_options(...,MYSQL_READ_DEFAULT,...). (Bug #5073)
mysql-test-run failed on the rpl_trunc_binlog test if
running test from the installed (the target of 'make install') directory. (Bug
#5050)
KILL. (Bug
#4810)
KILLed while it was doing
START SLAVE. (Bug #4827)
FLUSH TABLES WITH READ LOCK block COMMIT if server is
running with binary logging; this ensures that the binary log position is
trustable when doing a full backup of tables and the binary log. (Bug #4953)
auto_increment column was not reset by
TRUNCATE TABLE is the table was a temporary one. (Bug #5033)
database.table.column) when
lower_case_table_names=1. (Bug #4792)
SET CHARACTER SET was not replicated correctly. MySQL 4.1
does not have that bug. (Bug #4500)
Note: The windows packages had to be repackaged and re-released several times to resolve packaging issues (such as missing files). This did not affect the binaries included (they have not been recompiled), therefore the installation packages are of version 4.0.20d, while the binaries included still identify themselves as version 4.0.20b.
Functionality added or changed:
MATCH ... AGAINST ( ... IN BOOLEAN MODE) no longer
matches partial words.
Bugs fixed:
/ reporting incorrect metadata
(number of digits after the decimal point). It can be seen, for example,
in CREATE TABLE t1 SELECT "0.01"/"3". (Bug #3612)
DROP DATABASE on some configurations
(in particular, Linux 2.6.5 with ext3 are known to expose this bug).
(Bug #3594)
Note: The MySQL 4.0.19 binaries were uploaded to the download mirrors on May, 10th. However, a potential crashing bug was found just before the 4.0.19 release was publicly announced and published from the 4.0 download pages at http://dev.mysql.com/.
A fix for the bug was pushed into the MySQL source tree shortly after it could be reproduced and is included in MySQL 4.0.20. Users upgrading from MySQL 4.0.18 should upgrade directly to MySQL 4.0.20 or later.
See (Bug #3596) for details (it was reported against MySQL-4.1, but was confirmed to affect 4.0.19 as well).
Functionality added or changed:
"YYYY-MM-DD HH:MM:SS. This is done to make it easier
to use tables created in MySQL 4.1 to be used in MySQL 4.0.
RAID_CHUNKS with a value > 255 it is set to 255.
This was made to ensure that all raid directories are always 2 hex bytes.
(Bug #3182)
FORCE INDEX clause as a candidate to resolve ORDER BY
as well.
--log-warnings server option now is enabled by default.
Disable with --skip-log-warnings.
SELECT ... UNION SELECT ... ORDER BY ..., it was possible
to qualify a column name in the ORDER BY clause with a table name. This
is no longer possible. Column names in ORDER BY should refer to names
established in the first SELECT of the UNION. (Bug #3064)
max_insert_delayed_threads system variable as a synonym for
max_delayed_threads.
query_cache_wlock_invalidate system variable. It allows
emulation of MyISAM table write-locking behavior, even for queries
in the query cache. (Bug #2693)
MASTER_SERVER_ID is not reserved anymore.
InnoDB in this case always stores filenames in lower case:
You can now force lower_case_table_names to 0 from the command
line or a configuration file. This is useful with case-insensitive filesystems
when you have previously not used
lower_case_table_names=1 or lower_case_table_names=2 and
you have created InnoDB tables. With
lower_case_table_names=0, InnoDB tables were stored in mixed case
while setting lower_case_table_names to a non-zero value now forces it to
lower case (to make the table names case insensitive).
Because it's possible to crash MyISAM tables by referring to them with
different case on a case-insensitive filesystem, we recommend that you
use lower_case_table_names or lower_case_table_names=2 on
such filesystems.
The easiest way to convert to use lower_case_table_names=2 is to
dump all your InnoDB tables with mysqldump, drop them and then
restore them.
DROP TEMPORARY TABLE IF
EXISTS instead of DROP TEMPORARY TABLE, for more robustness.
--replicate-same-server-id.
Bugs fixed:
ft_stopword_file to myisamchk.
',' at the end of field specifications. (Bug #3481)
INTERVAL now can handle big values for seconds, minutes and hours.
(Bug #3498)
'%'. (Bug #3473)
SOUNDEX() to ignore non-alphabetic characters also
in the beginning of the string. (Bug #3556)
MATCH ... AGAINST() searches when another thread was
doing concurrent inserts into the MyISAM table in question. The first
--- full-text search -- query could return incorrect results in this case
(for example, ``phantom'' rows or not all matching rows, even an empty result set).
The easiest way to check whether you are affected is to start mysqld
with --skip-concurrent-insert switch and see if it helps.
DROP DATABASE on a directory containing non-
MySQL files. Now a proper error message is returned.
ANALYZE TABLE on a BDB table inside a transaction
that hangs server thread. (Bug #2342)
SELECT DISTINCT where all selected parts
where constants and there were hidden columns in the created temporary table.
(Bug #3203)
COUNT(DISTINCT) when there was a lot of
values and one had a big value for max_heap_table_size.
RAID tables. (Bug #2882)
max_relay_log_size and the slave thread did a
flush_io_cache() at the same time.
SLAVE START from different
threads at the same time. (Bug #2921)
DROP DATABASE with lower_case_table_names=2.
UNION when using lower_case_table_names=2.
(Bug #2858)
DELETE and UPDATE statements if thread is killed.
(Bug #2422)
INSERT DELAYED statement is written at
once if binary logging is enabled. (Bug #2491).
myisamchk -p, myisam_repair_threads) -
sometimes repair process failed to repair a table. (Bug #1334)
UPDATE statements involving at least one constant
table. Bug was exhibited in allowing non matching row to be updated.
(Bug #2996).
--set-character-set and --character-sets-dir options in
myisamchk now work.
mysqlbinlog that caused one pointer to be free'd twice
in some cases.
+
operator (for example,
MATCH ... AGAINST('+(+(word1 word2)) +word3*' IN BOOLEAN MODE).
DELETE that was caused by foreign key
constraints. If the order of the tables established by MySQL optimizer
did not match parent-child order, no rows were deleted and no error
message was provided. (Bug #2799)
OPTIMIZE TABLE or REPAIR
TABLE), this could sometimes stop the slave SQL thread. This does not lead
to any corruption, but you must use START SLAVE to get replication
going again. (Bug #1858)
The bug was accidentally not fixed in 4.0.17 as it was unfortunately
earlier said.
Rotate event is found by the slave SQL thread in the
middle of a transaction, the value of Relay_Log_Pos in SHOW SLAVE
STATUS remains correct. (Bug #3017)
InnoDB reports when it is doing a
crash recovery on a slave server. (Bug #3015)
DROP TEMPORARY TABLE statement is automatically
written to the binary log when a session ends, the statement is recorded with an
error code of value zero (this ensures that killing a SELECT on the
master does not result in a superfluous error on the slave). (Bug #3063)
INSERT DELAYED (also known as a
delayed_insert thread) is killed, its statements are recorded with an
error code of value zero (killing such a thread does not endanger
replication, so we thus avoid a superfluous error on the slave). (Bug #3081)
START SLAVE commands were run at the same
time. (Bug #2921)
--replicate-* options. The bug was that if the
statement had been killed on the master, the slave would stop. (Bug #2983)
--local-load option of mysqlbinlog now requires an argument.
LOAD DATA FROM MASTER after
RESET SLAVE. (Bug #2922)
Binlog has bad magic number and stop when it
was not necessary to do so. (Bug #3401)
Exec_master_log_pos (and its disk image in the
relay-log.info file) to be correct if the master had version 3.23 (it
was too big by 6 bytes). This bug does not exist in the 5.0 version.
(Bug #3400)
mysqlbinlog does not forget to print a USE command
under rare circumstances where the binary log contained a LOAD DATA
INFILE command. (Bug #3415)
LOAD DATA INFILE when the
master had version 3.23. Some smaller problems remain in this setup,
See section 6.7 Replication Features and Known Problems. (Bug #3422)
DELETE statements were always replicated by the slave if
there were some --replicate-*-ignore-table options and no
--replicate-*-do-table options. (Bug #3461)
--with-debug and replicating itself. (Bug #3568)
Functionality added or changed:
LOAD DATA by mysqlbinlog in remote mode.
(Bug #1378)
ENGINE is now a synonym for the TYPE option for
CREATE TABLE and ALTER TABLE.
lower_case_table_names system variable now can take a value of
2, to store table names in mixed case on case-insensitive filesystems.
It's forced to 2 if the database directory is located on a case-insensitive
filesystem.
MEMORY (HEAP) tables: Made the master
automatically write a DELETE FROM statement to its binary log when a
MEMORY table is opened for the first time since master's startup.
This is for the case where the slave has replicated a non-empty
MEMORY table, then the master is shut down and restarted: the table
is now empty on master; the DELETE FROM empties it on slave too. Note
that even with this fix, between the master's restart and the first use of
the table on master, the slave still has out-of-date data in the table. But
if you use the init-file option to populate the MEMORY table
on the master at startup, it ensures that the failing time interval is zero.
(Bug #2477)
--old-rpl-compat server option, which was a
holdover from the very first 4.0.x versions. (Bug #2428)
--sync-frm. It's on by default, to
instruct MySQL to sync to disk each time `.frm' file is created.
Use --disable-sync-frm to disable.
Bugs fixed:
mysqlhotcopy now works on NetWare.
DROP DATABASE could not drop databases with RAID tables that had
more than nine RAID_CHUNKS. (Bug #2627)
wait_timeout to 2147483 on Windows (OS limit). (Bug #2400)
--init-file crashes MySQL if it contains a large
SELECT. (Bug #2526)
SHOW KEYS now shows NULL in the Sub_part column for
FULLTEXT indexes.
mysqld
to run on Debian/IA-64 with a TLS-enabled glibc. (Bug #2599)
SELECT privilege is needed for tables that are only read in
multiple-table UPDATE statements. (Bug #2377)
LOCK TABLES ... ; INSERT
... SELECT and one used the same table in the INSERT and SELECT
part. (Bug #2296)
SELECT INTO ... DUMPFILE now deletes the generated file on error.
BDB tables. The
symptom was that data could be returned in the wrong lettercase. (Bug #2509)
TEXT columns if these columns happened to contain
values having trailing spaces. This bug was introduced in 4.0.17.
indexed_TEXT_column = expr
was present and the column contained values having trailing spaces.
This bug was introduced in 4.0.17.
TEXT columns
that happened to contain values having trailing spaces. This bug was
introduced in 4.0.17. (Bug #2295)
MyISAM tables for BLOB values longer
than 16MB. (Bug #2159)
mysqld_safe when running multiple instances of
MySQL. (Bug #2114)
HANDLER statement with tables not
from a current database. (Bug #2304)
UPDATE statements did not check that there was only one table
to be updated. (Bug #2103)
BLOB column
type index size being calculated incorrectly in MIN() and MAX()
optimizations.
(Bug #2189)
LOCK TABLES in
mysqldump. (Bug #2242)
mysqld_safe that caused mysqld to generate a
warning about duplicate user=xxx options if this option was specified in
the [mysqld] or [server] sections of `my.cnf'.
(Bug #2163)
INSERT DELAYED ... SELECT ... could cause table corruption because
tables were not locked properly. This is now fixed by ignoring DELAYED
in this context. (Bug #1983)
MyISAM table may first receive ``no space left
on device,'' but later complete when disk space becomes available.
See section A.4.3 How MySQL Handles a Full Disk.) The bug was that the master forgot to reset the error
code to 0 after success, so the error code got into its binary log, thus
causing the slave to issue false alarms such as ``did not get the same
error as on master.'' (Bug #2083)
LOAD DATA INFILE for an empty file from a 3.23 master to a
4.0 slave caused the slave to print an error. (Bug #2452)
lower_case_table_names to 1 if the file
system was case insensitive, mysqld could crash. This bug existed only
in MySQL 4.0.17. (Bug #2481)
TIMESTAMP columns
that was erroneously disabled in previous release. (Bug #2539) Fixed
SHOW CREATE TABLE to reflect these values. (Bug #1885) Note
that because of the auto-update feature for the first TIMESTAMP
column in a table, it makes no sense to specify a default value for
the column. Any such default is silently ignored (unless another
TIMESTAMP column is added before this one). Also fixed the meaning
of the DEFAULT keyword when it is used to specify the value to be
inserted into a TIMESTAMP column other than the first. (Bug #2464)
UNIX_TIMESTAMP() to produce incorrect results or that caused
non-zero values to be inserted into TIMESTAMP columns. (Bug #2523)
Also, current time zone now is taken into account when checking if datetime
values satisfy both range boundaries for TIMESTAMP columns. The
range allowed for a TIMESTAMP column is time zone-dependent and
equivalent to a range of 1970-01-01 00:00:01 UTC to 2037-12-31
23:59:59 UTC.
DELETE statements were never replicated by the slave if
there were any --replicate-*-table options. (Bug #2527)
query_prealloc_size,
query_alloc_block_size, trans_prealloc_size,
trans_alloc_block_size now
have an effect. (Bug #1948)
ALTER TABLE RENAME, when rename to the table with
the same name in another database silently dropped destination table if
it existed. (Bug #2628)
Functionality added or changed:
mysqldump no longer dumps data for MERGE tables. (Bug #1846)
lower_case_table_names is now forced to 1 if the database directory
is located on a case-insensitive filesystem. (Bug #1812)
realpath() doesn't
work. (Before one could use CREATE TABLE .. DATA DIRECTORY=.. even if
HAVE_BROKEN_REALPATH was defined. This is now disabled to avoid
problems when running ALTER TABLE).
AUTO_INCREMENT value in a MyISAM table
no longer updates the AUTO_INCREMENT counter to a big unsigned value.
(Bug #1366)
WEEK(..., mode) function.
See section 12.5 Date and Time Functions.
(Bug #1178)
UNION DISTINCT syntax.
mysql_server_init() now returns 1 if it can't initialize the
environment. (Previously mysql_server_init() called exit(1)
if it could not create a key with pthread_key_create(). (Bug #2062)
mysqld from MySql
to MySQL.
This should not affect usage, because service names are not case sensitive.
mysqld as a service on Windows systems, mysqld
reads startup options in option files from the option group with the same
name as the service name.
(Except when the service name is MySQL).
Bugs fixed:
SIGHUP to mysqld crashed the server if it was running with
--log-bin. (Bug #2045)
ON clauses
when lower_case_table_names is set. (Bug #1736)
IGNORE ... LINES option to LOAD DATA INFILE didn't work
when used with fixed length rows. (Bug #1704)
UNIX_TIMESTAMP() for timestamps close to 0.
(Bug #1998)
QUOTE()
function. (Bug #1868)
TEXT with endspace. (Bug #1651)
ENUM column that is set to the
empty string (for example, with REPLACE()). (Bug #2023)
mysql client program now correctly prints connection identifier
returned by mysql_thread_id() as unsigned integer rather than as signed
integer. (Bug #1951)
FOUND_ROWS() could return incorrect number of rows after a query
with an impossible WHERE condition. (Bug #1468)
SHOW DATABASES no longer shows .sym files (on Windows) that
do not point to a valid directory. (Bug #1385)
libmysql.so library. (from pthread_key_create()). (Bug #2061)
UNION statement with alias *. (Bug #1249)
DELETE ... ORDER BY ... LIMIT where the rows where not
deleted in the proper order. (Bug #1024, Bug #1697).
DECIMAL. (Bug #2128)
EXPLAIN if query contained an
ORDER BY NULL clause. (Bug #1335)
DROP DATABASE. (Bug #1898)
AND/OR queries. (Bug #1828)
ORDER BY when ordering by expression and identifier.
(Bug #1945)
HANDLER when an ALTER TABLE was
executed in a different connection. (Bug #1826)
trunc* operator of full-text search which
sometimes caused MySQL not to find all matched rows.
DECIMAL column values.
REF access plan was
preferred to more efficient RANGE on another column.
mysqld --install mysql --defaults-file=path-to-file.
(Bug #1643)
const tables
(such as one-row tables) and non-constant expression
(such as RAND()). (Bug #1271)
SQL_CALC_FOUND_ROWS into
account if LIMIT clause was present. (Bug #1274)
mysqlbinlog now asks for a password at the console when the -p
or --password option is used with no argument. This is consistent with
the way that other clients such mysqladmin and mysqldump
behave. Note: A consequence of this change is that it is no
longer possible to invoke mysqlbinlog as mysqlbinlog -p
pass_val (with a space between the -p option and the following
password value).
(Bug #1595)
STOP SLAVE was
issued.
A->B->C, if 2 sessions on A
updated temporary tables of the same name at the same time, the binary log
of B became incorrect, resulting in C becoming confused.
(Bug #1686)
A->B->C, if STOP SLAVE
was issued on B while it was replicating a temporary table from
A, then when START SLAVE was issued on B, the binary log
of B became incorrect, resulting in C becoming confused.
(Bug #1240)
MASTER_LOG_FILE and MASTER_LOG_POS were not
specified, CHANGE MASTER used the coordinates of the slave I/O
thread to set up replication, which broke replication if the slave SQL
thread lagged behind the slave I/O thread. This caused the slave
SQL thread to lose some events. The new behavior is to use the
coordinates of the slave SQL thread instead.
See section 13.6.2.1 CHANGE MASTER TO Syntax.
(Bug #1870)
TIMESTAMP or
DATETIME value checks of year, month, day, hour, minute
and second ranges are performed and numbers representing illegal
timestamps are converted to 0 value. This behavior is consistent
with manual and with behavior of string to
TIMESTAMP/DATETIME conversion. (Bug #1448)
BIT_AND() and BIT_OR() group functions
returned incorrect value if SELECT used a temporary table and
no rows were found. (Bug #1790).
BIT_AND() is now unsigned in all contexts. This means that it
now returns 18446744073709551615 (= 0xffffffffffffffff) instead of -1 if
there were no rows in the result.
BIT_AND() still returning signed value for an
empty set in some cases. (Bug #1972)
^ (XOR) and >> (bit shift) still returning
signed value in some cases. (Bug #1993)
UPDATE could produce a
wrong complain that some record was not found in one table, if the
UPDATE was preceded by a INSERT ... SELECT. (Bug #1701)
SHOW TABLE STATUS to be
very slow when a database contained a large number of tables, even if a single
particular table were specified. (Bug #1952)
Functionality added or changed:
mysqladmin debug. This works only on systems that support the
mallinfo() call (like newer Linux systems).
range_alloc_block_size, query_alloc_block_size,
query_prealloc_size, transaction_alloc_block_size, and
transaction_prealloc_size.
mysqlbinlog now reads option files. To make this work, you must now
specify --read-from-remote-server when reading binary logs from
a MySQL server. (Note that using a remote server is deprecated and may
disappear in future mysqlbinlog versions).
SIGPIPE signals also for non-threaded programs. The blocking is moved
from mysql_init() to mysql_server_init(), which is
automatically called on the first call to mysql_init().
--libs_r and --include options to mysql_config.
`> prompt for mysql. This prompt is similar to the
'> and "> prompts, but indicates that an identifier
quoted with backticks was begun on an earlier line and the closing backtick
has not yet been seen.
mysql_install_db to be able to use the local machine's IP
address instead of the hostname when building the initial grant tables if
skip-name-resolve has been specified. This
option can be helpful on FreeBSD to avoid thread-safety problems with the
FreeBSD resolver libraries. (Thanks to Jeremy Zawodny for the patch.)
--slave-load-tmpdir option. All these files are
needed when the slave resumes replication after you restore the slave's data.
Bugs fixed:
ERROR 14: Can't change size of file (Errcode: 2)
on Windows in DELETE FROM tbl_name without a WHERE clause or
TRUNCATE TABLE tbl_name, when tbl_name is a MyISAM
table. (Bug #1397)
thr_alarm queue is full warnings after
increasing the max_connections variable with SET GLOBAL.
(Bug #1435)
LOCK TABLES to work when Lock_tables_priv is granted on the
database level and Select_priv is granted on the table level.
FLUSH QUERY CACHE on queries that use same table
several times (Bug #988).
SQL_WARNINGS) to NULL.
ERROR 2013: Lost connection
to MySQL server during query for queries that lasted longer than 30 seconds,
if the client didn't specify a limit with mysql_options(). Users of
4.0.15 on Windows should upgrade to avoid this problem.
SELECT statement. (Bug #1194).
BLOB values with compressed tables.
This was a bug introduced in 4.0.14. It caused MySQL to regard some
correct tables containing BLOB values as corrupted.
(Bug #770, Bug #1304, and maybe Bug #1295)
SHOW GRANTS showed USAGE instead of the real column-level
privileges when no table-level privileges were given.
LOAD DATA FROM
MASTER dropped the corresponding database on the slave, thus erroneously
dropping tables that had no counterpart on the master and tables that
may have been excluded from replication using --replicate-*-table
rules. Now LOAD DATA FROM MASTER no longer drops the database.
Instead, it drops only the tables that have a counterpart on the master
and that match the --replicate-*-table
rules. --replicate-*-db rules can still be used to include or exclude a
database as a whole from LOAD DATA FROM MASTER. A database
also is included or excluded as a whole if there are some rules like
--replicate-wild-do-table=db1.% or
--replicate-wild-ignore-table=db1.%, as is the case for
CREATE DATABASE and DROP DATABASE in replication. (Bug
#1248)
mysqlbinlog crashed with a segmentation fault when used with the
-h or --host option. (Bug #1258)
mysqlbinlog crashed with a segmentation fault
when used on a binary log containing only final events for LOAD
DATA. (Bug #1340)
mysqlbinlog does not reuse temporary filenames from previous runs.
Previously mysqlbinlog failed if was used several times on the same
binary log file that contained a LOAD DATA command.
OPENSSL_DISABLE_OLD_DES_SUPPORT
option was enabled).
LOAD DATA INFILE command from its master.
The bug was that one slave MySQL server sometimes
deleted the `SQL_LOAD-*' files (used for replication of
LOAD DATA INFILE and located in the slave-load-tmpdir directory,
which defaults to tmpdir) belonging to the other slave MySQL server of
this machine, if these slaves had the same slave-load-tmpdir directory.
When that happened, the other slave could not replicate
LOAD DATA INFILE and complained about not being able to open some
SQL_LOAD-* file. (Bug #1357)
LOAD DATA INFILE failed for a small file, the master forgot to
write a marker (a Delete_file event) in its binary log, so the slave
could not delete 2 files (`SQL_LOAD-*.info' and
`SQL_LOAD-*.data' from its tmpdir. (Bug #1391)
SQL_LOAD-*.info
file from tmpdir after successfully replicating a LOAD DATA
INFILE command. (Bug #1392)
DROP TEMPORARY TABLE
statements to the binary log for all temporary tables which the
connection had not explicitly dropped. MySQL forgot to use backticks to quote
the database and table names in the statement. (Bug #1345)
IMPORTANT:
If you are using this release on Windows, you should upgrade at least your
clients (any program that uses libmysql.lib) to 4.0.16 or above. This
is because the 4.0.15 release had a bug in the Windows client library that
causes Windows clients using the library to die with a Lost connection
to MySQL server during query error for queries that take more than 30
seconds. This problem is specific to Windows; clients on other platforms are
unaffected.
Functionality added or changed:
mysqldump now correctly quotes all identifiers when communicating
with the server. This assures that during the dump process, mysqldump
never sends queries to the server that result in a syntax error.
This problem is not related to the
mysqldump program's output, which was not changed. (Bug #1148)
MIN() and MAX()
report that they can return NULL (this is true because an
empty set returns NULL). (Bug #324)
mysqld server is
started on the same TCP/IP port as a running mysqld server.
mysqld system variables wait_timeout,
net_read_timeout, and net_write_timeout now work on Windows.
One can now also set timeouts for read and writes in Windows clients with
mysql_options().
--sql-mode=NO_DIR_IN_CREATE to make it possible for
slaves to ignore INDEX DIRECTORY and
DATA DIRECTORY options given to CREATE TABLE.
When this is mode is on, SHOW CREATE TABLE does not show the
given directories.
SHOW CREATE TABLE now shows the INDEX DIRECTORY and
DATA DIRECTORY options, if they were specified when the table was
created.
open_files_limit system variable now shows the real open files limit.
MATCH ... AGAINST() in natural language mode
now treats words that are present
in more than 2,000,000 rows as stopwords.
.tar.gz) has been moved into a subdirectory docs.
See section 2.1.5 Installation Layouts.
info file in the binary
distributions. (Bug #1019)
libmysqld.a) by default. Due to a linking problem with non-gcc
compilers, it was not included in all packages of the initial 4.0.15
release. The affected packages were rebuilt and released as 4.0.15a.
See section 1.3.1.2 The Embedded MySQL Server.
BETWEEN with
non-constant limits. (Bug #991)
binlog-do-db and binlog-ignore-db options are tested
against the database on the master (see section 5.9.4 The Binary Log), and a
paragraph about how --replicate-do-db, --replicate-do-table
and analogous options are tested against the database and tables on the
slave (see section 6.8 Replication Startup Options).
SET PASSWORD
if it is configured to exclude the mysql database from
replication (using for example
--replicate-wild-ignore-table=mysql.%). This was the
case for GRANT and REVOKE since version 4.0.13 (although
there was Bug #980 in 4.0.13 & 4.0.14, which has been fixed in
4.0.15).
State column of SHOW
PROCESSLIST for replication threads and for MASTER_POS_WAIT()
and added the most common states for these threads to the
documentation, see section 6.3 Replication Implementation Details.
GRANT command that creates an anonymous user
(that is, an account with an empty username) no longer requires
FLUSH PRIVILEGES for the account to be recognized by the server.
(Bug #473)
CHANGE MASTER now flushes `relay-log.info'. Previously
this was deferred to the next run of START SLAVE, so if
mysqld was shutdown on the slave after CHANGE MASTER
without having run START SLAVE, the relay log's name and
position were lost. At restart they were reloaded from
`relay-log.info', thus reverting to their old (incorrect) values from
before CHANGE MASTER and leading to error messages
(as the old relay log did not exist any more) and the slave threads
refusing to start. (Bug #858)
Bugs fixed:
ALTER privilege on the
mysql.user table to execute random code or to gain shell access with
the UID of the mysqld process (thanks to Jedi/Sector One for spotting and
reporting this bug).
FORCE INDEX in a query that contained
"Range checked for each record" in the EXPLAIN output. (Bug #1172)
MYISAM introduced in 4.0.3 where the index file header
was not updated directly after an UPDATE of split dynamic rows.
The symptom was that the table had a corrupted delete-link if mysqld
was shut down or the table was checked directly after the update.
Can't unlock file error when running
myisamchk --sort-index on Windows. (Bug #1119)
key_buffer_size while
the key cache was actively used. (Bug #1088)
MyISAM and ISAM when a row is updated
in a table with a large number of columns and at least one BLOB/TEXT
column.
UNION and LIMIT #,# when
braces were not used around the SELECT parts.
UNION and ORDER BY .. LIMIT #
when one didn't use braces around the SELECT parts.
SELECT SQL_CALC_FOUND_ROWS ... UNION ALL ... LIMIT #
where FOUND_ROWS() returned incorrect number of rows.
1+1-1+1-1... in certain combinations. (Bug #871)
FULLTEXT index
from being marked as "analyzed".
SHOW CREATE TABLE is always larger than the data length.
The only known application that was affected by the old behavior was
Borland dbExpress, which truncated the output from the command.
(Bug #1064)
tis620 character
set. (Bug #1116)
ISAM bug in MAX() optimization.
myisamchk --sort-records=N no longer marks table as crashed if
sorting failed because of an inappropriate key. (Bug #892)
MyISAM compressed table handling that sometimes
made it impossible to repair compressed table in "Repair by sort" mode.
"Repair with keycache" (myisamchk --safe-recover) worked, though.
(Bug #1015)
PRIMARY key declared for a column
that is not explicitly marked NOT NULL was sorted after a
UNIQUE key for a NOT NULL column).
INTERVAL when applied to a DATE value.
(Bug #792)
XOR evaluation in WHERE clause. (Bug #992)
LEFT JOIN)
when ON condition is always false, and range search in used.
(Bug #926)
MATCH ... AGAINST() in some
joins. (Bug #942)
MERGE tables do not ignore Using index (from EXPLAIN output)
anymore.
myisamchk --sort-records crash when used on compressed table.
ALTER TABLE and related commands such as CREATE INDEX.
(Bug #712)
LOAD DATA FROM MASTER when
the master was running without the --log-bin option. (Bug #934)
REQUIRE SSL option specified for
their accounts.
GRANT
or REVOKE queries even if it was configured to exclude the
mysql database from replication (for example, using
--replicate-wild-ignore-table=mysql.%). (Bug #980)
Last_Errno and Last_Error fields in the output of
SHOW SLAVE STATUS are now cleared by CHANGE MASTER and
when the slave SQL thread starts. (Bug #986)
RESET SLAVE does not change
connection information (master host, port, user, and
password), whereas it does. The statement resets these to the startup options
(master-host etc) if there were some. (Bug #985)
SHOW SLAVE STATUS now shows correct information (master host,
port, user, and password) after RESET SLAVE
(that is, it shows the new values, which are copied from the startup
options if there were some). (Bug #985)
Exec_Master_Log_Pos and problems
with MASTER_POS_WAIT() in A->B->C replication setup. (Bug #1086)
mysqlbinlog when
--position=x was used with x being between a
Create_file event and its fellow Append_block,
Exec_load or Delete_file events. (Bug #1091)
mysqlbinlog printed superfluous warnings when using
--database, which caused syntax errors when piped to
mysql. (Bug #1092)
mysqlbinlog --database filter LOAD DATA INFILE too
(previously, it filtered all queries except LOAD DATA
INFILE). (Bug #1093)
mysqlbinlog in some cases forgot to put a leading '#' in
front of the original LOAD DATA INFILE (this command is
displayed only for information, not to be run; it is later reworked to
LOAD DATA LOCAL with a different filename, for execution by
mysql). (Bug #1096)
binlog-do-db and binlog-ignore-db incorrectly filtered
LOAD DATA INFILE (it was half-written to the binary log). This
resulted in a corrupted binary log, which could cause the slave to
stop with an error. (Bug #1100)
InnoDB table)
was updated, and later in the same transaction a
non-transactional table (such as a MyISAM table) was
updated using the updated content of the transactional table
(with INSERT ... SELECT for example), the queries were written
to the binary log in an incorrect order. (Bug #873)
INSERT ... SELECT updated a
non-transactional table, and ROLLBACK was issued, no error was
returned to the client. Now the client is warned that some changes
could not be rolled back, as this was the case for normal
INSERT. (Bug #1113)
STOP SLAVE was run while the slave
SQL thread was in the middle of a transaction, and then CHANGE
MASTER was used to point the slave to some non-transactional
statement, the slave SQL thread could get confused (because it would
still think, from the past, that it was in a transaction).
Functionality added or changed:
default_week_format system variable. The value is used as the
default mode for the WEEK() function.
mysqld now reads an additional option file group having a name
corresponding to the server's release series:
[mysqld-4.0] for 4.0.x servers, [mysqld-4.1] for 4.1.x servers,
and so forth. This allows options to be specified on a series-specific basis.
CONCAT_WS() function no longer skips empty strings. (Bug #586).
InnoDB now supports indexing a prefix of a column. This means, in
particular, that BLOB and TEXT columns can be indexed in
InnoDB tables, which was not possible before.
INTERVAL(NULL, ...) returns -1.
INSERT from SELECT when the table into which the records
are inserted is also a table listed in the SELECT.
CREATE TABLE and INSERT from any UNION.
SQL_CALC_FOUND_ROWS option now always returns the total number
of rows for any UNION.
--table option from mysqlbinlog
to avoid repeating mysqldump functionality.
basedir=c:\mysql # installation directory).
max_seeks_for_key variable that can
be used to force the optimizer to use keys instead of table scans
even if the cardinality of the index is low.
LEFT JOIN to normal join in
some cases.
CHANGE MASTER command. See section 13.6.2.1 CHANGE MASTER TO Syntax.
--nice option to mysqld_safe to allow setting the
niceness of the mysqld process. (Thanks to Christian Hammers for
providing the initial patch.) (Bug #627)
--read-only option to cause mysqld to allow no updates
except from slave threads or from users with the SUPER privilege.
(Original patch from Markus Benning).
SHOW BINLOG EVENTS FROM x where x is less than 4 now
silently converts x to 4 instead of printing an error. The same change
was done for CHANGE MASTER TO MASTER_LOG_POS=x and
CHANGE MASTER TO RELAY_LOG_POS=x.
mysqld now only adds an interrupt handler for the SIGINT signal
if you start it with the new --gdb option. This is because some
MySQL users encountered strange problems when they accidentally sent SIGINT to
mysqld threads.
RESET SLAVE now clears the Last_Errno and
Last_Error fields in the output of SHOW SLAVE STATUS.
max_relay_log_size variable; the relay log is rotated
automatically when its size exceeds max_relay_log_size. But if
max_relay_log_size is 0 (the default), max_binlog_size
is used (as in older versions). max_binlog_size still
applies to binary logs in any case.
FLUSH LOGS now rotates relay logs in addition to the other types of
logs it rotates.
Bugs fixed:
latin1_de character set was rewritten.
The old algorithm could not handle cases like "s@"a" > "ßa".
See section 5.8.1.1 Using the German Character Set. In rare cases it resulted in table
corruption.
ALTER TABLE ... UNION=(...) for MERGE table is now allowed
even if some underlying MyISAM tables are read-only. (Bug #702)
CREATE TABLE t1 SELECT x'41'. (Bug #801)
REPAIR TABLE on a table with a
multiple-part auto_increment key where one part was a packed CHAR.
INSERT statements not being replicated in the
event of a FLUSH LOGS command or when the binary log exceeds
max_binlog_size. (Bug #791)
INTERVAL and GROUP BY or
DISTINCT. (Bug #807)
mysqlhotcopy so it actually aborts for unsuccessful
table copying operations. Fixed another bug so that it succeeds
when there are thousands of tables to copy. (Bug #812)
mysqlhotcopy failing to read options from option
files. (Bug #808)
FULLTEXT indexes even though it was possible (for example, in
SELECT * FROM t1 WHERE MATCH a,b AGAINST("index") > 0).
UNION operations.
SHOW TABLES and
similar commands.
max_user_connections.
HANDLER without an index now works properly when a table has deleted
rows. (Bug #787)
LOAD DATA in mysqlbinlog. (Bug #670)
SET CHARACTER SET DEFAULT works. (Bug #462)
MERGE table behavior in ORDER BY ... DESC queries.
(Bug #515)
PURGE MASTER LOGS or SHOW MASTER LOGS
when the binary log is off. (Bug #733)
DATETIME column and an integer
constant. (Bug #504)
mysqlbinlog. (Bug #672)
ERROR 1105: Unknown error that occurred for some SELECT
queries, where a column that was declared as NOT NULL was compared
with an expression that took NULL value.
mysql_real_connect() to use poll() instead of
select() to work around problem with many open files in the client.
MATCH ... AGAINST used with a
LEFT JOIN query.
mysqld variables
to 4294967295 when they are specified on the command line.
HANDLER ... READ statements, when a table is referenced
via an alias.
safe_malloc, which caused MySQL to
give "Freeing wrong aligned pointer" errors on SCO 3.2.
ALTER TABLE ... ENABLE/DISABLE KEYS could cause a core dump when
done after an INSERT DELAYED statement on the same table.
mysqld. (Bug #549)
INSERT ... SELECT into an AUTO_INCREMENT column
not replicate well. This bug is in the master, not in the slave.
(Bug #490)
INSERT ... SELECT statement inserted rows into a
non-transactional table, but failed at some point (for example, due to
a ``Duplicate key'' error), the query was not written to the binary log.
Now it is written to the binary log, with its error code, as all other
queries are. About the slave-skip-errors option for how to
handle partially completed queries in the slave, see section 6.8 Replication Startup Options. (Bug #491)
SET FOREIGN_KEY_CHECKS=0 was not replicated properly.
The fix probably will not be backported to 3.23.
LOAD DATA INFILE which had no IGNORE or
REPLACE clause on the master, was replicated with IGNORE.
While this is not a problem if the master and slave data are identical
(a LOAD that produces no duplicate conflicts on the master
produces none on the slave anyway), which is true in normal operation, it
is better for debugging not to silently add the IGNORE. That way,
you can get an error message on the slave and discover that for some
reason, the data on master and slave are different and investigate why.
(Bug #571)
LOAD DATA INFILE printed an incomplete ``Duplicate entry
'%-.64s' for key %d''' message (the key name and value were not mentioned)
in case of duplicate conflict (which does not happen in normal operation).
(Bug #573)
--debug, CHANGE MASTER
TO RELAY_LOG_POS could cause a debug assertion failure. (Bug #576)
LOCK TABLES WRITE on an InnoDB table, commit could
not happen, if the query was not written to the binary log (for example, if
--log-bin was not used, or binlog-ignore-db was
used). (Bug #578)
DROP TABLE statements to its binary log, then the 4.0.13
slave would not notice the temporary tables have to be dropped, until the
slave mysqld server is restarted. This minor inconvenience is
fixed in 3.23.57 and 4.0.14 (meaning the master must be upgraded to
3.23.57 and the slave to 4.0.14 to remove the inconvenience). (Bug #254)
MASTER_POS_WAIT() was waiting, and the slave was idle, and
the slave SQL thread terminated, MASTER_POS_WAIT() would wait
forever. Now when the slave SQL thread terminates,
MASTER_POS_WAIT() immediately returns NULL (``slave
stopped''). (Bug #651)
RESET SLAVE; START SLAVE;, the Relay_Log_Space value
displayed by SHOW SLAVE STATUS was too big by four bytes. (Bug #763)
--replicate-ignore-table and other similar rules), the slave
still checked if the query got the same error code (0, no error) as on
the master. So if the master had an error on the query (for example,
``Duplicate entry'' in a multiple-row insert), then the slave stopped
and warned that the error codes didn't match. (Bug #797)
Functionality added or changed:
PRIMARY KEY now implies NOT NULL. (Bug #390)
--enable-local-infile to match the Unix build configuration.
mysql-test-run. time does not
accept all required parameters on many platforms (for example, QNX) and timing
the tests is not really required (it's not a benchmark anyway).
SHOW MASTER STATUS and SHOW SLAVE STATUS required the
SUPER privilege; now they accept REPLICATION CLIENT as well.
(Bug #343)
MyISAM repair optimization and
myisam_repair_threads variable to enable it.
See section 5.2.3 Server System Variables.
innodb_max_dirty_pages_pct variable which controls amount of
dirty pages allowed in InnoDB buffer pool.
CURRENT_USER() and Access denied error messages now report the
hostname exactly as it was specified in the GRANT command.
InnoDB tables now support 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 is the default behavior in MySQL 4.1. (Bug #152)
delayed_insert_timeout on Linux (most modern glibc
libraries have a fixed pthread_cond_timedwait()). (Bug #211)
max_delayed_threads. (Bug #211)
UPDATE ... LIMIT to apply the limit to rows that were matched,
whether or not they actually were changed. Previously the limit was applied
as a restriction on the number of rows changed.
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 15.5 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 binary logs when invoked with
the --master-data or --first-slave option;
while this behavior was convenient for some
users, others may suffer from it. Now you must explicitly ask for binary
logs to be deleted by using 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 are not replicated.
Bugs fixed:
Access denied error message had incorrect Using password
value. (Bug #398)
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 verify that they
work as you expected after upgrading MySQL to this version. (Bug #291)
mysql parser not to erroneously interpret `'' or `"'
characters within /* ... */ comment as beginning a quoted string.
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 now reads 000000 as a zero date instead of
"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.
mysql monitor program. It occurred if
program was started with --no-defaults, with a prompt that contained
the hostname and a connection to a non-existent database 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 column to CHAR. (Bug #283)
SELECT and wildcarded select list,
when user only had partial column SELECT privileges on the table.
MyISAM table as "analyzed" only when all the keys are indeed
analyzed.
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.
(Bug #183, Bug #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 columns and at least one BLOB/TEXT column. Bug was caused
by incorrect calculation of the needed buffer to pack data.
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 used only
current thread information instead of information from the linked list of threads.
InnoDB tables as well.
mysqldump when it was called with
--master-data: the CHANGE MASTER TO commands appended to
the SQL dump had incorrect 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-writable config files.
SHOW PROCESSLIST now includes the client TCP port after the
hostname to make it easier to know from which client the request
originated.
--new option can be used to make a 4.0 server return
TIMESTAMP as a string in 'YYYY-MM-DD HH:MM:SS'
format, the way that 4.1 servers do.
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)
InnoDB
tables. This bug occurred as, in many cases, InnoDB tables cannot
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 okay.
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 (although they were, but only
internally). (Bug #70)
LOAD DATA.
ANALYZE procedure with error.
CHAR(0) columns that could cause incorrect
results from the query.
AUTO_INCREMENT column,
as a secondary column in a multi-column key (see section 3.6.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 (such as
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 MySQL 4.0.2). This change was required to comply
with the SQL standard. (The original change was made because we thought
that standard SQL required NULL to be always sorted at the same position,
but this was incorrect).
START TRANSACTION (standard SQL 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:
InnoDB tables.
BLOB NOT NULL columns used with IS NULL.
InnoDB storage engine.
BLOB columns with long strings.
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 incorrect values when used in OUTER JOIN.
MIN(key_column) and MAX(key_column) could return incorrect
values if one of the tables was empty.
MyISAM tables with blobs.
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 forces 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.
mysqld server should now compile on NetWare.
GROUP BY ... ORDER BY NULL
then result is not sorted.
--ft-stopword-file command-line option for mysqld to
replace/disable the built-in stopword list that is used in full-text searches.
See section 5.2.3 Server System Variables.
glibc that requires a
stack size larger than 128K
for 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 simply replaces
MySQL.rpm.
replicate_wild_do_table=db.% or
replicate_wild_ignore_table=db.%, these rules are 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 column and also
uses LAST_INSERT_ID().
ORDER BY constant_expression.
myisamchk 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 an AUTO_INCREMENT column 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 columns then the slave data is corrupted and you
should reinitialize the affected tables from the master.
BLOB value larger than 16M
to the client.
NOT NULL column 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 for MyISAM tables treats all NULL
values as different when calculating cardinality. This helps in
optimizing 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.
MyISAM after each query. This reduces
memory in case you have a lot of big blobs in a table.
Bugs fixed:
mysqld server to die if the remote
hostname can't be resolved. This is now 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 read by
mysql_options(MYSQL_READ_DEFAULT_FILE).
for clients.
InnoDB tables now also support 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 the cache because of low memory).
mysqlcheck so it can deal with table names containing dashes.
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 12.8.3 Information Functions.
CHECK constraints to be compatible with standard SQL. This made
CHECK a reserved word. (Checking of CHECK constraints is
still not implemented).
CAST(... as CHAR).
LIMIT syntax:
SELECT ... LIMIT row_count OFFSET offset
mysql_change_user() now resets the connection to the state
of a fresh connect (Ie, ROLLBACK any active transaction, close
all temporary tables, reset all user variables etc..)
CHANGE MASTER and RESET SLAVE now require that slave
threads both be stopped; these commands return an error
if at least one of these two threads is running.
Bugs fixed:
multi table updates
--lower-case-table-names default on Mac OS X as the default
filesystem (HFS+) is case insensitive.
See section 9.2.2 Identifier Case Sensitivity.
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 also solved problems with LEFT JOIN.
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 multiple-table-update and
multiple-table-delete.
--replicate-do=xxx didn't work for UPDATE commands.
(Bug introduced in 4.0.0)
InnoDB bugs in REPLACE, AUTO_INCREMENT,
INSERT INTO ... SELECT ... were fixed. See the InnoDB changelog
in the InnoDB section of the manual.
RESET SLAVE caused a crash if the slave threads were running.
Functionality added or changed:
SHOW PROCESSLIST
command
WEEK() so that you can get
week number according to the ISO 8601 specification.
(Old code should still work).
INSERT DELAYED threads don't hang on Waiting for
INSERT when one sends a SIGHUP to mysqld.
AND works according to standard SQL when it comes to
NULL handling. In practice, this affects only queries where you
do something like WHERE ... NOT (NULL AND 0).
mysqld now resolves basedir to its full path (with
realpath()). This enables one to use relative symlinks to the
MySQL installation directory. This however causes 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 TABLE, it gets the table structure from the `.frm' file, so the
table can be repaired even if the .MYI header is corrupted.
MAX() optimization when used with JOIN and
ON expressions.
BETWEEN behavior changed (see section 12.1.3 Comparison Functions and 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
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.
InnoDB now supports also isolation levels
READ UNCOMMITTED and READ COMMITTED.
For a detailed InnoDB changelog, see section D.9 InnoDB Change History
in this manual.
Bugs fixed:
MAX() optimization 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 TABLE
or REPAIR TABLE.
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 columns.
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 row_count
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 1GB.
INSERT on a table with an
AUTO_INCREMENT key which was not in the first part of the key.
LOAD DATA INFILE to not re-create 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. You 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 does 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 reworked in a future release.
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 tbl_name
SELECT expression(),...
SELECT * FROM table-list GROUP BY ... and
SELECT DISTINCT * FROM ....
--slow-log when logging an administrator command
(like FLUSH TABLES).
OPTIMIZE TABLE 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 initialized.
MyISAM index file on update if not strictly necessary.
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 enumeration to allow it to be
set 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 columns 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 initialization 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.tbl_name.col_name=''.
DROP DATABASE
SET [GLOBAL | SESSION] syntax to change thread-specific and global
system 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 read_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 12.4.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
tbl_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 applies to 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 12.8.2 Encryption Functions.
AES_ENCRYPT() and AES_DECRYPT() functions to perform
encryption according to AES standard (Rijndael).
See section 12.8.2 Encryption Functions.
--single-transaction option to mysqldump, allowing a
consistent dump of InnoDB tables.
See section 8.8 The mysqldump Database Backup Program.
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 optimizer uses only indexes.
ALTER TABLE tbl_name RENAME new_tbl_name is as fast
as RENAME TABLE.
GROUP BY with two or more columns, where at least one
column 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 UNION. It works 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.
const tables.
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 optimization.
... 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 makes
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 12.7 Cast Functions and Operators.
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 5.6.4 Limiting Account 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(str) now returns the characters in str 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 tbl_name 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() initialization so that RAND(N) and
RAND(N+1) are more distinct.
UPDATE ... ORDER BY.
INSERT INTO ... SELECT always had IGNORE enabled.
Now MySQL stops (and possibly rolls 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 system variables.
ft_min_word_len, ft_max_word_len, and
ft_max_word_len_for_sort variables to myisamchk.
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 behavior 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(date,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 row_count.
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 variable as a synonym for the LAST_INSERT_ID
variable (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 optimization.
--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.10.3 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
ISAM library which is tuned for SQL and supports large files.
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.1.x and 5.0.x versions. Only serious bugfixes are added to 3.23.
MERGE tables
(even one MERGE table and MyISAM tables), that could've
resulted in a crash or hang of the server. (Bug #2408)
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)
ALTER TABLE RENAME. (Bug #3270)
ACOS(), ASIN() (Bug #2338)
and in FLOOR() (Bug #3051).
The cause of the problem is an overly strong optimization done by gcc
in this case.
INSERT ... SELECT statements where, if a NOT NULL
column is assigned a value of NULL, the following columns in the row
might be assigned a value of zero. (Bug #2012)
--replicate-ignore-table and other similar rules), the slave
still checked if the query got the same error code (0, no error) as on
the master. So if the master had an error on the query (for example,
``Duplicate entry'' in a multiple-row insert), then the slave stopped
and warned that the error codes didn't match. This is a backport of the
fix for MySQL 4.0. (Bug #797)
mysqlbinlog now asks for a password at console when the
-p/--password option is used with no argument. This is how the
other clients (mysqladmin, mysqldump..)
behave. Note that one now has to use mysqlbinlog -p<my_password>;
mysqlbinlog -p <my_password> does not work anymore (in other
words, put no space after -p). (Bug #1595)
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_connect() (which
required a compromised DNS server and certain operating systems). (Bug #4017)
ALTER privilege on the
mysql.user table to execute random code or to gain shell access with
the UID of the mysqld process (thanks to Jedi/Sector One for spotting and
reporting this bug).
mysqldump now correctly quotes all identifiers when communicating
with the server. This assures that during the dump process, mysqldump
never sends queries to the server that result in a syntax error. This
problem is not related to the mysqldump program's output,
which was not changed. (Bug #1148)
MyISAM and ISAM when a row is updated
in a table with a large number of columns and at least one BLOB/TEXT
column.
SHOW CREATE TABLE is always larger than the data length.
The only known application that was affected by the old behavior was
Borland dbExpress, which truncated the output from the command.
(Bug #1064)
ISAM bug in MAX() optimization.
Unknown error when doing ORDER BY on reference table which
was used with NULL value on NOT NULL column. (Bug #479)
mysqld (option file group name and
service name).
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 data file. (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 15.5 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 columns and at least one BLOB/TEXT column. Bug was caused
by incorrect calculation of the needed buffer to pack data.
TRUNCATE tbl_name or
DELETE FROM tbl_name statements, which could cause an INSERT to
tbl_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 behavior was convenient for some
users, others may suffer from it. Now you must 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 the column level.
HAVING with GROUP BY.
WHERE clause. (Bug #142)
myisamchk) crash on artificially
corrupted `.MYI' files.
mysqld no longer reads options from world-writable config files.
mysqld and safe_mysqld now use only 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 incorrect
results from the query.
SHOW VARIABLES on 64-bit platforms. The bug was
caused by incorrect 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 (such as
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() optimization in MERGE tables.
RAND() initialization 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=1 (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 you must log in 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.6 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
filesystem (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 col_name = key_col_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 uppercase letters.
--skip-show-database 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 lowercase.
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 TABLE 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).
InnoDB.
BEGIN/COMMIT around transaction in the binary log.
This makes replication honor transactions.
user.db
table.
RAND() 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() to return 0 for Duplicates value 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 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.
datadir
parameter to mysqld.
<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.
mysqld to die in free().
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.
mysqld with
--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.
MERGE table, DELETE FROM merge_table used without a
WHERE clause no longer clears the mapping for the table by emptying
the `.MRG' file. Instead, it deletes records from the mapped tables.
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 optimizer 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 now also generally
run faster on all platforms.
InnoDB now prints after crash recovery the
latest MySQL binary log name and the offset InnoDB was able to recover
to. This is useful, for example, when resynchronizing 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 expr[,expr]...
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 now use index when doing an
ORDER BY on the whole table.
BDB tables.
ANALYZE TABLE, REPAIR TABLE, 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), 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 data files and raw disk partitions bigger
than 4GB on those operating systems that have big files.
InnoDB calculates better table cardinality estimates for the
MySQL optimizer.
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.
INSERT DELAYED and FLUSH TABLES introduced
in 3.23.42.
SELECT with
many tables and multi-column indexes and 'range' type.
EXPLAIN SELECT when using
many tables and ORDER BY.
LOAD DATA FROM MASTER when using table with
CHECKSUM=1.
BDB tables.
BDB tables and UNIQUE columns defined
as NULL.
myisampack when using pre-space filled CHAR
columns.
--safe-user-create.
LOCK TABLES and BDB tables.
REPAIR TABLE on MyISAM tables with row
lengths in the range from 65517 to 65520 bytes.
mysqladmin shutdown when there was
a lot of activity in other threads.
INSERT DELAYED where delayed thread could be
hanging on upgrading locks for no apparent reason.
myisampack and BLOB.
MERGE table come from the same
database.
LOAD DATA INFILE and transactional tables.
INSERT DELAYED with wrong column definition.
REPAIR TABLE of some particularly broken tables.
InnoDB and AUTO_INCREMENT columns.
InnoDB and RENAME TABLE columns.
InnoDB and BLOB columns. If you have
used BLOB columns larger than 8000 bytes in an InnoDB
table, it is necessary to dump the table with mysqldump, drop it and
restore it from the dump.
InnoDB when one could get the error Can't
execute the given command... even when no transaction was active.
ALTER TABLE.) Now --lower_case_table_names
also works on Unix.
--sql-mode=value[,value[,value]] option to mysqld.
See section 5.2.1 mysqld Command-Line Options.
shutdown on Solaris where the
`.pid' file wasn't deleted.
InnoDB now supports < 4GB rows. The former limit was 8000 bytes.
doublewrite file flush method is used in InnoDB.
It reduces the need for Unix fsync() calls to a fraction and
improves performance on most Unix flavors.
InnoDB Monitor to print a lot of InnoDB state
information, including locks, to the standard output. This is useful in
performance tuning.
InnoDB have been fixed.
record_buffer to record_buffer and
record_rnd_buffer. To make things compatible to previous MySQL
versions, if record_rnd_buffer is not set, then it takes the
value of record_buffer.
ORDER BY where some ORDER BY parts
where wrongly removed.
ALTER TABLE and MERGE tables.
my_thread_init() and my_thread_end() to
`mysql_com.h'
--safe-user-create option to mysqld.
SELECT DISTINCT ... HAVING that caused error message
Can't find record in #...
--low-priority-updates and INSERT statements.
slave_net_timeout for replication.
UPDATE and BDB tables.
BDB tables when using key parts.
GRANT FILE ON database.* ...; previously
we added the DROP privilege for the database.
DELETE FROM tbl_name ... LIMIT 0 and
UPDATE FROM tbl_name ... LIMIT 0, which acted as though the
LIMIT clause was not present (they deleted or updated all selected
rows).
CHECK TABLE now checks whether an AUTO_INCREMENT column contains
the value 0.
SIGHUP to mysqld now only flushes the logs,
but does not reset the replication.
1.0e1 (no sign after e).
--force to myisamchk now also updates states.
--warnings to mysqld. Now mysqld
prints the error Aborted connection only if this option is used.
SHOW CREATE TABLE when you didn't have a
PRIMARY KEY.
innodb_unix_file_flush_method variable to
innodb_flush_method.
BIGINT UNSIGNED to DOUBLE. This caused
a problem when doing comparisons with BIGINT values outside of the
signed range.
BDB tables when querying empty tables.
COUNT(DISTINCT) with LEFT JOIN and
there weren't any matching rows.
GEMINI table
type. GEMINI is not released under an Open Source license.
AUTO_INCREMENT sequence wasn't reset when dropping
and adding an AUTO_INCREMENT column.
CREATE ... SELECT now creates non-unique indexes delayed.
LOCK TABLES tbl_name READ followed by
FLUSH TABLES put an exclusive lock on the table.
REAL @variable values were represented with only 2 digits when
converted to strings.
LOAD TABLE FROM MASTER failed.
myisamchk --fast --force no longer repairs tables
that only had the open count wrong.
-lcma thread library on HP-UX 10.20 so
that MySQL is more stable on HP-UX.
IF() and number of decimals in the result.
INSERT DELAYED was waiting for
a LOCK TABLE.
InnoDB when tablespace was full.
MERGE tables and big tables (larger than 4GB)
when using ORDER BY.
SELECT from MERGE table
sometimes results in incorrectly ordered rows.
REPLACE() when using the ujis character set.
BDB patches 3.2.9.1 and 3.2.9.2.
--skip-stack-trace option to mysqld.
CREATE TEMPORARY now works with InnoDB tables.
InnoDB now promotes sub keys to whole keys.
CONCURRENT to LOAD DATA.
max_allowed_packet is too low to
read a very long log event from the master.
SELECT DISTINCT ... HAVING.
SHOW CREATE TABLE now returns TEMPORARY for temporary tables.
Rows_examined to slow query log.
WHERE that didn't match any rows.
mysqlcheck.
CHECK
TABLE, REPAIR TABLE, OPTIMIZE TABLE.
InnoDB.
SELECT * FROM tbl_name, tbl_name2 ... ORDER BY key_part1 LIMIT row_count
use an index on key_part1 instead of filesort.
LOCK TABLE to_table WRITE,...; INSERT INTO to_table... SELECT ...
when to_table was empty.
LOCK TABLE and BDB tables.
MATCH() in HAVING clause.
HEAP tables with LIKE.
--mysql-version option to safe_mysqld
INNOBASE to InnoDB (because the INNOBASE
name was in use). All configure options and mysqld
start options now use innodb instead of innobase. This
means that before upgrading to this version, you have to change any
configuration files where you have used innobase options!
CHAR(255) NULL columns.
master-host is not set, as
long as server-id is set and valid `master.info' is present.
SET SQL_SLAVE_SKIP_COUNTER=1; SLAVE START after a manual sanity
check/correction of data integrity.
REGEXP on 64-bit machines.
UPDATE and DELETE with WHERE unique_key_part IS NULL
didn't update/delete all rows.
INSERT DELAYED for tables that support transactions.
TEXT/BLOB column
with wrong date format.
ALTER TABLE and LOAD DATA INFILE that disabled
key-sorting. These commands should now be faster in most cases.
FLUSH or REPAIR TABLE) would not use indexes for the
next query.
ALTER TABLE to InnoDB tables on FreeBSD.
mysqld variables myisam_max_sort_file_size and
myisam_max_extra_sort_file_size.
InnoDB.
tis620 character set to make comparisons
case-independent and to fix a bug in LIKE for this character set.
Note: All tables that uses the tis620 character set must be
fixed with myisamchk -r or REPAIR TABLE !
--skip-safemalloc option to mysqld.
mysqld is run
as root.
FLUSH TABLES and TEMPORARY tables.
(Problem with freeing the key cache and error Can't reopen table....)
InnoDB with other character sets than latin1
and another problem when using many columns.
DISTINCT and summary functions.
SET TRANSACTION ISOLATION LEVEL ...
SELECT ... FOR UPDATE.
UPDATE where keys weren't always used to find the
rows to be updated.
CONCAT_WS() where it returned incorrect results.
CREATE ... SELECT and INSERT ... SELECT to not
allow concurrent inserts as this could make the binary log hard to repeat.
(Concurrent inserts are enabled if you are not using the binary or update log.)
glibc 2.2.
ORDER BY.
CLIENT_TRANSACTIONS.
SHOW VARIABLES when using INNOBASE tables.
SELECT DISTINCT didn't work.
SHOW ANALYZE for small tables.
run-all-tests.
INNOBASE support
to be compiled.
INNOBASE storage engine and the BDB storage engine
to the MySQL source distribution.
GEMINI tables.
INSERT DELAYED that caused threads to hang when
inserting NULL into an AUTO_INCREMENT column.
CHECK TABLE / REPAIR TABLE that could cause
a thread to hang.
REPLACE would not replace a row that conflicts
with an AUTO_INCREMENT generated key.
mysqld now only sets CLIENT_TRANSACTIONS in
mysql->server_capabilities if the server supports a
transaction-safe storage engine.
LOAD DATA INFILE to allow numeric values to be read into
ENUM and SET columns.
ALTER TABLE ... ORDER BY.
max_user_connections variable to mysqld.
max_allowed_packet, not the
arbitrary limit of 4MB.
= in argument to --set-variable.
Waiting for table.
SHOW CREATE TABLE now displays the UNION=() for MERGE
tables.
ALTER TABLE now remembers the old UNION=() definition.
BDB storage engine that occurred when using an index
on multiple-part key where a key part may be NULL.
MAX() optimization on sub-key for BDB tables.
BDB
tables and BLOB or TEXT fields when joining many tables.
BDB tables and TEXT columns.
BLOB key where a const row wasn't found.
mysqlbinlog writes the timestamp value for each query.
This ensures that one gets same values for date functions like NOW()
when using mysqlbinlog to pipe the queries to another server.
--skip-gemini, --skip-bdb, and --skip-innodb
options to be specified when invoking mysqld, even if these storage
engines are not compiled in to mysqld.
ASC and DESC with GROUP BY columns
to specify a sort order.
SET code, when one ran SET @foo=bar,
where bar is a column reference, an error was not properly generated.
--character-sets-dir option to myisampack.
REPAIR TABLE ... EXTENDED.
GROUP BY on an alias,
where the alias was the same as an existing column name.
SEQUENCE() as an example UDF function.
mysql_install_db to use BINARY for CHAR
columns in the privilege tables.
TRUNCATE tbl_name to TRUNCATE TABLE tbl_name
to use the same syntax as Oracle. Until 4.0 we also allow
TRUNCATE tbl_name to not crash old code.
MyISAM tables when a BLOB was
first part of a multiple-part key.
CASE didn't work with GROUP BY.
--sort-recover option to myisamchk.
myisamchk -S and OPTIMIZE TABLE now work on Windows.
DISTINCT on results from functions that referred
to a group function, like:
SELECT a, DISTINCT SEC_TO_TIME(SUM(a)) FROM tbl_name GROUP BY a, b;
libmysqlclient library.
Fixed bug in handling STOP event after ROTATE event in
replication.
DROP DATABASE.
Table_locks_immediate and Table_locks_waited status
variables.
SET SQL_SLAVE_SKIP_COUNTER=n command to recover from
replication glitches without a full database copy.
max_binlog_size variable; the binary log is rotated
automatically when the size crosses the limit.
Last_Error, Last_Errno, and Slave_skip_counter
variables to SHOW SLAVE STATUS.
MASTER_POS_WAIT() function.
SIGILL, and SIGBUS in addition to
SIGSEGV.
mysqltest to take care of the timing issues in the test
suite.
ALTER TABLE can now be used to change the definition for a
MERGE table.
MERGE tables on Windows.
--temp-pool option to mysqld. Using this option
causes most temporary files created to use a small set of names,
rather than a unique name for each new file. This is to work around a
problem in the Linux kernel dealing with creating a bunch of new files
with different names. With the old behavior, Linux seems to "leak"
memory, as it's being allocated to the directory entry cache instead of
the disk cache.
BACKUP TABLE, RESTORE TABLE, CHECK TABLE, REPAIR TABLE, and
ANALYZE TABLE.
FULL to SHOW COLUMNS. Now we show the
privilege list for the columns only if this option is given.
SHOW LOGS when there weren't any BDB logs.
mysql_list_fields(). This is
to keep this code compatible with SHOW FIELDS.
MERGE tables didn't work on Windows.
SET PASSWORD=... on Windows.
TRIM("foo" from "foo") didn't return an empty string.
--with-version-suffix option to configure.
mysql_close().
RESTORE TABLE when trying to restore from a non-existent
directory.
SET PASSWORD.
MASTER_POS_WAIT() function.
BDB interface code. During
testing we found and fixed many errors in the interface code.
HAVING on an empty table could produce one result row when
it shouldn't.
HEAP tables on Windows.
SHOW TABLE STATUS didn't show correct average row length for tables
larger than 4GB.
CHECK TABLE ... EXTENDED didn't check row links for fixed size tables.
MEDIUM to CHECK TABLE.
DECIMAL() keys on negative numbers.
HOUR() (and some other TIME functions) on a CHAR column
always returned NULL.
setrlimit() on Linux to get
-O --open_files_limit=# to work on Linux.
bdb_version variable to mysqld.
SELECT ... FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=t2.aIn this case the test in the
WHERE clause was wrongly optimized away.
MyISAM when deleting keys with possible NULL
values, but the first key-column was not a prefix-compressed text column.
mysql.server to read the [mysql.server] option file group
rather than the [mysql_server] group.
safe_mysqld and mysql.server to also read the
server option section.
Threads_created status variable to mysqld.
SHOW OPEN TABLES command.
myisamdump works against old mysqld servers.
myisamchk -k# so that it works again.
LOCK TABLES now automatically starts a new transaction.
BDB tables to not use internal subtransactions and reuse
open files to get more speed.
--mysqld=# option to safe_mysqld.
--fields-*-by and
--lines-terminated-by options to mysqldump and
mysqlimport. By Paul DuBois.
--safe-show-database option to mysqld.
have_bdb, have_gemini, have_innobase,
have_raid and have_openssl to SHOW VARIABLES to make it
easy to test for supported extensions.
--open-files-limit option to mysqld.
--open-files option to --open-files-limit in
safe_mysqld.
HEAP tables
that had many keys.
--bdb-no-sync works.
--bdb-recover to --bdb-no-recover as recover should
be on by default.
BDB locks to 10000.
BDB tables.
mysqld_multi.sh to use configure variables. Patch by
Christopher McCrory.
--skip-networking on Debian Linux.
UNOPENED in error messages.
SHOW LOGS queries.
<=> operator.
REPLACE with BDB tables.
LPAD() and RPAD() shortens the result string if it's longer
than the length argument.
SHOW LOGS command.
BDB logs on shutdown.
PRIMARY keys first, followed by
UNIQUE keys.
UPDATE involving multiple-part keys where you
specified all key parts both in the update and the WHERE part. In
this case MySQL could try to update a record that didn't match
the whole WHERE part.
mysqld to report the
hostname as '' in some error messages.
HEAP type tables; the variable
max_heap_table_size wasn't used. Now either MAX_ROWS or
max_heap_table_size can be used to limit the size of a HEAP
type table.
server-id value to 1 for masters and 2 for slaves
to make it easier to use the binary log.
bdb_lock_max variable to bdb_max_lock.
AUTO_INCREMENT on sub-fields for BDB tables.
ANALYZE TABLE of BDB tables.
BDB tables, we now store the number of rows; this helps to optimize
queries when we need an approximation of the number of rows.
ROLLBACK when you have updated a non-transactional table
you get an error as a warning.
--bdb-shared-data option to mysqld.
Slave_open_temp_tables status variable to mysqld
binlog_cache_size and max_binlog_cache_size variables to
mysqld.
DROP TABLE, RENAME TABLE, CREATE INDEX and
DROP INDEX are now transaction endpoints.
DROP DATABASE on a symbolically linked database, both
the link and the original database are deleted.
DROP DATABASE to work on OS/2.
SELECT DISTINCT ... table1 LEFT JOIN
table2 ... when table2 was empty.
--abort-slave-event-count and
--disconnect-slave-event-count options to mysqld for
debugging and testing of replication.
SHOW KEYS now shows whether key is FULLTEXT.
mysqld_multi. See section 5.1.5 The mysqld_multi Program for Managing Multiple MySQL Servers.
mysql-multi.server.sh. Thanks to
Tim Bunce Tim.Bunce@ig.co.uk for modifying mysql.server to
easily handle hosts running many mysqld processes.
safe_mysqld, mysql.server, and mysql_install_db have
been modified to use mysql_print_defaults instead of various hacks
to read the `my.cnf' files. In addition, the handling of various
paths has been made more consistent with how mysqld handles them
by default.
FULLTEXT indexes in one table.
REPAIR
TABLE/OPTIMIZE TABLE.
Yuri Dario.
FLUSH TABLES tbl_name didn't always flush the index tree
to disk properly.
--bootstrap is now run in a separate thread. This fixes a problem
that caused mysql_install_db to core dump on some Linux machines.
mi_create() to use less stack space.
MATCH() when used
with UNIQUE key.
crash-me and the MySQL benchmarks to also work
with FrontBase.
RESTRICT and CASCADE after DROP TABLE to make
porting easier.
--slow-log.
connect_timeout variable to mysql and mysqladmin.
connect-timeout as an alias for timeout for option files
read by mysql_options().
--pager[=...], --no-pager,
--tee=... and --no-tee to the mysql client. The
new corresponding interactive commands are pager, nopager,
tee and notee. See section 8.3 mysql, the Command-Line Tool, mysql --help
and the interactive help for more information.
MyISAM table failed.
SELECT, UPDATE and INSERT statements
constantly were running. The symptom was that the UPDATE and
INSERT queries were locked for a long time while new SELECT
statements were executed before the updates.
options_files with mysql_options() the
return-found-rows option was ignored.
interactive-timeout in the option file that
is read by mysql_options(). This makes it possible to force
programs that run for a long time (like mysqlhotcopy) to use the
interactive_timeout time instead of the wait_timeout time.
--log-long-format then also queries that
do not use an index are logged, even if the query takes less than
long_query_time seconds.
LEFT JOIN which caused all columns in a reference
table to be NULL.
NATURAL JOIN without keys.
TEXT or BLOB.
DROP of temporary tables wasn't stored in the update/binary log.
SELECT DISTINCT * ... LIMIT row_count only returned one row.
strstr() for SPARC and cleaned up
the `global.h' header file to avoid a problem with bad aliasing with
the compiler submitted with Red Hat 7.0. (Reported by Trond Eivind Glomsrød)
--skip-networking option now works properly on NT.
ISAM tables when a row with a length
of more than 65KB was shortened by a single byte.
MyISAM when running multiple updating processes on
the same table.
FLUSH TABLE tbl_name.
--replicate-ignore-table, --replicate-do-table,
--replicate-wild-ignore-table, and --replicate-wild-do-table
options to mysqld.
IO_CACHE mechanism instead of
FILE to avoid OS problems when there are many files open.
--open-files and --timezone options to safe_mysqld.
CREATE TEMPORARY TABLE ... SELECT ....
CREATE TABLE ... SELECT NULL.
large_file_support,net_read_timeout,
net_write_timeout and query_buffer_size to SHOW VARIABLES.
Created_tmp_files and Sort_merge_passes
to SHOW STATUS.
FOREIGN KEY definition.
TRUNCATE tbl_name as a synonym for
DELETE FROM tbl_name.
BDB key compare function when comparing part keys.
bdb_lock_max variable to mysqld.
mysql_connect() now aborts on Linux if the server doesn't answer in
timeout seconds.
SLAVE START did not work if you started with
--skip-slave-start and had not explicitly run CHANGE MASTER TO.
SHOW MASTER STATUS to be consistent with
SHOW SLAVE STATUS. (It now has no directory in the log name.)
PURGE MASTER LOGS TO.
SHOW MASTER LOGS statement to display a list of binary log files.
--safemalloc-mem-limit option to mysqld to simulate memory
shortage when compiled with the --with-debug=full option.
SHOW SLAVE STATUS was using an uninitialized mutex if the slave had
not been started yet.
ELT() and MAKE_SET() when the query used
a temporary table.
CHANGE MASTER TO without specifying MASTER_LOG_POS would
set it to 0 instead of 4 and hit the magic number in the master binary log.
ALTER TABLE ... ORDER BY ... syntax added. This creates the
new table with the rows in a specific order.
MyISAM tables sometimes failed
when the data file was corrupt.
SHOW CREATE when using AUTO_INCREMENT columns.
BDB tables to use new compare function in Berkeley DB 3.2.3.
latin5 (turkish) character set.
FLUSH MASTER and FLUSH SLAVE to RESET MASTER
and RESET SLAVE.
<> to work properly with NULL.
SUBSTRING_INDEX() and REPLACE().
(Patch by Alexander Igonitchev)
CREATE TEMPORARY TABLE IF NOT EXISTS not to produce an error
if the table exists.
PRIMARY KEY in a BDB table, a hidden
PRIMARY KEY is created.
BDB tables.
LEFT JOIN in some cases preferred a full table scan when there was
no WHERE clause.
--log-slow-queries, don't count the time waiting for a lock.
MyISAM tables if you start mysqld with
--myisam-recover.
TYPE= keyword from CHECK TABLE and
REPAIR TABLE. Allow CHECK TABLE options to be combined. (You can still
use TYPE=, but this usage is deprecated.)
--replicate-rewrite-db option to mysqld.
--skip-slave-start option to mysqld.
INSERT INTO foo(some_key)
values (1),(1)) erroneously terminated the slave thread.
DISTINCT is used only on columns
from some of the tables.
1e1).
SHOW GRANTS didn't always show all column grants.
--default-extra-file=# option to all MySQL clients.
INSERT statements now are initialized properly.
UPDATE didn't always work when used with a range on a timestamp that
was part of the key that was used to find rows.
FULLTEXT index when inserting a NULL column.
mkstemp() instead of tempnam(). Based
on a patch from John Jones.
databasename works as second argument to mysqlhotcopy.
UMASK and UMASK_DIR environment variables
now can be specified in octal by beginning the value with a zero.
RIGHT JOIN. This makes RIGHT a reserved word.
@@IDENTITY as a synonym for LAST_INSERT_ID().
(This is for MSSQL compatibility.)
myisamchk and REPAIR TABLE when using FULLTEXT
index.
LOAD DATA INFILE now works with FIFOs.
(Patch by Toni L. Harbaugh-Blackford.)
FLUSH LOGS broke replication if you specified a log name with an
explicit extension as the value of the log-bin option.
MyISAM with packed multiple-part keys.
CHECK TABLE on Windows.
FULLTEXT index always used the koi8_ukr
character set.
CHECK TABLE.
MyISAM repair/reindex code didn't use the --tmpdir
option for its temporary files.
BACKUP TABLE and RESTORE TABLE.
CHANGE MASTER TO when the slave did not have
the master to start with.
Time in the processlist for Connect of
the slave thread.
FLUSH MASTER if you didn't specify
a filename argument to --log-bin.
--memlock option to mysqld to lock mysqld
in memory on systems with the mlockall() call (as in Solaris).
HEAP tables didn't use keys properly. (Bug from 3.23.23.)
MERGE tables (keys, mapping, creation,
documentation...). See section 14.2 The MERGE Storage Engine.
mysqldump from 3.23 which caused some CHAR columns
not to be quoted.
analyze, check, optimize and repair code.
OPTIMIZE TABLE is now mapped to REPAIR TABLE with statistics and
sorting of the index tree. This means that for the moment it only
works on MyISAM tables.
ORDER BY bug with BDB tables.
mysqld couldn't remove the `.pid' file
under Windows.
--log-isam to log MyISAM tables instead of isam
tables.
CHECK TABLE to work on Windows.
pwrite() safe on Windows.
Created_tmp_disk_tables variable to mysqld.
TIMESTAMP(X) columns, MySQL now reports columns with X
other than 14 or 8 to be strings.
latin1 as it was before MySQL 3.23.23.
Any table that was created or modified with 3.23.22 must be repaired if it has
CHAR columns that may contain characters with ASCII values greater than
128!
BDB tables and reading on a unique (not primary) key.
win1251 character set (it's now only marked deprecated).
REPAIR TABLE or myisamchk before use!
--core-file option to mysqld to get a core file on
Linux if mysqld dies on the SIGSEGV signal.
mysql now starts with option
--no-named-commands (-g) by default. This option can be
disabled with --enable-named-commands (-G). This may cause
incompatibility problems in some cases, for example, in SQL scripts that
use named commands without a semicolon, etc.! Long format commands
still work from the first line.
DROP TABLE statements at
the same time.
LEFT JOIN on an
empty table.
mysqld with incorrect options.
free() bug in mysqlimport.
MyISAM index handling of
DECIMAL/NUMERIC keys.
MyISAM tables. In some contexts,
usage of MIN(key_part) or MAX(key_part) returned an empty set.
mysqlhotcopy to use the new FLUSH TABLES table_list
syntax. Only tables which are being backed up are flushed now.
--enable-thread-safe-client so
that both non-threaded (-lmysqlclient) and threaded
(-lmysqlclient_r) libraries are built. Users who linked
against a threaded -lmysqlclient need to link against
-lmysqlclient_r now.
RENAME TABLE command.
NULL values in COUNT(DISTINCT ...).
ALTER TABLE, LOAD DATA INFILE on empty tables and
INSERT ... SELECT ... on empty tables to create non-unique indexes
in a separate batch with sorting. This makes these statements much
faster when you have many indexes.
ALTER TABLE now logs the first used insert_id correctly.
BLOB column.
DATE_ADD/DATE_SUB where it returned a datetime instead
of a date.
***DEAD*** in SHOW PROCESSLIST.
pthread_rwlock_rdlock code.
HEAP table, all rows
weren't always deleted.
HEAP tables for searches on a part
index.
SELECT on part keys to work with BDB tables.
INSERT INTO bdb_table ... SELECT to work with BDB tables.
CHECK TABLE now updates key statistics for the table.
ANALYZE TABLE now only updates tables that have been changed
since the last ANALYZE TABLE. Note that this is a new feature and tables
are not marked to be analyzed until they are updated in any way with
3.23.23 or newer. For older tables, you have to do CHECK TABLE
to update the key distribution.
CHECK TABLE, ANALYZE
TABLE,
REPAIR TABLE and SHOW CREATE commands.
CHANGE MASTER TO statement.
FAST, QUICK EXTENDED check types to
CHECK TABLES.
myisamchk so that --fast and
--check-only-changed are also honored with --sort-index and
--analyze.
LOAD TABLE FROM MASTER that did not lock the
table during index re-build.
LOAD DATA INFILE broke replication if the database was excluded
from replication.
SHOW SLAVE STATUS and SHOW MASTER STATUS.
SLAVE STOP now does not return until the slave thread actually exits.
MATCH() function and FULLTEXT index type
(for MyISAM files). This makes FULLTEXT a reserved word.
lex_hash.h is created properly for each MySQL
distribution.
MASTER and COLLECTION are not reserved words.
--slow-query-log didn't contain the whole queries.
BDB tables are rolled back if the
connection is closed unexpectedly.
gcc 2.96 (intel) and gcc 2.9
(IA-64) in gen_lex_hash.c.
host= in the
`my.cnf' file.
DATE_ADD()/DATE_SUB()
against a number.
-F, --fast for myisamchk. Added
-C, --check-only-changed option to myisamchk.
ANALYZE tbl_name to update key statistics for tables.
0x... to be regarded as integers by default.
SHOW PROCESSLIST.
auto-rehash on reconnect for the mysql client.
MyISAM, where the index file couldn't
get bigger than 64MB.
SHOW MASTER STATUS and SHOW SLAVE STATUS.
mysql_character_set_name() function to the
MySQL C API.
mysql_config script.
< or > with a char column that was only
partly indexed.
mysqladmin to use CREATE DATABASE and DROP
DATABASE statements instead of the old deprecated API calls.
chown warning in safe_mysqld.
ORDER BY that was introduced in 3.23.19.
DELETE FROM tbl_name to do a drop+create of
the table if we are in AUTOCOMMIT mode (needed for BDB tables).
ISAM/MyISAM
index files get full during an INSERT/UPDATE.
myisamchk didn't correctly update row checksum when used with
-ro (this only gave a warning in subsequent runs).
REPAIR TABLE so that it works with tables without indexes.
DROP DATABASE.
LOAD TABLE FROM MASTER is sufficiently bug-free to announce it as
a feature.
MATCH and AGAINST are now reserved words.
DELETE FROM tbl_name removed the `.frm' file.
SHOW CREATE TABLE.
MyISAM table
when doing update based on key on a table with many keys and some key changed
values.
ORDER BY can now use REF keys to find subsets of the rows
that need to be sorted.
print_defaults program to my_print_defaults
to avoid name confusion.
NULLIF() to work as required by standard SQL.
net_read_timeout and net_write_timeout as startup
parameters to mysqld.
myisamchk --sort-records
on a table with prefix compressed index.
pack_isam and myisampack to the standard MySQL
distribution.
BEGIN WORK (the same as BEGIN).
ORDER BY on a CONV() expression.
LOAD TABLE FROM MASTER.
FLUSH MASTER and FLUSH SLAVE.
FLUSH TABLES WITH READ LOCK to make a global lock suitable for
making a copy of MySQL data files.
CREATE TABLE ... SELECT ... PROCEDURE now works.
GROUP BY on VARCHAR/CHAR columns.
READ and a
WRITE lock.
myisamchk and RAID tables.
FIND_IN_SET() when the first argument was NULL.
LEFT JOIN and ORDER BY where the first
table had only one matching row.
duplicated key problem when doing big GROUP BY operations.
(This bug was probably introduced in 3.23.15.)
INNER JOIN to match standard SQL.
NATURAL JOIN syntax.
BDB interface.
--no-defaults and --defaults-file to
safe_mysqld.sh and mysql_install_db.sh.
USE INDEX works with PRIMARY keys.
BEGIN statement to start a transaction in AUTOCOMMIT mode.
AUTOCOMMIT mode
and if there is a pending transaction. If there is a pending transaction,
the client library gives an error before reconnecting to the server to
let the client know that the server did a rollback.
The protocol is still backward-compatible with old clients.
KILL now works on a thread that is locked on a 'write' to a dead client.
log-slave-updates option to mysqld, to allow
daisy-chaining the slaves.
pthread_t
is not the same as int.
INSERT DELAYED code when doing
ALTER TABLE.
INSERT DELAYED.
SLAVE START and SLAVE STOP statements.
TYPE=QUICK option to CHECK TABLE and to REPAIR TABLE.
REPAIR TABLE when the table was in use by other threads.
gdb when one does a lot of reconnects. This also improves
systems where you can't use persistent connections.
UPDATE IGNORE does not abort if an update results in a
DUPLICATE_KEY error.
CREATE TEMPORARY TABLE commands in the update log.
delay_key_write tables and CHECK TABLE.
--replicate-do-db and --replicate-ignore-db options to
mysqld, to restrict which databases get replicated.
SQL_LOG_BIN option.
mysqld as root, you must now use the --user=root option.
FLUSH TABLES command.
slow_launch_time variable and the Slow_launch_threads
status variable to mysqld. These can be examined with
mysqladmin variables and mysqladmin extended-status.
INET_NTOA() and INET_ATON().
IF() now depends on the second and
third arguments and not only on the second argument.
myisamchk could go into a loop when trying to
repair a crashed table.
INSERT DELAYED to update log if SQL_LOG_UPDATE=0.
REPLACE on HEAP tables.
SHOW VARIABLES output.
DELETE of many rows on a table with
compressed keys where MySQL scanned the index to find the rows.
CHECK TABLE on table with deleted keyblocks.
LAST_INSERT_ID() to update
a table with an AUTO_INCREMENT key.
NULLIF() function.
LOAD DATA INFILE on a table with
BLOB/TEXT columns.
MyISAM to be faster when inserting keys in sorted order.
EXPLAIN SELECT ... now also prints out whether MySQL needs to
create a temporary table or use file sorting when resolving the SELECT.
ORDER BY parts where the part is a
constant expression in the WHERE part. Indexes can now be used
even if the ORDER BY doesn't match the index exactly, as long as
all the unused index parts and all the extra ORDER BY
columns are constants in the WHERE clause. See section 7.4.5 How MySQL Uses Indexes.
UPDATE and DELETE on a whole unique key in the WHERE part
are now faster than before.
RAID_CHUNKSIZE to be in 1024-byte increments.
LOAD_FILE(NULL).
mysqlbinlog program for displaying binary log files in text
format.
mysql_real_escape_string() function to the MySQL C API.
CONCAT() where one of the arguments was a function
that returned a modified argument.
myisamchk, where it updated the header in
the index file when one only checked the table. This confused the
mysqld daemon if it updated the same table at the same time. Now
the status in the index file is only updated if one uses
--update-state. With older myisamchk versions you should
use --read-only when only checking tables, if there is the
slightest chance that the mysqld server is working on the table at the
same time!
DROP TABLE is logged in the update log.
DECIMAL() key field
where the column data contained leading zeros.
myisamchk when the AUTO_INCREMENT column isn't
the first key.
DATETIME in ISO8601 format: 2000-03-12T12:00:00
mysqld binary can now handle many different
character sets (you can choose which when starting mysqld).
REPAIR TABLE statement.
mysql_thread_safe() function to the MySQL C API.
UMASK_DIR environment variable.
CONNECTION_ID() function to return the client connection thread
ID.
= on BLOB or VARCHAR BINARY keys, where
only a part of the column was indexed, the whole column of the result
row wasn't compared.
sjis character set and ORDER BY.
GROUP BY part.
LOCK TABLE command; this fixed the problem one got when running
the test-ATIS test with --fast or --check-only-changed.
SQL_BUFFER_RESULT option to SELECT.
CHECK TABLE command.
MyISAM in 3.23.12 that didn't get into the source
distribution because of CVS problems.
mysqladmin shutdown waits for the local server
to close down.
print_defaults program to the `.rpm' files. Removed
mysqlbug from the client `.rpm' file.
MyISAM involving REPLACE ... SELECT ... which could
give a corrupted table.
myisamchk where it incorrectly reset the
AUTO_INCREMENT value.
DISTINCT on HEAP temporary tables to use hashed
keys to quickly find duplicated rows. This mostly concerns queries of
type SELECT DISTINCT ... GROUP BY .... This fixes a problem where
not all duplicates were removed in queries of the above type. In
addition, the new code is MUCH faster.
IF NOT EXISTS clause to CREATE DATABASE.
--all-databases and --databases options to mysqldump
to allow dumping of many databases at the same time.
DECIMAL() index in MyISAM tables.
mysqladmin shutdown on a local connection, mysqladmin
now waits until the PID file is gone before terminating.
COUNT(DISTINCT ...) queries.
myisamchk works properly with RAID tables.
LEFT JOIN and key_col IS NULL.
net_clear() which could give the error Aborted
connection in the MySQL clients.
USE INDEX (key_list) and IGNORE INDEX (key_list) as
parameters in SELECT.
DELETE and RENAME should now work on RAID tables.
HIGH_PRIORITY option to INSERT. This overrides the
effect of the --low-priority-updates server option and does not
perform concurrent inserts.
ALTER TABLE tbl_name ADD (field_list) syntax.
GRANT/REVOKE ALL PRIVILEGES doesn't affect
GRANT OPTION.
SHOW GRANTS.
UNIQUE INDEX in CREATE statements.
mysqlhotcopy - fast online hot-backup utility for local
MySQL databases. By Tim Bunce.
mysqlaccess. Thanks to Steve Harvey for this.
--i-am-a-dummy and --safe-updates options to mysql.
select_limit and max_join_size variables to mysql.
SQL_MAX_JOIN_SIZE and SQL_SAFE_UPDATES options.
READ LOCAL lock that doesn't lock the table for concurrent
inserts. (This is used by mysqldump.)
LOCK TABLES ... READ no longer allows concurrent
inserts.
--skip-delay-key-write option to mysqld.
_rowid can now be used as an alias for an integer type unique indexed
column.
SIGPIPE when compiling with --thread-safe-clients
to make things safe for old clients.
LOCK TABLES.
INSERT DELAYED.
date_col BETWEEN const_date AND const_date works.
NULL in a table with
BLOB/TEXT columns.
WHERE K1=1 and K3=2 and (K2=2 and K4=4 or K2=3 and K4=5)
source command to mysql to allow reading of batch files
inside the mysql client. Original patch by Matthew Vanecek.
WITH GRANT OPTION option.
GRANT error when using tables from many
databases in the same query.
SELECT when using many overlapping indexes.
MySQL should now be able to choose keys even better when there
are many keys to choose from.
=). For example, the following type of queries should now
be faster: SELECT * from key_part_1=const and key_part_2 > const2
VARCHAR columns to CHAR columns
didn't change row type from dynamic to fixed.
SELECT FLOOR(POW(2,63)).
mysqld startup option from --delay-key-write to
--delay-key-write-for-all-tables.
read-next-on-key to HEAP tables. This should fix all
problems with HEAP tables when using non-UNIQUE keys.
--log-slow-queries option to mysqld to log all queries
that take a long time to a separate log file with a time indicating how
long the query took.
WHERE key_col=RAND(...).
SELECT ... LEFT JOIN ... key_col IS NULL,
when key_col could contain NULL values.
LOAD DATA INFILE.
NISAM.
ISAM when doing some ORDER BY ... DESC queries.
--delay-key-write didn't enable delayed key writing.
TEXT column which involved only case changes.
INSERT DELAYED doesn't update timestamps that are given.
YEARWEEK() and options x, X, v and
V to DATE_FORMAT().
MAX(indexed_column) and HEAP tables.
BLOB NULL keys and LIKE "prefix%".
MyISAM and fixed-length rows < 5 bytes.
GROUP BY queries.
ENUM field value
was too big.
pthread_mutex_timedwait(),
which is used with INSERT DELAYED. See section 2.12.1 Linux Notes.
MyISAM with keys > 250 characters.
MyISAM one can now do an INSERT at the same time as other
threads are reading from the table.
max_write_lock_count variable to mysqld to force a
READ lock after a certain number of WRITE locks.
delay_key_write on show variables.
concurrency variable to thread_concurrency.
LOCATE(substr,str), POSITION(substr IN str),
LOCATE(substr,str,pos), INSTR(str,substr),
LEFT(str,len), RIGHT(str,len),
SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len),
MID(str,pos,len), SUBSTRING(str,pos), SUBSTRING(str
FROM pos), SUBSTRING_INDEX(str,delim,count), RTRIM(str),
TRIM([[BOTH | TRAILING] [remstr] FROM] str),
REPLACE(str,from_str,to_str), REVERSE(str),
INSERT(str,pos,len,newstr), LCASE(str), LOWER(str),
UCASE(str) and UPPER(str); patch by Wei He.
FULL to SHOW PROCESSLIST.
--verbose to mysqladmin.
HEAP to MyISAM.
HEAP tables when doing insert + delete + insert + scan the
table.
REPLACE() and LOAD DATA INFILE.
interactive_timeout variable to mysqld.
mysql_data_seek() from ulong to
ulonglong.
-O lower_case_table_names={0|1} option to mysqld to allow
users to force table names to lowercase.
SELECT ... INTO DUMPFILE.
--ansi option to mysqld to make some functions
standard SQL compatible.
#sql.
` (" in --ansi mode).
snprintf() when printing floats to avoid some buffer
overflows on FreeBSD.
FLOOR() overflow safe on FreeBSD.
--quote-names option to mysqldump.
PRIMARY KEY NOT NULL.
encrypt() to be thread-safe and not reuse buffer.
mysql_odbc_escape_string() function to support big5 characters in
MyODBC.
FLOAT and DOUBLE (without any length modifiers)
no longer are fixed decimal point numbers.
FLOAT(X): Now this is the same as FLOAT if
X <= 24 and a DOUBLE if 24 < X <= 53.
DECIMAL(X) is now an alias for DECIMAL(X,0) and DECIMAL
is now an alias for DECIMAL(10,0). The same goes for NUMERIC.
ROW_FORMAT={DEFAULT | DYNAMIC | FIXED | COMPRESSED} to
CREATE_TABLE.
DELETE FROM tbl_name didn't work on temporary tables.
CHAR_LENGTH() to be multi-byte character safe.
ORD(string).
SELECT DISTINCT ... ORDER BY RAND().
MyISAM
level.
ALTER TABLE didn't work.
AUTO_INCREMENT column in two keys
MyISAM, you now can have an AUTO_INCREMENT column as a key
sub part:
CREATE TABLE foo (a INT NOT NULL AUTO_INCREMENT, b CHAR(5), PRIMARY KEY (b,a))
MyISAM with packed char keys that could be NULL.
AS on field name with CREATE TABLE tbl_name SELECT ... didn't
work.
NATIONAL and NCHAR when defining character columns.
This is the same as not using BINARY.
NULL columns in a PRIMARY KEY (only in UNIQUE
keys).
LAST_INSERT_ID() if one uses this in ODBC:
WHERE auto_increment_column IS NULL. This seems to fix some problems
with Access.
SET SQL_AUTO_IS_NULL=0|1 now turns on/off the handling of
searching for the last inserted row with WHERE
auto_increment_column IS NULL.
concurrency to mysqld for Solaris.
--relative option to mysqladmin to make
extended-status more useful to monitor changes.
COUNT(DISTINCT ...) on an empty table.
LOAD DATA INFILE and BLOB columns.
~ (negation).
DATETIME into a TIME column no longer
try to store 'days' in it.
SUM().)
LIKE "%" on an index that may have NULL values.
REVOKE ALL PRIVILEGES didn't revoke all privileges.
GRANT option for a database, the account
couldn't grant privileges to other users.
SHOW GRANTS FOR user (by Sinisa).
date_add syntax: date/datetime + INTERVAL # interval_type.
By Joshua Chamas.
LOAD DATA REPLACE.
REGEXP is now case-insensitive if you use non-binary strings.
MyISAM.
ASC is now the default again for ORDER BY.
LIMIT to UPDATE.
mysql_change_user() function to the MySQL C API.
SHOW VARIABLES.
--[whitespace] comments.
INSERT INTO tbl_name VALUES (), that is, you may now specify
an empty value list to insert a row in which each column is set to its
default value.
SUBSTRING(text FROM pos) to conform to standard SQL. (Before this
construct returned the rightmost pos characters.)
SUM() with GROUP BY returned 0 on some systems.
SHOW TABLE STATUS.
DELAY_KEY_WRITE option to CREATE TABLE.
AUTO_INCREMENT on any key part.
YEAR(NOW()) and YEAR(CURDATE()).
CASE construct.
COALESCE() function.
SELECT * FROM tbl_name WHERE
key_part1 >= const AND (key_part2 = const OR key_part2 = const). The
bug was that some rows could be duplicated in the result.
myisamchk without -a updated the index
distribution incorrectly.
SET SQL_LOW_PRIORITY_UPDATES=1 was causing a parse error.
WHERE clause.
UPDATE tbl_name SET KEY=KEY+1 WHERE KEY > 100
'1999-01-00'.
SELECT ... WHERE key_part1=const1 AND
key_part_2=const2 AND key_part1=const4 AND
key_part2=const4; indextype
should be range instead of ref.
egcs 1.1.2 optimizer bug (when using BLOB values) on Linux Alpha.
LOCK TABLES combined with DELETE FROM table.
MyISAM tables now allow keys on NULL and BLOB/TEXT columns.
SELECT ... FROM t1 LEFT JOIN t2 ON ... WHERE t2.not_null_column IS NULL.
ORDER BY and GROUP BY can be done on functions.
ORDER BY RAND().
WHERE key_column = function.
WHERE key_column = col_name even if
the columns are not identically packed.
WHERE col_name IS NULL.
MyISAM tables)
HEAP temporary tables to MyISAM tables
in case of ``table is full'' errors.
--init-file=file_name option to mysqld.
COUNT(DISTINCT value, [value, ...]).
CREATE TEMPORARY TABLE now creates a temporary table, in its own
namespace, that is automatically deleted if connection is dropped.
CASE): CASE, THEN, WHEN, ELSE and END.
EXPORT_SET() and MD5().
MyISAM) with a lot of new features.
See section 14.1 The MyISAM Storage Engine.
HEAP tables which are extremely fast for
lookups.
LOAD_FILE(filename) to get the contents of a file as a
string value.
<=> operator that acts as = but returns TRUE
if both arguments are NULL. This is useful for comparing changes
between tables.
EXTRACT(interval FROM datetime) function.
FLOAT(X) are not rounded on storage and may be
in scientific notation (1.0 E+10) when retrieved.
REPLACE is now faster than before.
LIKE character comparison to behave as =;
This means that 'e' LIKE ''e' is now true. (If the line doesn't
display correctly, the latter 'e' is a French 'e' with an acute accent above.)
SHOW TABLE STATUS returns a lot of information about the tables.
LIKE to the SHOW STATUS command.
Privileges column to SHOW COLUMNS.
Packed and Comment columns to SHOW INDEX.
CREATE TABLE ... COMMENT 'xxx').
UNIQUE, as in
CREATE TABLE tbl_name (col INT NOT NULL UNIQUE)
CREATE TABLE tbl_name SELECT ...
CREATE TABLE IF NOT EXISTS ...
CHAR(0) columns.
DATE_FORMAT() now requires `%' before any format character.
DELAYED is now a reserved word (sorry about that :( ).
analyse, file: `sql_analyse.c'.
This describes the data in your query. Try the following:
SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([max elements,[max memory]])This procedure is extremely useful when you want to check the data in your table!
BINARY cast to force a string to be compared in case-sensitive fashion.
--skip-show-database option to mysqld.
UPDATE now also works
with BLOB/TEXT columns.
INNER join syntax. Note: This made INNER
a reserved word!
IP/NETMASK syntax.
NOT NULL DATE/DATETIME column with IS
NULL, this is changed to a compare against 0 to satisfy some ODBC
applications. (By shreeve@uci.edu.)
NULL IN (...) now returns NULL instead of 0. This
ensures that null_column NOT IN (...) doesn't match
NULL values.
TIME columns.
TIME strings to be more strict. Now the
fractional second part is detected (and currently skipped). The
following formats are supported:
DATETIME.
LOW_PRIORITY attribute to LOAD DATA INFILE.
LOAD DATA INFILE.
DECIMAL(x,y) now works according to standard SQL.
LAST_INSERT_ID() is now updated for INSERT INTO ... SELECT.
SELECT DISTINCT is much faster; it uses the new UNIQUE
functionality in MyISAM. One difference compared to MySQL 3.22
is that the output of DISTINCT is no longer sorted.
mysql_num_fields() on
a MYSQL object, you must use mysql_field_count() instead.
LIBWRAP; patch by Henning P. Schmiedehausen.
AUTO_INCREMENT for other than numerical columns.
AUTO_INCREMENT now automatically makes the column
NOT NULL.
NULL as the default value for AUTO_INCREMENT columns.
SQL_BIG_RESULT; SQL_SMALL_RESULT is now default.
--enable-large-files and --disable-large-files options
to configure. See `configure.in' for some systems where this is
automatically turned off because of broken implementations.
readline to 4.0.
CREATE TABLE options: PACK_KEYS and CHECKSUM.
--default-table-type option to mysqld.
The 3.22 version has faster and safer connect code than version 3.21, as well as a lot of new nice enhancements. As there aren't really any major changes, upgrading from 3.21 to 3.22 should be very easy and painless. See section 2.10.5 Upgrading from Version 3.21 to 3.22.
STD().
ISAM library from 3.23.
INSERT DELAYED.
LEFT JOIN/STRAIGHT_JOIN
on a table with only one row.
GROUP BY on TINYBLOB columns; this
caused bugzilla to not show rows in some queries.
LOCK TABLE.
SELECT DISTINCT queries.
mysqlhotcopy, a fast online hot-backup utility for local MySQL
databases. By Tim Bunce.
mysqlaccess. Thanks to Steve Harvey for this.
GROUP functions.
ISAM code when deleting rows on tables with
packed indexes.
SELECT when using many overlapping indexes.
SELECT FLOOR(POW(2,63)).
WITH GRANT OPTION option.
GROUP BY queries.
ENUM field value
was too big.
mysqlshutdown.exe and mysqlwatch.exe to the Windows
distribution.
ORDER BY on a reference key.
INSERT DELAYED doesn't update timestamps that are given.
LEFT JOIN and COUNT() on a column which
was declared NULL + and it had a DEFAULT value.
CONCAT() in a WHERE clause.
AVG() and STD() with NULL values.
BLOB columns.
ROUND() now works on Windows.
BLOB/TEXT column argument to
REVERSE().
/*! */ with version numbers.
SUBSTRING(text FROM pos) to conform to standard SQL. (Before this
construct returned the rightmost 'pos' characters.)
LOCK TABLES combined with DELETE FROM table
INSERT ... SELECT didn't use BIG_TABLES.
SET SQL_LOW_PRIORITY_UPDATES=# didn't work.
GRANT ... IDENTIFIED BY
SELECT * FROM tbl_name WHERE key_part1 >= const AND (key_part2 = const
OR key_part2 = const).
ISAM.
DATA is no longer a reserved word.
LOCK TABLES tbl_name READ; FLUSH TABLES;
isamchk should now work on Windows.
libtool 1.3.2.
configure.
--defaults-file=file_name to option file handling to force
use of only one specific option file.
CREATE syntax to ignore MySQL 3.23 keywords.
INSERT DELAYED on a table locked with
LOCK TABLES.
DROP TABLE on a table that was
locked by another thread.
GRANT/REVOKE commands in the update log.
isamchk to detect a new error condition.
NATURAL LEFT JOIN.
mysql_close() directly after
mysql_init().
delayed_insert_thread counting when you couldn't create a new
delayed_insert thread.
CONCAT() with many arguments.
DELETE FROM TABLE when table was locked by
another thread.
LEFT JOIN involving empty tables.
mysql.db column from CHAR(32) to CHAR(60).
MODIFY and DELAYED are no longer reserved words.
TIME column.
Host '...' is not allowed to connect to this MySQL
server after one had inserted a new MySQL user with a GRANT
command.
TCP_NODELAY also on Linux (should give faster TCP/IP
connections).
STD() for big tables when result should be 0.
INSERT DELAYED had some garbage at end in the update log.
mysql_install_db (from 3.22.17).
BLOB
columns.
shutdown
not all threads died properly.
-O flush_time=# to mysqld. This is mostly
useful on Windows and tells how often MySQL should close all
unused tables and flush all updated tables to disk.
VARCHAR column compared with CHAR column
didn't use keys efficiently.
--log-update and connecting
without a default database.
configure and portability problems.
LEFT JOIN on tables that had circular dependencies caused
mysqld to hang forever.
mysqladmin processlist could kill the server if a new user logged in.
DELETE FROM tbl_name WHERE key_column=col_name didn't find any matching
rows. Fixed.
DATE_ADD(column, ...) didn't work.
INSERT DELAYED could deadlock with status upgrading lock.
ENCRYPT() to take longer salt strings than 2 characters.
longlong2str is now much faster than before. For Intel x86
platforms, this function is written in optimized assembler.
MODIFY keyword to ALTER TABLE.
GRANT used with IDENTIFIED BY didn't take effect until privileges
were flushed.
SHOW STATUS.
ORDER BY with 'only index' optimization when there
were multiple key definitions for a used column.
DATE and DATETIME columns are now up to 5 times faster than
before.
INSERT DELAYED can be used to let the client do other things while the
server inserts rows into a table.
LEFT JOIN USING (col1,col2) didn't work if one used it with tables
from 2 different databases.
LOAD DATA LOCAL INFILE didn't work in the Unix version because of
a missing file.
VARCHAR/BLOB on very short rows (< 4 bytes);
error 127 could occur when deleting rows.
BLOB/TEXT through formulas didn't work for short (< 256 char)
strings.
GRANT on a new host, mysqld could die on the first
connect from this host.
ORDER BY on column name that was the same
name as an alias.
BENCHMARK(loop_count,expression) function to time expressions.
mysqld to make it easier to start from shell
scripts.
TIMESTAMP column to NULL didn't record the timestamp
value in the update log.
INSERT INTO TABLE ... SELECT ... GROUP BY.
localtime_r() on Windows so that it no longer crashes
if your date is > 2039, but instead returns a time of all zero.
^Z (ASCII 26) to \Z as ^Z doesn't
work with pipes on Windows.
mysql_fix_privileges adds a new column to the mysql.func to
support aggregate UDF functions in future MySQL releases.
NOW(), CURDATE() or CURTIME() directly in a
column didn't work.
SELECT COUNT(*) ... LEFT JOIN ... didn't work with no WHERE part.
pthread_cond() on the Windows version.
get_lock() now correctly times out on Windows!
DATE_ADD() and DATE_SUB() in a
WHERE clause.
GRANT ... TO user
IDENTIFIED BY 'password' syntax.
GRANT checking with SELECT on many tables.
mysql_fix_privilege_tables to the RPM
distribution. This is not run by default because it relies on the client
package.
SQL_SMALL_RESULT to SELECT to force use of
fast temporary tables when you know that the result set is small.
DATE_ADD/DATE_SUB() doesn't have enough days.
GRANT compares columns in case-insensitive fashion.
ALTER TABLE dump core in
some contexts.
user@hostname can now include `.' and `-'
without quotes in the context of the GRANT, REVOKE and
SET PASSWORD FOR ... statements.
isamchk for tables which need big temporary files.
mysql_fix_privilege_tables script
when you upgrade to this version! This is needed because of the new
GRANT system. If you don't do this, you get Access
denied when you try to use ALTER TABLE, CREATE INDEX, or
DROP INDEX.
GRANT to allow/deny users table and column access.
USER() to return a value in user@host format.
Formerly it returned only user.
PASSWORD for another user.
FLUSH STATUS that resets most status variables to zero.
aborted_threads, aborted_connects.
connection_timeout.
SET SQL_WARNINGS=1 to get a warning count also for simple
(single-row) inserts.
SIGTERM instead of SIGQUIT with
shutdown to work better on FreeBSD.
\G (print vertically) to mysql.
SELECT HIGH_PRIORITY ... killed mysqld.
IS NULL on a AUTO_INCREMENT column in a LEFT JOIN didn't
work as expected.
MAKE_SET().
mysql_install_db no longer starts the MySQL server! You
should start mysqld with safe_mysqld after installing it! The
MySQL RPM , however, starts the server as before.
--bootstrap option to mysqld and recoded
mysql_install_db to use it. This makes it easier to install
MySQL with RPMs.
+, - (sign and minus), *, /, %,
ABS() and MOD() to be BIGINT aware (64-bit safe).
ALTER TABLE that caused mysqld to crash.
INSERT.)
INSERT INTO tbl_name SET col_name=value, col_name=value, ...
MYSQL_INIT_COMMAND to mysql_options() to make
a query on connect or reconnect.
MYSQL_READ_DEFAULT_FILE and
MYSQL_READ_DEFAULT_GROUP to mysql_options() to read the
following parameters from the MySQL option files: port,
socket, compress, password, pipe, timeout,
user, init-command, host and database.
maybe_null to the UDF structure.
IGNORE to INSERT statements with many rows.
koi8 character sets; users of
koi8 must run isamchk -rq on each table that has an
index on a CHAR or VARCHAR column.
mysql_setpermission, by Luuk de Boer. It allows easy
creation of new users with permissions for specific databases.
LOAD DATA INFILE).
SHOW STATUS and changed format of output to
be like SHOW VARIABLES.
extended-status command to mysqladmin which shows the
new status variables.
SET SQL_LOG_UPDATE=0 caused a lockup of the server.
FLUSH [ TABLES | HOSTS | LOGS | PRIVILEGES ] [, ...]
KILL thread_id.
ALTER TABLE from a INT
to a short CHAR() column.
SELECT HIGH_PRIORITY; this gets a lock for the
SELECT even if there is a thread waiting for another
SELECT to get a WRITE LOCK.
wild_compare() to string class to be able to use LIKE on
BLOB/TEXT columns with \0.
ESCAPE option to LIKE.
mysqladmin debug.
mysqld on Windows with the --flush option.
This flushes all tables to disk after each update. This makes things
much safer on the Windows platforms but also much slower.
DA