MySQL Bugs: #54194: ENUM limit of 65,535 elements isn't true

Bug #54194	ENUM limit of 65,535 elements isn't true
Submitted:	3 Jun 2010 5:50	Modified:	14 Mar 2013 16:54
Reporter:	Stewart Smith	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1.46, 5.0, 5.1, 5.6.99	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
With a CREATE TABLE with a single ENUM column with "only" 65500 elements should be okay (see http://dev.mysql.com/doc/refman/5.0/en/enum.html "An enumeration can have a maximum of 65,535 elements.")
mysql> source /tmp/b
ERROR 1117 (HY000): Too many columns

The actual limit is somewhere between 10850 and 10900.

(10850 does create a 72k FRM file)

The limit that's being hit is (see sql/unireg.cc)

  /* Hack to avoid bugs with small static rows in MySQL */
  reclength=max(file->min_record_length(table_options),reclength);
  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);
  }

Which roughly works out to be "is the FRM larger than 64kb"... but it isn't really... it's *close* to that however.

But you certainly cannot create an ENUM column with anywhere near 65535 elements.

How to repeat:
See attached CREATE TABLE statement (it's only 86kb)

mysql> source /tmp/c
ERROR 1117 (HY000): Too many columns

Suggested fix:
This can be viewed either as a server bug or a documentation bug.

CREATE TABLE with ENUM with 10,900 elements (which fails)

Attachment: c (application/octet-stream, text), 85.02 KiB.

Thank you for the report.

Verified as described.

I used PHP script to create table:

$cat bug54194.php
<?php
$cr="create table t1(f1 enum(";
for ($i=0; $i < 65533; $i ++) 
$cr .= "'$i', ";
$cr = rtrim($cr, ", ");
$cr .= ");";
echo $cr;
?>

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.

Factored out information about .frm file to this section:
http://dev.mysql.com/doc/refman/5.5/en/limits-frm-file.html

Added reference to ENUM limit imposed by .frm file to this section:
http://dev.mysql.com/doc/refman/5.5/en/enum.html