MySQL and ENUM fields

Turns out that a database update wasn’t done, so an ENUM field did not allow the values requested by the application. This in turn caused a weird error message that, in my opinion, just could not be!

mysql> CREATE TABLE foo(
    ->    something ENUM('a', 'b', 'c')
    -> );
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO foo
    -> SET something = 'x';
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SELECT * FROM foo;
+-----------+
| something |
+-----------+
|           |
+-----------+
1 row in set (0.00 sec)

Turns out that when you feed an invalid ENUM value into MySQL, it does not throw an error, but simply sets the value to an empty string. Trying to reproduce it on the console, the following is what I got:

Note how at least here, a warning is produced. Unfortunately, most DB libs do not catch them, so the problem shows up later in the process as a completely different phenomenon… Really NOT helpful, MySQL!