###前提・実現したいこと
GCP(Compute Engine + SQL)でウェブサイトを構築しています。
既存のSQLインスタンスのデータをエクスポートし、新しく作成したSQLインスタンスにデータをインポートしようとすると以下のエラーが発生してしまいました。

MySQL
1ERROR 1227 (42000) at line 885: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

データのインポートは一見できているように見えるのですが、
GROUP_CONCAT(tag.id SEPARATOR ',')
といった命令が入ったリクエストすると動作しなくなってしまいました。

何か打開策をご存じの方が居られましたらご教授お願い致します。

行動規範の内容に同意します

回答1件

ベストアンサー

推測による回答ではありますが、、

おそらく、以下のような状況ではありませんか？

mysqldump コマンドでインポートするデータを作成している。
その中に "CREATE VIEW 〜"文が含まれている。
エクスポート元のインスタンスのユーザー名とインポート先のインスタンスのユーザー名が異なる。

であれば、インポートするデータの中に以下のような行が含まれているはずです。

sql
1/*!50013 DEFINER=`[エクスポート元のユーザー名]`@`[そのインスタンスにアクセスする IP またはホスト名]` SQL SECURITY DEFINER */

この行を削除し、再度、インポートしてみてください。

エラーの内容は
「SUPER 権限を持っていないユーザーが、それを必要とする SQL を実行しようとした」
というものです。
https://dev.mysql.com/doc/refman/5.6/ja/error-messages-server.html

エラー: 1227 SQLSTATE: 42000 (ER_SPECIFIC_ACCESS_DENIED_ERROR)

メッセージ: アクセスは拒否されました。この操作には %s 権限が (複数の場合はどれか 1 つ) 必要です。

MySQL のビューはそのビューにアクセスできるユーザーを制限できるようになっており、デフォルトでは、それはそのビューを作成したユーザーです。
https://dev.mysql.com/doc/refman/5.6/ja/create-view.html

DEFINER および SQL SECURITY 句は、そのビューを参照しているステートメントの実行時に、そのビューに対するアクセス権限を確認するときにどの MySQL アカウントを使用するかを決定します。

（中略）
DEFINER のデフォルト値は、CREATE VIEW ステートメントを実行するユーザーです。

で、手元の環境（MySQL5.7）で、以下のような現象となることが確認できました。

DEFINER を明示せずビューを作成しても、mysqldump でエクスポートすると、ダンプファイル内では上述の 'DEFINER=' が明示される。
SUPER 権限を持っていないユーザーで自分以外のユーザーを DEFINER に指定しようとすると、ご質問のエラーメッセージが表示される。
ダンプファイルのインポートで↑の操作を行なった場合、そのビューの作成だけがスキップされる（その他のテーブルは正常に作成される）。

ちなみに、Cloud SQL では SUPER 権限を持つユーザーを作成できないようです。
https://cloud.google.com/sql/faq

Google Cloud SQL does not support SUPER privileges,

追記

ダウンロードしたSQLファイルで「SECURITY DEFINER」キーワードを探してみたのですが、存在が確認できませんでした。

そもそも、エクスポート元のインスタンスで VIEW を作成した記憶はありませんか？

「SECURITY DEFINER」では、検索するキーワードとして長過ぎます。
スペースの数が違うだけでも、ヒットしなくなってしまいますので。

"DEFINER" または "INVOKER" だけで検索してみてください。

また、どうやらストアドプログラム (プロシージャー、関数、トリガー、およびイベント) も、ビューと同じように実行可能なユーザーを制限できるようです。
https://dev.mysql.com/doc/refman/5.6/ja/stored-programs-security.html

すべてのストアドプログラム (プロシージャー、関数、トリガー、およびイベント) とビューには、MySQL アカウントを指名する DEFINER 属性を含めることができます。

念のため、こちらも含まれていないか、確認してみてください。
それぞれの確認方法は、「MySQL プロシージャー一覧」などで検索すると分かると思います。

Cloud SQLでデータベースをインポートする際は、コマンドではなく管理画面でインポートボタンを押すことで実行するので「mysqldump コマンドでインポートするデータを作成している。」については問題ないと思います。

マニュアルによると、
Cloud SQL では、エクスポートするデータの形式は"MySQL ダンプファイル"または"CSV"のいずれかを選択でき、
エクスポートする際、フォーマットに"SQL"を指定すると、"MySQL ダンプファイル"となるようです。

https://cloud.google.com/sql/docs/import-export/exporting

Decide what file type you are creating: a MySQL dump file or a CSV file.

To export data to a MySQL dump file
（中略）
5. Set the Format to SQL.

おそらく、"MySQL ダンプファイル"はmysqldump コマンドを実行した場合と全く同じか、同じような形式のデータになっているはずです。
mysqldump コマンドで作成したデータも、実体は SQL 文ですので。

それでも解決できない場合、可能であれば、実行に失敗する SQL文（どれか1つで結構です）の全文と、その SQL文がアクセスしている全てのテーブルのテーブル定義を質問に追記してください。

テーブル定義は、以下の SQL を実行すれば分かります。

sql
1SHOW CREATE TABLE [テーブル名];

https://dev.mysql.com/doc/refman/5.6/ja/show-create-table.html

追記2

今までは、SQLインスタンスまるまるエクスポートしていたのですが、試しにコピーしたいデータベースのみをエクスポートするとエラーが吐かれませんでした。

なるほど。
恐らく（information_schema など）MySQL が勝手に(w)作成するスキーマの中に含まれていた何らかのオブジェクトが、インポート時にエラーになっていたのかもしれません。

「コピーしたいデータベースのみをエクスポートするとエラーが吐かれません」
であれば、それで問題ないかと思います。

で、

ただ、SQL文のエラー結果は変わりませんでした。

そうすると、こちらはインポート時のエラーとは別の問題だと考えられます。
出力されるエラーメッセージを確認してみてください。

PHPからmysqliでSQL文を実行するとエラーが発生するようです。

ということなので、mysqli で問題の SQL文を実行（$mysqli->query()またはmysqli_query()）した直後に$mysqli->errorまたはmysqli_error($link)で、エラーメッセージを取得できるはずです。
http://php.net/manual/ja/mysqli.error.php

投稿2016/09/03 08:52

編集2016/09/03 16:15

KiyoshiMotoki

総合スコア4791

RyuichiTani

2016/09/03 09:25

ご回答ありがとうございます！ただ、解決することができませんでした。すみません。。。ダウンロードしたSQLファイルで「SECURITY DEFINER」キーワードを探してみたのですが、存在が確認できませんでした。また、Cloud SQLでデータベースをインポートする際は、コマンドではなく管理画面でインポートボタンを押すことで実行するので「mysqldump コマンドでインポートするデータを作成している。」については問題ないと思います。