Bug #35780 InnoDB maximum row size changed
Submitted: 2 Apr 2008 23:06 Modified: 2 Apr 2008 23:20
Reporter: Todd Farmer (OCA) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.0.51 OS:Any
Assigned to: CPU Architecture:Any

[2 Apr 2008 23:06] Todd Farmer 
Description:
The InnoDB storage engine generates errors when the row size exceeds 65535, even when this is not the case:

mysql> CREATE TABLE t (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 B
LOBs
mysql> select version();
+--------------------------+
| version()                |
+--------------------------+
| 5.0.51a-community-nt-log |
+--------------------------+
1 row in set (0.00 sec)

The same CREATE TABLE command is successful in 5.0.45:

mysql> CREATE TABLE t (b VARCHAR(10000),
    -> c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
    -> f VARCHAR(10000), g VARCHAR(10000)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.14 sec)

mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 5.0.45-community-nt-log |
+-------------------------+
1 row in set (0.00 sec)

The documentation makes the following claim:

Although InnoDB supports row sizes larger than 65535 internally, you cannot define a row containing VARBINARY or VARCHAR columns with a combined size larger than 65535

How to repeat:
CREATE TABLE t (b VARCHAR(10000),
c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
f VARCHAR(10000), g VARCHAR(10000)) ENGINE=InnoDB;

Suggested fix:
Help InnoDB count correctly.
[2 Apr 2008 23:20] Todd Farmer 
Problem was caused by differences in default character set (UTF8 vs. ASCII).
[4 Apr 2008 15:37] Wayne Seguin 
We just ran into this issue as well. A client was creating 114 varchar(255) columns (don't ask) and this error was reached. This happens with BOTH InnoDB and MyISAM. Reducing 255 -> 180 allowed the table to be created. Note that 114*255 = 29070 < 65535... This must be a bug. All databases we use are utf8.
[4 Apr 2008 17:44] Todd Farmer 
The "65535" limit is in bytes, although there are places in the documentation that this is not explicitly stated.  UTF8 requires 3 bytes per character, and VARCHAR(255) requires an additional 2 bytes overhead to store the actual length.  In your case, this means:

114 * ( 2 + ( 255 * 3 ) ) = 87438 bytes required per row

Please see http://dev.mysql.com/doc/refman/5.0/en/column-count-limit.html for details.
[9 Dec 2009 4:14] Trent Lloyd 
If you are not experienced with character sets, the "fix" here (in the case where it worked in 5.0 and not in later versions or 5.1), you can put

 CHARACTER SET latin1;

On the end of your CREATE TABLE statement.  This of course presumes it is OK to use latin1 rather than utf8, etc.