MySQL Bugs: #15752: Lost connection to MySQL server when calling a SP from C API

Bug #15752	Lost connection to MySQL server when calling a SP from C API
Submitted:	14 Dec 2005 18:10	Modified:	17 Aug 2006 9:37
Reporter:	Alexey Kopytov	Email Updates:
Status:	Closed
Category:	C API	Severity:	S3 (Non-critical)
Version:	5.0.18	OS:
Assigned to:	Konstantin Osipov	Target Version:

Description:
The following test program fails with "Lost connection to MySQL server during query"
after random number of iterations:

#include <stdio.h>
#include <mysql.h>
#include <mysqld_error.h>

int main()
{
  char *query = "CALL p()";
  MYSQL con;
  int   i;
  MYSQL_RES *res;

  mysql_init(&con);
  if (!mysql_real_connect(&con, "localhost", "root", NULL, "test", 3306,  NULL,
                          CLIENT_MULTI_STATEMENTS))
  {
    printf("Unable connect to MySQL server: %s\n", mysql_error(&con));
    return 1;
  }

  for (i = 0; ;i++)
  {
    if (mysql_real_query(&con, query, strlen(query)))
    {
      printf("%s failed: %s\n", query, mysql_error(&con));
      break;
    }
    res = mysql_store_result(&con);
    mysql_free_result(res);
  }

  printf("i = %d\n", i);
  return 0;
}

Store procedure definition:
mysql> show create procedure p;
+-----------+----------+---------------------------------+
| Procedure | sql_mode | Create Procedure                |
+-----------+----------+---------------------------------+
| p         |          | CREATE PROCEDURE `p`()
SELECT 1 |
+-----------+----------+---------------------------------+

How to repeat:
Create the above stored procedure in the 'test' database, then compile and run the above
test program.

I failed to reproduce that with the command line client, since the bug seems to be
time-sensitive. The loops breaks after random number of iterations. Also, if I insert
some usleep() into the loop, then all works as expected.

