## B.3.7 Known Issues in MySQL 

This section lists known issues in recent versions of MySQL. 

For information about platform-specific issues, see the installation and debugging instructions in Section 2.1, “General Installation Guidance” , and Section 5.9, “Debugging MySQL” . 

The following problems are known: 

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

•Dropping a FOREIGN KEY  constraint does not work in replication because the constraint may have another name on the replica. 

• REPLACE  (and LOAD DATA  with the REPLACE  option) does not trigger ON DELETE CASCADE . 

• DISTINCT  with ORDER BY  does not work inside GROUP_CONCAT()  if you do not use all and only those columns that are in the DISTINCT  list. 

• When inserting a big integer value (between 2$^{63}$ and 2 64 − 1) into a decimal or string column, it is inserted as a negative value because the number is evaluated in signed integer context. 

•With statement-based binary logging, the source server writes the executed queries to the binary log. This is a very fast, compact, and efficient logging method that works perfectly in most cases. However, it is possible for the data on the source and replica to become different if a query is designed in such a way that the data modification is nondeterministic (generally not a recommended practice, even outside of replication). 

For example: 

• CREATE TABLE ... 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 the preceding 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 results 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 source and replica. 

A query is optimized differently on the source and replica only if: 

•The table is stored using a different storage engine on the source than on the replica. (It is possible to use different storage engines on the source and replica. For example, you can use InnoDB  on the source, but MyISAM  on the replica if the replica has less available disk space.) 

•MySQL buffer sizes ( key_buffer_size , and so on) are different on the source and replica. 

