|
MariaDB [bugreport]> select version();
|
+----------------+
|
| version() |
|
+----------------+
|
| 5.5.38-MariaDB |
|
+----------------+
|
1 row in set (0.00 sec)
|
MariaDB [bugreport]> # Show that comment execution is honored:
|
MariaDB [bugreport]> /*!60101 select 1 */;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [bugreport]> /*!50101 select 1 */;
|
+---+
|
| 1 |
|
+---+
|
| 1 |
|
+---+
|
1 row in set (0.00 sec)
|
|
MariaDB [bugreport]> # Prepare for bug demo:
|
MariaDB [bugreport]> # (1) unconditionally:
|
MariaDB [bugreport]> create table t1 (id int not null primary key auto_increment) engine = innodb ;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [bugreport]> # (2) conditionally via executable comment:
|
MariaDB [bugreport]> /*!50101 create table t2 (id int not null primary key auto_increment) engine = innodb */ ;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [bugreport]> show create table t1\G
|
*************************** 1. row ***************************
|
Table: t1
|
Create Table: CREATE TABLE `t1` (
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8
|
1 row in set (0.00 sec)
|
|
MariaDB [bugreport]> show create table t2\G
|
*************************** 1. row ***************************
|
Table: t2
|
Create Table: CREATE TABLE `t2` (
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8
|
1 row in set (0.00 sec)
|
|
MariaDB [bugreport]> # All fine for now.
|
MariaDB [bugreport]> # Now for tables that refer to t1.ID:
|
MariaDB [bugreport]> # (1) unconditionally:
|
MariaDB [bugreport]> 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.00 sec)
|
|
MariaDB [bugreport]> # (2) conditionally via executable comment:
|
MariaDB [bugreport]> /*!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.00 sec)
|
|
MariaDB [bugreport]> 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=utf8
|
1 row in set (0.00 sec)
|
|
MariaDB [bugreport]> 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=utf8
|
1 row in set (0.00 sec)
|
|
MariaDB [bugreport]> # BUG: Missing CONSTRAINT `a` FOREIGN KEY (`rid`) REFERENCES `t1` (`id`) in table t4!
|
MariaDB [bugreport]> # Trying to add it:
|
MariaDB [bugreport]> # (1) conditionally via executable comment:
|
MariaDB [bugreport]> /*!50101 alter table t4 add FOREIGN KEY (rid) REFERENCES t1(id) */ ;
|
Query OK, 0 rows affected (0.01 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
MariaDB [bugreport]> 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 `rid` (`rid`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8
|
1 row in set (0.00 sec)
|
|
MariaDB [bugreport]> # BUG: Missing CONSTRAINT `a` FOREIGN KEY (`rid`) REFERENCES `t1` (`id`) in table t4!
|
MariaDB [bugreport]> # Trying to add it (2) unconditionally:
|
MariaDB [bugreport]> alter table t4 add FOREIGN KEY (rid) REFERENCES t1(id) ;
|
Query OK, 0 rows affected (0.01 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
MariaDB [bugreport]> 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 `rid` (`rid`),
|
CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`rid`) REFERENCES `t1` (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8
|
1 row in set (0.00 sec)
|
|
# CONSTRAINT are only created if statement is not executed within /*!#### */
|