(mysql.info.gz) Open bugs
Info Catalog
(mysql.info.gz) Errors in 4
(mysql.info.gz) Bugs
1.5.7.3 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
logs.
* 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
needed.
* 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.)
* `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 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
`mysqlbinlog|mysql'.
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:
SELECT DISTINCT LEFT(BLOB_COL,2048) FROM TBL_NAME;
* 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;
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 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
Constraints.
* 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
times:
UPDATE TBL_NAME SET KEY=KEY+1 WHERE KEY > 100;
A workaround is to use:
UPDATE TBL_NAME SET KEY=KEY+1 WHERE KEY+0 > 100;
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
Porting.
Info Catalog
(mysql.info.gz) Errors in 4
(mysql.info.gz) Bugs
automatically generated byinfo2html