ERROR 1118 (42000) at line 1: Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored in-line.
CAUSE :
By default each BLOB or text field using the Antelope innodb_file_format is stored in a prefix and overflow pages. The prefix for each field (maximum size = 768 bytes) will be included in the row.
So if you have a lot of these data types in the table's definition, the modified row can be larger than the InnoDB's internal limit : 8126 for a row.
The central tablespace file (ibdata1) is always in Antelope format. If you use file-per-table, you can make the individual files use Barracuda format by setting innodb_file_format=Barracuda in my.cnf.
Basic points:
One 16KB page of InnoDB data must hold at least two rows of data. Plus each page has a header and a footer containing page checksums and log sequence number and so on. That's where you get your limit of a bit less than 8KB per row.
Fixed-size data types like INTEGER, DATE, FLOAT, CHAR are stored on this primary data page and count toward the row size limit.
Variable-sized data types like VARCHAR, TEXT, BLOB are stored on overflow pages, so they don't count fully toward the row size limit. In Antelope, up to 768 bytes of such columns are stored on the primary data page in addition to being stored on the overflow page. Barracuda supports a dynamic row format, so it may store only a 20-byte pointer on the primary data page.
Variable-size data types are also prefixed with 1 or more bytes to encode the length. And InnoDB row format also has an array of field offsets. So there's an internal structure more or less documented in their wiki.
Barracuda also supports a ROW_FORMAT=COMPRESSED to gain further storage efficiency for overflow data.
For more information about innoDB row formats and file formats go through https://dev.mysql.com/doc/refman/5.5/en/innodb-row-format-dynamic.html
SOLUTION :
To avoid those errors, you need to use :
innodb_file_format = Barracuda
innodb_file_per_table = 1
the table's ROW_FORMAT = DYNAMIC or ROW_FORMAT = COMPRESSED
The first 2 options are global and dynamic variables, the third one is a CREATE TABLE option.
set global innodb_file_format = barracuda;
set global innodb_file_per_table = 1;
CREATE TABLE t (
...
) engine = InnoDB
ROW_FORMAT=DYNAMIC
...
Note1 : After changing innodb_file_format, while you are creating the innodb table externalle, you should specify the row_format=dynamic; then only it will work, other wise table will be created with antelope(default) format onlyl.
Note2 : if you later alter the table, you'll have to set the dynamic variables, otherwise the table will be rebuilt using the current innodb_file_per_table and innodb_file_format values.
If the values are incompatible with ROW_FORMAT = DYNAMIC, the table will be created with a warning that you may not notice in time.
Example when innodb_file_per_table = 0 or innodb_file_format=antelope:
mysql> create table t(id int, b blob) engine = InnoDB row_format=dynamic;
Query OK, 0 rows affected, 2 warnings (0.16 sec)
mysql> show warnings;
+---------+------+------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------+
| Warning | 1478 | InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table. |
| Warning | 1478 | InnoDB: assuming ROW_FORMAT=COMPACT. |
+---------+------+------------------------------------------------------------+
2 rows in set (0.00 sec)
For this reason, it can be good to permanently change the settings in the configuration file as well :
innodb_file_format = Barracuda
innodb_file_per_table = 1
Its always a good practice to change configuration file when you change and global variables dynamically inside server.
MySQL Internals, MySQL HighAvailability, MySQL Performance Tuning, MySQL Query Optimization, MySQL performance, MySQL Fabric, MySQL HA, MySQL Installation, MySQL Upgrade, InnoDB Performance Tuning, InnoDB Buffer Pool Size, MySQL Performance Tuning, MySQL ClusterMySQL Latest News, News and Events, MySQL Customers
CAUSE :
By default each BLOB or text field using the Antelope innodb_file_format is stored in a prefix and overflow pages. The prefix for each field (maximum size = 768 bytes) will be included in the row.
So if you have a lot of these data types in the table's definition, the modified row can be larger than the InnoDB's internal limit : 8126 for a row.
The central tablespace file (ibdata1) is always in Antelope format. If you use file-per-table, you can make the individual files use Barracuda format by setting innodb_file_format=Barracuda in my.cnf.
Basic points:
One 16KB page of InnoDB data must hold at least two rows of data. Plus each page has a header and a footer containing page checksums and log sequence number and so on. That's where you get your limit of a bit less than 8KB per row.
Fixed-size data types like INTEGER, DATE, FLOAT, CHAR are stored on this primary data page and count toward the row size limit.
Variable-sized data types like VARCHAR, TEXT, BLOB are stored on overflow pages, so they don't count fully toward the row size limit. In Antelope, up to 768 bytes of such columns are stored on the primary data page in addition to being stored on the overflow page. Barracuda supports a dynamic row format, so it may store only a 20-byte pointer on the primary data page.
Variable-size data types are also prefixed with 1 or more bytes to encode the length. And InnoDB row format also has an array of field offsets. So there's an internal structure more or less documented in their wiki.
Barracuda also supports a ROW_FORMAT=COMPRESSED to gain further storage efficiency for overflow data.
For more information about innoDB row formats and file formats go through https://dev.mysql.com/doc/refman/5.5/en/innodb-row-format-dynamic.html
SOLUTION :
To avoid those errors, you need to use :
innodb_file_format = Barracuda
innodb_file_per_table = 1
the table's ROW_FORMAT = DYNAMIC or ROW_FORMAT = COMPRESSED
The first 2 options are global and dynamic variables, the third one is a CREATE TABLE option.
set global innodb_file_format = barracuda;
set global innodb_file_per_table = 1;
CREATE TABLE t (
...
) engine = InnoDB
ROW_FORMAT=DYNAMIC
...
Note1 : After changing innodb_file_format, while you are creating the innodb table externalle, you should specify the row_format=dynamic; then only it will work, other wise table will be created with antelope(default) format onlyl.
Note2 : if you later alter the table, you'll have to set the dynamic variables, otherwise the table will be rebuilt using the current innodb_file_per_table and innodb_file_format values.
If the values are incompatible with ROW_FORMAT = DYNAMIC, the table will be created with a warning that you may not notice in time.
Example when innodb_file_per_table = 0 or innodb_file_format=antelope:
mysql> create table t(id int, b blob) engine = InnoDB row_format=dynamic;
Query OK, 0 rows affected, 2 warnings (0.16 sec)
mysql> show warnings;
+---------+------+------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------+
| Warning | 1478 | InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table. |
| Warning | 1478 | InnoDB: assuming ROW_FORMAT=COMPACT. |
+---------+------+------------------------------------------------------------+
2 rows in set (0.00 sec)
For this reason, it can be good to permanently change the settings in the configuration file as well :
innodb_file_format = Barracuda
innodb_file_per_table = 1
Its always a good practice to change configuration file when you change and global variables dynamically inside server.
MySQL Internals, MySQL HighAvailability, MySQL Performance Tuning, MySQL Query Optimization, MySQL performance, MySQL Fabric, MySQL HA, MySQL Installation, MySQL Upgrade, InnoDB Performance Tuning, InnoDB Buffer Pool Size, MySQL Performance Tuning, MySQL ClusterMySQL Latest News, News and Events, MySQL Customers
No comments:
Post a Comment