目次
はじめに
MySQL5.6から8.0環境に移行した際に以下エラーで正常に動作しませんでした。
この記事ではエラーの解消する手順を紹介したいと思います。
The user specified as a definer ('mysql.infoschema'@'localhost') does not exist原因
移行前と移行後で標準データベースエンジンがMyISAMからInnoDBに切り替わったため。
Note
If MySQL is upgraded from an older version but the grant tables have not been upgraded from MyISAM to InnoDB, the server considers them read only and account-management statements produce an error.
Due to the change of storage engine from MyISAM to InnoDB, SELECT without ORDER BY on grant tables can produce different row orders than previously. If a query result must have specific row ordering characteristics, include an ORDER BY clause. (WL #7158, WL #9045)
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-0.html
前提条件
通常mysql_upgrade(mysqld upgrade)で解消されるかと思いますがだめだったため進めています。
- 移行前のMySQLのエンジンがMyISAMであること確認済み。
- MySQL 5.7が乗っている別サーバで5.6から5.7にupgrade済み。
mysqld upgrade --FORCEなどのupgrade実行後も解消されない。- 移行後はログイン以外のコマンドでエラー。
5.X環境で修正
確認コマンド
以下SQLでMyISAMが使われているテーブルを確認できます。
SELECT TABLE_NAME, ENGINE
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'performance_schema', 'sys')
AND ENGINE = 'MyISAM';MyISAM→InnoDB変換SQL出力sed
ダンプしたファイルに対して実行し、その後リストアします。
sed 's/ENGINE=MyISAM/ENGINE=InnoDB/g' [DATABASE_FILE].sqlMyISAM→InnoDB変換SQL出力コマンド
sedなど書き換えるとダンプファイルが破損する場合はSQLで修正できます。
以下実行でMyISAM→InnoDBに変換できるSQLを出力するのでそれを別途実行します。
SET @DATABASE_NAME = '{ここにテーブル名}';
SELECT CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements
FROM information_schema.tables AS tb
WHERE table_schema = @DATABASE_NAME
AND `ENGINE` = 'MyISAM'
AND `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name DESC;確認コマンド
出力が0件になっていれば大丈夫です。
SELECT TABLE_NAME, ENGINE
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'performance_schema', 'sys')
AND ENGINE = 'MyISAM';ユーザ再作成
新環境で以下ユーザ存在しておらず積むのであらかじめこちらで作成しておきます。
mysql> CREATE USER 'mysql.infoschema'@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT SELECT ON *.* TO `mysql.infoschema`@`localhost`;
Query OK, 0 rows affected (0.00 sec)8.0環境で確認
上記修正後、ダンプしてからリストアし動作を確認して完了になります。
おわりに
MyISAMからInnoDBへの変更に伴う問題に直面しなおかつupgradeで修正できませんでしたが無事に解決することができました。この記事がどこかしらなにかしら参考になれば幸いです。
参考にさせていただいた記事
https://stackoverflow.com/questions/54292491/how-to-fix-error-1726-hy000-storage-engine-myisam-does-not-support-system-t
https://stackoverflow.com/questions/62127983/error-1449-hy000-the-user-specified-as-a-definer-mysql-infoschemalocalho
https://www.kabanoki.net/8271/
https://stackoverflow.com/questions/3856435/how-to-convert-all-tables-from-myisam-into-innodb
https://www.digitalocean.com/community/tutorials/how-to-create-a-new-user-and-grant-permissions-in-mysql-ja
https://cloud.google.com/sql/docs/mysql/faq?hl=ja
