Bug #53750 Prepared statements parsing problem with multi-statement DDL and ALTER TABLE

• Submitted: 18 May 2010 12:56
• Modified: 4 Jun 2010 0:33
• Reporter: Marko Mäkelä
• Status: Not a Bug
• Category: MySQL Server: Prepared statements
• Severity: S3 (Non-critical)
• OS: Any
• CPU Architecture: Any

[18 May 2010 12:56] Marko Mäkelä

Description:
The innodb_bug48024.test fails in mtr --ps-protocol mode, apparently due to a syntax error in the MySQL parser.

How to repeat:
Run mysql-test-run with this input (innodb_bug48024.test):

# Bug #48024 Innodb doesn't work with multi-statements

--source include/have_innodb_plugin.inc

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;
# Bug #53644 InnoDB thinks that /*/ starts and ends a comment
ALTER TABLE bug48024 /*/ADD CONSTRAINT FOREIGN KEY(c) REFERENCES(a),/*/
ADD CONSTRAINT FOREIGN KEY(b) REFERENCES bug48024_b(b);

DROP TABLE bug48024,bug48024_b;

delimiter |;
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(c) REFERENCES(a),/*/
ADD CONSTRAINT FOREIGN KEY(b) REFERENCES bug48024_b(b)|
delimiter ;|

DROP TABLE bug48024,bug48024_b;

The case before "delimiter |;" works fine. The CREATE TABLE statements are executed as COM_STMT_PREPARE, COM_STMT_EXECUTE, COM_STMT_CLOSE, and the ALTER TABLE is executed as COM_QUERY. When the CREATE and ALTER are submitted as a multi-statement, all of it is in a single COM_STMT_PREPARE, and the MySQL parser seems to fail at the /*/.

I will work this around by adding
-- disable_ps_protocol
before the "delimiter |;"

[19 May 2010 7:56] 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/108572

[19 May 2010 7:56] 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/108573

[19 May 2010 11:40] Marko Mäkelä

Resetting bug status to Verified. The commits made so far work around this bug in the test suite.

[31 May 2010 8:28] Bugs System

Pushed into 5.1.48 (revid:[e-mail]-20100531082307-9x08gg1g7zybx2jy) (version source revid:[e-mail]-20100513074652-0cvlhgkesgbb2bfh) (merge vers: 5.5.5-m3) (pib:16)

[1 Jun 2010 13:33] Konstantin Osipov

Prepared statements do not support multi-statements.
Either don't use multi-statements, or disable the PS protocol.
This is a documented limitation.

[4 Jun 2010 0:34] Omer Barnir

If you want to have parametrized multi-statement processes, use a stored procedure. Prepared statements are meant to optimize tokenized, pre-parsed single-term statements. The two interfaces are distinct and different based on their functional requirements.

[17 Jun 2010 6:13] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100615145247-8bj0vmuqlotbqsn9) (version source revid:[e-mail]-20100518130658-rd00ql7h02ooakh1) (merge vers: 5.1.48) (pib:16)

[17 Jun 2010 6:16] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100615150216-cubqoyn1fj9b6a2p) (version source revid:[e-mail]-20100518130658-rd00ql7h02ooakh1) (pib:16)

[14 Oct 2010 8:26] Bugs System

Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:[e-mail]-20101014082627-jrmy9xbfbtrebw3c) (version source revid:[e-mail]-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:[e-mail]-20101014083757-5qo48b86d69zjvzj) (version source revid:[e-mail]-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:[e-mail]-20101014084420-y54ecj85j5we27oa) (version source revid:[e-mail]-20100513074652-0cvlhgkesgbb2bfh) (merge vers: 5.5.5-m3) (pib:21)