( Open bugs

Info Catalog ( Errors in 4 ( Bugs Open Bugs and Design Deficiencies 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.0.
    * Even if you are using `lower_case_table_names=2' (which enables
      MySQL to remember the used case for databases and table names)
      MySQL will not on case insensitive systems remember the used case
      for database names for the function `DATABASE()' or in various
    * 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
      in 5.0 by having the `DROP TABLE' wait until the table is not used
      in any transaction.
    * When inserting a big integer value (between 2^63 and 2^64-1) into a
      decimal/string column, it is inserted as a negative value because
      the number is evaluated in a signed integer context. We plan to
      fix this in MySQL 4.1.
    * `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, you will
      see errors of the following form in the MySQL error file:
           001207 22:07:56  bdb:  log_flush: LSN past current end-of-log
    * MySQL accepts parentheses in the `FROM' clause of a `SELECT'
      statement, but silently ignores them.  The reason for not giving
      an error is that many clients that automatically generate queries
      add parentheses in the `FROM' clause even where they are not
    * Concatenating many `RIGHT JOINS' or combining `LEFT' and `RIGHT'
      join in the same query may not give a correct answer because MySQL
      only generates `NULL' rows for the table preceding a `LEFT' or
      before a `RIGHT' join.  This will be fixed in 5.0 at the same time
      we add support for parentheses in the `FROM' clause.
    * Don't execute `ALTER TABLE' on a `BDB' table on which you are
      running multiple-statement transactions until all those
      transactions complete.  (The transaction will probably be ignored.)
      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
    * `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 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.
      Although we have never heard of it actually occurring, it is
      theoretically 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; that is, left to the will
      of the query optimizer. (That generally is not a good practice
      anyway, 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 all these 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 will result
      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 will be optimized
      differently on the master and slave only if:
         - The files used by the two queries are not exactly the same;
           for example, `OPTIMIZE TABLE' was run on the master tables
           and not on the slave tables. (To fix this, `OPTIMIZE TABLE',
           `ANALYZE TABLE', and `REPAIR TABLE' are written to the binary
           log as of MySQL 4.1.1).
         - 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
      The easiest way to avoid this problem in all cases is to add an
      `ORDER BY' clause to such 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 problems 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 like `--log-bin=OLD_HOST_NAME-bin' if you change your
      hostname to something else. Another option is to just rename the
      old files to reflect your hostname change (and if these are binary
      logs, you will also need to edit the binary log index file and fix
      the binlog names there).  Server options.
    * `mysqlbinlog' will not delete temporary files left after a `LOAD
      DATA INFILE' command.  `mysqlbinlog' mysqlbinlog.
    * `RENAME' doesn't work with `TEMPORARY' tables or tables used in a
      `MERGE' table.
    * Due to the way table definition 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 have 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.  MIT-pthreads.
    * `BLOB' and `TEXT'values can't "reliably" be used in `GROUP BY' or
      `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' value is 1024. It can be changed at
      server startup time.  As of MySQL 4.0.3, it can also be changed at
      runtime. For older versions, a workaround for most cases is to use
      a substring.  For example:
    * 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 done 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 bigger
      than 63 bits (9223372036854775807) for anything other than bit
      fields.  MySQL Server 4.0 has better `BIGINT' handling than 3.23.
    * All string columns, except `BLOB' and `TEXT' columns, automatically
      have all trailing spaces removed when retrieved. For `CHAR' types,
      this is okay. The bug is that in MySQL Server, `VARCHAR' columns
      are treated the same way.
    * You can have only 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 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 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;
           SELECT DISTINCT band_downloads.mp3id
                  FROM band_downloads,band_mp3
                  WHERE band_downloads.userid = 9
                  AND = band_downloads.mp3id
                  ORDER BY DESC;
      In the second case, you might in MySQL Server 3.23.x 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 you don't have the
      `ORDER BY' columns in the result.
    * Because MySQL Server allows you to work with table types that don't
      support transactions, and thus can't roll back data, some things
      behave a little differently in MySQL Server than in other SQL
      servers.  This is just to ensure that MySQL Server never needs to
      do a rollback for an SQL statement.  This may be a little awkward
      at times because column values must be checked in the application,
      but this will actually give you a nice speed increase because it
      allows MySQL Server to do some optimizations that otherwise would
      be very hard to do.
      If you set a column to an incorrect value, MySQL Server will,
      instead of doing a rollback, store the "best possible value" in
      the column. For information about how this occurs, see 
    * 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 whether the
      underlying tables are of compatible types.
    * If you use `ALTER TABLE' first to add a `UNIQUE' index to a table
      used in a `MERGE' table and then to add a normal index on the
      `MERGE' table, the key order will be different for the tables if
      there was an old key that was not unique 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.
 The following are known bugs in earlier versions of MySQL:
    * 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 Server 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
      A workaround is to use:
      This will work because MySQL Server will not use an index on
      expressions in the `WHERE' clause.
    * Before MySQL Server 3.23, all numeric types were treated as
      fixed-point fields. That means that you had to specify how many
      decimals a floating-point field should have. All results were
      returned with the correct number of decimals.
 For information about platform-specific bugs, see the installation and
 porting instructions in  Operating System Specific Notes and
Info Catalog ( Errors in 4 ( Bugs
automatically generated byinfo2html