adiary開発日誌

Webアプリケーションで、prepare_cached() を使うと色々問題があったのでメモ。

• 問題あり
• テストコード
• DBD::Pgの場合 on PostgreSQL
• DBD::mysql on MariaDBの場合
• prepare()でも落ちるのか？
• まとめ

問題あり

prepare_cached() を使用すると、キャッシュヒット時（同じSQL文実行時）に$sthを再生成せずに再利用され、データ取得を含めた実行速度が220usが200usになるぐらいの効果があります。

しかし、prepare_cached() された状態でテーブルのカラムが変更されると問題が起こることが分かりました。

テストコード

#!/usr/bin/perl
use strict;
use DBI ();

my %DB_attr = ();
my $dbh = DBI->connect("DBI:Pg:database=test", 'test', 'test');
# my $dbh = DBI->connect("DBI:mysql:database=test", 'test', 'test');

&do_sql("DROP   TABLE test");
&do_sql("CREATE TABLE test(pkey serial, x int)");
&do_sql("INSERT INTO  test(x) values(100)");

my $sel = "SELECT * FROM test";
&do_sql($sel);
&do_sql("ALTER TABLE test ADD y int default 333");
&do_sql($sel);

sub do_sql {
	my $sql = shift;
	my $sth = $dbh->prepare_cached($sql);
	print "$sql\n";
	$sth->execute(@_);
	if ($sql =~ /^select /i && 0<$sth->rows) {
		print "fetch()\n";
		my $h = $sth->fetchall_arrayref({})->[0];
		print "x=$h->{x}, y=$h->{y}\n";
	}
	$sth->finish();
}

DBD::Pgの場合 on PostgreSQL

DROP   TABLE test
CREATE TABLE test(pkey serial, x int)
INSERT INTO  test(x) values(100)
SELECT * FROM test
fetch()
x=100, y=
ALTER TABLE test ADD y int default 333
SELECT * FROM test
fetch()
Segmentation fault

fetchするとSegmentation faultで落ちます。

DBD::mysql on MariaDBの場合

DROP   TABLE test
CREATE TABLE test(pkey serial, x int)
INSERT INTO  test(x) values(100)
SELECT * FROM test
fetch()
x=100, y=
ALTER TABLE test ADD y int default 333
SELECT * FROM test
fetch()
x=100, y=

さすがに落ちたりはしませんでしたが、追加したカラムのデータが取得できていません（y=NULLになってしまう）。

prepare()でも落ちるのか？

prepare_cached()ではなくprepare()してからsleep(10)とかして、他プロセスからテーブルを変更した（zカラムを追加した）場合はどうなるか検証してみました。

my $sth = $dbh->prepare($sql);
sleep(10);
$sth->execute(@_);
$sth->fetchall_arrayref({});

DBD::Pg, DBD::mysql共に、zの値を含めて取得できました。

しかも、1回だけのprepare_cached()ならば、同様にzの値を含めて取得できました。

my $sth = $dbh->prepare_cached($sql);
sleep(10);
$sth->execute(@_);
$sth->fetchall_arrayref({});

ただし、2回目の以降（再利用された$sthハンドル）の場合は、他プロセスからの変更でも同様に無力でした（PostgreSQL, MySQL共に症状変わらず）。

更に調べると、一度でも「fetchall_arrayref()」したハンドルを、テーブルカラム変更後に再利用すると問題が起こることが分かりました。

ソースは確認していませんが、fetch処理でテーブルのカラム情報がハンドルに保存（キャッシュ）されていると予想。

まとめ

• prepare_cached() で再利用したハンドルは、テーブルの変更に対して問題が多い。
• プロセスが常駐するなど、prepare_cached()でハンドルが永続化され、しかもテーブルの変更が起こりうる状況ではprepare_cached()を使用しないほうが良い。