7.5.14 Restrictions on InnoDB Tables

• In Windows InnoDB stores the database names and table names internally always in lower case. To move databases in a binary format from Unix to Windows or from Windows to Unix you should have all table and database names in lower case.
• Warning: do NOT convert MySQL system tables from MyISAM TO InnoDB tables! This is not supported; if you do this MySQL will not restart until you restore the old system tables from a backup or re-generate them with the mysql_install_db script.
• SHOW TABLE STATUS does not give accurate statistics on InnoDB tables, except for the physical size reserved by the table. The row count is only a rough estimate used in SQL optimisation.
• If you try to create a unique index on a prefix of a column you will get an error:

  CREATE TABLE T (A CHAR(20), B INT, UNIQUE (A(5))) TYPE = InnoDB;
  

  If you create a non-unique index on a prefix of a column, InnoDB will create an index over the whole column.
• INSERT DELAYED is not supported for InnoDB tables.
• The MySQL LOCK TABLES operation does not know of InnoDB row level locks set in already completed SQL statements: this means that you can get a table lock on a table even if there still exist transactions of other users which have row level locks on the same table. Thus your operations on the table may have to wait if they collide with these locks of other users. Also a deadlock is possible. However, this does not endanger transaction integrity, because the row level locks set by InnoDB will always take care of the integrity. Also, a table lock prevents other transactions from acquiring more row level locks (in a conflicting lock mode) on the table.
• You cannot have a key on a BLOB or TEXT column.
• A table cannot contain more than 1000 columns.
• DELETE FROM TABLE does not regenerate the table but instead deletes all rows, one by one, which is not that fast. In future versions of MySQL you can use TRUNCATE which is fast.
• The default database page size in InnoDB is 16 KB. By recompiling the code one can set it from 8 KB to 64 KB. The maximun row length is slightly less than half of a database page in versions <= 3.23.40 of InnoDB. Starting from source release 3.23.41 BLOB and TEXT columns are allowed to be < 4 GB, the total row length must also be < 4 GB. InnoDB does not store fields whose size is <= 128 bytes on separate pages. After InnoDB has modified the row by storing long fields on separate pages, the remaining length of the row must be less than half a database page. The maximun key length is 7000 bytes.
• On some operating systems datafiles must be < 2 GB. The combined size of log files must be < 4 GB.
• The maximum tablespace size is 4 billion database pages. This is also the maximum size for a table. The minimum tablespace size is 10 MB.
• When you restart the MySQL server, InnoDB may reuse an old value for an AUTO_INCREMENT column.
• You cannot set the first AUTO_INCREMENT column value in InnoDB with CREATE TABLE ... AUTO_INCREMENT=... (or ALTER TABLE ...). To set the value insert a dummy row with a value one less, and delete that dummy row.

User Comments

Add your own comment.