enum: Yet Another Reason to Hate MySQL

Ovid on 2007-03-05T14:54:48

I really hope that the MySQL developers have fixed this bug, but since we're not in a position to upgrade right now, it doesn't really matter. Note the foo column in the following table definition:

CREATE TABLE `testaa` (
  `a` int(11) NOT NULL default '0',
  `b` double default NULL,
  `foo` enum('this', 'that') NOT NULL DEFAULT 'this',
  PRIMARY KEY  (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Looks reasonable, yes? Now let's try to insert some bogus data:

mysql> insert into testaa (a, b, foo) values (1, 2.0, '');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'foo' at row 1 |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into testaa (a, b, foo) values (2, 2.0, 'asdf');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'foo' at row 1 |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from testaa;

+---+------+-----+
| a | b    | foo |
+---+------+-----+
| 1 |    2 |     |
| 2 |    2 |     |
+---+------+-----+
2 rows in set (0.00 sec)

mysql> 

MySQL's default behavior, in many cases, is to just truncate anything which won't fit into a column. Simply discarding information is so very, very wrong and often leads to corrupt data. As you might guess, this is closely related to a bug I'm trying to hunt down right now :(

---

Modes

jplindstrom on 2007-03-05T16:22:18

I guess it's something like modes you're refering to (although it doesn't mention enums explicitly, the enum page does mention modes).

Well, at least they fixed it in v5. Or is that the version you already use and where it doesn't work?

Re:Modes

Ovid on 2007-03-05T16:39:15

Nope, we're on an older version. At least v5 isn't quite as crippled as older versions.

Regrettably, the 'modes' available pre v5 don't offer protection for this. At least I can use a trigger to ... oh, wait ... sigh. (And in a totally unrelated note, the problem the 'enum' was trying to fix could have been solved with a view, but since we don't have those either, the 'enum' solution was used)

Re:Modes

jplindstrom on 2007-03-05T17:35:22

It sounds like a FK to a value table with the enum values as PKs would solve your problem.

enums are for saving space

perrin on 2007-03-05T20:19:43

MySQL has enums to save space (and thus improve speed), not to enforce values. For that, you should use a lookup table and a foreign key, like you would with Oracle. (Constraints are a lousy place to put a list of acceptable values because they are hard to edit from user apps.) Or you could run the database in "traditional" mode, which should make it throw an error for data that doesn't fit in an enum. Personally, I just avoid enums and use lookup tables, except for data warehouse situations where I have tight control over how the data gets into the table.