Movatterモバイル変換

MySQL 9.3 Reference Manual

17.9.1.7 SQL Compression Syntax Warnings and Errors

This section describes syntax warnings and errors that you may encounter when using the table compression feature withfile-per-table tablespaces andgeneral tablespaces.

SQL Compression Syntax Warnings and Errors for File-Per-TableTablespaces

Wheninnodb_strict_mode is enabled (the default), specifyingROW_FORMAT=COMPRESSED orKEY_BLOCK_SIZE inCREATE TABLE orALTER TABLE statements produces the following error ifinnodb_file_per_table is disabled.

ERROR 1031 (HY000): Table storage engine for 't1' doesn't have this option

Note

The table is not created if the current configuration does not permit using compressed tables.

Wheninnodb_strict_mode is disabled, specifyingROW_FORMAT=COMPRESSED orKEY_BLOCK_SIZE inCREATE TABLE orALTER TABLE statements produces the following warnings ifinnodb_file_per_table is disabled.

mysql> SHOW WARNINGS;+---------+------+---------------------------------------------------------------+| Level   | Code | Message                                                       |+---------+------+---------------------------------------------------------------+| Warning | 1478 | InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.        || Warning | 1478 | InnoDB: ignoring KEY_BLOCK_SIZE=4.                            || Warning | 1478 | InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. || Warning | 1478 | InnoDB: assuming ROW_FORMAT=DYNAMIC.                          |+---------+------+---------------------------------------------------------------+

Note

These messages are only warnings, not errors, and the table is created without compression, as if the options were not specified.

The“non-strict” behavior lets you import amysqldump file into a database that does not support compressed tables, even if the source database contained compressed tables. In that case, MySQL creates the table inROW_FORMAT=DYNAMIC instead of preventing the operation.

To import the dump file into a new database, and have the tables re-created as they exist in the original database, ensure the server has the proper setting for theinnodb_file_per_table configuration parameter.

The attributeKEY_BLOCK_SIZE is permitted only whenROW_FORMAT is specified asCOMPRESSED or is omitted. Specifying aKEY_BLOCK_SIZE with any otherROW_FORMAT generates a warning that you can view withSHOW WARNINGS. However, the table is non-compressed; the specifiedKEY_BLOCK_SIZE is ignored).

Level	Code	Message
Warning	1478	`InnoDB: ignoring KEY_BLOCK_SIZE=n unless ROW_FORMAT=COMPRESSED.`

If you are running withinnodb_strict_mode enabled, the combination of aKEY_BLOCK_SIZE with anyROW_FORMAT other thanCOMPRESSED generates an error, not a warning, and the table is not created.

Table 17.9, “ROW_FORMAT and KEY_BLOCK_SIZE Options” provides an overview theROW_FORMAT andKEY_BLOCK_SIZE options that are used withCREATE TABLE orALTER TABLE.

Table 17.9 ROW_FORMAT and KEY_BLOCK_SIZE Options

Option	Usage Notes	Description
`ROW_FORMAT=REDUNDANT`	Storage format used prior to MySQL 5.0.3	Less efficient than`ROW_FORMAT=COMPACT`; for backward compatibility
`ROW_FORMAT=COMPACT`	Default storage format since MySQL 5.0.3	Stores a prefix of 768 bytes of long column values in the clustered index page, with the remaining bytes stored in an overflow page
`ROW_FORMAT=DYNAMIC`		Store values within the clustered index page if they fit; if not, stores only a 20-byte pointer to an overflow page (no prefix)
`ROW_FORMAT=COMPRESSED`		Compresses the table and indexes using zlib
`KEY_BLOCK_SIZE=n`		Specifies compressed page size of 1, 2, 4, 8 or 16 kilobytes; implies`ROW_FORMAT=COMPRESSED`. For general tablespaces, a`KEY_BLOCK_SIZE` value equal to the`InnoDB` page size is not permitted.

Table 17.10, “CREATE/ALTER TABLE Warnings and Errors when InnoDB Strict Mode is OFF” summarizes error conditions that occur with certain combinations of configuration parameters and options on theCREATE TABLE orALTER TABLE statements, and how the options appear in the output ofSHOW TABLE STATUS.

Wheninnodb_strict_mode isOFF, MySQL creates or alters the table, but ignores certain settings as shown below. You can see the warning messages in the MySQL error log. Wheninnodb_strict_mode isON, these specified combinations of options generate errors, and the table is not created or altered. To see the full description of the error condition, issue theSHOW ERRORS statement: example:

mysql>CREATE TABLE x (id INT PRIMARY KEY, c INT)    ->ENGINE=INNODB KEY_BLOCK_SIZE=33333;ERROR 1005 (HY000): Can't create table 'test.x' (errno: 1478)mysql>SHOW ERRORS;+-------+------+-------------------------------------------+| Level | Code | Message                                   |+-------+------+-------------------------------------------+| Error | 1478 | InnoDB: invalid KEY_BLOCK_SIZE=33333.     || Error | 1005 | Can't create table 'test.x' (errno: 1478) |+-------+------+-------------------------------------------+

