Bug #78823 sys.create_synonym_db() does not like reserved-word db names
Submitted: 13 Oct 2015 18:21 Modified: 30 Jun 2016 17:31
Reporter: Paul DuBois Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: SYS Schema Severity:S3 (Non-critical)
Version:5.7.9 OS:Any
Assigned to: CPU Architecture:Any

[13 Oct 2015 18:21] Paul DuBois
Description:
The sys schema create_synonym_db() function fails if the synonym name is a reserved word.

How to repeat:
mysql> CALL create_synonym_db('INFORMATION_SCHEMA','abc');
+--------------------------------------+
| summary                              |
+--------------------------------------+
| Created 63 views in the abc database |
+--------------------------------------+
1 row in set (1.45 sec)

Query OK, 0 rows affected (1.45 sec)

mysql> CALL create_synonym_db('INFORMATION_SCHEMA','is');
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 'is' at line 1

Suggested fix:
create_synonym_db() does not quote the synonym name as an identifier. Perhaps this could be addressed using a stored function that I use in MySQL Cookbook for this purpose:

# quote_identifer() is like the built-in QUOTE() function
# but for identifiers. It returns the argument as a string
# safe for insertion as an identifier into SQL statements.
# The argument is returned enclosed within backticks and
# with internal backticks doubled.

DROP FUNCTION IF EXISTS quote_identifier;
CREATE FUNCTION quote_identifier(id TEXT)
RETURNS TEXT DETERMINISTIC
RETURN CONCAT('`',REPLACE(id,'`','``'),'`’);

The function is part of a publicly available code distribution, so if you'd like to include something similar in the sys schema, be my guest.
[30 Jun 2016 17:31] Paul DuBois
Posted by developer:
 
Noted in 5.7.14 changelog.

The sys schema create_synonym_db() function failed if the synonym
name was a reserved word or contained backtick (`) characters.

The sys schema now has a quote_identifier() function that, given an
identifier argument, produces a quoted identifier suitable for
inclusion in SQL statements.