Windows7上のMySQLストアドプロシージャを体験してみた

相変わらず、スケジューラ関連のカレンダーデータの周辺をうろうろしている。
シリアル値とセットの日付、その日が営業日かどうかのフラグといった3フィールドの簡単なテーブルを作ることを考える。
但し、1日1レコードの数万から十数万行のテーブルを想定する。
シリアル値は重複せず欠落もしない引き続いた非負の整数とする。
シリアル値と日付表現は厳密には関数関係を持つため情報として重複するがそこは実用の利便を考えて不問。
PHPからメンテすることを考えて、テーブルの生成（CREATE）、削除（DROP）、消去（DELETE）は特に問題なし。

但し、表の初期化で問題発覚。

遠隔で数万回のループでSQLを発行していると、通信がフリーズしてしまう。
追いかけると、2000行を超えたところで、処理が凍結してしまう。
各種タイムアウトの設定をいじっても特に改善しない。
また、サーバローカルで同じ操作をすると、多少時間は食うものの、特に問題なく処理は完結する。

まだ、この状況は解法の見通しは立っていないが、PHP内でのループをやめて、
MySQLの中で回ってもらおうかという試行錯誤のメモ。
いろいろ調べたが、これにより早くはならないとする情報は見たものの、
そもそもMySQLのストアドプロシージャの易しいまとまった解説が見当たらない。
その中で、取り敢えず動いたことと、その中で問題と思われるポイントのメモ。

今回はphpMyAdminの「データベース【データベース名】上でクエリを実行する」という場所で実行を行なったためか、テーブル名を『`【データベース名】`.`mst_calenderinfo`』とデータベース名まで含めて指定するとエラーが出た（ような気がする）
“END IF;”をスペースを使わずに“ENDIF;”と記述したらエラーが出た。なんだこりゃ？
主キーがiSerである行が存在するか否かを見つける定石『SELECT COUNT(*) FROM 〜 WHERE 〜』は使わない方が良いという話を聞き、そこの部分がたどたどしくなっていて、それが好いのかどうだかよく分からない。けど動いてそうだからよしとする。
で、動いたものが次の通り。（iSerをループで変化させ、主キーがiSerの行が見つかればUPDATEし、見つからなければINSERTする）

DROP PROCEDURE IF EXISTS calTblInit_sp;
DELIMITER $$
CREATE PROCEDURE calTblInit_sp
(
  IN iSerSta MEDIUMINT UNSIGNED,
  IN iSerSto MEDIUMINT UNSIGNED,
  IN dateSta DATE,
  IN eigyobi_cd_init TINYINT
)
routineBody: BEGIN
  DECLARE dateInLoop DATE;
  DECLARE cnt INT UNSIGNED;
  DECLARE iSer MEDIUMINT UNSIGNED;
  SET iSer = iSerSta;
  SET dateInLoop = dateSta;
  mainLoop: REPEAT
    SELECT COUNT(`dateSerial`) INTO cnt FROM `mst_calenderinfo` WHERE EXISTS (SELECT * FROM `mst_calenderinfo` WHERE `dateSerial`=iSer);
    IF cnt > 0 THEN
      UPDATE `mst_calenderinfo` SET `eigyobi_cd`=eigyobi_cd_init, `dateValue`=dateInLoop WHERE `dateSerial`=iSer;
    ELSE
      INSERT INTO `mst_calenderinfo` (`dateSerial`, `dateValue`, `eigyobi_cd`) VALUES (iSer, dateInLoop, eigyobi_cd_init);
    END IF;
    SET iSer = iSer + 1;
    SET dateInLoop = ADDDATE(dateInLoop, INTERVAL 1 DAY);
  UNTIL iSer > iSerSto
  END REPEAT mainLoop;
END routineBody;
$$
DELIMITER ;

PHPからは、次のように呼び出す。

