A.8. Problemas conocidos en MySQL

MySQL 5.0

A.8. Problemas conocidos en MySQL

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.

A.8.1. Problemas de la versión 3.23 resueltos en una versión posterior de MySQL

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 to lock multiple tables and then in the same connection use to drop one of them while another thread is trying to lock it. (To break the deadlock, you can use to terminate any of the threads involved.)

  • Fixed in MySQL 4.0.11: where one of the tables are empty doesn't return but instead returns the maximum value for the column.

  • without a clause doesn't work on a locked table.

A.8.2. Problemas de la versión 4.0 resueltos en una versión posterior de MySQL

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 , you can get a crash or wrong result if you use an alias to a 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 , the first determines the type, , and properties for the resulting columns.

  • Fixed in MySQL 4.1: In with many tables, you can't refer to tables to be deleted through an alias.

  • Fixed in MySQL 4.1.2: You cannot mix and in the same query. If you use for one , it is used for all of them.

  • does not block , 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: behaved like , so the dump was a blocking one.

  • When using the 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. did not work).

A.8.3. Problemas de la versión 4.1 resueltos en una versión posterior de MySQL

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: and did not remember end space.

A.8.4. Cuestiones abiertas en MySQL

The following problems are known and fixing them is a high priority:

  • If you compare a value to a subquery using and the subquery returns an empty result, the comparison might evaluate to the non-standard result of rather than to or . This will be fixed in MySQL 5.1.

  • Subquery optimization for is not as effective as for .

  • Even if you use (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 function or within the various logs (on case-insensitive systems).

  • Dropping a constraint doesn't work in replication because the constraint may have another name on the slave.

  • (and with the option) does not trigger .

  • with doesn't work inside if you don't use all and only those columns that are in the 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 command before the table is used in the transaction itself. We plan to fix this by having the 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.

  • does not block if the server is running without binary logging, which may cause a problem (of consistency between tables) when doing a full backup.

  • on a 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 on a table on which you are running multiple-statement transactions until all those transactions complete. (The transaction might be ignored.)

  • , , and may cause problems on tables for which you are using .

  • Performing and doesn't guarantee that there isn't a half-finished transaction in progress on the table.

  • tables are relatively slow to open. If you have many tables in a database, it takes a long time to use the mysql client on the database if you are not using the option or if you are using . 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:

    • or statements that insert zero or values into an column.

    • if you are deleting rows from a table that has foreign keys with properties.

    • , if you have duplicate key values in the inserted data.

    If and only if the preceding queries have no clause guaranteeing a deterministic order.

    For example, for with no , the may return rows in a different order (which results in a row having different ranks, hence getting a different number in the 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, was run on the master tables and not on the slave tables. (To fix this, , , and 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 on the master, but on the slave if the slave has less available disk space.)

    • MySQL buffer sizes (, 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 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 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 -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 command. See Sección 8.5, “La utilidad mysqlbinlog para registros binarios”.

  • doesn't work with tables or tables used in a table.

  • Due to the way table definition files are stored, you cannot use character 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 , you can't use translated characters in database, table, and column names.

  • You can't use '' or '' with in .

  • If you have a column in which the same number is stored in different formats (for example, , , ), 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”.

  • and values can't “reliably” be used in , or . Only the first bytes are used when comparing values in these cases. The default value of 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(,2048) FROM ;
    
  • Numeric calculations are done with or (both are normally 64 bits long). Which precision you get depends on the function. The general rule is that bit functions are performed with precision, and with or precision, and the rest with 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 better handling than 3.23.

  • You can have up to 255 and columns in one table.

  • In , , and other aggregate functions, MySQL currently compares and 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, and are still redirected to the old log. If you use extensively, you should edit mysqld_safe to log to .err instead of .log so that you can easily reclaim the space for the old log by deleting it and executing mysqladmin refresh.

  • In an 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 by , not :

    mysql> UPDATE  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 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 comparison. We will probably change this in the future to never compare the hidden columns when executing .

    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 column may differ).

    Note that this happens only for queries where that do not have the columns in the result.

  • If you execute a on a query that returns an empty set, in some cases the does not transform the columns.

  • Creation of a table of type doesn't check whether the underlying tables are compatible types.

  • If you use to add a index to a table used in a table and then add a normal index on the table, the key order is different for the tables if there was an old, non- key in the table. This is because puts indexes before normal indexes to be able to detect duplicate keys as early as possible.