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:
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.
What other 'overhead' must I consider when calculating the row size?
Why does the error message change from 8126 to 65535 when I go from creating tables with varchar(109) columns to varchar(110) columns?