MySQL – Resolving Row Size Too Large Error

limitsMySQL

I have a table with 156 columns and most of them are either VARCHAR(1000) or TEXT.

Now, when entering data into one such row, I get

mysql_error() = 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
inline.

I am thinking of changing the table type to MyISAM.
Its on a shared server, but can setup a private MySQL instance.

From here :

InnoDB implements transactions, foreign keys and relationship
constraints, MyISAM does not.

I can still use JOINS etc on MyISAM, right ? I'm not implementing any other complex stuff on these tables.

Best Answer

5.5.14 and 5.6.3 and 5.7 have innodb_file_format=Barracuda as an option.

Prior to 5.7.7, do these:

SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_large_prefix=1;
logout & login (to get the global values);
ALTER TABLE tbl ROW_FORMAT=DYNAMIC;

In 5.7.7 and after, most of that is defaulted as you need.

DYNAMIC is the format you need -- it leaves only 22 bytes in the main part of the row (which is limited to about 8KB), and puts the rest in overflow blocks. This applies to VARCHAR, TEXT, VARBINARY and BLOB. 22*156 is well under 8K. "When the row is too long, InnoDB chooses the longest columns for off-page storage." That is, some of the shorter columns for a given row may still reside 'on-page'.

I think the "22" is a 2-byte length, plus a 20-byte "pointer" to the data. That is, none of the data is stored 'on-page', and the 768 is not relevant.

Another possibility (but I don't recommend it) is to increase the default page size from 16KB to 32KB, thereby increasing the max from about 8K to about 16K. (There is a 64KB page size, but the limit is still 16KB for each row.)

Also, COMPRESSED can be used instead of DYNAMIC, but again, I don't recommend it, especially before 5.7. ("Compressed = Dynamic plus compression.")

Splitting the table ("vertical partitioning") is a good idea.

MyISAM is a bad idea.

Checking to see if you really need 1000 is a good idea.

ISSUE #1 : Characters Vs Bytes

Since UTF-8 uses 3 bytes per characters, you may not have allocated enough for character fields.

ISSUE #2 : InnoDB Row Length

By default, an index key for a single-column index can be up to 767 bytes. The same length limit applies to any index key prefix. See Section 13.1.13, “CREATE INDEX Syntax”. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a UTF-8 character set and the maximum of 3 bytes for each character. When the innodb_large_prefix configuration option is enabled, this length limit is raised to 3072 bytes, for InnoDB tables that use the DYNAMIC and COMPRESSED row formats.

When you attempt to specify an index prefix length longer than allowed, the length is silently reduced to the maximum length. This configuration option changes the error handling for some combinations of row format and prefix length longer than the maximum allowed. See innodb_large_prefix for details.

The InnoDB internal maximum key length is 3500 bytes, but MySQL itself restricts this to 3072 bytes. This limit applies to the length of the combined index key in a multi-column index.

The maximum row length, except for variable-length columns (VARBINARY, VARCHAR, BLOB and TEXT), 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 BLOB and TEXT columns, must be less than 4GB.

If a row is less than half a page long, all of it is stored locally within the page. If it exceeds half a page, variable-length columns are chosen for external off-page storage until the row fits within half a page, as described in Section 14.3.12.2, “File Space Management”.

Although InnoDB supports row sizes larger than 65,535 bytes internally, MySQL itself imposes a row-size limit of 65,535 for the combined size of all columns:

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 See Section E.10.4, “Table Column-Count and Row-Size Limits”.

Mysql – system wide way to change MariaDB to create tables using ROW_FORMAT = DYNAMIC

InnoDB has Limitations on row lengths and BLOBs. According to the MySQL Documentation:

The maximum row length, except for variable-length columns (VARBINARY, VARCHAR, BLOB and TEXT), 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 BLOB and TEXT columns, must be less than 4GB.

If a row is less than half a page long, all of it is stored locally within the page. If it exceeds half a page, variable-length columns are chosen for external off-page storage until the row fits within half a page, as described in Section 14.3.12.2, “File Space Management”.

Although InnoDB supports row sizes larger than 65,535 bytes internally, MySQL itself imposes a row-size limit of 65,535 for the combined size of all columns:
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
See Section E.10.4, “Limits on Table Column Count and Row Size”.

You are going to have to recreate the mysqldump so that the Storage Engine is Specified. Perhaps just drop the --compatible=mssql from the mysqldump command. The end result is the that the table will remain a MyISAM table when being imported into MariaDB.

This is just a guess but look at the error message you posted. If a BLOB prefix is 768 bytes and you have 10 BLOBs, that 7680 bytes. That leaves you with 320 bytes. If the remaining datatypes exceed 320 bytes, then it is impossible to convert to InnoDB.

Since a TEXT column is the variant of a BLOB, converting to TEXT does nothing since the storage requirements for TEXT and BLOB field are identical.

Best Answer

Related Solutions

Mysql – error 1118: row size too large. utf8 innodb

ISSUE #1 : Characters Vs Bytes

ISSUE #2 : InnoDB Row Length

Mysql – system wide way to change MariaDB to create tables using ROW_FORMAT = DYNAMIC

Related Question