2011年1月12日水曜日

MySQL:VIEWを利用する際の注意点


MySQLのVIEW機能は複数のクエリ結果を1つのテーブルとして扱え、毎回複雑なクエリを打たなくても詳細な結果が得られるのでとても便利な機能です。



無駄にかっこよく言いましたが、複雑なクエリのエイリアスを作れるということです。



いきさつ


DBはレプリケーションが有効な通常の冗長構成です。


VIEW作ったのにスレーブ側にSELECTができないという現象に遭遇し該当のテーブルを調べたところwarinigがでていました。


さらに、毎日DBのバックアップを取っていたのですがAccess denied when using LOCK TABLESのエラーも発生しました。


実際に該当のテーブルにSELECTすると1044: Access denied for userと表示されSELECTできないことを確認しました。


rootなのになぜかアクセス権がないようです。



VIEW構文


VIEWを使うには以下のクエリを使います。



利用方法は今回の記事では割愛させていただきますがこちらの記事などが詳細に解説されております。



CREATE


[OR REPLACE]


[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]


[DEFINER = { user | CURRENT_USER }]


[SQL SECURITY { DEFINER | INVOKER }]


VIEW view_name [(column_list)]


AS select_statement


[WITH [CASCADED | LOCAL] CHECK OPTION]



CREATE VIEW 構文



SQL SECURITY


VIEWにはVIEWを作成するたびにアクセス権を設定することができます。


デフォルトでは、CREATE VIEWを実行したユーザになります。


前述したAccess denied for userはこのアクセス権に引っかかっていたようです。


先に解決策としてはSQL SECURITY DEFINER ではなく SQL SECURITY INVOKERにする事です。


この影響でバックアップ時にテーブルロックが拒否されバックアップもできてなかったようです。



なぜおきたのか


実は各DBのユーザを厳密化するために、mysql.userテーブルはレプリケーションにより同期されない仕組みにしていました。


その結果マスターでCREATE VIEWが実行され、各スレーブにも同様のクエリが流れるがスレーブにも伝わりますがユーザがいないためSELECTした際にエラーがでるということです。


A5ERなどのツールを用いてDBを管理している場合はSQL SECURITY INVOKERが使えないことがあるので注意してください。


突然Access denied for useが出た場合はVIEWを疑ってみるといいかもしれません。




0 件のコメント:

コメントを投稿