| 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 | Impact on me: | |
| Category: | MySQL Server: C API (client library) | Severity: | S3 (Non-critical) |
| Version: | 5.0.18 | OS: | |
| Assigned to: | Konstantin Osipov | CPU Architecture: | Any |
[2 Jan 2006 22:11]
Hasani Blackwell
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.
[13 Feb 2006 19:03]
Lorraine Gray
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.
[23 Feb 2006 15:47]
Lorraine Gray
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.
[20 Apr 2006 23:51]
Chris Huber
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.
[21 Apr 2006 2:17]
Hasani Blackwell
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;
}
[24 Apr 2006 6:32]
Daniel Hultberg
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.
[21 Jul 2006 15:12]
Konstantin Osipov
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.
[22 Jul 2006 5:25]
Richard Boaz
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
[22 Jul 2006 15:02]
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/9447
[22 Jul 2006 15:30]
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/9448
[22 Jul 2006 16:05]
Konstantin Osipov
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.
[22 Jul 2006 16:08]
Konstantin Osipov
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.
[24 Jul 2006 10:58]
Konstantin Osipov
Magnus, thank you for the review.
[24 Jul 2006 10:58]
Konstantin Osipov
Pushed into 5.0 runtime
[2 Aug 2006 15:49]
Konstantin Osipov
Pushed into 5.0.25
[3 Aug 2006 6:46]
Richard Boaz
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
[3 Aug 2006 7:35]
Konstantin Osipov
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.
[14 Aug 2006 20:37]
Konstantin Osipov
Merged into 5.1.12
[17 Aug 2006 9:37]
Jon Stephens
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.
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.