This section lists known issues in recent versions of MySQL.
The following problems are known:
Subquery optimization for
IN is not as
effective as for
Even if you use
(which enables MySQL to remember the case used for databases
and table names), MySQL does not remember the case used for
database names for the function
DATABASE() or within the
various logs (on case-insensitive systems).
FOREIGN KEY constraint does
not work in replication because the constraint may have
another name on the slave.
does not work inside
GROUP_CONCAT() if you do not
use all and only those columns that are in the
If one user has a long-running transaction and another user
drops a table that is updated in the transaction, there is
small chance that the binary log may contain the
DROP TABLE statement before
the table is used in the transaction itself.
When inserting a big integer value (between 263 and 264–1) into a decimal or string column, it is inserted as a negative value because the number is evaluated in a signed integer context.
LOCK TABLE ... and
FLUSH TABLES ... does not guarantee that
there isn't a half-finished transaction in progress on the
With statement-based binary logging, the master writes the executed queries to the binary log. This is a very fast, compact, and efficient logging method that works perfectly in most cases. However, it is possible for the data on the master and slave to become different if a query is designed in such a way that the data modification is nondeterministic (generally not a recommended practice, even outside of replication).
DELETE if you are
deleting rows from a table that has foreign keys with
ON DELETE CASCADE properties.
INSERT IGNORE ...
SELECT if you have duplicate key values in the
If and only if the preceding queries
ORDER BY clause guaranteeing a
For example, for
SELECT with no
ORDER BY, the
SELECT may return rows in a
different order (which results in a row having different
ranks, hence getting a different number in the
AUTO_INCREMENT column), depending on the
choices made by the optimizers on the master and slave.
A query is optimized differently on the master and slave only if:
The table is stored using a different storage engine on
the master than on the slave. (It is possible to use
different storage engines on the master and slave. For
example, you can use
InnoDB on the
MyISAM on the slave if
the slave has less available disk space.)
MySQL buffer sizes
so on) are different on the master and slave.
The master and slave run different MySQL versions, and the optimizer code differs between these versions.
This problem may also affect database restoration using mysqlbinlog|mysql.
The easiest way to avoid this problem is to add an
ORDER BY clause to the aforementioned
nondeterministic queries to ensure that the rows are always
stored or modified in the same order. Using row-based or
mixed logging format also avoids the problem.
Log file names are based on the server host name if you do
not specify a file name with the startup option. To retain
the same log file names if you change your host name to
something else, you must explicitly use options such as
See Section 5.1.3, “Server Command Options”. Alternatively, rename
the old files to reflect your host name change. If these are
binary logs, you must edit the binary log index file and fix
the binary log file names there as well. (The same is true
for the relay logs on a slave server.)
mysqlbinlog does not delete temporary
files left after a
INFILE statement. See
Section 4.6.7, “mysqlbinlog — Utility for Processing Binary Log Files”.
RENAME does not work with
TEMPORARY tables or tables used in a
Due to the way table format (
files are stored, you cannot use character 255
CHAR(255)) in table names, column names,
SET CHARACTER SET, you cannot
use translated characters in database, table, and column
You cannot use “
Only the first
max_sort_length bytes are
used when comparing data values. This means that values
cannot reliably be used in
ORDER BY or
if they are not distinct in the first
max_sort_length bytes. To
work around this, increase the variable value. The default
1024 and can be changed at server startup time or at
Numeric calculations are done with
DOUBLE (both are normally 64
bits long). Which precision you get depends on the function.
The general rule is that bit functions are performed with
DOUBLE precision, and the
You should try to avoid using unsigned long long values if
they resolve to be larger than 63 bits (9223372036854775807)
for anything other than bit fields.
columns are updated from left to right. If you refer to an
updated column, you get the updated value instead of the
original value. For example, the following statement
You can refer to multiple temporary tables in the same query, but you cannot refer to any given temporary table more than once. For example, the following does not work:
SELECT * FROM temp_table, temp_table AS t2;ERROR 1137: Can't reopen table: 'temp_table'
The optimizer may handle
differently when you are using “hidden” columns
in a join than when you are not. In a join, hidden columns
are counted as part of the result (even if they are not
shown), whereas in normal queries, hidden columns do not
participate in the
An example of this is:
SELECT DISTINCT mp3id FROM band_downloads WHERE userid = 9 ORDER BY id DESC;
SELECT DISTINCT band_downloads.mp3id FROM band_downloads,band_mp3 WHERE band_downloads.userid = 9 AND band_mp3.id = band_downloads.mp3id ORDER BY band_downloads.id DESC;
In the second case, using MySQL Server 3.23.x, you may get
two identical rows in the result set (because the values in
id column may differ).
Note that this happens only for queries that do not have the
ORDER BY columns in the result.
If you execute a
PROCEDURE on a query
that returns an empty set, in some cases the
PROCEDURE does not transform the columns.
Creation of a table of type
not check whether the underlying tables are compatible
If you use
ALTER TABLE to add
UNIQUE index to a table used in a
MERGE table and then add a normal index
MERGE table, the key order is
different for the tables if there was an old,
UNIQUE key in the table. This is
ALTER TABLE puts
UNIQUE indexes before normal indexes to
be able to detect duplicate keys as early as possible.