This section is a list of the known issues in recent versions of MySQL.
For information about platform-specific issues, see the installation and porting instructions in Sección 2.12, “Notas específicas sobre sistemas operativos” and Apéndice D, Portar a otros sistemas.
The following known issues have not been fixed in MySQL 3.23 for various reasons, and are not classified as critical.
-
Fixed in MySQL 4.0: Avoid using spaces at the end of column names because this can cause unexpected behavior. (Bug #4196)
-
Fixed in MySQL 4.0.12: You can get a deadlock (hung thread) if you use
LOCK TABLE
to lock multiple tables and then in the same connection useDROP TABLE
to drop one of them while another thread is trying to lock it. (To break the deadlock, you can useKILL
to terminate any of the threads involved.) -
Fixed in MySQL 4.0.11:
SELECT MAX(key_column) FROM t1,t2,t3...
where one of the tables are empty doesn't returnNULL
but instead returns the maximum value for the column. -
DELETE FROM heap_table
without aWHERE
clause doesn't work on a lockedHEAP
table.
The following known issues have not been fixed in MySQL 4.0 for various reasons, and are not classified as critical.
-
Fixed in MySQL 4.1.10: Using
HAVING
, you can get a crash or wrong result if you use an alias to aRAND()
function. This will not be fixed in 4.0 because the fix may break compatability with some applications. -
Fixed in MySQL 4.1.1: In a
UNION
, the firstSELECT
determines the type,max_length
, andNULL
properties for the resulting columns. -
Fixed in MySQL 4.1: In
DELETE
with many tables, you can't refer to tables to be deleted through an alias. -
Fixed in MySQL 4.1.2: You cannot mix
UNION ALL
andUNION DISTINCT
in the same query. If you useALL
for oneUNION
, it is used for all of them. -
FLUSH TABLES WITH READ LOCK
does not blockCREATE TABLE
, which may cause a problem with the binary log position when doing a full backup of tables and the binary log. -
Fixed in MySQL 4.1.8:
mysqldump --single-transaction --master-data
behaved likemysqldump --master-data
, so the dump was a blocking one. -
When using the
RPAD()
function (or any function adding spaces to the right) in a query that had to be resolved by using a temporary table, all resulting strings had rightmost spaces removed (i.e.RPAD()
did not work).
The following known issues have not been fixed in MySQL 4.1 for various reasons, and are not classified as critical.
-
Fixed in 5.0.3:
VARCHAR
andVARBINARY
did not remember end space.
The following problems are known and fixing them is a high priority:
-
If you compare a
NULL
value to a subquery usingALL/ANY/SOME
and the subquery returns an empty result, the comparison might evaluate to the non-standard result ofNULL
rather than toTRUE
orFALSE
. This will be fixed in MySQL 5.1. -
Subquery optimization for
IN
is not as effective as for=
. -
Even if you use
lower_case_table_names=2
(which enables MySQL to remember the case used for databases and table names), MySQL does not remember the case used for database names for the functionDATABASE()
or within the various logs (on case-insensitive systems). -
Dropping a
FOREIGN KEY
constraint doesn't work in replication because the constraint may have another name on the slave. -
REPLACE
(andLOAD DATA
with theREPLACE
option) does not triggerON DELETE CASCADE
. -
DISTINCT
withORDER BY
doesn't work insideGROUP_CONCAT()
if you don't use all and only those columns that are in theDISTINCT
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 by having theDROP TABLE
command wait until the table is not being used in any transaction. -
When inserting a big integer value (between 263 and 264–1) into a decimal or string column, it is inserted as a negative value because the number is evaluated in a signed integer context.
-
FLUSH TABLES WITH READ LOCK
does not blockCOMMIT
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 aBDB
table may in some cases make the table unusable until you restart mysqld. If this happens, look for errors of the following form in the MySQL error file:001207 22:07:56 bdb: log_flush: LSN past current end-of-log
-
Don't execute
ALTER TABLE
on aBDB
table on which you are running multiple-statement transactions until all those transactions complete. (The transaction might be ignored.) -
ANALYZE TABLE
,OPTIMIZE TABLE
, andREPAIR TABLE
may cause problems on tables for which you are usingINSERT DELAYED
. -
Performing
LOCK TABLE ...
andFLUSH TABLES ...
doesn't guarantee that there isn't a half-finished transaction in progress on the table. -
BDB
tables are relatively slow to open. If you have manyBDB
tables in a database, it takes a long time to use the mysql client on the database if you are not using the-A
option or if you are usingrehash
. This is especially noticeable 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.
It is 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 (generally not a recommended practice, even outside of replication).
For example:
-
CREATE ... SELECT
orINSERT ... SELECT
statements that insert zero orNULL
values into anAUTO_INCREMENT
column. -
DELETE
if you are deleting rows from a table that has foreign keys withON DELETE CASCADE
properties. -
REPLACE ... SELECT
,INSERT IGNORE ... SELECT
if you have duplicate key values in the inserted data.
If and only if the preceding queries have no
ORDER BY
clause guaranteeing a deterministic order.For example, for
INSERT ... SELECT
with noORDER BY
, theSELECT
may return rows in a different order (which results in a row having different ranks, hence getting a different number in theAUTO_INCREMENT
column), depending on the choices made by the optimizers on the master and slave.A query is 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
, andREPAIR 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, butMyISAM
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 is to add an
ORDER BY
clause to the aforementioned 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 issues 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 such as
--log-bin=
old_host_name
-bin if you change your hostname to something else. Another option is to rename the old files to reflect your hostname change (if these are binary logs, you need to edit the binary log index file and fix the binlog names there as well). See Sección 5.3.1, “Opciones del comando mysqld”. -
mysqlbinlog does not delete temporary files left after a
LOAD DATA INFILE
command. See Sección 8.5, “La utilidad mysqlbinlog para registros binarios”. -
RENAME
doesn't work withTEMPORARY
tables or tables used in aMERGE
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 implement 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 '%
' withESCAPE
inLIKE ... 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. See Sección 2.8.5, “Notas sobre MIT-pthreads”.
-
BLOB
andTEXT
values can't “reliably” be used inGROUP BY
,ORDER BY
orDISTINCT
. Only the firstmax_sort_length
bytes are used when comparingBLOB
values in these cases. The default value ofmax_sort_length
value is 1024 and can be changed at server startup time. As of MySQL 4.0.3, it can be changed at runtime. For older versions, a workaround is to use a substring. For example:SELECT DISTINCT LEFT(
blob_col
,2048) FROMtbl_name
; -
Numeric calculations are done with
BIGINT
orDOUBLE
(both are normally 64 bits long). Which precision you get depends on the function. The general rule is that bit functions are performed withBIGINT
precision,IF
andELT()
withBIGINT
orDOUBLE
precision, and the rest withDOUBLE
precision. You should try to avoid using unsigned long long values if they resolve to be larger than 63 bits (9223372036854775807) for anything other than bit fields. MySQL Server 4.0 has betterBIGINT
handling than 3.23. -
You can have up to 255
ENUM
andSET
columns in one table. -
In
MIN()
,MAX()
, and other aggregate functions, MySQL currently comparesENUM
andSET
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
andstderr
are still redirected to the old log. If you use--log
extensively, you should edit mysqld_safe to log tohost_name
.err instead ofhost_name
.log so that you can easily reclaim the space for the old log by deleting it and executing mysqladmin refresh. -
In an
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 incrementsKEY
by2
, not1
: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 theDISTINCT
comparison. We will probably change this in the future to never compare the hidden columns when executingDISTINCT
.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, using MySQL Server 3.23.x, you may 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 that do not have the
ORDER BY
columns in the result. -
If you execute a
PROCEDURE
on a query that returns an empty set, in some cases thePROCEDURE
does not transform the columns. -
Creation of a table of type
MERGE
doesn't check whether the underlying tables are compatible types. -
If you use
ALTER TABLE
to add aUNIQUE
index to a table used in aMERGE
table and then add a normal index on theMERGE
table, the key order is different for the tables if there was an old, non-UNIQUE
key in the table. This is becauseALTER TABLE
putsUNIQUE
indexes before normal indexes to be able to detect duplicate keys as early as possible.