InnoDB create table error: "Row size too large"

Question

We have some engineers that flatten a normalized db structure into a temporary table for the purposes of generating a report. The columns are specified as TEXT NOT NULL (I know "why are they doing that?"; let's just assume that we are addressing this).

We use MySQL 5.1.48 Community RHEL5 with InnoDB plug-in 1.0.9 on Linux.

When using MyISAM we never encountered table size limits of max columns or max row length (during investigation we have hit the max columns limit at 2598 (the 2599th causes error 1117). With InnoDB we are hitting limits. These limits are manifesting when creating the table (no data insertion) as:

ERROR 1118 (42000) at line 1: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. You have to change some columns to TEXT or BLOBs

I am looking for answers to the following:

1. What is the detailed formula for determining row size particulalry when using lot's of v/v/b/t columns? I've tried a few different formuals using varchar(N) columns (where N is between 1 and 512), the UTF8 charset (*3), and as many columns as the table will take until failure. None of the combos I've tried give values that match with actual test results.

2. What other 'overhead' must I consider when calculating the row size?

3. Why does the error message change from 8126 to 65535 when I go from creating tables with varchar(109) columns to varchar(110) columns?

Answer 1

The answers to your questions are complex, because they vary by InnoDB file format. Today, there are two formats, called Antelope and Barracuda.

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. [EDIT] Dead link - here looks better now.

Barracuda also supports a ROW_FORMAT=COMPRESSED to gain further storage efficiency for overflow data.

I also have to comment that I've never seen a well-designed table exceed the row size limit. It's a strong "code smell" that you're violating the repeating groups condition of First Normal Form.

Answer 2

My situation is slightly different. One of the data elements I need to store in each row is potentially very large. (The data field is a LONGBLOB for a document that may contain multiple embedded images. My sample database contains documents as large as 25 - 30 MB, but these documents could be larger in some cases.) None of the solutions I found online provided relief. (Changed the InnoDB file type to Barracuda, increased the log file size, set the row format to COMPRESSED.)

The only solution I've found that has worked for me was to revert back to MySQL 5.5.x from MySQL 5.6.x.