銀河鉄道~ぽんこつエンジニアのブログ~

思いつきで書いています。

sql_mode=STRICT_TRANS_TABLESにはまった話

MySQL5.6からSQLモードに「STRICT_TRANS_TABLES」がデフォルトで追加された。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 1.7.3.3 無効データの制約

これに関連して、プロシージャーの実行にはまったのでメモ。

環境は以下の通り。
・MySQL5.7
・クライアント:DBeaver
・ドライバ:JDBCドライバ

上記の環境で、DBeaverからストアドプロシージャーを適用し、実行すると「1366 (HY000): Incorrect string value」というエラーが発生。
ストアドプロシージャーの中で、int型変数を空文字('')で初期化しようとしていたことを怒られたのですが、
この現象、以前は発生していませんでした。

以前はと何が違うかというと、クライアントにMySQL workbenchを使用していました。
今回、諸事情により、クライアントをDBeaverに変更したことでこの事象が発生するようになりました。

調査した結果、DBeaverが使用しているJDBCドライバは、デフォルトで「STRICT_TRANS_TABLES」を使用する仕様(バグではなさそう。JDBC準拠?)になっており、
DBeaverでつなぐとセッション変数のsql_modeに強制的に「STRICT_TRANS_TABLES」が追加されていたことがわかりました。

MySQL Bugs: #24526: JDBC connector forces strict mode regardless of server setting
MySQL :: MySQL 5.1 リファレンスマニュアル :: 24.4.4.3 Java 、JDBC および MySQL のタイプ



しかも、もう一つはまった要因として、

「ストアドプロシージャーはCreateされた時のsql_modeで実行される」ということ。
このおかげで、いくらsql_modeを変更してもストアドプロシージャーの動きが変わらずエラーが出続けていました。


対応策として、以下のどちらかで、正常終了するようです。
JDBCドライバの接続のURLでjdbc:mysql://localhost/dbname?jdbcCompliantTruncation=false とする。
②セッション変数のsql_modeから「STRICT_TRANS_TABLES」を外した状態で、Create Procedure を行う。

ちなみにストアドプロシージャーが何のsql_modeに紐づいているかを確認するには、「SHOW CREATE PROCEDURE test1 \G」を実行するとよいです。

MySQLパフォーマンスチューニング

その1 ボトルネックをみつける。
 Explainにを読んでどこに時間がかかっているか確認する。
 対象SQLのJOINを一つずつ外してみる。
 
その2 インデックスの検討。
 正しいインデックを使っているか。
 複合インデックスを使う。
 余計なインデックスを外す

その3 SQLの改善。
 サブクエリ、相関サブクエリをJOIN句に書き換える。
 LEFT JOINをExists文に書き換える。
 UNIONに書き換える。
 UNIONを外す。

その4 テーブルIOの改善。
 バルクインサートを使う。
 ファイルに書き出してからloadコマンドで取り込む。
 メモリテーブルに変更する。

その5 ロジックの検討。
 ぐるぐる系をどかん系に変更する。
 ワークテーブルを使う。

その6 ハードの改善
 メモリを増やす。
 性能の良いハードディスクに替える。

「羊と鋼の森」を読んだ。

バイブルにしたい本に出会いました。

文体が軽やかで、すらすら読めます。すらすら読めすぎて、気がついたら物語に引き込まれてました。

登場する主人公含め調律師4人は、それぞれ人間的で、それでいて魅力的です。

 

音楽、ピアノ、技術。苦悩、葛藤、憧れ。調律師という職業と主人公の半人前感が、エンジニアとして通じるものがあり、大事なことを教えてくれている気がました。

f:id:giovanni0517:20180605092210j:image

 

MySQL5.7でSELECTでロックが保持されてしまう

MySQLで日次バッチ実行中に、ロック待ちタイムアウトが発生しエラーとなりました。

java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction


原因はもう一つ裏で動いていたプロシージャーB。
そのプロシージャー内の処理で、以下のように記述をしていた箇所で、
なぜか行ロック(Sロック)を取得していました。

※カーソルによるループ

start transaction;

    if exists( select 1 from t_table where id = xxxx ) then
        /* 何らかの処理 */
    end if;

commit;


そのため、プロシージャーAでt_tableのid = xxxx のデータをUPDATEしようとしてロック待ちが発生し、
プロシージャーBの処理が長引いたため、タイムアウトが発生したようです。

select ~ for updateをつけているわけでもなく、単純なSELECT文なのに
Sロックを取得する理由がわからず...。
ちなみに分離レベルは、REPEAD TABLEでした。

いったん上記を下記のように修正したところ、ロックを取得することはなくなりました。

※カーソルによるループ

start transaction;

    select count(*) into HOGE from t_table where id = xxxx;

    if HOGE > 0 then
        /* 何らかの処理 */
    end if;

commit;

MySQL MEMORYストレージエンジンとテンポラリテーブルについて

MySQLは、テーブル作成の際、いくつかのストレージエンジンから選択することができます。

デフォルトはInnoDB。その他、MyISM、MEMORYなど様々な種類があります。

 

MEMORYストレージエンジンは、格納されたデータがすべてメモリ上に保持されます。

InnDBやMyISMでは、データはハードディスクへ書き込まれるため、読み書きのオーバーヘッドがなく、IOが非常に高速になります。

 

ただし以下の注意点があります。

1、メモリ上にデータを保持するため、DBサーバーが落とされると格納されたデータは消える。(空のテーブルのみ残る。)

2、ロック制御がテーブルロック(MyISMと同様、InnoDBは行ロック)のため、並列処理でパフォーマンスが落ちやすい。

3、カラムはすべて固定長に変換されるため、一レコードのバイト数が多くなる。また、BLOB、TEXTは使用できない。

4、インデックスは「HASH」がデフォルト。InnoDBやMyISMは「BTREE」がデフォルト。どちらも使用可能。「HASH」は場合によって遅くなる可能性あり。

 

2、の解決案として、一時テーブル(Temporary Table)として使えば、クライアントごとに別々になるため並列アクセスが起きなくできる。

結果、パフォーマンスを保てるのではないか。

 

ただ、一時テーブルは指定の容量を超えると自動的にInnoDBに変換されるため、変換コストが発生するという話もあるが、、、

 

(以下追記

⇒ユーザーが作成したMERORYテーブルは変換されないと公式に見つけた。

⇒CREATE TEMPORARY TEBLEで作成されたMEMORYテーブルは自動変換されない。自動変換されるのはあくまで、内部的に自動で作られる一時テーブルのみ。

truncate tableと制約

truncate tableは、外部キーなど制約がかかっているとエラーになる。

相手側テーブルにデータがあってもなくても同様。
制約を解除するか、DELETE文で削除する。

straight_join

一般的には、joinの順序はオプティマイザが一番効率の良い順序を探し、

その順番で実行してくれます。

しかし、まれにそれよりもレスポンスの良い順序に指定したい場合があります。
MySQLでは、そのような場合、joinの代わりに、straight_joinを使います。
 
straight_joinは、左のテーブルの次に右のテーブルを読み込むことを強制します。
ある場合においてはstraight_joinを使用することでパフォーマンスが改善する可能性があります。
 
ただし、オプティマイザは、データ数やSQLの条件によって順序を決めていますので、
straight_joinを使って順序を固定することで逆にパフォーマンスが落ちる可能性もあります。