Peter Marklund's Home

Coming from mostly a PostgreSQL and Oracle background I experienced some major friction working with MySQL today.

The first obstacle to overcome was the syntax for foreign keys. I started out using

  create table test (
    service_id integer NOT NULL references services(id) on delete cascade
  );

MySQL 5.0.21 on OSX swallowed this syntax happily with no complaints. Only by running Rails unit tests did I discover that to actually create a foreign key in MySQL (and not just look like you are) you need to write:

  create table test (
    service_id integer NOT NULL,
    foreign key (service_id) references services(id) on delete cascade
  );

When I then actually had working foreign keys the ordering of table create and drop statements became very sensitive. If table A has a foreign key to table B then table B must be created before table A. Also, MySQL lacks the DROP TABLE CASCADE feature so the order of drop table statements needs to be the reverse of the create table statements. In this case (apparently for porting reasons) you can still write DROP TABLE CASCADE but MySQL will just ignore the CASCADE command. This, just like the foreign key example above, is a form of silent failure that I think in general is the root of much frustration among developers. It's an anti-pattern.

Now here comes the grand finale and my worst discover when it comes to MySQL today - the implementation of NOT NULL. You see in this case, the NOT NULL instruction is not ignored by MySQL, but arguably it's not fully implemented either (see a good discussion on this here). It turns out that if you try to insert NULL into a NOT NULL column MySQL throws an exception. This is to be expected and is exactly what we want. However, if you instead try to update the column to NULL MySQL happily sets the column to the empty string...

CREATE TABLE peter (
  name varchar(100) NOT NULL
) ENGINE=InnoDB;

mysql> insert into peter (name) values (NULL);
ERROR 1048 (23000): Column 'name' cannot be null

mysql> insert into peter (name) values ('foobar');
Query OK, 1 row affected (0.06 sec)

mysql> update peter set name = NULL;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> select count(*) from peter where name = '';
+----------+
| count(*) |
+----------+
|        1 | 
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from peter where name is null;
+----------+
| count(*) |
+----------+
|        0 | 
+----------+
1 row in set (0.00 sec)

I'm at a loss of words...