MySQL Gotchas

Head-scratching MySQL oddities which haven't been incorporated into the MySQL Gotchas list yet.

Marko Tiikkaja points out this interesting SQL gotcha, where (in PostgreSQL) two string constants separated by a newline character are implicitly concatenated - whereas separated by any other whitespace, an error is raised:

postgres=# SELECT 'foo' IN ('foo','bar');
(1 row)

postgres=# SELECT 'foo' IN ('foo'
postgres(#  'bar');
(1 row)

postgres=# SELECT 'foo' IN ('foo' 'bar');
ERROR:  syntax error at or near "'bar'"
LINE 1: SELECT 'foo' IN ('foo' 'bar');

According to the PostgreSQL documentation, this slightly bizarre behavior is specified by SQL.

How does MySQL stack up in this admittedly obscure piece of SQL quirkiness?

Posted at 8:23 AM

So, I was just loading a MySQL schema from a test application I use to experiment with various databases, and suddenly it started spitting out errors like this:

  BLOB/TEXT column 'some_column' can't have a default value

which was kind of odd because it had evidently been working fine before, and what kind of database doesn't allow default values on certain datatypes anyway?

Posted at 5:26 AM

In MySQL, user is not a reserved word and can be used as-is as a table name. As web applications in particular often have their own user table, it is in common use, which is perfectly understandable.

However, according to the various ANSI SQL standards, user is a reserved word (see e.g. here or here), and this leads to problems when converting MySQL schema definitions to other databases, such as that described here.

Note that as of MySQL 5.0.19, even when selecting MySQL's ANSI Mode, "user" does not appear to treated as a reserved word, and no warnings are given when it is used to create a table.

Posted at 10:57 PM

Beginning with version 5.0.3, MySQL now includes a "real" TRUNCATE command for InnoDB tables, similar to that provided by Oracle and PostgreSQL.

George-Cristian Bîrzan (gcbirzan {at} constanta dot rdsnet dot ro) wrote to point out that it is affected by the following interesting gotcha:

Posted at 2:43 PM

Many of the gotchas in pre 5.0 MySQL versions stem from the fact that operations silently failed without any type of error message or warning. That situation has improved with 5.0, with explicit warnings being emmitted on some operations, such as when data is truncated, making it easier to catch potential problems.

However, with DDL operations (e.g. DROP TABLE) in a transactional context, there are still no warnings which would alert the user to potential problems. Of course, in MySQL DDL operations are by nature not transactional, but sometimes it is easy to forget this.

Posted at 7:41 AM

This is the first gotcha I've found for the current 5.0 series.

"INSERT INTO" seems to accept the phrase "VALUE" as equivalent to the more usual "VALUES". While I'm not sure whether this is standard SQL, I've never encountered it before and can find no reference to this syntax on the documentation page at http://dev.mysql.com/doc/refman/5.0/en/insert.html.

For example:

Posted at 4:57 AM