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 ]
• Status: Can't repeat
• 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 ]

Description:
see the begining of the saga on the following link:
http://bugs.php.net/bug.php?id=35333

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"

C:\temp>bug15181
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,
													UNIX_SOCKET, CLIENT_MULTI_STATEMENTS) == NULL )
	{
    printf("Error connecting: %d:%s\n", mysql_errno(mysql), mysql_error(mysql));
    return 1;
	}
	else
    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;
	}
	else
    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;
	}
	else
    printf("Select query done\n");

	mysql_close(mysql);

  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:
0
1

...when it should print out:
0
0

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