A.8.1. Open Issues in MySQL
The following problems are known and fixing them is a high
If you compare a
NULL value to a subquery
ALL/ANY/SOME and the subquery
returns an empty result, the comparison might evaluate to
the non-standard result of
This will be fixed in MySQL 5.1.
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
FOREIGN KEY constraint doesn't
work in replication because the constraint may have another
name on the slave.
REPLACE option) does not trigger
ON DELETE CASCADE.
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 command before the table is
used in the transaction itself. We plan to fix this by
DROP TABLE command wait until
the table is not being used in any transaction.
When inserting a big integer value (between
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.
FLUSH TABLES WITH READ LOCK does not
COMMIT if the server is running
without binary logging, which may cause a problem (of
consistency between tables) when doing a full backup.
ANALYZE TABLE on a
table may in some cases make the table unusable until you
restart mysqld. If this happens, look for
errors of the following form in the MySQL error file:
001207 22:07:56 bdb: log_flush: LSN past current end-of-log
ALTER TABLE on a
BDB table on which you are running
multiple-statement transactions until all those transactions
complete. (The transaction might be ignored.)
REPAIR TABLE may
cause problems on tables for which you are using
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 non-deterministic (generally not a
recommended practice, even outside of replication).
CREATE ... SELECT or
... SELECT statements that insert zero or
NULL values into an
DELETE if you are deleting rows from
a table that has foreign keys with
REPLACE ... SELECT,
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
INSERT ... SELECT with
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
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 (
and 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
The easiest way to avoid this problem is to add an
ORDER BY clause to the aforementioned
non-deterministic 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 filenames are based on the server hostname (if you don't
specify a filename with the startup option). You have to use
options such as
if you change your hostname to something else. Another
option is to rename the old files to reflect your hostname
change (if these are binary logs, you need to edit the
binary log index file and fix the binlog names there as
well). See Section 5.2.1, “mysqld Command Options”.
mysqlbinlog does not delete temporary
files left after a
LOAD DATA INFILE
command. See Section 8.8, “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 files.
SET CHARACTER SET, you can't
use translated characters in database, table, and column
You can't use ‘
If you have a
DECIMAL column in which the
same number is stored in different formats (for example,
GROUP BY may
regard each value as a different value.
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.8.5, “MIT-pthreads Notes”.
can't reliably be used in
ORDER BY or
Only the first
max_sort_length bytes are
used when comparing
BLOB values in these
cases. The default value of
max_sort_length is 1024 and can be
changed at server startup time or at runtime.
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 rest with
DOUBLE precision. You should try to avoid
using unsigned long long values if they resolve to be larger
than 63 bits (9223372036854775807) for anything other than
You can have up to 255
SET columns in one table.
other aggregate functions, MySQL currently compares
by their string value rather than by the string's relative
position in the set.
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
--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
UPDATE statement, 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 increments
tbl_name SET KEY=KEY+1,KEY=KEY+1;
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
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
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 where that do not
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
check whether the underlying tables are compatible types.
If you use
ALTER TABLE to add a
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.