Bug #42373 libmysql can mess a connection at connect
Submitted: 27 Jan 2009 13:24 Modified: 14 Oct 2010 13:27
Reporter: Andrey Hristov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: C API (client library) Severity:S3 (Non-critical)
Version:4.1, 5.0, 5,1, 6.0 bzr OS:Any
Assigned to: Jim Winstead CPU Architecture:Any

[27 Jan 2009 13:24] Andrey Hristov
Description:
One can set commands to be executed right after connect with MYSQL_INIT_COMMAND, on a handle returned by mysql_init() and before mysql_real_connect(). INIT_COMMAND can be several statements, that's ok and libmysql handles it, but it _can't_ handle statements which include calling of a stored procedure which returns at least one result set (the server sends always one RSet for status data). libmysql expects that there will be only one result set. Another issue is that if the user sets MULTI_STATEMENTS as option, one can pass a INIT_COMMAND that contains two or more statements, but at the end only one result set will be consumed. This will mess the connection, at the end and lead to out-of-sync reading of data.
Here is the code, from libmysql 5.1.30

Beware, the same code is used in the server, so it might have also implications on the way the server works with INIT_COMMAND.

How to repeat:
  if (mysql->options.init_commands)
  {
    DYNAMIC_ARRAY *init_commands= mysql->options.init_commands;
    char **ptr= (char**)init_commands->buffer;
    char **end_command= ptr + init_commands->elements;

    my_bool reconnect=mysql->reconnect;
    mysql->reconnect=0;

    for (; ptr < end_command; ptr++)
    {
      MYSQL_RES *res;
      if (mysql_real_query(mysql,*ptr, (ulong) strlen(*ptr)))
        goto error;
      if (mysql->fields)
      {
	if (!(res= cli_use_result(mysql)))
          goto error;
	mysql_free_result(res);
      }
    }
    mysql->reconnect=reconnect;
  }

Suggested fix:
Needs more care with the use of equivalents of mysql_more_results() and mysql_next_result(). Because the code is in client.c, the functions above won't work, they are part of the libmysql interface. The corresponding functions in client.c should be used.
[28 Jan 2009 8:57] Sveta Smirnova
Thank you for the report.

Verified as described.

Test case:

#include <stdio.h>
#include <mysql.h>
#include <assert.h>
#include <string.h>

int main(int argc, char **argv)
{
        MYSQL conn;
        MYSQL_RES *results;
        MYSQL_FIELD *field;
        int OK;
		
		const char* query1= "select 1";
		int query1len = strlen(query1);
		
		mysql_init(&conn);
		
		mysql_options(&conn, MYSQL_INIT_COMMAND, "call test.p1()");

        if (!mysql_real_connect(&conn, "127.0.0.1", "root", "", "test", 3350, NULL,0))
                printf("Error: %s\n", mysql_error(&conn));
		
		OK = mysql_real_query (&conn, query1, query1len);
		printf("Res: %d;\t", OK);
		assert(0 == OK);
		printf("Query #: %d\n", count ++);
		mysql_free_result(mysql_use_result(&conn));
}

Assuming exists test.p1:  create procedure p1() select 1;
[28 Jan 2009 9:03] Sveta Smirnova
Test case with CLIENT_MULTI_STATEMENTS:

#include <stdio.h>
#include <mysql.h>
#include <assert.h>
#include <string.h>

int main(int argc, char **argv)
{
        MYSQL conn;
        MYSQL_RES *results;
        MYSQL_FIELD *field;
        int OK;
		
		const char* query1= "select 1";
		int query1len = strlen(query1);
		
		mysql_init(&conn);
		
		mysql_options(&conn, MYSQL_INIT_COMMAND, "call test.p1()");
		//mysql_options(&conn, MYSQL_INIT_COMMAND, "select 1; select 2");

        if (!mysql_real_connect(&conn, "127.0.0.1", "root", "", "test", 3350, NULL, CLIENT_MULTI_STATEMENTS | CLIENT_MULTI_RESULTS))
                printf("Error: %s\n", mysql_error(&conn));
		
		OK = mysql_real_query (&conn, query1, query1len);
		printf("Res: %d;\t", OK);
		assert(0 == OK);
		mysql_free_result(mysql_use_result(&conn));
}
[7 Aug 2009 22:55] 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/80400

