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
