無精で短気で傲慢なプログラマ

UNIX や web やプログラムの技術的なことを中心に。

MySQL と RaiseError とサーバサイド prepare

昨日から今日にかけて はまったこと。

DBI + DBD::mysql + MySQL で、RaiseError が効かない。

 use DBI;
my $dbh = DBI->connect(
'DBI:mysql:dbname', 'user', 'password',
{AutoCommit => 0, RaiseError => 1, PrintError => 0}
) || die;
my $sth = $dbh->prepare("select * from not_exist_table");
$sth->execute;


上記のように、存在しないテーブルやカラムに対して DML を発行しようとすると、
RaiseError を ON にしておけば SQL 解析の prepare で勝手に die してくれる。

エラーはこんな感じ。
  DBD::mysql::st execute failed: Table 'dbname.not_exist_table' doesn't exist at a line 5.

これは Perl における use strict のようなもので、RaiseError を ON
にするのは当然の話。

しかし、ある環境で実行すると、
  Can't call method "execute" on an undefined value at b line 6.
となってしまった。一応プログラムは異常終了しているのだが、これはテーブルが
存在しないので prepare は undef を返して終了し、undef なものに対して execute
メソッドを実行したから落ちただけのこと。prepare の時点で RaiseError により
落ちたわけではない。

RaiseError も効かないし、PrintError も効かない。それどころか
  my $sth = $dbh->prepare("select * from not_exist_table") || die "$DBI::errstr";

でも落ちない。

どうしたものかと思って調べていたら、

DBD::mysqlより引用:
Prepared statement support (server side prepare)

As of 3.0002_1, server side prepare statements are on by default
(if your server is >= 4.1.3)

To use driver emulated prepared statements, all you need to do
is set the variable mysql_emulated_prepare in the connect:

$dbh = DBI->connect( "DBI:mysql:database=test;host=localhost;mysql_emulated_prepare=1",
"", "", { RaiseError => 1, AutoCommit => 1 } );

* Note: delimiter for this param is ';'

To make sure that the 'make test' step tests whether server prepare works,
you just need to export the env variable MYSQL_SERVER_PREPARE:

export MYSQL_EMULATED_PREPARE=1


ということらしい。DBD::mysql-3.0002_1 から「サーバサイド prepare」というものが
デフォルトで有効になったとか。

というわけで、
 my $dbh = DBI->connect(
    'DBI:mysql:dbname;mysql_emulated_prepare=1', 'user', 'password',
    {AutoCommit => 0, RaiseError => 1, PrintError => 0}
   ) || die;

として解決。

で、「サーバサイド prepare」とは何かと言うと、
 MySQL Lists: mysql-ja: Prepared Statement (訳)
らしい。
  • これまでの DBD::mysql の prepare はクライアントサイドの prepare を使っており、
    速度向上にはあまり寄与していなかった。
  • DBD::mysql-3.0002_1 からサーバサイドの prepare を使うようにした。

というのはわかるが、mysql_emulated_prepare=1、つまり prepare をエミュレートするとは
どういうことか? サーバサイドの prepare はなぜ RaiseError が効かないのか?
ってのが全くわからない。

Oracle だと prepare ってのは要はカーソルオープンしっぱなしにするわけで、
つまりはサーバサイドなわけだが、MySQL の言うサーバサイド prepare ってのは
それと違う?

よくわからん人はおとなしく DBD::mysql-2.9x でも使ってなさいってことか。
PageTop

3値論理――神のいない論理

以前、DB 歴 10年目くらいの人が
 「あれ、NULL が入っている行って『カラム名 = NULL』で SELECT できるんじゃないの?」
とわたしに言った。情けない。

と嘆いたところで業界から不勉強な人が減るわけでもないので、カラムには極力 NOT NULL をつけましょう。

NULL を嫌悪する者として、いろんなところで宣伝してまわっているのが以下のページ。

世の中のカラムに NOT NULL 制約がひとつでも増えることを願います。
トップページは ミックのページ
PageTop

perl+DBI プログラムの鉄則 - fetchrow_arrayref を使え

perl+DBI プログラムの鉄則 のつつき。

鉄則その3.

   while ( my $arr_ref = $sth->fetchrow_arrayref ){
      my ($hoge, $fuga) = @$arr_ref;
      ...
   }

SELECT 時は上記のように fetchrow_arrayref を使うこと。そして必ずスカラーに
代入すること。せっかく fetchrow_arrayref を使っていても

   while ( my $arr_ref = $sth->fetchrow_arrayref ){
      print "$$arr_ref[0] $$arr_ref[1]\n";
   }

などと書いては台無しである。配列のインデックスで指定すると取得カラムの
増減に非常に弱い。そしてなりより、$$arr_ref[1] が何を意味するのか
さっぱりわからない。


fetchrow_array は使わない。複数カラムを取得する場合は問題ないが、
1カラムのみ取得する際に

   $sql = "SELECT hoge FROM table1";
   $sth->prapre($sql);
   $sth->execute;
   while ( $hoge = $sth->fetchrow_array ){
      ...
   }

とスカラーコンテキストで fetchrow_array を使ってしまうと問題が
出てくる。もしこのとき hoge が NULL であった場合、fetch 途中にも
かかわらずループが終了してしまうから。

fetchrow_hashref は使わない。

   while ( my $hash_ref = $sth->fetchrow_hashref ){
      print "hoge=[$hash_ref->{hoge}] fuga=[$hash_ref-{fuga}]\n";
   }

