GNU Info

Info Node: (mysql.info)Bugs

(mysql.info)Bugs


Prev: Commit-rollback Up: Compatibility
Enter node , (file) or (file)node

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