Reserved words usage in MySQL

Written by Ivan Groenewold | May 2, 2016 4:00:00 AM

It is not uncommon to come across MySQL databases where reserved words are in use as identifiers for any kind of database objects. Perhaps when the application schema was implemented, the words were not reserved yet, and they became reserved later on a subsequent MySQL release. It is a good practice to check reserved words usage prior to doing any database upgrades, as any newly reserved keywords will cause syntax errors on the new version.

This is usually not a problem if proper quoting is used for referencing the objects, as described on the official manual page. The actual steps to do this depend on the environment; for example, the following can be configured to tell Hibernate to escape identifiers:

 property name="hibernate.globally_quoted_identifiers" value="true"

This does not appear to be documented properly (there is an open bug unresolved at the time of this writing). However, we cannot make the assumption that all application code is properly escaped to deal with this kind of issues.

So what are the symptoms?

Error 1064 will be reported while trying to use a reserved word:

 mysql> CREATE TABLE interval (begin INT, end INT);
 ERROR 1064 (42000): You have an error in your SQL syntax ...
 near 'interval (begin INT, end INT)'

How can we check for reserved words?

The following procedure can help you find out if any particular MySQL version's reserved words are in use:

Using the list on the corresponding manual page, create a text file with one reserved word on each line

Load data into a temporary table

USE test;
 CREATE TABLE reserved_words VARCHAR(50); 
 LOAD DATA INFILE 'reserved_words.txt' INTO TABLE test.reserved_words;

Check for any column names using reserved keywords

SELECT table_schema, table_name, column_name, ordinal_position 
 FROM information_schema.columns
 WHERE table_schema NOT IN ( 'mysql', 'information_schema', 'performance_schema' ) 
 AND column_name = ANY ( SELECT * FROM test.reserved_words ) 
 ORDER BY 1,2,4;

Check for any table names using reserved keywords

SELECT table_schema, table_name
 FROM information_schema.tables
 WHERE table_schema NOT IN ( 'mysql', 'information_schema', 'performance_schema' ) 
 AND table_name = ANY ( SELECT * FROM test.reserved_words );

Check for any procedures or functions

 SELECT routine_schema, routine_name, routine_type
 FROM information_schema.routines
 WHERE routine_schema NOT IN ( 'mysql', 'information_schema', 'performance_schema' )
 AND routine_name = ANY ( select * from test.words );

I hope this post helps you avoid one of the many issues you may encounter during the challenging task of database upgrades.

MySQL Database Consulting

Looking to optimize your MySQL use?

View full post