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

[13 Oct 2009 15:35] Georgi Kodinov
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.
[15 Oct 2009 8:09] Gleb Shchepa
Also see bug #38816 -- a lock around InnoDB thd::query access is desirable.
[11 May 2010 20:04] Marko Mäkelä
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;
[14 May 2010 12:17] Marko Mäkelä
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.
[14 May 2010 13: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/108326
[14 May 2010 13: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/108327
[14 May 2010 13:08] 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/108328
[14 May 2010 13:08] 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/108329
[14 May 2010 13:11] 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/108331
[14 May 2010 13:11] 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/108333
[31 May 2010 8:28] Bugs System
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)
[17 Jun 2010 6:13] Bugs System
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)
[17 Jun 2010 6:17] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100615150216-cubqoyn1fj9b6a2p) (version source revid:marko.makela@oracle.com-20100514130815-ym7j7cfu88ro6km4) (pib:16)
[28 Jun 2010 17:57] John Russell
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?
[30 Jun 2010 19:02] John Russell
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.
[14 Oct 2010 8:28] 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:43] 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:58] 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:41] Jon Stephens
Already documented, as above; no additional changelog entries required. Set back to Closed state.