Bug #29985 mysqlslap -- improper handling of resultsets in SPROCs
Submitted: 23 Jul 2007 19:56 Modified: 5 Nov 2007 2:34
Reporter: Johan Andersson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S2 (Serious)
Version:5.1.* OS:Any
Assigned to: Gleb Shchepa CPU Architecture:Any

[23 Jul 2007 19:56] Johan Andersson
Description:
johan@stingray:~/dev/mysqlslapd$  mysqlslap --defaults-file=/data/etc/my.cnf  --query=query.txt 
mysqlslap: Cannot run query call test.myproc1(2); ERROR : Commands out of sync; you can't run this command now
johan@stingray:~/dev/mysqlslapd$ 

Dooh!

Two simple SPROCS are defined:

drop procedure myproc1;
delimiter $$
create procedure myproc1( in id integer)
begin
  set @stmt = concat("select b from t1 where a = ", id);
  ##select @stmt;
  prepare stmt from @stmt;
  execute stmt;
  drop prepare stmt;
end;
$$
delimiter ;

drop procedure myproc2;
delimiter $$
create procedure myproc2( in id integer, in name char(10))
begin

  set @stmt = concat("insert into t1 values(",id,",'",name,"')");
  ##select @stmt;
  prepare stmt from @stmt;
  execute stmt;
  drop prepare stmt;
end;
$$
delimiter ;

How to repeat:
1) create the sprocs above.
2) make a query.txt  - cat query.txt:

call test.myproc2(1,'johan1');
call test.myproc2(2,'johan2'); 
call test.myproc2(3,'johan3'); 
call test.myproc2(4,'johan4'); 
call test.myproc2(5,'johan5'); 
call test.myproc1(1); 
call test.myproc1(2);
call test.myproc1(3); 
call test.myproc1(4); 
call test.myproc1(5); 

3) create a table in database test;

create table t1(a integer, b char(10), primary key(a)) engine=ndb;

4) 
mysqlslap --defaults-file=/data/etc/my.cnf  --query=query.txt 

Suggested fix:
just do it!
[23 Jul 2007 21:48] Johan Andersson
How to fix it:
http://dev.mysql.com/doc/refman/5.1/en/c-api-multiple-queries.html
[24 Jul 2007 18:42] Sveta Smirnova
test case

Attachment: bug29985.test (application/octet-stream, text), 1.35 KiB.

[24 Jul 2007 18:43] Sveta Smirnova
Thank you for the report.

Verified as described using attached test case.
[17 Sep 2007 16:29] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/34350

ChangeSet@1.2614, 2007-09-17 21:39:07+05:00, gshchepa@gleb.loc +3 -0
  Fixed bug #29985.
  Multiple-result processing is required during the execution
  of CALL statements for stored procedures, however the mysqlslap
  client lacked that processing.
[29 Oct 2007 8:47] Bugs System
Pushed into 5.1.23-beta
[29 Oct 2007 8:51] Bugs System
Pushed into 6.0.4-alpha
[5 Nov 2007 2:34] Paul DuBois
Noted in 5.1.23, 6.0.4 changelogs.

mysqlslap did not properly handle multiple result sets from stored procedures.