Interesting MySQL 5.5 upgrade gotcha

Today I discovered an interesting upgrade problem with a client migrating from MySQL 5.0 to 5.5. The client who is undertaking the upgrade reported that MySQL 5.5 did not support the DECIMAL(18,5) data type. I easily confirmed this not to be the case:

mysql> drop table if exists x;
mysql> create table x (col1 DECIMAL(18,5));
Query OK, 0 rows affected (0.01 sec)

Delving more into the issue in question, I looked at the complete CREATE TABLE statement, recreating the syntax.

mysql> drop table if exists x;
mysql> create table x ( MinValue DECIMAL(18,5));
Query OK, 0 rows affected (0.00 sec)

No problem there.

mysql> drop table if exists x;
mysql> create table x (Department INT NOT NULL, MinValue DECIMAL(18,5) NULL, MaxValue DECIMAL(18,5) NULL);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MaxValue DECIMAL(18,5) NULL)' at line 1

I could immediately see the problem because if you look closely at the error message it starts with the word “MaxValue”, most likely this is a reserved word. This was easily confirmed with:

mysql> create table x (Department INT NOT NULL, MinValue DECIMAL(18,5) NULL, `MaxValue` DECIMAL(18,5) NULL);
Query OK, 0 rows affected (0.00 sec)

And a confirmation with the 5.5 Reference Manual does indeed show there are new reserved words in MySQL 5.5. These are:

  • GENERAL
  • IGNORE_SERVER_IDS
  • MASTER_HEARTBEAT_PERIOD
  • MAXVALUE
  • RESIGNAL SIGNAL
  • SLOW

Tags: , , ,

One Response to “Interesting MySQL 5.5 upgrade gotcha”

  1. Sheeri says:

    So, did you recommend that your client, oh, I don’t know, READ the really good manual page on how to upgrade?

    http://dev.mysql.com/doc/refman/5.5/en/upgrading-from-previous-series.html

    The last bullet point CLEARLY states: “Some keywords may be reserved in MySQL 5.5 that were not reserved in MySQL 5.1. See Section 8.3, “Reserved Words”.”

    Of course, the client is already doing something odd, because they are upgrading from 5.0 to 5.5 — are they actually going through 5.1 in between? If not, did you recommend that to them?

    They should stop and read the upgrading page, and in general should take the time to read the changelogs when upgrading. It takes time but it’s worth every minute spent.