MySQL Bugs: #78631: Statements in "Executable Comments" are ignoring CONSTRAINTs

Bug #78631	Statements in "Executable Comments" are ignoring CONSTRAINTs
Submitted:	29 Sep 2015 18:43	Modified:	8 Oct 2019 17:40
Reporter:	Elena Stepanova	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.1, 5.5, 5.6, 5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Filing on behalf of our user, initially reported as https://mariadb.atlassian.net/browse/MDEV-8835.

Conditional statement execution using special comment syntax does not work as expected: It is ok if the commented statement is not executed et all. But if executed, some parts of the command may be ignored, in particular CONSTRAINT attributes are not honored.

Output from SHOW CREATE in 'How to repeat' section:

# Unconditional CREATE

*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `rid` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`rid`),
  CONSTRAINT `a` FOREIGN KEY (`rid`) REFERENCES `t1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

# Conditional CREATE

*************************** 1. row ***************************
       Table: t4
Create Table: CREATE TABLE `t4` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `rid` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `b` (`rid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

How to repeat:
create table t1 (id int not null primary key auto_increment) engine = innodb;
create table t2 (id int not null primary key auto_increment) engine = innodb;

# (1) unconditionally:
create table t3 (id int not null primary key auto_increment, rid int not null, CONSTRAINT a FOREIGN KEY (rid) REFERENCES t1(id)) engine = innodb   ;

# (2) conditionally via executable comment:
/*!50101 create table t4 (id int not null primary key auto_increment, rid int not null, CONSTRAINT b FOREIGN KEY (rid) REFERENCES t1(id)) engine = innodb */;

show create table t3\G
show create table t4\G

Thank you for the bug report.

C:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --debug-info --prompt="mysql 5.1 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.74-Win X64 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.1 > create database d3;
Query OK, 1 row affected (0.00 sec)

mysql 5.1 > use d3
Database changed
mysql 5.1 > create table t1 (id int not null primary key auto_increment) engine = innodb;
Query OK, 0 rows affected (0.01 sec)

mysql 5.1 > create table t2 (id int not null primary key auto_increment) engine = innodb;
Query OK, 0 rows affected (0.01 sec)

mysql 5.1 >
mysql 5.1 > # (1) unconditionally:
mysql 5.1 > create table t3 (id int not null primary key auto_increment, rid int not null, CONSTRAINT a FOREIGN KEY (rid) REFERENCES t1(id)) engine = innodb   ;
Query OK, 0 rows affected (0.05 sec)

mysql 5.1 >
mysql 5.1 > # (2) conditionally via executable comment:
mysql 5.1 > /*!50101 create table t4 (id int not null primary key auto_increment, rid int not null, CONSTRAINT b FOREIGN KEY (rid) REFERENCES t1(id)) engine = innodb */;
Query OK, 0 rows affected (0.01 sec)

mysql 5.1 >
mysql 5.1 > show create table t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `rid` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`rid`),
  CONSTRAINT `a` FOREIGN KEY (`rid`) REFERENCES `t1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.02 sec)

mysql 5.1 > show create table t4\G
*************************** 1. row ***************************
       Table: t4
Create Table: CREATE TABLE `t4` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `rid` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `b` (`rid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql 5.1 >

C:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.10 Source distribution PULL: 2015-SEP-26

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > create database d3;
Query OK, 1 row affected (0.00 sec)

mysql 5.7 > use d3
Database changed
mysql 5.7 > create table t1 (id int not null primary key auto_increment) engine = innodb;
Query OK, 0 rows affected (0.03 sec)

mysql 5.7 > create table t2 (id int not null primary key auto_increment) engine = innodb;
Query OK, 0 rows affected (0.03 sec)

mysql 5.7 >
mysql 5.7 > # (1) unconditionally:
mysql 5.7 > create table t3 (id int not null primary key auto_increment, rid int not null, CONSTRAINT a FOREIGN KEY (rid) REFERENCES t1(id)) engine = innodb   ;
Query OK, 0 rows affected (0.04 sec)

mysql 5.7 >
mysql 5.7 > # (2) conditionally via executable comment:
mysql 5.7 > /*!50101 create table t4 (id int not null primary key auto_increment, rid int not null, CONSTRAINT b FOREIGN KEY (rid) REFERENCES t1(id)) engine = innodb */;
Query OK, 0 rows affected (0.03 sec)

mysql 5.7 >
mysql 5.7 > show create table t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `rid` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`rid`),
  CONSTRAINT `a` FOREIGN KEY (`rid`) REFERENCES `t1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql 5.7 > show create table t4\G
*************************** 1. row ***************************
       Table: t4
Create Table: CREATE TABLE `t4` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `rid` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `b` (`rid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Posted by developer:
 
Fixed in 8.0.19.

Foreign key definitions used in CREATE TABLE and ALTER TABLE
statements for InnoDB tables were ignored if these statements were
wrapped in conditional comments (such as /*!50101 ... */ or /*! ...
*/).