(mysql.info) innodb-restrictions
Info Catalog
(mysql.info) innodb-error-handling
(mysql.info) innodb
(mysql.info) innodb-troubleshooting
14.2.16 Restrictions on `InnoDB' Tables
---------------------------------------
* *Warning:* Do _not_ convert MySQL system tables in the `mysql'
database from `MyISAM' to `InnoDB' tables! This is an unsupported
operation. If you do this, MySQL does not restart until you
restore the old system tables from a backup or re-generate them
with the `mysql_install_db' script.
* A table cannot contain more than 1000 columns.
* The internal maximum key length is 3500 bytes, but MySQL itself
restricts this to 1024 bytes.
* The maximum row length, except for `VARCHAR', `BLOB' and `TEXT'
columns, is slightly less than half of a database page. That is,
the maximum row length is about 8000 bytes. `LONGBLOB' and
`LONGTEXT' columns must be less than 4GB, and the total row length,
including also `BLOB' and `TEXT' columns, must be less than 4GB.
`InnoDB' stores the first 768 bytes of a `VARCHAR', `BLOB', or
`TEXT' column in the row, and the rest into separate pages.
* Although `InnoDB' supports row sizes larger than 65535 internally,
you cannot define a row containing `VARCHAR' columns with a
combined size larger than 65535:
mysql> CREATE TABLE t (a VARCHAR(8000), b VARCHAR(10000),
-> c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
-> f VARCHAR(10000), g VARCHAR(10000)) ENGINE=InnoDB;
ERROR 1118 (42000): Row size too large. The maximum row size for the
used table type, not counting BLOBs, is 65535. You have to change some
columns to TEXT or BLOBs
* On some older operating systems, files must be less than 2GB.
This is not a limitation of `InnoDB' itself, but if you require a
large tablespace, you will need to configure it using several
smaller data files rather than one or a file large data files.
* The combined size of the `InnoDB' log files must be less than 4GB.
* The minimum tablespace size is 10MB. The maximum tablespace size
is four billion database pages (64TB). This is also the maximum
size for a table.
* `InnoDB' tables do not support `FULLTEXT' indexes.
* `InnoDB' tables do not support spatial data types before MySQL
5.0.16.
* `ANALYZE TABLE' determines index cardinality (as displayed in the
`Cardinality' column of `SHOW INDEX' output) by doing eight random
dives to each of the index trees and updating index cardinality
estimates accordingly. Note that because these are only estimates,
repeated runs of `ANALYZE TABLE' may produce different numbers.
This makes `ANALYZE TABLE' fast on `InnoDB' tables but not 100%
accurate as it doesn't take all rows into account.
MySQL uses index cardinality estimates only in join optimization.
If some join is not optimized in the right way, you can try using
`ANALYZE TABLE'. In the few cases that `ANALYZE TABLE' doesn't
produce values good enough for your particular tables, you can use
`FORCE INDEX' with your queries to force the use of a particular
index, or set the `max_seeks_for_key' system variable to ensure
that MySQL prefers index lookups over table scans. See
server-system-variables, and optimizer-issues.
* `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.
* `InnoDB' does not keep an internal count of rows in a table. (In
practice, this would be somewhat complicated due to
multi-versioning.) To process a `SELECT COUNT(*) FROM t' statement,
`InnoDB' must scan an index of the table, which takes some time if
the index is not entirely in the buffer pool. To get a fast count,
you have to use a counter table you create yourself and let your
application update it according to the inserts and deletes it
does. If your table does not change often, using the MySQL query
cache is a good solution. `SHOW TABLE STATUS' also can be used if
an approximate row count is sufficient. See innodb-tuning.
* On Windows, `InnoDB' always stores database and table names
internally in lowercase. To move databases in binary format from
Unix to Windows or from Windows to Unix, you should always use
explicitly lowercase names when creating databases and tables.
* For an `AUTO_INCREMENT' column, you must always define an index
for the table, and that index must contain just the
`AUTO_INCREMENT' column. In `MyISAM' tables, the `AUTO_INCREMENT'
column may be part of a multi-column index.
* In MySQL 5.0 before MySQL 5.0.3, `InnoDB' does not support the
`AUTO_INCREMENT' table option for setting the initial sequence
value in a `CREATE TABLE' or `ALTER TABLE' statement. To set the
value with `InnoDB', insert a dummy row with a value one less and
delete that dummy row, or insert the first row with an explicit
value specified.
* While initializing a previously specified `AUTO_INCREMENT' column
on a table, `InnoDB' sets an exclusive lock on the end of the
index associated with the `AUTO_INCREMENT' column. In accessing the
auto-increment counter, `InnoDB' uses a specific table lock mode
`AUTO-INC' where the lock lasts only to the end of the current SQL
statement, not to the end of the entire transaction. Note that
other clients cannot insert into the table while the `AUTO-INC'
table lock is held; see innodb-and-autocommit.
* When you restart the MySQL server, `InnoDB' may reuse an old value
that was generated for an `AUTO_INCREMENT' column but never stored
(that is, a value that was generated during an old transaction
that was rolled back).
* When an `AUTO_INCREMENT' column runs out of values, `InnoDB' wraps
a `BIGINT' to `-9223372036854775808' and `BIGINT UNSIGNED' to `1'.
However, `BIGINT' values have 64 bits, so do note that if you were
to insert one million rows per second, it would still take nearly
three hundred thousand years before `BIGINT' reached its upper
bound. With all other integer type columns, a duplicate-key error
results. This is similar to how `MyISAM' works, because it is
mostly general MySQL behavior and not about any storage engine in
particular.
* `DELETE FROM TBL_NAME' does not regenerate the table but instead
deletes all rows, one by one.
* Under some conditions, `TRUNCATE TBL_NAME' for an `InnoDB' table
is mapped to `DELETE FROM TBL_NAME' and doesn't reset the
`AUTO_INCREMENT' counter. See truncate.
* In MySQL 5.0, the MySQL `LOCK TABLES' operation acquires two locks
on each table if `innodb_table_locks=1' (the default). In addition
to a table lock on the MySQL layer, it also acquires an `InnoDB'
table lock. Older versions of MySQL did not acquire `InnoDB' table
locks; the old behavior can be selected by setting
`innodb_table_locks=0'. If no `InnoDB' table lock is acquired,
`LOCK TABLES' completes even if some records of the tables are
being locked by other transactions.
* All `InnoDB' locks held by a transaction are released when the
transaction is committed or aborted. Thus, it does not make much
sense to invoke `LOCK TABLES' on `InnoDB' tables in `AUTOCOMMIT=1'
mode, because the acquired `InnoDB' table locks would be released
immediately.
* Sometimes it would be useful to lock further tables in the course
of a transaction. Unfortunately, `LOCK TABLES' in MySQL performs
an implicit `COMMIT' and `UNLOCK TABLES'. An `InnoDB' variant of
`LOCK TABLES' has been planned that can be executed in the middle
of a transaction.
* The `LOAD TABLE FROM MASTER' statement for setting up replication
slave servers does not yet work for `InnoDB' tables. A workaround
is to alter the table to `MyISAM' on the master, do then the load,
and after that alter the master table back to `InnoDB'. Do not do
this if the tables use `InnoDB'-specific features such as foreign
keys.
* The default database page size in `InnoDB' is 16KB. By recompiling
the code, you can set it to values ranging from 8KB to 64KB. You
must update the values of `UNIV_PAGE_SIZE' and
`UNIV_PAGE_SIZE_SHIFT' in the `univ.i' source file.
* Currently, triggers are not activated by cascaded foreign key
actions.
* Prior to MySQL 5.0.19, `InnoDB' does not ignore trailing spaces
when comparing `BINARY' or `VARBINARY' column values. See
binary-varbinary and news-5-0-19.
Info Catalog
(mysql.info) innodb-error-handling
(mysql.info) innodb
(mysql.info) innodb-troubleshooting
automatically generated byinfo2html