Bug #15853 Connection appears lost (but isn't) when executing a Stored Procedure
Submitted: 19 Dec 2005 10:33 Modified: 18 Jun 2006 21:12
Reporter: Peter Bengtson Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.16 OS:
Assigned to: CPU Architecture:Any

[19 Dec 2005 10:33] Peter Bengtson
Description:
This bug is similar to #15752, but there are enough differences to create a separate bug entry.

When executing a query involving a stored procedure, the connection will appear to go away. However, the connection is NOT lost, but the results of the query can be retrieved in the normal manner. This of course plays havoc with autoconnect logic in database abstraction layers, making it impossible to get around the bug. Dropping the connection and connecting again always fixes the problem -- the CALL will always succeed on a fresh connection -- but of course reconnecting will not recreate temporary tables and variables. Thus it is impossible to code around this bug, making it extremely critical. 

The bug is also time-sensitive. Uncommenting the call to sleep(1) does away with the erroneous error condition, but is of course not an alternative in production setups.

In the enclosed code, making the query "CALL get_user(1)" will appear to fail the second and subsequent times in the loop. Using "SELECT * FROM user WHERE userid = 1" will always succeed.

How to repeat:
<?php

/* To create the databases:

USE test;

CREATE TABLE `user` (
  `userid` int(11) NOT NULL auto_increment,
  `some_data` char(40) default NULL,
  PRIMARY KEY  (`userid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `user` VALUES  (1,'This is the data');

DELIMITER %%
CREATE PROCEDURE `get_user`(IN uid INT)
BEGIN
  SELECT * FROM user WHERE userid = uid;
END%%
DELIMITER ;

*/

print("<h1>Test</h1>$eol");

$the_query = "CALL get_user(1)";
//$the_query = "SELECT * FROM user WHERE userid = 1";

$db = new mysqli('localhost', 'root', '', 'test');
if (mysqli_connect_errno()):
  print("Connection error: " . mysqli_connect_error() . ' (' .mysqli_connect_errno() . ')');
endif;

$n = 10;
while ($n--) {
  print("<hr><p><b>Trial $n</b></p>");
  print("<p>Query: <b>$the_query</b></p>");
  $res = $db->query($the_query);
  //sleep(1);
  report($db);
  $row = $res->fetch_array();
  print('<p>Result: <b>' . $row['some_data'] . '</b></p>');
  $res->free();
}

$db->close();

function report($link) {
	print("<p>Stat: " . $link->stat());
	print("<br>Error: " . $link->error . ' (' . $link->errno . ')');
	print("<br>Client info: " . $link->client_info);
	print("<br>Host info: " . $link->host_info);
	print("<br>Server info: " . $link->server_info);
	print("<br>Ping: " . $link->ping());
	print("</p>");
}

?>

Suggested fix:
The only solution seems to be to remove all calls to Stored Procedures. Not an alternative.
[19 Dec 2005 10:35] Peter Bengtson
The code will only break on the *second* call to an SP. Sometimes, doing other queries in between, the connection will appear to break on other, unrelated queries, like a SELECT. But it will *always* break on the second immediate call to an SP.
[19 Dec 2005 19:10] Aleksey Kishkin
Peter, I tested it in the console client and it seems working properly:

mysql> call get_user(1);
+--------+------------------+
| userid | some_data        |
+--------+------------------+
|      1 | This is the data |
+--------+------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call get_user(1);
+--------+------------------+
| userid | some_data        |
+--------+------------------+
|      1 | This is the data |
+--------+------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call get_user(1);
+--------+------------------+
| userid | some_data        |
+--------+------------------+
|      1 | This is the data |
+--------+------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

1) what OS and hardware do you use?
2) could you please check your test  in the console client (not in php)?
[19 Dec 2005 19:12] Aleksey Kishkin
mysql> call get_user(1); call get_user(1); call get_user(1);
+--------+------------------+
| userid | some_data        |
+--------+------------------+
|      1 | This is the data |
+--------+------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+--------+------------------+
| userid | some_data        |
+--------+------------------+
|      1 | This is the data |
+--------+------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+--------+------------------+
| userid | some_data        |
+--------+------------------+
|      1 | This is the data |
+--------+------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
[19 Dec 2005 20:34] Peter Bengtson
1) I have observed exactly the same behaviour under Mac OS 10.4 (running mySQL locally), as well as under Linux (Ubuntu/Debian and Red Hat 9), running a replicated setup involving five machines, connecting locally to the slave, in all cases using pipes. I also get the same behaviour, only more rarely, when connecting to another server on the same subnet (both servers running Mac OS X).

2) The test executes correctly in the console for me too. Is there reconnect logic or delays involved? If there is a delay of 0.0001 seconds, the error does not appear as often. Inserting a PHP usleep(1000) before error checking after executing a CALL query reduces the errors considerably, though sometimes the delay has to be as high as 50000 usec in order not to give a false error result.
[21 Dec 2005 11:31] Aleksey Kishkin
It must not be any delays between calls in last test, but
OK, let me rewrite your test on C and check if this error appears.
[21 Dec 2005 13:02] Peter Bengtson
Why don't you try the PHP code I provided?
[27 Dec 2005 13:27] Aleksey Kishkin
because I am want to avoid parts in the testcase that we do not maintain
[14 Jan 2006 15:58] Matthew Green
I seem to have exactly the same problem, and am also using PHP. The only thing I would add is that the MYSQL server log reports an aborted connection (got an error reading communication packets) whenever this happens.

As with the reporter of this bug I don't get the problem consistently.
[15 Jan 2006 10:44] Matthew Green
The problem seems to be caused by the fact that stored procedures return multiple result sets. So, you have to use multi_query and next_result().

I would, however, like to remark that a more helpful error message should be produced and that the aborted connection message isn't very good!

I suggest that something along the lines of Sybase's "results pending" message might be better.
[16 Mar 2006 14:03] Giles McArdell
On my setup - SUSE LINUX + MySQL 5.0.18 I get a very similar problem. 
I have one Stored proc which is passed a date range, this loops through the dates in the range and calls a sequence of other stored procs for each date.

At some point during each run I get 'Lost connection to MySQL server during query', but from one run to the next it never occurs at the same point, it seems to choose a different stored proc and different date each time. (hence why its so difficult to report as a bug as I cannot give a test case that shows the bug).

I can find no work-around for this one and it has basically but a stop on my current project until it is resolved.
[18 May 2006 21:12] Valeriy Kravchuk
Why do you think it is not a duplicate of (verified) bug #15752? Plain C API bug report, no PHP involved at all... Please, try to repeat with a newer version, 5.0.2, just to be sure.
[19 May 2006 6:39] Giles McArdell
I eventually pinned down the variant of this bug I was getting and reported it as bug 19399, which has been verified (but no other progress as of yet).

This has code to demonstrate the bug and it has also been verified in v5.1.xx.
[18 Jun 2006 23:00] Bugs System
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".
[16 Jan 2008 1:06] Saggi Malachi
Peter, I've solved this problem by using mysqli_multi_query and mysqli_store_result.
for example:

if (mysqli_multi_query($conn,$queryString)) {
		do {
			$result = mysqli_store_result($conn);
			if ($result) {
				while ($row = mysqli_fetch_array($result)) {
					//do something
				}
			}
		} while  (mysqli_next_result($conn));
	}
[16 Jan 2008 1:07] Saggi Malachi
Once you call the stored procedure using that method the next queries will run properly