I'm experiencing the same problem except it sometimes occurs during the 1st iteration of
the loop =(. I doubt it's server activity as I was the only one connected to the server
though. I did create 100 unused connections beforehand for connection pooling but the
code I was executing was using the same MYSQL* pointer to execute the queries.

We are experiencing this same problem (running 5.0.15) when we make repetative calls to
stored procedures from within C api.  It can be to the same SP or to different ones, but
the calls are executed quickly one after the other.  The pattern is random.  This is
critical for us since we are heavily commited to using stored procedures in the next
release of our product.  To get around this problem we are connecting and disconnecting
between calls.  This is not a great workaround and we need to resolve this quickly.  We
consider this a higher severity problem than S3.

To get around this, we are making an extra call to mysql_next_result even when there are
no more results (per prior call to mysql_more_results).  This appears to be a problem
with stored procedures that bring back no results being called in between ones that do.

I am experiencing the same behavior using PHP 5.1.2 and MySQL 5.0.18-standard on OS 10.4.
I am looping and sequentially calling the same procedure which executes a random number
of times before returning the 2013 error.

I believe I can no longer reproduce the error.
Can you try the following code works for you?
basically, I made it retrieve and close all resultsets returned by the query.

==============================================================

int main()
{
	char *query = "CALL p()";
	MYSQL con;
	int   i;
	MYSQL_RES *res;

	mysql_init(&con);
	if (!mysql_real_connect(&con, "localhost", "root", NULL, "test", 3306,  NULL,
CLIENT_MULTI_STATEMENTS | CLIENT_MULTI_RESULTS))
	{
		printf("Unable connect to MySQL server: %s\n", mysql_error(&con));
		return 1;
	}

	for (i = 0; ;i++)
	{
		if (mysql_real_query(&con, query, strlen(query)))
		{
			printf("%s failed: %s\n", query, mysql_error(&con));
			break;
		}
		res = mysql_store_result(&con);
		while(true)
		{
			if(res != NULL)
			{
				mysql_free_result(res);
				res = NULL;
			}

			int next_result = mysql_next_result(&con);
			switch(next_result)
			{
			case 0:
				res = mysql_store_result(&con);
				break;
			case -1:
				if(res != NULL)
				{
					mysql_free_result(res);
					res = NULL;
				}
				return 0;
			default:
				if(res != NULL)
				{
					mysql_free_result(res);
					res = NULL;
				}
				return next_result;
			}
		}
	}

	printf("i = %d\n", i);
	return 0;
}

Yes, the error is reproducable. I have run the above test programs on SuSE Linux, Windows
XP and Mac OS X 10.4 running MySQL 5.0.18, 5.0.19, 5.0.20 and even 5.1.7 beta and the
error occurs every time.

But you don't get the error running the same sp in the mysql terminal client.

The cause of the first problem is the pending result set for the stored procedure that is
ignored by the application. 
The client library should return an error (commands out of sync) from mysql_real_query if
the application haven't read all the result sets that the previous query returned.
An alternative solution is to read and drop these result sets automatically (that,
however, will be not in sync with the behaviour of mysql_use_result + mysql_real_query).

The second test case is different and needs additional investigation.

Hi there,

I've noticed that comments relating to this bug talk about both stored procedures as well
as result sets being ignored by the caller.

While I understand these relationships (between calls and unread returned data), I have
an exmple that demonstrates this bug that does not employ stored procedures, nor
generates any returned data:

I execute the following code via my c api/gui client:

at startup connection, i want to create some temporary tables for the gui's use, this is
done with a multiple statement/single call to the server thus:

"CREATE TEMPORARY TABLE P1 (PSD_PK INT(10) UNSIGNED NOT NULL PRIMARY KEY) ENGINE=MEMORY;
CREATE TEMPORARY TABLE P2 (CNT INT(8) UNSIGNED NOT NULL) ENGINE=MEMORY;"

This statement executes fine, it's when I attempt the next server query that I receive
the error message that the connection has been lost to the server.

As you can see, the sql statements above do not return any data, meaning, there's no data
to read.  I am getting around this by following the suggestion to loop over
mysql_next_result() until there's nothing left, so the workaround is fine.  It's just
annoying that every time I write multiple statements that return no rows, this workaround
must be employed.

Rather than this being a problem relating to the client not properly reading returned
data (thus causing the whole shebang to come to be out of sync), I think the real problem
lies more in the fact that the server creates the out-of-sync situation itself by assuming
that client requests will always result in returned data.  (Or is there some interesting
returned data from a CREATE TABLE command I don't know about?)

Just wanted to comment since, from my point of view, if the investigation here surround
stored procedures and unread returned data (actual), my version of the bug will never get
treated.

warm regards,

richard boaz

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/9447

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/9448

Dear Richard,
every command you invoke returns a result. In case of multi-statements, you have
as many results as many statements in your packet were executed.
And you should retrieve every result, as multi-statements are not an all-or-nothing
operation: if execution of one of the statements in the list returns an error, it is
aborted. Whether it will cause rollback of the results of previous statements depends on
the storage engine and the value of autocommit variable. The result of INSERT/UPDATE
contains such information as affected rows and the number of warnings.
Personally, I think that multi-statements are error prone and recommend using stored
procedures instead.

And in case of CREATE, each result contains either success and the number of warnings or
an error and the actual SQLSTATE, error number, and message.

Magnus, thank you for the review.

Pushed into 5.0 runtime

Pushed into 5.0.25

Hi Konstantin,

Thanks for the fix and release.  But what is the fix exactly?  

1) That the server silently handles unread client messages, i.e., ignores this condition
and proceeds with the next client request.

2 Or that the error message is simply something more accurate when encountering this
situation, e.g., "Cannot process current request until previous messages have been read
off of the queue."

3) Or something else entirely?

thanks,

richard boaz

Richard, in short, #2. I believe that flushing such result sets automatically is a bad
idea, because it promotes bad programming habits. Every returned result set reflects a
piece of work performed by the server, and each piece may either succeed or fail.
In other words, in my opinion, it's a good idea to check success/failure of the SP itself
regardless whether it returns another result set or not.
We may perhaps implement such an option in the client library if there is user demand.

Merged into 5.1.12

Thank you for your bug report. This issue has been committed to our source repository of
that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available
version, including the bug fix. More information about accessing the source trees is
available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.0.25 and 5.1.12 changelogs.