Must explicitly finish() prepared procedure CALLs when autocommit is off [rt.cpan.org #83349]

[mbeijen]

trafficstars

Migrated from rt.cpan.org#83349 (status was 'new')

Requestors:

• [email protected]

From [email protected] on 2013-02-15 16:38:58:

Description:
With AutoCommit disabled, calling $dbh->commit() on a transaction with
an un-finish()ed prepared statement handle which CALLs a procedure will
generate "Commands out of sync; you can't run this command now"

The problem disappears if the end-user manually calls finish(), or if
AutoCommit is enabled (even if within a user transaction).  However, per
the DBI docs, explicitly calling finish() shouldn't ever really be
necessary.

See
http://stackoverflow.com/questions/9990464/perl-mysql-procedure-with-insert-and-select-fai...
and
http://stackoverflow.com/questions/6454840/dbi-begin-work-doesnt-work-with-stored-procedur...

How to repeat:
#!/usr/bin/env perl

use strict;
use warnings;

use DBI;

my $d = DBI->connect(@ARGV, { AutoCommit => 0, RaiseError => 1 });
$d->do("CREATE PROCEDURE p() SELECT 1");
my $p1 = $d->prepare('CALL p()');
$p1->execute;
$p1->fetch;
$d->commit;

From [email protected] on 2013-02-15 16:41:29:

This bug was originally reported by Mike Pomraning at
http://bugs.mysql.com/bug.php?id=64857