Bug #24660 "enum" field type definition problem
Submitted: 28 Nov 2006 16:24 Modified: 26 Feb 2007 18:27
Reporter: Claude Morin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.23-BK, 4.1.22,5.0BK OS:Linux (Linux x86 generic RPM)
Assigned to: Tatiana Azundris Nuernberg CPU Architecture:Any
Tags: bfsm_2007_02_01

[28 Nov 2006 16:24] Claude Morin
Description:
Since version 4.1.22 upgrade, the enum fields that contain commas in their VALUES definition, i.e.:

     `FieldName` enum('E','F','E,F','F,E') NOT NULL DEFAULT'E',

are now saved as:

     enum('E','F','EÿF','FÿE')

and it creates problem with applications linked to this field definition.

I dropped and recreated the Table: same thing.

How to repeat:
Try defining an enum field like in the example above.

Suggested fix:
I tried putting a ";" instead of the comma, i.e. 'E;F' and it worked. Its only the comma (as far as I know) that causes the problem, but I don't want to change all my applications that rely on the 'E,F' value.
[28 Nov 2006 16:35] Claude Morin
Changed seriousness from S3 (Non-critical) to S2 (Serious).
[29 Nov 2006 2:19] Valeriy Kravchuk
Thank you for a bug report. Verified just as described, also - with latest 4.1.23-BK on Linux:

openxs@suse:~/dbs/4.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.23

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table te(c1 enum('E','F','E,F','F,E') NOT NULL DEFAULT'E');
Query OK, 0 rows affected (0.02 sec)

mysql> show create table te\G
*************************** 1. row ***************************
       Table: te
Create Table: CREATE TABLE `te` (
  `c1` enum('E','F','EяF','FяE') NOT NULL default 'E'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show variables like 'char%';
+--------------------------+--------------------------------------------+
| Variable_name            | Value                                      |
+--------------------------+--------------------------------------------+
| character_set_client     | latin1                                     |
| character_set_connection | latin1                                     |
| character_set_database   | latin1                                     |
| character_set_results    | latin1                                     |
| character_set_server     | latin1                                     |
| character_set_system     | utf8                                       |
| character_sets_dir       | /home/openxs/dbs/4.1/share/mysql/charsets/ |
+--------------------------+--------------------------------------------+
7 rows in set (0.00 sec)
[14 Dec 2006 1:33] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/16922

ChangeSet@1.2588, 2006-12-14 02:34:06+01:00, tnurnberg@mysql.com +4 -0
  Bug#24660: "enum" field type definition problem
  
  ENUMs weren't allowed to have character 0xff, a perfectly good character in some locales.
  This was circumvented by mapping 0xff in ENUMs to ',', thereby prevent actual commas from
  being used. Now if 0xff makes an appearance, we find a character not used in the enum and
  use that as a separator. If no such character exists, we throw an error.
  
  Any solution would have broken some sort of existing behaviour. This solution should
  serve both fractions (those with 0xff and those with ',' in their enums), but
  WILL REQUIRE A DUMP/RESTORE CYCLE FROM THOSE WITH 0xff IN THEIR ENUMS. :-/
  That is, mysqldump with their current server, and restore when upgrading to one with
  this patch.
[7 Jan 2007 23:05] [ name withheld ]
This same bug is in 5.1.14-beta
[9 Jan 2007 19:19] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17803

ChangeSet@1.2381, 2007-01-09 20:23:12+01:00, tnurnberg@mysql.com +4 -0
  Bug#24660: "enum" field type definition problem
  
  ENUMs weren't allowed to have character 0xff, a perfectly good character in many locales.
  This was circumvented by mapping 0xff in ENUMs to ',', thereby prevent actual commas from
  being used (because they too would get converted to 0xff on load). Now if 0xff makes an
  appearance, we find a character not used in the enum and use that as a separator. If no
  such character exists, we throw an error.
  
  Any solution would have broken some sort of existing behaviour. This solution should
  serve both fractions (those with 0xff and those with ',' in their enums), but
  WILL REQUIRE A DUMP/RESTORE CYCLE FROM THOSE WITH 0xff IN THEIR ENUMS. :-/
  That is, mysqldump with their current server, and restore when upgrading to one with
  this patch.
  
  (port of the original 4.1 patch. incorporates some suggestions by kaamos.)
[12 Jan 2007 10:02] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/18013

ChangeSet@1.2385, 2007-01-12 11:06:38+01:00, tnurnberg@mysql.com +4 -0
  Bug#24660: "enum" field type definition problem
  
  ENUMs weren't allowed to have character 0xff, a perfectly good
  character in some locales.  This was circumvented by mapping 0xff in
  ENUMs to ',', thereby prevent actual commas from being used. Now if
  0xff makes an appearance, we find a character not used in the enum and
  use that as a separator. If no such character exists, we throw an
  error.
  
  Any solution would have broken some sort of existing behaviour. This
  solution should serve both fractions (those with 0xff and those with
  ',' in their enums), but WILL REQUIRE A DUMP/RESTORE CYCLE FROM THOSE
  WITH 0xff IN THEIR ENUMS. :-/ That is, mysqldump with their current
  server, and restore when upgrading to one with this patch.
[17 Jan 2007 17:45] Tatiana Azundris Nuernberg
fixed in 5.1.15
[23 Jan 2007 20:01] Paul DuBois
Noted in 5.1.15 changelog.

For ENUM columns defined such that enumeration values contained
commas, the commas were mapped to 0xff.
[26 Jan 2007 14:27] J.R. del Rio
Hello,

I have the same problem in my debian Etc 5.0.30-3 (ia64).

Thank you.
[31 Jan 2007 0:04] Christian Hammers
Please check this one again:

1. Commits only went to 4.1 and 5.1 repositories, not to 5.0 although 5.0 is 
   listed as affected, too.

2. The changelog state that a complete dump/restore is needed. That's almost
   inacceptable for distributions like RedHat or Debian who cannot do a complete
   dump/restore per default as the local installation might have 15GB of data :)
   
