Description:
http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html says:
>A table cannot contain more than 1000 columns.
This is true, there is a limit of 1000.
But it is incomplete: there are more limits, and theymay be below 1000.
Therefore the documentation is incomplete and missleading.
A similiar documentation for other Engines seems to be missing (I checked MyIsam and Heap) there is no limitation documentated.
The total amount of columns can be much lower (less than 500) and it depends on the overall length needed to store the column definition (including comments). This can not exceed 64K.
I supply for perl 1 liners, printing the "create table" sql (as the actual sql is rather big)
The output is suitable for piping into mysql
the 2nd and 4th perl print a "create table" sql which causes the error.
How to repeat:
# works
# 250 col named c?? with comment 100 char long
perl -wle 'print "drop table if exists t1; create table t1 (".join(",",map{"c$_ int comment \"".("x" x 100)."\""} 1..250).") engine = innodb;"; '
# ERR Too many columns
# 250 col named c?? with comment 500 char long
perl -wle 'print "drop table if exists t1; create table t1 (".join(",",map{"c$_ int comment \"".("x" x 500)."\""} 1..250).") engine = innodb;"; '
# WORKS
# 250 col with no comment (column name len 62/63 char)
perl -wle ' print "drop table if exists t1; create table t1 (".join(",",map{("x" x 60)."c$_ int"} 1..250).") engine = innodb;";
# ERR Too many columns
# 950 col with no comment (column name len 62/63 char)
perl -wle ' print "drop table if exists t1; create table t1 (".join(",",map{("x" x 60)."c$_ int"} 1..950).") engine = innodb;"; '
Suggested fix:
I believe this is a case for documentation.
To lift the limit the .frm file format would probably need to chnage. Maybe in 5.1 or 5.2?
If this should be considered "correctly documented" (except for the missing bits in myisam/heap/...) then the problem is caused in sql/unireq.cc function pack_header around line 780ff
if (info_length + (ulong) create_fields.elements *FCOMP + 288 +
n_length + int_length + com_length > 65535L || int_count > 255)
{
my_message(ER_TOO_MANY_FIELDS, ER(ER_TOO_MANY_FIELDS), MYF(0));
DBUG_RETURN(1);
}
bzero((char*)forminfo,288);
length=(info_length+create_fields.elements*FCOMP+288+n_length+int_length+
com_length);
int2store(forminfo,length);
As you can see on the last line quoted, only 2 bytes are available to store the total length.
