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)
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)