Table 17.10 CREATE/ALTER TABLE Warnings and Errors when InnoDB Strict Mode is OFF

Syntax	Warning or Error Condition	Resulting`ROW_FORMAT`, as shown in`SHOW TABLE STATUS`
`ROW_FORMAT=REDUNDANT`	None	`REDUNDANT`
`ROW_FORMAT=COMPACT`	None	`COMPACT`
`ROW_FORMAT=COMPRESSED` or`ROW_FORMAT=DYNAMIC` or`KEY_BLOCK_SIZE` is specified	Ignored for file-per-table tablespaces unless`innodb_file_per_table` is enabled. General tablespaces support all row formats. SeeSection 17.6.3.3, “General Tablespaces”.	`the default row format for file-per-table tablespaces; the specified row format for general tablespaces`
Invalid`KEY_BLOCK_SIZE` is specified (not 1, 2, 4, 8 or 16)	`KEY_BLOCK_SIZE` is ignored	the specified row format, or the default row format
`ROW_FORMAT=COMPRESSED` and valid`KEY_BLOCK_SIZE` are specified	None;`KEY_BLOCK_SIZE` specified is used	`COMPRESSED`
`KEY_BLOCK_SIZE` is specified with`REDUNDANT`,`COMPACT` or`DYNAMIC` row format	`KEY_BLOCK_SIZE` is ignored	`REDUNDANT`,`COMPACT` or`DYNAMIC`
`ROW_FORMAT` is not one of`REDUNDANT`,`COMPACT`,`DYNAMIC` or`COMPRESSED`	Ignored if recognized by the MySQL parser. Otherwise, an error is issued.	the default row format or N/A

Wheninnodb_strict_mode isON, MySQL rejects invalidROW_FORMAT orKEY_BLOCK_SIZE parameters and issues errors. Strict mode isON by default. Wheninnodb_strict_mode isOFF, MySQL issues warnings instead of errors for ignored invalid parameters.

It is not possible to see the chosenKEY_BLOCK_SIZE usingSHOW TABLE STATUS. The statementSHOW CREATE TABLE displays theKEY_BLOCK_SIZE (even if it was ignored when creating the table). The real compressed page size of the table cannot be displayed by MySQL.

SQL Compression Syntax Warnings and Errors for General Tablespaces

IfFILE_BLOCK_SIZE was not defined for the general tablespace when the tablespace was created, the tablespace cannot contain compressed tables. If you attempt to add a compressed table, an error is returned, as shown in the following example:
```
mysql> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1 ROW_FORMAT=COMPRESSED       KEY_BLOCK_SIZE=8;ERROR 1478 (HY000): InnoDB: Tablespace `ts1` cannot contain a COMPRESSED table
```
Attempting to add a table with an invalidKEY_BLOCK_SIZE to a general tablespace returns an error, as shown in the following example:
```
mysql> CREATE TABLESPACE `ts2` ADD DATAFILE 'ts2.ibd' FILE_BLOCK_SIZE = 8192 Engine=InnoDB;mysql> CREATE TABLE t2 (c1 INT PRIMARY KEY) TABLESPACE ts2 ROW_FORMAT=COMPRESSED       KEY_BLOCK_SIZE=4;ERROR 1478 (HY000): InnoDB: Tablespace `ts2` uses block size 8192 and cannotcontain a table with physical page size 4096
```
For general tablespaces, theKEY_BLOCK_SIZE of the table must be equal to theFILE_BLOCK_SIZE of the tablespace divided by 1024. For example, if theFILE_BLOCK_SIZE of the tablespace is 8192, theKEY_BLOCK_SIZE of the table must be 8.

Attempting to add a table with an uncompressed row format to a general tablespace configured to store compressed tables returns an error, as shown in the following example:

mysql> CREATE TABLESPACE `ts3` ADD DATAFILE 'ts3.ibd' FILE_BLOCK_SIZE = 8192 Engine=InnoDB;mysql> CREATE TABLE t3 (c1 INT PRIMARY KEY) TABLESPACE ts3 ROW_FORMAT=COMPACT;ERROR 1478 (HY000): InnoDB: Tablespace `ts3` uses block size 8192 and cannotcontain a table with physical page size 16384

innodb_strict_mode is not applicable to general tablespaces. Tablespace management rules for general tablespaces are strictly enforced independently ofinnodb_strict_mode. For more information, seeSection 15.1.23, “CREATE TABLESPACE Statement”.

For more information about using compressed tables with general tablespaces, seeSection 17.6.3.3, “General Tablespaces”.

PREV HOME UP NEXT