MySQL Bugs: #64857: Must explicitly finish() prepared procedure CALLs when autocommit is off

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

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;

Thank you for the problem report. Verified with DBD::mysql 4.020 and MySQL server 5.5.23 on 64-bit FC14.

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.