Bug #64857 Must explicitly finish() prepared procedure CALLs when autocommit is off
Submitted: 3 Apr 2012 21:27 Modified: 15 Feb 2013 16:40
Reporter: Mike Pomraning Email Updates:
Status: Unsupported Impact on me:
None 
Category:Connectors: DBD::mysql ( Perl ) Severity:S2 (Serious)
Version:4.020 OS:Linux
Assigned to: CPU Architecture:Any

[3 Apr 2012 21:27] Mike Pomraning
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;
[4 Apr 2012 6:54] Valeriy Kravchuk
Thank you for the problem report. Verified with DBD::mysql 4.020 and MySQL server 5.5.23 on 64-bit FC14.
[15 Feb 2013 16:40] Sveta Smirnova
Thank you for the report.

We don't work on DBD::mysql bugs anymore. All its bugs should go to CPAN: https://rt.cpan.org/Public/Dist/Display.html?Name=DBD-mysql

I re-submitted your report to https://rt.cpan.org/Public/Bug/Display.html?id=83349
Please subscribe to the new report on CPAN and work with DBD::mysql developers in case if they need additional details.