##php/Laravelでのクエリ発行はEloquent/Query Builderを使用してDB処理を行うことが多い
と思います。
それを使用するとクエリで使用する文字列をインジェクションができないようにクリーンにすることなくSQLにバインドすることができるので、非常に便利。シンプルなSQLであれば直感的に記述しやすいですね。
使いやすい反面、SQLデータにバインドするデータの量は注意が必要 です。
##MySQLのプリペアドSQLで使用できるplaceholderは65,535 (2^16-1)個までしか使えない
という制約があります。制約の数を超えてSQLは実行エラーPrepared statement contains too many placeholderになります。
リストをフィルタしたとの結果に対して全チェックして処理をしたい場合に、対象データの数、更新対象カラムが多いとMySQLを利用したアプリケーションだと対応が必要になります。
今回の事象は一括更新処理にて発生しました。
記述されている処理の内容、対象データとしては下記のような状態でした。
・Modelの中の総カラム数が70で更新対象レコードが3,000件
++弊社の環境ではModelに拡張してbulkUpdatesのメソッドを追加しています。
##問題のコード
$getRecords = Model::whereIn('id', $posted->lists('model_id'))
->lockForUpdate()
->get(); // ・・・ (A)
$bulkUpdateRecords = collect(); // ・・・ (B)
foreach ($getRecords as $getRecord) {
$getRecord->target_column_a = 'aaa';
$getRecord->target_column_b = 'bbb';
$getRecord->target_column_c = 'ccc';
$bulkUpdateRecords->push($getRecord); // ・・・ (C)
}
Model::bulkUpdates($bulkUpdateRecords); // ・・・ (D)
(A) getRecordsにidでデータ取得とともにレコードロックをかけておきます。
(B) 一括更新用データ格納のための変数をcollection()を格納し、初期化します。
(C) 取得したレコードのカラムを更新する値をセットします。今回は三つのカラムに値をセットしています。
(D) 一括更新用データ(bulkUpdateRecords)をモデルに拡張されたメソッドに引き渡し一括更新を行っています。
bulkUpdates内で更新対象データを1,000件ごとにchunkして一括更新を行なっています。
この処理の流れで(D)の段階でPrepared statement contains too many placeholderのエラーがログに出力されました。
取得したレコードはテーブルに存在するカラム全ての値を保持した状態なので1レコード70カラム、更新対象を1,000件毎にchunkして更新ということで70 * １000 = 70,000のplaceholderを使用したSQLが実行されたことになります。
##対応方法
下記2点が考えられると思います。
- chunk数を500などに減らす
- キーカラム＋更新対象をbulk処理へ引き渡す
1.の対応方法はchunkの設定を変えるだけなのでお手軽に対応ができます。ただテーブルカラムが増えていくとエラーは再現してしまいます。
2.の対応方法はコードの量は断然増えてしまいますが、テーブルカラム増ではエラーが起きないので、いつかのマイグレーションでここの処理がエラーすることはなくなります。
今回は2.の対応で修正しました。
##修正後のコード
$getRecords = Model::whereIn('id', $posted->lists('model_id'))
->lockForUpdate()
->get();
$bulkUpdateRecords = collect();
foreach ($getRecords as $getRecord) {
$update = new Model(); // ・・・ (a)
$update->id = $getRecord->id; // ・・・ (b)
$update->target_column_a = 'aaa';
$update->target_column_b = 'bbb';
$update->target_column_c = 'ccc'; // ・・・ (c)
$bulkUpdateRecords->push($update);
}
Model::bulkUpdates($bulkUpdateRecords);
(a) 更新テーブルのクラスで新しいインスタンスを作成します。
(b) 今回はupdate処理なので新しく作成したインスタンスにidをセットする必要があります。
(c) 新しく作成したインスタンスの更新カラムに値をセットします。
記述は特に難しくなく新しいモデルインスタンスを作成しidをはじめとした必要カラムをセットしてbukl処理にのせるだけです。これでPrepared statement contains too many placeholderの対応は完了です。
##too many placeholderが起きる可能性は至る所にあります。
今回のコードの中でもロックの際にidをwhere inで取得しているところもidが65,535を超えない保証はなかったり、bulkupdateでなく、bulkinsertの処理では基本的には全カラムを指定してクエリを作成しないといけない場面などなど。
selectの場合はBuilderをクローンしてchunkで処理、レコードをマージする、bulkinsertではchunkを調整する、などケースバイケースで対応していくしかないと思います。
MySQL環境でクエリを作成・実行する際にはplaceholderで指定する動的データ数を常に意識して楽しいMySQLライフを送りましょう。