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
Triage: Triaged: D3 (Medium)

[3 Jun 2010 5:50] Stewart Smith
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.
[3 Jun 2010 5:52] Stewart Smith
CREATE TABLE with ENUM with 10,900 elements (which fails)

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

[3 Jun 2010 6:18] Sveta Smirnova
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;
?>
[14 Mar 2013 16:54] Paul Dubois
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