Copyright (C) 2000-2012 |
GNU Info (mysql.info)BugsKnown 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 |