The following problems are known and have a very high priority to get fixed:
ANALYZE TABLEon 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
ALTER TABLEon a
BDBtable on which you are running not completed multi-statement transactions. (The transaction will probably be ignored).
LOCK TABLE ..and
FLUSH TABLES ..doesn't guarantee that there isn't a half-finished transaction in progress on the table.
mysqlclient on the database if you are not using the
-Aoption or if you are using
rehash. This is especially notable when you have a big table cache.
The following problems are known and will be fixed in due time:
MERGEtable doesn't include the current mapped tables.
MATCHonly works with
SET CHARACTER SET, one can't use translated characters in database, table and column names.
DELETE FROM merge_tableused without a
WHEREwill only clear the mapping for the table, not delete everything in the mapped tables
BLOBvalues can't ``reliably'' be used in
DISTINCT. Only the first
max_sort_lengthbytes (default 1024) are used when comparing
BLOBbs in these cases. This can be changed with the
-O max_sort_lengthoption to
mysqld. A workaround for most cases is to use a substring:
SELECT DISTINCT LEFT(blob,2048) FROM tbl_name.
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
DOUBLEprecision and the rest with
DOUBLEprecision. One should try to avoid using bigger unsigned long long values than 63 bits (9223372036854775807) for anything else than bit fields!
TEXTcolumns, automatically have all trailing spaces removed when retrieved. For
CHARtypes this is okay, and may be regarded as a feature according to ANSI SQL92. The bug is that in MySQL,
VARCHARcolumns are treated the same way.
SETcolumns in one table.
safe_mysqldre-directs all messages from
mysqldlog. One problem with this is that if you execute
mysqladmin refreshto close and reopen the log,
stderrare still redirected to the old log. If you use
--logextensively, you should edit
safe_mysqldto 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
UPDATEstatement, columns are updated from left to right. If you refer to a 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+1will update
2instead of with
select * from temporary_table, temporary_table as t2;
DISTINCTdifferently 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
DISTINCTcomparison. We will probably change this in the future to never compare the hidden columns when executing
DISTINCTAn 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.
rollbackdata, 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 akward 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 a wrong value, MySQL will, instead of doing a rollback, store the
best possible valuein the column:
NULLinto a column that doesn't take
NULLvalues, MySQL will store 0 or
''(empty string) in it instead. (This behavior can, however, be changed with the -DDONT_USE_DEFAULT_FIELDS compile option).
DATETIMEcolumns. (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.
enumto an unsupported value, it will be set to the error value 'empty string', with numeric value 0.
PROCEDUREon a query that returns an empty set, in some cases the
PROCEDUREwill not transform the columns.
MERGEdoesn't check if the underlying tables are of compatible types.
The following are known bugs in earlier versions of MySQL:
DROP TABLEon a table that is one among many tables that is locked with
UPDATEthat updated a key with a
WHEREon 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
For platform-specific bugs, see the sections about compiling and porting.
Go to the first, previous, next, last section, table of contents.