3056 Jim Winstead	2009-08-07
      Using an initial command with mysql_options(..., MYSQL_INIT_COMMAND, ...)
      that generated multiple result sets (such as a stored procedure or a
      multi-statement command) would leave the connection unusable. (Bug #42373)
      
      A side-effect of this bug fix is to make MYSQL_INIT_COMMAND settings ignored
      when connecting from within the server, but none of the existing mechanisms
      for connecting from within the server use or need to set the initial command.
[11 Aug 2009 6:21] Vladislav Vaintroub
Jim, 
that looks fine. the only thing that makes me think is that
+#ifndef MYSQL_SERVER

that was not there before. Is it really necessary? The code is also enclosed in if (init_command != NULL) block. Maybe it would be already sufficient?
[12 Aug 2009 19:49] Jim Winstead
The code in question is disabled because after the patch, it relies on functions that are not available to the more limited version of the libmysql API used within the server itself.
[10 May 2010 3:03] 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/107813

3375 Jim Winstead	2010-05-09
      Using an initial command with mysql_options(..., MYSQL_INIT_COMMAND, ...)
      that generated multiple result sets (such as a stored procedure or a
      multi-statement command) would leave the connection unusable. (Bug #42373)
      
      A side-effect of this bug fix is to make MYSQL_INIT_COMMAND settings ignored
      when connecting from within the server, but none of the existing mechanisms
      for connecting from within the server use or need to set the initial command.
[10 May 2010 7:50] Jim Winstead
Queued to mysql-5.1-bugteam and mysql-pe.
[28 May 2010 5:59] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:alik@sun.com-20100512070920-xgpmqeytp0gc183c) (pib:16)
[28 May 2010 6:28] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:alik@sun.com-20100514054548-91z72f0mcskr84kj) (merge vers: 6.0.14-alpha) (pib:16)
[28 May 2010 6:56] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:alexey.kopytov@sun.com-20100511082753-a4r5a38fe9nuf5fw) (merge vers: 5.5.5-m3) (pib:16)
[2 Jun 2010 8:49] Bugs System
Pushed into 5.1.48 (revid:georgi.kodinov@oracle.com-20100602084411-2yu607bslbmgufl3) (version source revid:jimw@mysql.com-20100510030205-pqc3o0uyjo964cun) (merge vers: 5.1.47) (pib:16)
[4 Jun 2010 1:43] Paul DuBois
Noted in 5.1.48, 5.5.0, 6.0.14 changelogs.

Using an initial command with mysql_options(..., MYSQL_INIT_COMMAND,
...) that generated multiple result sets (such as a stored procedure
or a multi-statement command) left the connection unusable.
[14 Oct 2010 8:26] Bugs System
Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (version source revid:vasil.dimov@oracle.com-20100513074652-0cvlhgkesgbb2bfh) (merge vers: 5.5.5-m3) (pib:21)
[14 Oct 2010 8:41] Bugs System
Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (version source revid:vasil.dimov@oracle.com-20100513074652-0cvlhgkesgbb2bfh) (merge vers: 5.5.5-m3) (pib:21)
[14 Oct 2010 8:56] Bugs System
Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (version source revid:vasil.dimov@oracle.com-20100513074652-0cvlhgkesgbb2bfh) (merge vers: 5.5.5-m3) (pib:21)
[14 Oct 2010 13:27] Jon Stephens
Already documented in the 5.1.48 changelog; no additional changelog entries required. Set back to Closed state.
[8 Sep 2011 21:20] Gerardo Sánchez
this is a bug?:
i use mysql_options/MYSQL_INIT_COMMAND this way:

mysql_options(my, MYSQL_INIT_COMMAND, "SET GLOBAL FLUSH = ON; SET SESSION completion_type = 1; USE Ambar;");// 3 stmts, multi statement command
mysql_real_connect(my, (LPCSTR)mysql_host, (LPCSTR)UserName, (LPCSTR)Authentication, NULL, 0, NULL, CLIENT_MULTI_STATEMENTS | CLIENT_FOUND_ROWS | CLIENT_ODBC);

raizes an error when i try:
mysql_autocommit(my, 0);
error message is: "Commands out of sync; you can't run this command now"

because (i think) init command release one result set, but this query generates 3 results sets

I "fix" this error this way:
mysql_options(my, MYSQL_INIT_COMMAND, "SET GLOBAL FLUSH = ON");
mysql_options(my, MYSQL_INIT_COMMAND, "SET SESSION completion_type = 1");
mysql_options(my, MYSQL_INIT_COMMAND, "USE Ambar");
mysql_real_connect(my, (LPCSTR)mysql_host, (LPCSTR)UserName, (LPCSTR)Authentication, NULL, 0, NULL, CLIENT_FOUND_ROWS | CLIENT_ODBC);
mysql_autocommit(my, 0);

this works because init command handles this 3 result sets separately

The questions is: is this a error? or must be documented this feature of mysql_options. I FIND THIS SOLUTION LOOKING IN SOURCE CODE! i dont guess multiple calls to mysql_options/MYSQL_INIT_COMMAND is valid. it must be documented in mysql_options help

Thanks