と書けるのは便利ではあるが、ハッシュはタイプミス耐性がない。

   print $hash_ref->{hoge};



   print $hash_ref->{moge};

とタイプミスした場合、値は undef になる。しかし DB 内の NULL は、DBI に
おいては undef として扱われるため、タイプミスと NULL の区別がつかない
(exists で調べれば区別はつくが、そんなことをわざわざやる人はほとんど
いない)。

fetchall 系は、最初は教えない。数百万・数千万・数億レコードの相手を
するようになったときに困るから。最終的にオンメモリに置くデータなら
別にかまわないが、それはメモリサイズを直感的に把握できるようになってから。


fetch というメソッドもあるが、これは fetchrow_arrayref の別名となっている。
fetch という短くてタイプしやすいメソッド名の権利を獲得したのは
fetchrow_array でもなく、fetchrow_hashref でもなく、fetchrow_arrayref
なのである。

これこそまさに fetchrow_arrayref を使うべし、という DBI 製作者のメッセージ
ではなかろうか (知らんけど)。
PageTop

perl+DBI プログラムの鉄則 - eval で例外処理

perl+DBI プログラムの鉄則 のつつき。

鉄則その2.

DB に接続したらすぐに別の関数に飛ばし、そこですべての処理を行う。
main 部分は

    my $dbh = DBI->connect(...) || die "$!";
    eval {
       ...
       $dbh->commit;
       $dbh->disconnect;
    };
    if ( $@ ){
       $dbh->rollback;
       $dbh->disconnect;
    }

とだけ書いておく。これは Java で言うところの try ~ catch に相当する。
内部で die すれば if ( $@ ) で引っかかるわけだ。なお、RaiseError を
ON にしておかないと、エラーが起こっても自動では die してくれないので
注意。disconnect の部分には、エラーが発生したことをログに記録するなどの
後始末を処理を付け加えること。

try ~ catch は何段階でもネストできるのと同様、
  eval { ... }; if ( $@ ){ ... }
もネストできる。

一部の処理でエラーを無視したい場合がある。例えば INSERT の際に一意制約が
発生しても処理を続行したい場合は、

  my $sql =
      "INSERT INTO table2 ( ".
      "  col1, ".
      "  col2 ".
      ") VALUES (".
      "  ?. ".
      "  ? ".
      ")";
  eval {
    my $sth = $dbh->prepare($sql);
    $sth->bind_param(1, $col1_value);
    $sth->bind_param(2, $col2_value);
  };
  if ( $@ ){
    if (  DBI::errstr !~ m/ORA-00001/ ){
       print "一意制約が発生したけど続行します。\n";
    } else {
       die "$@";
    }
  }

などとする (ORA-00001 ってのは Oracle の場合)。一意制約ではなかった場合、
再度 die しているのがポイント。この die は、上位の eval (サンプルの
プログラムでは main 部分の eval) があれば、そこで引っかかってくれる。
PageTop

perl+DBI プログラムの鉄則 - RaiseError と AutoCommit

perl+DBI プログラムの鉄則 のつつき。

鉄則その 1.

    my $dbh = DBI->connect($dsn, $user, $password,
         {RaiseError => 1, PrintError => 0, AutoCommit => 0 });

connect 時にするべきこと。

RaiseError は ON にする。そうすれば DB の処理でエラーが発生した
ときに勝手に die してくれる。いちいちエラーチェックをする
必要もなくなるし、エラーチェック漏れも起こりえない。楽できるのに、
なおかつ質も向上する。プログラマたるもの、怠惰でなくてはならない。

デフォルトでは PrintError が ON で RaiseError を OFF になっている。
RaiseError を ON にするとエラー発生時にエラーメッセージがダブって
表示されてしまうため、PrintError は OFF にする。


AutoCommit は OFF にする。コミットは、すべての処理が成功した場合に
最後に一度だけ行うもの (基本的には)。DBI のデフォルトは AutoCommit が
ON なので、INSERT・UPDATE するたびに毎回コミットしてしまう。必ず
OFF にすること。

MySQL の MyISAM と InnoDB

MySQL で、トランザクションの機能がない MyISAM 型を使っていた場合は
AutoCommit を OFF にしても意味がない。トランザクション機能を持つ
InnoDB を使うこと。どうしてもパフォーマンスが必要な場合のみ
MyISAM の使用を検討すればよい。


MySQL は MyISAM がデフォルトなためか、MySQL で育った人は、トラン
ザクションを知らない、または軽視している人が多そうな気がする (わたしは
Oracle で育って PostgreSQL にちょっと手を出した人間なので、信じられない)。

調べてみると、初期の MySQL は速度重視・トランザクションなんて不要、
という立場だったらしく、現在の MySQL のドキュメントもかなり速度を
重視したスタンスとなっている。その分、トランザクションがなくても
実装でなんとかなる、的なことが書いてある。

しかしエンタープライズ用途ではトランザクションがないと話にならないので、
MySQL も InnoDB を開発し、選択肢を増やしたわけである。

MyISAM と InnoDB の利点と欠点を知った上で比較検討し、その結果 MyISAM を
使うなら何も言わないが、InnoDB のことを知らない人はすぐに勉強すること。
まずは InnoDB を使って、パフォーマンスが悪いときに MyISAM の使用を
検討するべきである。
PageTop