MySQL8.0.22でsys.create_synonym_db('information_schema', 'i_s')ができない件
MySQL 8.0.22リリースされましたね:)
というわけでいつものようにリリースノートを読む前に早速インストール。
僕はsys.create_sysnonym_db()を使ってinformation_schema, performance_schemaをそれぞれi_s, p_sにリネームしてから使うのが好きなのでこちらも実行。
mysql-8022>select version(); +-----------+ | version() | +-----------+ | 8.0.22 | +-----------+ 1 row in set (0.00 sec) mysql-8022>call sys.create_synonym_db('information_schema', 'i_s'); ERROR 1352 (HY000): View's SELECT refers to a temporary table 'j'
エラーメッセージを見るとViewが一時的なテーブルjを参照していることによるエラーであることが分かります。
なおperformance_schemaに関しては問題なく実行できました。
mysql-8022>call sys.create_synonym_db('performance_schema', 'p_s'); +-----------------------------------------+ | summary | +-----------------------------------------+ | Created 108 views in the `p_s` database | +-----------------------------------------+ 1 row in set (2.93 sec) Query OK, 0 rows affected (2.94 sec)
エラー原因を突き止める
既存のcreate_synonym_dbの定義を使ってどのテーブルでエラーが出ているのか突き止めます。
: : +-------------------------------+ | txt | +-------------------------------+ | `KEY_COLUMN_USAGE` is CREATED | +-------------------------------+ 1 row in set (0.54 sec) +----------+ | KEYWORDS | +----------+ | KEYWORDS | +----------+ 1 row in set (0.54 sec) ERROR 1352 (HY000): View's SELECT refers to a temporary table 'j'
何回かi_sを削除→再作成を繰り返していると他のテーブルでも同じエラーが出ることを確認できました。
最終的に下記のテーブルを作成しようとしたときにエラーが出ていることを確認しました。
ADMINISTRABLE_ROLE_AUTHORIZATIONS APPLICABLE_ROLES ENABLED_ROLES KEYWORDS ROLE_COLUMN_GRANTS ROLE_ROUTINE_GRANTS ROLE_TABLE_GRANTS ST_UNITS_OF_MEASURE
これらの共通点をあげてみるとjson_table()を使っているということが言えます。
mysql-root@localhost[information_schema]>show create table ST_UNITS_OF_MEASURE\G *************************** 1. row *************************** View: ST_UNITS_OF_MEASURE Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`mysql.infoschema`@`localhost` SQL SECURITY DEFINER VIEW `ST_UNITS_OF_MEASURE` AS select `ST_UNITS_OF_MEASURE`.`UNIT_NAME` AS `UNIT_NAME`,`ST_UNITS_OF_MEASURE`.`UNIT_TYPE` AS `UNIT_TYPE`,`ST_UNITS_OF_MEASURE`.`CONVERSION_FACTOR` AS `CONVERSION_FACTOR`,`ST_UNITS_OF_MEASURE`.`DESCRIPTION` AS `DESCRIPTION` from json_table('[["British link (Sears 1922 truncated)","LINEAR","",0.20116756],["foot","LINEAR","",0.3048],["British yard (Benoit 1895 B)","LINEAR","",0.9143992042898124],["British yard (Sears 1922 truncated)","LINEAR","",0.914398],["British yard (Benoit 1895 A)","LINEAR","",0.9143992],["British chain (Sears 1922)","LINEAR","",20.116765121552632],["Clarke\'s link","LINEAR","",0.201166195164],["Clarke\'s yard","LINEAR","",0.9143917962],["British chain (Benoit 1895 A)","LINEAR","",20.1167824],["kilometre","LINEAR","",1000],["US survey foot","LINEAR","",0.30480060960121924],["British yard (Sears 1922)","LINEAR","",0.9143984146160287],["centimetre","LINEAR","",0.01],["millimetre","LINEAR","",0.001],["British foot (Sears 1922)","LINEAR","",0.3047994715386762],["British link (Sears 1922)","LINEAR","",0.2011676512155263],["fathom","LINEAR","",1.8288],["US survey chain","LINEAR","",20.11684023368047],["US survey link","LINEAR","",0.2011684023368047],["metre","LINEAR","",1],["German legal metre","LINEAR","",1.0000135965],["nautical mile","LINEAR","",1852],["British link (Benoit 1895 A)","LINEAR","",0.201167824],["British foot (Benoit 1895 B)","LINEAR","",0.30479973476327077],["British chain (Benoit 1895 B)","LINEAR","",20.116782494375872],["Statute mile","LINEAR","",1609.344],["British link (Benoit 1895 B)","LINEAR","",0.2011678249437587],["British foot (1865)","LINEAR","",0.30480083333333335],["Indian foot (1962)","LINEAR","",0.3047996],["Indian foot","LINEAR","",0.30479951024814694],["Indian foot (1937)","LINEAR","",0.30479841],["British foot (Benoit 1895 A)","LINEAR","",0.3047997333333333],["Indian foot (1975)","LINEAR","",0.3047995],["Indian yard (1962)","LINEAR","",0.9143988],["Indian yard (1975)","LINEAR","",0.9143985],["Indian yard","LINEAR","",0.9143985307444408],["link","LINEAR","",0.201168],["British foot (1936)","LINEAR","",0.3048007491],["Clarke\'s chain","LINEAR","",20.1166195164],["yard","LINEAR","",0.9144],["US survey mile","LINEAR","",1609.3472186944375],["Gold Coast foot","LINEAR","",0.3047997101815088],["chain","LINEAR","",20.1168],["Indian yard (1937)","LINEAR","",0.91439523],["British foot (Sears 1922 truncated)","LINEAR","",0.30479933333333337],["Clarke\'s foot","LINEAR","",0.3047972654],["British chain (Sears 1922 truncated)","LINEAR","",20.116756]]', '$[*]' columns (`UNIT_NAME` varchar(255) character set utf8mb4 path '$[0]', `UNIT_TYPE` varchar(7) character set utf8mb4 path '$[1]', `DESCRIPTION` varchar(255) character set utf8mb4 path '$[2]', `CONVERSION_FACTOR` double path '$[3]')) `ST_UNITS_OF_MEASURE` character_set_client: utf8 collation_connection: utf8_general_ci
ちなみにMySQL 8.0.21においてもこれらのテーブル定義は変わりありません。
sys.create_synonym_dbの動きを理解する
sys.create_synonym_db()は簡単に説明すると次のように動きます。
Step1. シノニムの元となるデータベースが存在するかチェックする、存在しなければその時点でエラーメッセージを返して終了する
Step2. シノニムに指定した名前を持つデータベースがすでに存在していないかチェックする、存在していればその時点でエラーメッセージを返して終了する
Step3. シノニムに指定した名前を持つデータベースを作成する
Step4. シノニムの元となるデータベースに存在するテーブル1つずつに対してViewを作成する。（FORループのように動作する）
今回の場合Step4で実行されるSQL文は次の通りです。
CREATE SQL SECURITY INVOKER VIEW `i_s`.`synonym_table` AS SELECT * FROM `information_schema`.`orgin_table`;
このSQLを手動で実行してみます。
mysql-root@localhost[i_s]>CREATE SQL SECURITY INVOKER VIEW `i_s`.`ADMINISTRABLE_ROLE_AUTHORIZATIONS` AS SELECT * FROM `information_schema`.`ADMINISTRABLE_ROLE_AUTHORIZATIONS`; Query OK, 0 rows affected (0.03 sec)
あれ？できる…謎だ(´・ω・`)
バグレポしますた
MySQL Bugs: #101258: Can't create synonym of information_schema using create_synonym_db()
短いですが今回はこの辺りで。
