(mysql.info) open-bugs
Info Catalog
(mysql.info) bugs
(mysql.info) bugs
A.8.1 Open Issues in MySQL
--------------------------
The following problems are known and fixing them is a high priority:
* If you compare a `NULL' value to a subquery using `ALL/ANY/SOME'
and the subquery returns an empty result, the comparison might
evaluate to the non-standard result of `NULL' rather than to
`TRUE' or `FALSE'. This will be fixed in MySQL 5.1.
* 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).
* Dropping a `FOREIGN KEY' constraint doesn't work in replication
because the constraint may have another name on the slave.
* `REPLACE' (and `LOAD DATA' with the `REPLACE' option) does not
trigger `ON DELETE CASCADE'.
* `DISTINCT' with `ORDER BY' doesn't work inside `GROUP_CONCAT()' if
you don't use all and only those columns that are in the
`DISTINCT' list.
* 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
having the `DROP TABLE' command 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.
* `FLUSH TABLES WITH READ LOCK' does not block `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 `BDB' 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
* Don't execute `ALTER TABLE' on a `BDB' table on which you are
running multiple-statement transactions until all those
transactions complete. (The transaction might be ignored.)
* `ANALYZE TABLE', `OPTIMIZE TABLE', and `REPAIR TABLE' may cause
problems on tables for which you are using `INSERT DELAYED'.
* Performing `LOCK TABLE ...' and `FLUSH TABLES ...' doesn't
guarantee that there isn't a half-finished transaction in progress
on the table.
* `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 using `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).
For example:
* `CREATE ... SELECT' or `INSERT ... SELECT' statements that
insert zero or `NULL' values into an `AUTO_INCREMENT' column.
* `DELETE' if you are deleting rows from a table that has
foreign keys with `ON DELETE CASCADE' properties.
* `REPLACE ... SELECT', `INSERT IGNORE ... SELECT' if you have
duplicate key values in the inserted data.
*If and only if the preceding queries have no `ORDER BY' clause
guaranteeing a deterministic order*.
For example, for `INSERT ... 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 master, but `MyISAM' on the slave if the
slave has less available disk space.)
* MySQL buffer sizes (`key_buffer_size', 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
`mysqlbinlog|mysql'.
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 -log-bin=OLD_HOST_NAME-bin 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 server-options.
* `mysqlbinlog' does not delete temporary files left after a `LOAD
DATA INFILE' command. See mysqlbinlog.
* `RENAME' doesn't work with `TEMPORARY' tables or tables used in a
`MERGE' table.
* Due to the way table format (`.frm') 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.
* When using `SET CHARACTER SET', you can't use translated
characters in database, table, and column names.
* You can't use ‘`_'’ or ‘`%'’ with `ESCAPE' in `LIKE ...
ESCAPE'.
* If you have a `DECIMAL' column in which the same number is stored
in different formats (for example, `+01.00', `1.00', `01.00'),
`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 mit-pthreads.
* `BLOB' and `TEXT' values can't reliably be used in `GROUP BY',
`ORDER BY' or `DISTINCT'. 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 `BIGINT' or `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
`BIGINT' precision, `IF' and `ELT()' with `BIGINT' or `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 bit
fields.
* You can have up to 255 `ENUM' and `SET' columns in one table.
* In `MIN()', `MAX()', and other aggregate functions, MySQL
currently compares `ENUM' and `SET' columns 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 the log, `stdout' and `stderr' are
still redirected to the old log. If you use -log extensively, you
should edit `mysqld_safe' to log to `HOST_NAME.err' instead of
`HOST_NAME.log' so that you can easily reclaim the space for the
old log by deleting it and executing `mysqladmin refresh'.
* In an `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 `KEY' by `2', *not* `1':
mysql> UPDATE 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:
mysql> SELECT * FROM temp_table, temp_table AS t2;
ERROR 1137: Can't reopen table: 'temp_table'
* The optimizer may handle `DISTINCT' 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 `DISTINCT' comparison. We will probably change
this in the future to never compare the hidden columns when
executing `DISTINCT'.
An example of this is:
SELECT DISTINCT mp3id FROM band_downloads
WHERE userid = 9 ORDER BY id DESC;
and
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 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 `MERGE' doesn't 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 on the `MERGE'
table, the key order is different for the tables if there was an
old, non-`UNIQUE' key in the table. This is because `ALTER TABLE'
puts `UNIQUE' indexes before normal indexes to be able to detect
duplicate keys as early as possible.
Info Catalog
(mysql.info) bugs
(mysql.info) bugs
automatically generated byinfo2html