Bug #40877 multi statement execution fails in 5.1.30

Description:
Multi statement execution fails in 5.1

How to repeat:
#include <stdio.h>
#include <mysql.h>

int main ()
{

	MYSQL *mysql = mysql_init(NULL);
	MYSQL_RES *res;
	int i, status;

	char *sql[] = {
"SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO",
"DROP TABLE IF EXISTS `bank_transactions`",
"CREATE TABLE `bank_transactions` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `client_id` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `client` (`client_id`)) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC",
"DROP TABLE IF EXISTS `clients`",
"CREATE TABLE `clients` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`)) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC",
"ALTER TABLE `bank_transactions` ADD CONSTRAINT `bank_transactions_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON UPDATE CASCADE"};
	char multi_statement[10000];

	mysql_real_connect(mysql, "localhost", "root", "", "test", 0, NULL, CLIENT_MULTI_STATEMENTS);

	printf("Client: %s\nServer: %s\n", mysql_get_client_info(), mysql_get_server_info(mysql));

	mysql_query(mysql, "SET AUTOCOMMIT=0");

	/* process single statements */
	for (i=0; i < 6; i++) {
		if (mysql_query(mysql, sql[i]))
		   printf("Single statement error: %s\n", mysql_error(mysql));
	}

	/* process multi statement */
	sprintf((char *)&multi_statement, "%s;%s;%s;%s;%s;%s", sql[0], sql[1], sql[2], sql[3], sql[4], sql[5]);
	
	status = mysql_query(mysql, multi_statement);
	i= 0;

	do {
		if ((res = mysql_store_result(mysql))) {
			mysql_free_result(res);
		}
		status = mysql_next_result(mysql);
		i++;
	
	} while (status == 0);
	if (mysql_error(mysql))
	   printf("multi statement error: %s\n", mysql_error(mysql));
} 

Output:
georg@bing:~/work/php/test> ./test
Client: 5.1.30
Server: 5.1.30-debug-log
multi statement error: Cannot delete or update a parent row: a foreign key constraint fails

Thank you for the bug report. Verified on 6.0/5.0 too.

C:\temp>bug40877
Client: 5.1.31
Server: 5.1.31-nt-debug-log
multi statement error: Cannot delete or update a parent row: a foreign key constraint fails

C:\temp>bug60.exe
Client: 6.0.9-alpha
Server: 6.0.9-alpha-nt-debug-log
multi statement error: Cannot delete or update a parent row: a foreign key constraint fails

C:\temp>bug50.exe
Client: 5.0.74
Server: 5.0.74-nt-log
multi statement error:

There's problems on two levels that prevent multi statements working as they should with this example : 
1. The Innodb storage engine assumes the string returned by thd_query() (THD::query actually) is null-terminated. This is true with single statements. But with multi statements it's not true. Innodb should be  extended to use char pointer/length instead of null-terminated strings. But it (currently) has no way getting this information out of the server. Which brings us to problem #2

2. The MySQL server has a THD::query_length that contains the length of THD::query. We need to expose this to the Innodb storage engine. This is best done by a new function that will return a LEX_STRING instead of char *. And while at it we may just as well wrap the string pointer and the length into a LEX_STRING internally and provide accessor methods for easy code update.

I will use this bug report to track the solution of problem #2. #2 is a prerequisite for the real fix (#1). 
I will open a new bug for problem #1.

Problem #2 reported as bug #48024.

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

3156 Georgi Kodinov	2009-10-13
      Bug #40877: multi statement execution fails in 5.1.30
      
      Implemented the server infrastructure for the fix:
      
      1. Added a function LEX_STRING *thd_query_string(THD) to return
      a LEX_STRING structure instead of char *.
      This is the function that must be called in innodb instead of 
      thd_query()
      
      2. Did some encapsulation in THD : aggregated thd_query and 
      thd_query_length into a LEX_STRING and made accessor and mutator 
      methods for easy code updating. 
      
      3. Updated the server code to use the new methods where applicable.

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

3177 Georgi Kodinov	2009-10-16
      Bug #40877: multi statement execution fails in 5.1.30
            
      Implemented the server infrastructure for the fix:
      
      1. Added a function LEX_STRING *thd_query_string(THD) to return
      a LEX_STRING structure instead of char *.
      This is the function that must be called in innodb instead of 
      thd_query()
      
      2. Did some encapsulation in THD : aggregated thd_query and 
      thd_query_length into a LEX_STRING and made accessor and mutator 
      methods for easy code updating. 
      
      3. Updated the server code to use the new methods where applicable.

Pushed in 5.1-bugteam and merged to mysql-pe

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091022063126-l0qzirh9xyhp0bpc) (version source revid:alik@sun.com-20091019135554-s1pvptt6i750lfhv) (merge vers: 6.0.14-alpha) (pib:13)

Noted in 6.0.14 changelog.

Multiple-statement execution could fail.

Setting report to NDI pending push into 5.1.x, 5.5.x.

Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6) (version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)

Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt) (version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)

Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0) (version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)

Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606) (version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)

Setting report to NDI pending push into 5.1.x, 5.5.x.

Noted in 5.1.42 changelog.

Setting report to Need Merge pending push to Celosia.

Noted in 5.5.7 changelog.