Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

Ovid (2709)

Ovid
  (email not shown publicly)
http://publius-ovidius.livejournal.com/
AOL IM: ovidperl (Add Buddy, Send Message)

Stuff with the Perl Foundation. A couple of patches in the Perl core. A few CPAN modules. That about sums it up.

Journal of Ovid (2709)

Monday March 05, 2007
09:54 AM

enum: Yet Another Reason to Hate MySQL

[ #32577 ]

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 :(

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • I guess it's something like modes [mysql.com] you're refering to (although it doesn't mention enums explicitly, the enum [mysql.com] 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?
    • 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)

  • 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 hav