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