GNU Info
(mysql.info)Bugs
Known errors and design deficiencies in MySQL
---------------------------------------------
The following problems are known and have a very high priority to get
fixed:
* `ANALYZE TABLE' on a BDB table may in some case make the table
unusable until one has restarted `mysqld'. When this happens you
will see errors like the following 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 not completed multi-statement transactions. (The
transaction will probably be ignored).
* `ANALYZE TABLE', `OPTIMIZE TABLE' and `REPAIR TABLE' may cause
problems on tables for which you are using `INSERT DELAYED'.
* Doing a `LOCK TABLE ..' and `FLUSH TABLES ..' doesn't guarantee
that there isn't a half-finished transaction in progress on the
table.
* BDB tables are a bit slow to open. If you have many BDB tables in a
database, it will take 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 notable when you have a big table
cache.
* Th current replication protocol cannot deal with `LOAD DATA INFILE'
and line terminator characters of more than 1 character.
The following problems are known and will be fixed in due time:
* For the moment `MATCH' only works with `SELECT' statements.
* When using `SET CHARACTER SET', one can't use translated
characters in database, table and column names.
* `DELETE FROM merge_table' used without a `WHERE' will only clear
the mapping for the table, not delete everything in the mapped
tables
* You cannot build in another directory when using MIT-pthreads.
Because this requires changes to MIT-pthreads, we are not likely
to fix this.
* `BLOB' values can't "reliably" be used in `GROUP BY' or `ORDER BY'
or `DISTINCT'. Only the first `max_sort_length' bytes (default
1024) are used when comparing `BLOB'bs in these cases. This can
be changed with the `-O max_sort_length' option to `mysqld'. A
workaround for most cases is to use a substring: `SELECT DISTINCT
LEFT(blob,2048) FROM tbl_name'.
* Calculation is done with `BIGINT' or `DOUBLE' (both are normally
64 bits long). It depends on the function which precision one
gets. The general rule is that bit functions are done with `BIGINT'
precision, `IF', and `ELT()' with `BIGINT' or `DOUBLE' precision
and the rest with `DOUBLE' precision. One should try to avoid
using bigger unsigned long long values than 63 bits
(9223372036854775807) for anything else than bit fields!
* All string columns, except `BLOB' and `TEXT' columns, automatically
have all trailing spaces removed when retrieved. For `CHAR' types
this is okay, and may be regarded as a feature according to ANSI
SQL92. The bug is that in MySQL, `VARCHAR' columns are treated the
same way.
* You can only have up to 255 `ENUM' and `SET' columns in one table.
* `safe_mysqld' re-directs 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 `safe_mysqld' to log to
`'hostname'.err' instead of `'hostname'.log' so you can easily
reclaim the space for the old log by deleting the old one and
executing `mysqladmin refresh'.
* In the `UPDATE' statement, columns are updated from left to right.
If you refer to an updated column, you will get the updated value
instead of the original value. For example:
mysql> UPDATE tbl_name SET KEY=KEY+1,KEY=KEY+1;
This will update `KEY' with `2' instead of with `1'.
* You can't use temporary tables more than once in the same query.
For example, the following doesn't work.
select * from temporary_table, temporary_table as t2;
* `RENAME' doesn't work with `TEMPORARY' tables.
* The optimizer may handle `DISTINCT' differently if you are using
'hidden' columns in a join or not. In a join, hidden columns are
counted as part of the result (even if they are not shown) while in
normal queries hidden columns doesn'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 you may in MySQL 3.23.x get two identical rows
in the result set (because the hidden 'id' column may differ).
Note that the this only happens for queries where you don't have
the ORDER BY columns in the result, something that is you are not
allowed to do in ANSI SQL.
* Because MySQL allows you to work with table types that doesn't
support transactions (and thus can't `rollback' data) some things
behaves a little different in MySQL than in other SQL servers:
(This is just to ensure that MySQL never need to do a rollback for
a SQL command). This may be a little awkward at times as column
Because MySQL allows you to work with table types that don't
support transactions, and thus can't `rollback' data, some things
behave a little differently in MySQL than in other SQL servers.
This is just to ensure that MySQL never need to do a rollback for
a SQL command. This may be a little awkward at times as column
values must be checked in the application, but this will actually
give you a nice speed increase as it allows MySQL to do some
optimizations that otherwise would be very hard to do.
If you set a column to an incorrect value, MySQL will, instead of
doing a rollback, store the `best possible value' in the column:
- If you try to store a value outside the range in a numerical
column, MySQL will instead store the smallest or biggest
possible value in the column.
- If you try to store a string that doesn't start with a number
into a numerical column, MySQL will store 0 into it.
- If you try to store `NULL' into a column that doesn't take
`NULL' values, MySQL will store 0 or `''' (empty string) in
it instead. (This behavior can, however, be changed with the
-DDONT_USE_DEFAULT_FIELDS compile option).
- MySQL allows you to store some wrong date values into `DATE'
and `DATETIME' columns. (Like 2000-02-31 or 2000-02-00). If
the date is totally wrong, MySQL will store the special
0000-00-00 date value in the column.
- If you set an `enum' to an unsupported value, it will be set
to the error value 'empty string', with numeric value 0.
* If you execute a `PROCEDURE' on a query that returns an empty set,
in some cases the `PROCEDURE' will not transform the columns.
* Creation of a table of type `MERGE' doesn't check if the underlying
tables are of compatible types.
* MySQL can't yet handle `NaN', `-Inf' and `Inf' values in double.
Using these will cause problems when trying to export and import
data. We should as an intermediate solution change `NaN' to `NULL'
(if possible) and `-Inf' and `Inf' to the Minimum respective
maximum possible `double' value.
* `LIMIT' on negative numbers are treated as big positive numbers.
* If you use `ALTER TABLE' to first add an `UNIQUE' index to a table
used in a `MERGE' table and then use `ALTER TABLE' to add a normal
index on the `MERGE' table, the key order will be different for
the tables if there was an old not-unique key in the table. This
is because `ALTER TABLE' puts `UNIQUE' keys before normal keys to
be able to detect duplicate keys as early as possible.
The following are known bugs in earlier versions of MySQL:
* You can get a hung thread if you do a `DROP TABLE' on a table that
is one among many tables that is locked with `LOCK TABLES'.
* In the following case you can get a core dump:
- Delayed insert handler has pending inserts to a table.
- `LOCK table' with `WRITE'
- `FLUSH TABLES'
* Before MySQL Version 3.23.2 an `UPDATE' that updated a key with a
`WHERE' on the same key may have failed because the key was used to
search for records and the same row may have been found multiple
times:
UPDATE tbl_name SET KEY=KEY+1 WHERE KEY > 100;
A workaround is to use:
mysql> UPDATE tbl_name SET KEY=KEY+1 WHERE KEY+0 > 100;
This will work because MySQL will not use index on expressions in
the `WHERE' clause.
* Before MySQL Version 3.23, all numeric types where treated as
fixed-point fields. That means you had to specify how many
decimals a floating-point field shall have. All results were
returned with the correct number of decimals.
For platform-specific bugs, see the sections about compiling and
porting.
automatically generated by info2www version 1.2.2.9