MySQL Bugs: #23083: Server side prepared statements conflict with stored procedures

Bug #23083	Server side prepared statements conflict with stored procedures
Submitted:	8 Oct 2006 0:36	Modified:	7 Mar 2007 11:33
Reporter:	Mike Cardwell	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connectors: DBD::mysql ( Perl )	Severity:	S2 (Serious)
Version:	3.0007_2	OS:
Assigned to:		CPU Architecture:	Any

Description:
When using DBD::mysql version 3.0007_2 (not tested others), if you leave the default server side prepared statements enabled it causes stored procedures that return more than one result set to fail. The first result set is fine, but when you try to fetch from the second you get errors such as:

DBD::mysql::st fetchrow_array failed: fetch() but fetch already done [for Statement "CALL session_check(?)" with ParamValues: 0='ikOAvglXHH6BjF1GeZTCn0qZm3Gc6bRC']

Using "mysql_emulated_prepare => 1" in your args when creating the db connection stops this error from occuring.

How to repeat:
mike@rabies:~$ perl --version|grep built
This is perl, v5.8.4 built for i386-linux-thread-multi
mike@rabies:~$ perl -MDBD::mysql -e 'print $DBD::mysql::VERSION."\n";'
3.0007_2
mike@rabies:~$ cat > stored_procedure.txt
DELIMITER ;;
CREATE PROCEDURE test_proc () BEGIN
   SELECT 1;
   SELECT 2;
END;
;;
DELIMITER ;
mike@rabies:~$ cat stored_procedure.txt|mysql -uroot -p******* test
mike@rabies:~$ perl -e 'use DBI;$dbh=DBI->connect("dbi:mysql:test","root",*******);$sth = $dbh->prepare("call test_proc()");$sth->execute();do{ while( my @ary = $sth->fetchrow_array()){ print join(",",@ary)."\n";}} while $sth->more_results;'
1
DBD::mysql::st fetchrow_array failed: fetch() but fetch already done at -e line 1.
mike@rabies:~$ perl -e 'use DBI;$dbh=DBI->connect("dbi:mysql:test","root",*******,{mysql_emulated_prepare=>1});$sth = $dbh->prepare("call test_proc()");$sth->execute();do{ while( my @ary = $sth->fetchrow_array()){ print join(",",@ary)."\n";}} while $sth->more_results;'
1
2

Suggested fix:
I'm not a C coder. I've traced it back as far as I can. In dbdimp.c there's a function named "dbd_st_fetch". Near the top of that function it does a check to make sure imp_sth->fetch_done and imp_sth->done_desc aren't set (only if you're using server side prepared statements). It seems these values are set, so presumably they're supposed to be unset somewhere when the next statement handle is used.

Thank you for a problem report, and my apology for the delay with it. Please, try to repeat with a newer versions, 3.0008_1 and/or 3.0008, and inform about the results.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Although this response is long overdue. I just thought I'd update it. I just checked this bug out on DBD::mysql version 4.00, and it appears to have been fixed at some point.