Bug #101258 Can't create synonym of information_schema using create_synonym_db()
Submitted: 21 Oct 2020 7:18 Modified: 21 Oct 2020 7:22
Reporter: Fumiaki Segawa Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: SYS Schema Severity:S3 (Non-critical)
Version:8.0.22 OS:Linux
Assigned to: CPU Architecture:Any
Tags: regression

[21 Oct 2020 7:18] Fumiaki Segawa
Description:
CALL sys.create_synonym_db('information_schema', 'i_s') always fail with error 1352.

Hello, I installed MySQL 8.0.22 from yum repository, then executed sys.create_synonym_db('information_schema', 'i_s') and got error message.

As I confirmed, following tables seem to be cause.

ADMINISTRABLE_ROLE_AUTHORIZATIONS
APPLICABLE_ROLES
ENABLED_ROLES
KEYWORDS
ROLE_COLUMN_GRANTS
ROLE_ROUTINE_GRANTS
ROLE_TABLE_GRANTS
ST_UNITS_OF_MEASURE

All those table have json_table() function in table definition.

However I also confirmed that following SQL succeeded.

CREATE SQL SECURITY INVOKER VIEW `i_s`.`KEYWORDS` AS SELECT * FROM `information_schema`.`KEYWORDS`

However, MySQL 8.0.21 doesn't cause same problem.

How to repeat:

=== MySQL 8.0.21 ===

mysql> SELECT version();
+-----------+
| version() |
+-----------+
| 8.0.21    |
+-----------+
1 row in set (0.00 sec)

mysql> CALL sys.create_synonym_db('information_schema', 'i_s');
+----------------------------------------+
| summary                                |
+----------------------------------------+
| Created 78 views in the `i_s` database |
+----------------------------------------+
1 row in set (2.46 sec)

Query OK, 0 rows affected (2.46 sec)

=== MySQL 8.0.22 ===

mysql> SELECT version();
+-----------+
| version() |
+-----------+
| 8.0.22    |
+-----------+
1 row in set (0.00 sec)

mysql> CALL sys.create_synonym_db('information_schema', 'i_s');
ERROR 1352 (HY000): View's SELECT refers to a temporary table 'j'

mysql> SHOW TABLES FROM i_s;
+---------------------------------------+
| Tables_in_i_s                         |
+---------------------------------------+
| CHARACTER_SETS                        |
| CHECK_CONSTRAINTS                     |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMNS_EXTENSIONS                    |
| COLUMN_STATISTICS                     |
| EVENTS                                |
| FILES                                 |
| INNODB_DATAFILES                      |
| INNODB_FIELDS                         |
| INNODB_FOREIGN                        |
| INNODB_FOREIGN_COLS                   |
| INNODB_TABLESPACES_BRIEF              |
| KEY_COLUMN_USAGE                      |
+---------------------------------------+
15 rows in set (0.00 sec)

-- However this SQL will be success

mysql> CREATE SQL SECURITY INVOKER VIEW `i_s`.`KEYWORDS` AS SELECT * FROM `information_schema`.`KEYWORDS`;
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT * FROM i_s.KEYWORDS LIMIT 5;
+------------+----------+
| WORD       | RESERVED |
+------------+----------+
| ACCESSIBLE |        1 |
| ACCOUNT    |        0 |
| ACTION     |        0 |
| ACTIVE     |        0 |
| ADD        |        1 |
+------------+----------+
5 rows in set (0.00 sec)
[21 Oct 2020 7:22] MySQL Verification Team
Hello Segawa,

Thank you for the report and feedback.

regards,
Umesh