MySQL Bugs: #16538: "Lost connection to MySQL server during query" while executing stored procedure

Bug #16538	"Lost connection to MySQL server during query" while executing stored procedure
Submitted:	16 Jan 2006 15:54	Modified:	3 Jul 2006 14:00
Reporter:	Florian Engelhardt	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S2 (Serious)
Version:	5.0.18	OS:	Linux (Gentoo Linux 32-bit)
Assigned to:		CPU Architecture:	Any

Description:
I have a small database, with two tables, one view and one stored procedure.
The procedure just selects * from the view v1.
If i execute the stored procedure p1 twice, i get a code 2013 mostly at the second call, but sometimes at the third.
I made a backtrace:
(gdb) bt
#0  0x08179859 in handle_one_connection ()
#1  0xb7df4eea in pthread_start_thread () from /lib/libpthread.so.0
#2  0xb7c6ecea in clone () from /lib/libc.so.6

after the handle_one_connection() function, the mysql-query-browser show this error message. I was not able to reproduce this bug using the mysql console client, but i had no problem reproducing it with a php script.

There are no special compiler flags (-O2 -march=pentium4).

How to repeat:

DROP TABLE IF EXISTS t2;
CREATE TABLE t2 (
 id int not null,
 t1_fid int not null,
 street char(50),
 constraint pk_t2 primary key (id)
) engine = InnoDB;

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
 id int not null,
 name char(50),
 constraint pk_t1 primary key (id)
) engine = InnoDB;

alter table t2 add constraint fk_t2_t1_id
foreign key (t1_fid)
references t1 (id);

DROP VIEW IF EXISTS v1;
create view v1 as
select t1.name, t2.street
FROM t1
left outer join t2
on t1.id=t2.t1_fid
;

delimiter //
drop procedure if exists p1 //
create procedure p1 ()
begin
  select * from v1;
end
//

delimiter ;

insert into t1 values (1,'name1');
insert into t2 values (1,1,'street1');
insert into t2 values (2,1,'street2');
insert into t2 values (3,1,'street3');
insert into t2 values (4,1,'street4');

-- and than query the sp

call p1();
call p1();
call p1();
call p1();

Suggested fix:
If i use the select statement from the view directly in the procedure, everything works fine:

delimiter //
drop procedure if exists p1 //
create procedure p1 ()
begin
  select t1.name, t2.street
  FROM t1
  left outer join t2
  on t1.id=t2.t1_fid;
end
//

I was unable to reproduce this issue calling the procedure several times.
I tested on latest BK source then could you please wait for the next relase
or compile it from source. Thanks in advance.

mysql> call p1();
+-------+---------+
| name  | street  |
+-------+---------+
| name1 | street1 |
| name1 | street2 |
| name1 | street3 |
| name1 | street4 |
+-------+---------+
4 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

I can provide you with a better backtrace in a few minutes, i am recompiling mysql with the -g CFLAG.
If everything seems fine, try to do two or three select statements in the procedure, than it works.
The workaround i sugested isn´t working anymore, so it seems to me, that there is no workaround anymore.

Please try the following

delimiter //
drop procedure if exists p1 //
create procedure p1 ()
begin
  select * from v1;
  select * from v1;
  select * from v1;
  select * from v1;
end
//

call p1();
call p1();
call p1();
call p1();

And please try it in the mysql-query-browser. The same behavior can be seen, when calling it from php an doing a browser refresh while the page loads.

I have developet a simple PHP-Script. The Problem seem to be concurrent querys.
Here is the script:

<?php

$conn = mysqli_connect('127.0.0.1','username','password','test');

function test () 
{
  global $conn;
  $res = mysqli_query($conn,'call p1();');

  while ($aTmp = mysqli_fetch_array($res))
  {
    $aFoobar = $aTmp;
  }
  print_r($aFoobar);

  print_r(mysqli_error($conn));

  mysqli_free_result($res);
}

test();
test();
test();
test();
test();
test();

?>

I was able to reproduce the bug on three different machines. The last machine is an amd64 with 64bit gentoo linux and also 64bit mysql running.
I got exaclty the same problem as above, i now have a trace file, i will attach it.

Ok, the file upload is limited to 200k files, so i uploaded the 900k trace file to the following URL:
http://dotbox.org/mysql/mysqld.trace

Hello again,

i did several tests:
1. I installed the latest source from bk, but still the same error.
2. I was running the client in the gdb debuger and this verified my thesis, that the connection only gets lost, if two calls or more to p1(); are made at the same time.

Florian,

please note that you have to use mysqli_multi_query instead of mysqli_query (PHP).

You rock man.
This seemes to fix it.
Then it seems, that this is a bug in the mysql-query-browser.
But should´t you send another error message, than just close the connection?

If it is a Query Browser bug, please, try to repeat with the latest version, 1.1.20 (Widnows) or 1.1.18 (Linux), 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".