Reserved words usage in MySQL
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 );
