The following problems are known:
If you compare a
NULL value to a
the subquery returns an empty result, the comparison might
evaluate to the nonstandard result of
NULL rather than to
issue has been fixed in MySQL 5.0 and later (Bug #8804).
Subquery optimization for
IN is not as
effective as for
Even if you use
lower_case_table_names=2 (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
doesn't work in replication because the constraint may
have another name on the slave.
BY doesn't work inside
GROUP_CONCAT() if you don't
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. We plan to
fix this by having the
TABLE statement wait until the table is not
being used in any transaction.
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.
001207 22:07:56 bdb: log_flush: LSN past current end-of-log
BDB table on which you are running
multiple-statement transactions until all those
transactions complete. (The transaction might be ignored.)
LOCK TABLE ... and
FLUSH TABLES ... doesn't guarantee that
there isn't a half-finished transaction in progress on the
BDB tables are relatively slow to open.
If you have many
BDB tables in a
database, it takes a long time to use the
mysql client on the database if you are
not using the
-A option or if you are
rehash. This is especially
noticeable when you have a large table cache.
Replication uses query-level 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.
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 inserted data.
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
A query is optimized differently on the master and slave only if:
The files used by the two queries are not exactly the
same; for example,
TABLE was run on the master tables and not
on the slave tables. (To fix this,
ANALYZE TABLE, and
REPAIR TABLE are
written to the binary log as of MySQL 4.1.1).
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
the master, but
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.
In future MySQL versions, we will automatically add an
ORDER BY clause when needed.
The following issues are known and will be fixed in due time:
Log file names are based on the server host name (if you
don't specify a file name with the startup option). You
have to use options such as
if you change your host name to something else. Another
option is to rename the old files to reflect your host
name change (if these are binary logs, you need to edit
the binary log index file and fix the binary log file
names there as well). See
Section 5.1.2, “Server Command Options”.
mysqlbinlog does not delete temporary
files left after a
INFILE statement. See
Section 4.6.6, “mysqlbinlog — Utility for Processing Binary Log Files”.
RENAME doesn't 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, or enumerations. This is scheduled to be fixed in
version 5.1 when we implement new table definition format
SET CHARACTER SET, you can't
use translated characters in database, table, and column
You can't use “
If you have a
column in which the same number is stored in different
formats (for example,
GROUP BY may regard each value as a
You cannot build the server in another directory when using MIT-pthreads. Because this requires changes to MIT-pthreads, we are not likely to fix this. See Section 2.9.6, “MIT-pthreads Notes”.
TEXT values can't
“reliably” be used in
ORDER BY or
DISTINCT. Only the first
max_sort_length bytes are
used when comparing
values in these cases. The default value of
max_sort_length value is
1024 and can be changed at server startup time. As of
MySQL 4.0.3, it can be changed at runtime. For older
versions, a workaround is to use a substring. For example:
SELECT DISTINCT LEFT(
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
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.
MySQL Server 4.0 has better
BIGINT handling than 3.23.
mysqld_safe redirects all messages from
mysqld to the mysqld
log. One problem with this is that if you execute
mysqladmin refresh to close and reopen
stderr are still redirected to the old
log. If you use the general query log extensively, you
should edit mysqld_safe to log to
so that you can easily reclaim the space for the old log
by deleting it and executing mysqladmin
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 doesn't 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
don't participate in the
comparison. We will probably change this in the future to
never compare the hidden columns when executing
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 the hidden
id column may differ).
Note that this happens only for queries where that do not
ORDER BY columns in the
If you execute a
PROCEDURE on a query
that returns an empty set, in some cases the
PROCEDURE does not transform the
Creation of a table of type
doesn't check whether the underlying tables are compatible
If you use
ALTER TABLE to
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.