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: | |
Category: | MySQL Server: Parser | Severity: | S3 (Non-critical) |
Version: | 5.1, 5.5, 5.6, 5.7 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[29 Sep 2015 18:43]
Elena Stepanova
[29 Sep 2015 21:51]
MySQL Verification Team
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)
[8 Oct 2019 17:40]
Paul DuBois
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 /*! ... */).