MySQL Bugs: #29616: please document maximum number of columns per table

Bug #29616	please document maximum number of columns per table
Submitted:	7 Jul 2007 14:29	Modified:	12 Sep 2007 18:47
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
Nowhere did I find documentation on how many columns per table a (MyISAM) table can contain.

Upon checking the source code, it seems that there is no hard set limit.  It's a combination of the sum of all the strlen(colname) and strlen(comment), and other items ....

So, debugging this error message is not so simple: error: Too many columns:1117

How to repeat:
Please see two different example of CREATE TABLE that I upload.  Notice different number of columns in each.

Suggested fix:
Document the error message better on http://dev.mysql.com/doc/refman/5.0/en/myisam-storage-engine.html

Or, have the .frm files store a definate maximum of 4096 columns per table, regardless of length of names or comment fields..

in unireg.cc we have this:

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);
  }

1340 cols long colname, cannot be created. comment 1 out and it's works

Attachment: bug29616_test1.sql (application/octet-stream, text), 63.11 KiB.

2599 cols doesn't work with short colnames. comment out 1 and it works...

Attachment: bug29616_test2.sql (application/octet-stream, text), 62.43 KiB.

Commenting out 1 column from the above two tables causes the table to be created, but instead of an error, a warning is shown!  The warning is the same as the previous, except the table is really truncated and 1 column is missing!  This might be a server bug afterall.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Factors that affect the maximum number of columns in a table now are described here:

http://dev.mysql.com/doc/refman/5.0/en/column-count-limit.html

The documentation seems to imply that the .frm file cannot be larger that 64KB (See below). However, a table can be created where the actual size of the .frm file is up to 80KB.

"Each table has an .frm file that contains the table definition. The .frm file size limit is fixed at 64KB."