MySQL Bugs: #48024: Innodb doesn't work with multi-statements

Bug #48024	Innodb doesn't work with multi-statements
Submitted:	13 Oct 2009 15:35	Modified:	14 Oct 2010 13:41
Reporter:	Georgi Kodinov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	5.1+	OS:	Any
Assigned to:	John Russell	CPU Architecture:	Any

Description:
This is a spin-off of bug #40877.

Innodb threats the query text that is passed to it by the server (through thd_query()) as a null-terminated string.
This causes problems with multi-statements that contain foreign key definition code, as demonstrated by the example in bug #40877 because the individual statements in such a multi statement are not null terminated and Innodb gets confused by reading the foreign key definitions in the next statement(s).

How to repeat:
See the example in bug #40877

Suggested fix:
I will implement a new function for 5.1 that returns a pointer to LEX_STRING instead (string pointer and length).

Please update the innodb logic to use the new function in 5.1 codebase instead of thd_query() (that I want to obsolete).
Please update the the 5.0 innodb code to use store and use THD::query_length.

Note that the fix for bug #40877 (the extended API function) is a pre-requisite for this bug.

Also see bug #38816 -- a lock around InnoDB thd::query access is desirable.

I have a patch for this, but I would like to write a test case too. Is it possible to have mysql-test-run submit multiple statements at a time? I tried it, but my test passed both with and without my patch. I tried with these statements (the CREATE/ALTER also on a single line):

CREATE TABLE bug48024(a int PRIMARY KEY,b int NOT NULL,KEY(b)) ENGINE=InnoDB;
CREATE TABLE bug48024_b(b int PRIMARY KEY) ENGINE=InnoDB;
ALTER TABLE bug48024 ADD CONSTRAINT FOREIGN KEY(b) REFERENCES bug48024_b(b);

DROP TABLE bug48024,bug48024_b;

While working on this, I noticed and filed Bug #53644 (InnoDB thinks that /*/ starts and ends a comment). The fix will fix that bug as well.

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

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

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

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

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

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

Pushed into 5.1.48 (revid:vasil.dimov@oracle.com-20100531082307-9x08gg1g7zybx2jy) (version source revid:vasil.dimov@oracle.com-20100513074652-0cvlhgkesgbb2bfh) (merge vers: 5.5.5-m3) (pib:16)

Pushed into 5.5.5-m3 (revid:alexey.kopytov@sun.com-20100615145247-8bj0vmuqlotbqsn9) (version source revid:marko.makela@oracle.com-20100514130815-ym7j7cfu88ro6km4) (merge vers: 5.1.48) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100615150216-cubqoyn1fj9b6a2p) (version source revid:marko.makela@oracle.com-20100514130815-ym7j7cfu88ro6km4) (pib:16)

This bug is in status "Documenting". Is there any documentation that needs to be changed, i.e. do we say somewhere that these multi-statements will not work?

Here is what I am putting in the change log:

Multi-statement execution could fail with an error about foreign
key constraints. This problem could affect calls to
mysql_query() and
mysql_real_query(), and
CALL statements that invoke stored 
procedures.

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)

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)

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)

Already documented, as above; no additional changelog entries required. Set back to Closed state.