$link = mysqli_connect('【サーバ名】','【ユーザ名】','【パスワード】','【データベース名】') or die("FAILURE for connection to MySQL");
mysqli_query($link,"set names utf8");
$result = mysqli_query($link,"CALL calTblInit_sp(【開始シリアル値】, 【終了シリアル値】, '【開始日付YYYY-mm-dd形式】',【初期化する営業日フラグ（0など）】);") or die("FAILURE for sending QUERY");
mysqli_close($link) or die("FAILURE for disconnetion from MySQL");

さて、遠隔環境でどうなるか楽しみ。（~~まだ試していない~~続きは↓）

結果

タイムアウト（30秒）になることに変わりはなかった。
MySQLサーバを見てみると、要求した処理は完了していた。（数万行のレコードは初期化されていた。）PHPでループをまわすと全くデータ自体に変化がなかったため、ここが大きく変わるポイント
今後の課題
- 姑息対応：もしかしたらタイムアウト時間調整で逃げられる（これ以上の重い処理の想定がなければ‥）⇒ダメっぽい
- 本質対応：非同期の導入（どうやって？）⇒curlというものがあるみたい

バッチジョブをサブミットして、それからのレスポンスは/dev/nullへ捨てる‥
みたいな処理は出来ないかな。
タイムアウトさせて投げ捨てる感じかなぁ（なんか無責任なｗ）

よくまとまっていて参考にした資料

MySQL ストアドプロシージャ
公式マニュアルなど‥

cURLで望みの動作は出来た

PHP.iniのコメントを外す。（『extension=php_curl.dll』の行ね）
所定の公式指定のDLL 2ファイルをPATHを通す。（PHPフォルダにある『libeay32.dll』と『ssleay32.dll』を「System32」フォルダへコピー）
Apache再起動

で、cURLが使えるようになる。
ストアドプロシージャを使うだけのPHP スクリプトを書いて、マルチスレッドの機能を応用して、サブ接続として実行する。

で、ポイント

セッション変数は飛ばせない。
ので、リンクは変数として渡せないから、呼ぶスクリプト内で新規にMySQLサーバと通信させる。
但し、require_once()とかは利くから、共通の外部変数とかは使える（初期化は別途やる必要あり）
変数はGETで送ります。一つ目の変数は?だけど、二つ目以降の変数が&でなければならないところに嵌った。基本的なことだけど。
ストアドプロシージャは、PHP（＆WEBサーバ）のタイムアウトが起きても、延々と最後まで実行を続けるようだ。これを逆手にとって、タイムアウトの設定を30秒よりも十分短く指定して子プロセスを呼んでやれば、一方通行だけど擬似的に非同期の動作が実現できた。結果の回収は全く考えていない。
タイムアウトになったことを主プロセス側で認識できたら、この姑息解としては完璧なんだが‥後で調べてみる

その後

curl_multi_select()関数で、返値が0の時がタイムアウトらしい。
http://php.net/manual/ja/function.curl-multi-select.php#85010
だが、ちょっとまだ完璧には理解できていない。
タイムアウトの指定時間が短すぎると、1度だけ0を返して一連の処理は終わるが、どうもストアドプロシージャは開始していない。
タイムアウトの時間をある程度（最適値が分からない、少なくとも2秒以上）にすれば、2回以上タイムアウトの返値が発生する。そしてその際はストアドプロシージャは開始している。
そこで、返値が0である回数をループの中で数えて、2回以上ならば、バックグラウンドジョブ投入が成功したとみなしておくことにした。
ただ、嵌まったのは、この方法では、参考にしたページ
http://qiita.com/items/1c67b51040246efb4254
とは、飛ばす子プロセスの内容が違うため、タイムアウトのイベントで同じように
contunue文でcurl_multi_exec()をスキップすると無限ループになりえらい目に遭います。何も受け付けなくなり再起動せざるを得なくなりました。

何はともあれ、これで必要な手段は出揃った感じです。