| 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: | |
| Category: | MySQL Server: SYS Schema | Severity: | S3 (Non-critical) |
| Version: | 5.7.9 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[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.
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.