Bug #15181 confusing error message after multi query non-select results
Submitted: 23 Nov 2005 11:14 Modified: 23 Nov 2005 14:43
Reporter: [ name withheld ] Email Updates:
Status: Can't repeat Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.x; 5.0.x OS:Windows (WinXP, linux)
Assigned to: MySQL Verification Team CPU Architecture:Any

[23 Nov 2005 11:14] [ name withheld ]
see the begining of the saga on the following link:

after sending two non-select queries together, separated with ";", using mysql_query() API function with "multi-statements" option and then run another mysql_query(), confusing error text appears: "MySQL server has gone away".

while when use several "select" queries in first mysql_query() and then run another mysql_query(), then the message is different, and makes more sence: "Commands out of sync; you can't run this command now".

Why then the server goes away in first case?

How to repeat:
described before

Suggested fix:
avoid server going away and replace error message.
[23 Nov 2005 13:43] MySQL Verification Team
Using C API I was unable to get the reported error message:
"MySQL server has gone away"

Connected with the Server: 4.1.15-nt
Multi-query done
Select query Error: 2013:Lost connection to MySQL server during query

#include <stdlib.h>
#include <stdio.h>
#include <my_global.h>
#include <mysql.h>

const char *HOST_NAME = NULL;
const char *USER_NAME = "root";
const char *USER_PWD = "";
const char *DB_NAME = "test";
const char *UNIX_SOCKET = NULL; //"/tmp/mysql.sock";

int main()
  MYSQL *mysql = NULL;

	if ((mysql = mysql_init((MYSQL *) 0)) == NULL)
    printf("Error: %d:%s\n", mysql_errno( mysql ), mysql_error( mysql ));
    return 1;
  if (mysql_real_connect(mysql, HOST_NAME, USER_NAME, USER_PWD, DB_NAME, 0,
    printf("Error connecting: %d:%s\n", mysql_errno(mysql), mysql_error(mysql));
    return 1;
    printf("Connected with the Server: %s\n", mysql_get_server_info( mysql ));

	if ( mysql_query(mysql,"DROP TEMPORARY TABLE IF EXISTS z1;\
                   CREATE TEMPORARY TABLE z1(u INT)") )
    printf("Multi-query Error: %d:%s\n", mysql_errno(mysql), mysql_error(mysql));
    return 1;
    printf("Multi-query done\n");

	if ( mysql_query(mysql,"SELECT count(*) FROM z1"))
    printf("Select query Error: %d:%s\n", mysql_errno(mysql), mysql_error(mysql));
    return 1;
    printf("Select query done\n");


  return 0;

[23 Nov 2005 14:43] [ name withheld ]
Does your script return any error messages?
[12 Mar 2006 21:58] Charles Salvia
I have seen this bug reported before, and experienced it myself.  As far as I can tell, it is a legitimate bug.  The problem is that after you perform a multi-statement query, any subsequent queries (whether "SELECT" or "INSERT" or whatever) will return an error value of 1.  Using mysql_error will report a lost connection.

Here's an easy way to observe this problem:

char* q = "INSERT into mytable (column1) VALUES (\"abcdef\"); INSERT into
mytable (column2) VALUES (\"ghijkl\")";

printf("%d\n",mysql_query(conn, q));

q = "INSERT into mytable (column1) VALUES (\"mnopqr\")";
printf("%d\n",mysql_query(conn, q));

This prints out:

...when it should print out:

The second query returns 1 even though it was successful. This makes error
checking impossible when using multiple statement queries.