Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Oct 7 '11 at 15:26

This question came from our site for professional and enthusiast programmers.

    
I had the same problem. When I was checking a database I found one of the add-ons to the web browser was inserting html code to page source code (even to the form) and this was causing the problem. –  Wojciech Piernicki Jul 7 at 13:44
add comment

1 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.

share|improve this answer
2  
Great answer. Great to see another MySQL/Percona Gladiator in the DBA.SE !!! (+1) –  RolandoMySQLDBA Oct 7 '11 at 16:27
2  
It is very easy for engineers who are not DB-concious to go the flat data route. it NEVER performs. My own legacy DB which has a similar situation is not hitting row size so less dramatic but boy is it a performance boon! I'd say your reporting engineer needs to accept that they will have to do joins and just make up for that work with well throughout out indexing. –  TechieGurl Oct 10 '11 at 15:15
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.