3. Apart from that this should be handled with "CHECK TABLE ... FOR UPGRADE", 
   or? I.e. if mysq_upgrade is called after the upgrade, the table should be 
   marked as "corrupt" until the admin or --auto-repair issues a REPAIR TABLE.

bye,

-christian-
[31 Jan 2007 9:39] Tatiana Azundris Nuernberg
J.R., Christian,

Yes, this exists in 4.1 and 5.0, and is fixed in 5.1.

1. As stated in the commit message, the fix was committed to my *local* 4.1 repo; this was *not* pushed to main or maint trees, that is, the fix is *not* in any official 4.1.

2. Specifically, you need a dump/restore cycle iff you have 0xff in one or many of your enum values, for the tables that do, and have been using a mysqld that has the patch for Bug#20922 (FWIW)
[12 Feb 2007 0:58] Tatiana Azundris Nuernberg
The ticket was closed because the original orders were to fix in 5.1 only; this was completed, and the ticket was subsequently closed. After more discussion, I am reopening this bug now. Will fix in 4.1 and 5.0 now.
[12 Feb 2007 13:31] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/19687

ChangeSet@1.2588, 2007-02-12 14:31:44+01:00, tnurnberg@mysql.com +4 -0
  Bug#24660: "enum" field type definition problem
  
  ENUMs weren't allowed to have character 0xff, a perfectly good character in some locales.
  This was circumvented by mapping 0xff in ENUMs to ',', thereby prevent actual commas from
  being used. Now if 0xff makes an appearance, we find a character not used in the enum and
  use that as a separator. If no such character exists, we throw an error.
  
  Any solution would have broken some sort of existing behaviour. This solution should
  serve both fractions (those with 0xff and those with ',' in their enums), but
  WILL REQUIRE A DUMP/RESTORE CYCLE FROM THOSE WITH 0xff IN THEIR ENUMS. :-/
  That is, mysqldump with their current server, and restore when upgrading to one with
  this patch.
[14 Feb 2007 9:08] Mads Martin Joergensen
Merged to 4.1 and 5.0
[14 Feb 2007 14:59] Chad MILLER
Available in 4.1.23, 5.0.36, and 5.1.16-beta.
[26 Feb 2007 18:27] Paul DuBois
Revised 5.1.15 changelog entry:

Incompatible change: For ENUM columns that had enumeration values
containing commas, the commas were mapped to 0xff internally. 
However, this rendered the commas indistinguishable from true 0xff
characters in the values. This no longer occurs. However, the fix
requires that you dump and reload any tables that have ENUM columns
containing true 0xff in their values: Dump the tables using mysqldump
with the current server before upgrading from a version of MySQL 5.1
older than 5.1.15 to version 5.1.15 or newer.

Similar items are noted in the 4.1.23 and 5.0.36 changelogs, and in the
upgrade section for the 4.1, 5.0, and 5.1 manuals.