Peter Marklund

Peter Marklund's Home

Fri December 08, 2006
Programming

MySQL Gotchas and a Deadly Sin

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...

Comments

Luis de la Rosa said over 8 years ago:

Have you tried setting the SQL mode to TRADITIONAL? That’s the workaround described in Misleading error message if not NULL column set to NULL, SQL mode TRADITIONAL

--------------------------------------------------------------------------------

Peter Marklund said over 8 years ago:

Wow, thanks Luis! I added sql_mode=TRADITIONAL to my my.cnf file and that solved the NOT NULL problem. I wonder how many other switches I need to set to make MySQL behave like a “traditional” database. I look forward to the Falcon storage engine that MySQL is developing although I’m not sure it will solve the kind of problems I have described here.

--------------------------------------------------------------------------------

Mark Aufflick said over 8 years ago:

"Gotcha"s aren’t annoying. They are risk. Risk to you and risk to your client’s data.

Rails on mysql might be the “standard”, but so is MS Windows. Why look forward to the future with MySQL (which is usually over promised, under delivered) when postgres has what you want now?!

--------------------------------------------------------------------------------