GNU Info

(mysql.info)InnoDB restrictions

---

Next: InnoDB contact information Prev: Error handling Up: InnoDB

---

Enter node , (file) or (file)node

Some restrictions on InnoDB tables
----------------------------------

   * `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 optimization.

   * If you try to create an 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.

   * Before dropping a database with InnoDB tables one has to drop the
     individual InnoDB tables first.

   * 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 <= 30
     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 slightly less than half a database page.

   * The maximum data or log file size is 2 GB or 4 GB depending on how
     large files your operating system supports. Support for > 4 GB
     files will be added to InnoDB in a future version.

   * 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.