Debian Codegraph @

mysql-test/suite/innodb/t/foreign_sql_mode.test

raw
1 --source include/have_innodb.inc
2 --source alter_sql_mode.inc
3 call mtr.add_suppression("InnoDB: In ALTER TABLE .* has or is referenced in foreign key constraints which are not compatible with the new table definition.");
4
5 let $combination=`select regexp_replace('$MTR_COMBINATIONS', 'innodb,\|,innodb', '')`;
6
7 let $copy_algo=`select ((strcmp(substring_index('$combination', ",", 1), "COPY") = 0) or (strcmp(substring_index('$combination', ",", -1), "COPY") = 0))`;
8
9 let $inplace_algo=`select ((strcmp(substring_index('$combination', ",", 1), "INPLACE") = 0) or (strcmp(substring_index('$combination', ",", -1), "INPLACE") = 0))`;
10
11 let $algorithm=COPY;
12 if ($inplace_algo)
13 {
14 let $algorithm=INPLACE;
15 }
16 let $sql_mode = `SELECT @@SQL_MODE`;
17 let $error_code = 0;
18 if ($sql_mode == "STRICT_TRANS_TABLES") {
19 let $error_code = ER_FK_COLUMN_NOT_NULL;
20 }
21
22 --echo # modify child column NOT NULL on UPDATE CASCADE..parent column NULL
23 CREATE TABLE t1(f1 INT, f2 INT, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
24 CREATE TABLE t2(f1 INT, FOREIGN KEY(f1) REFERENCES t1(f2) ON UPDATE CASCADE)ENGINE=InnoDB;
25 replace_result ,ALGORITHM=COPY '' ,ALGORITHM=INPLACE '';
26 --error $error_code
27 eval ALTER TABLE t2 MODIFY COLUMN f1 INT NOT NULL,ALGORITHM=$algorithm;
28 INSERT INTO t1 VALUES(1, 1);
29 INSERT INTO t2 VALUES(1);
30
31 let $dml_error_code = ER_ROW_IS_REFERENCED_2;
32 if ($sql_mode == "STRICT_TRANS_TABLES")
33 {
34 let $dml_error_code = 0;
35 }
36
37 --error $dml_error_code
38 UPDATE t1 SET f2= NULL;
39 DELETE FROM t2;
40 SELECT * FROM t1;
41 UPDATE t1 SET f2 = NULL;
42 SELECT * FROM t1;
43 DROP TABLE t2, t1;
44
45 let $error_code= ER_ERROR_ON_RENAME;
46 if ($algorithm == "INPLACE")
47 {
48 let $error_code= ER_FK_COLUMN_NOT_NULL;
49 }
50
51 if ($sql_mode == "STRICT_TRANS_TABLES")
52 {
53 let $error_code = ER_FK_COLUMN_NOT_NULL;
54 }
55
56 # Modifying referenced column from NULL to NOT NULL fails when foreign
57 # clause is ON UPDATE SET NULL or ON DELETE SET NULL irrespective
58 # of SQL_MODE variable. This is the behaviour even before MDEV-34392
59
60 --echo # modify child column NOT NULL ON UPDATE SET NULL
61 CREATE TABLE t1(f1 INT, f2 INT, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
62 CREATE TABLE t2(f1 INT, f2 INT, FOREIGN KEY(f1) REFERENCES t1(f1) ON UPDATE SET NULL)ENGINE=InnoDB;
63 replace_result ,ALGORITHM=COPY '' ,ALGORITHM=INPLACE '';
64 --replace_regex /Error on rename of '.*' to '.*'/Error on rename of 'OLD_FILE_NAME' to 'NEW_FILE_NAME'/
65 --error $error_code
66 eval ALTER TABLE t2 MODIFY COLUMN f1 INT NOT NULL,ALGORITHM=$algorithm;
67 INSERT INTO t1 VALUES(1, 1);
68 INSERT INTO t2 VALUES(1, 1);
69 UPDATE t1 SET f1= 2;
70 SELECT * FROM t2;
71 DROP TABLE t2, t1;
72
73 --echo # modify child column NOT NULL ON DELETE SET NULL
74 CREATE TABLE t1(f1 INT, f2 INT, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
75 CREATE TABLE t2(f1 INT, f2 INT, FOREIGN KEY (f2) REFERENCES t1(f2) ON DELETE SET NULL)ENGINE=InnoDB;
76 replace_result ,ALGORITHM=COPY '' ,ALGORITHM=INPLACE '';
77 --replace_regex /Error on rename of '.*' to '.*'/Error on rename of 'OLD_FILE_NAME' to 'NEW_FILE_NAME'/
78 --error $error_code
79 eval ALTER TABLE t2 MODIFY COLUMN f2 INT NOT NULL,ALGORITHM=$algorithm;
80 DROP TABLE t2, t1;
81
82 if ($sql_mode == "STRICT_TRANS_TABLES")
83 {
84 let $dml_error_code = ER_BAD_NULL_ERROR;
85 }
86
87 let $error_code= 0;
88 if ($sql_mode == "STRICT_TRANS_TABLES")
89 {
90 let $error_code = ER_FK_COLUMN_CANNOT_CHANGE_CHILD;
91 }
92
93 --echo # modify parent column NULL ON UPDATE CASCADE child column NOT NULL
94 CREATE TABLE `t#1`(f1 INT, f2 INT NOT NULL, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
95 CREATE TABLE `t#2`(f1 INT NOT NULL,
96 FOREIGN KEY(f1) REFERENCES `t#1`(f2)
97 ON UPDATE CASCADE)ENGINE=InnoDB;
98 replace_result ,ALGORITHM=COPY '' ,ALGORITHM=INPLACE '';
99 --error $error_code
100 eval ALTER TABLE `t#1` MODIFY COLUMN f2 INT,ALGORITHM=$algorithm;
101 INSERT INTO `t#1` VALUES(1, 1);
102 INSERT INTO `t#2` VALUES(1);
103 --error $dml_error_code
104 UPDATE `t#1` SET f2= NULL;
105 DELETE FROM `t#2`;
106 SELECT * FROM `t#1`;
107 DROP TABLE `t#2`, `t#1`;
108
109 let $error_code= 0;
110 if ($sql_mode == "STRICT_TRANS_TABLES")
111 {
112 let $error_code = ER_CANT_CREATE_TABLE;
113 }
114
115 CREATE TABLE t1(f1 INT NOT NULL AUTO_INCREMENT,
116 f2 INT DEFAULT NULL,
117 PRIMARY KEY(f1),
118 FOREIGN KEY(f2) REFERENCES t1(f1))ENGINE=InnoDB;
119 --error $error_code
120 CREATE TABLE t2 (f1 INT NOT NULL,
121 f2 INT NOT NULL,
122 f3 INT DEFAULT NULL,
123 PRIMARY KEY(f1, f2),
124 FOREIGN KEY(f2, f3) REFERENCES t1(f2, f1)
125 ON UPDATE CASCADE)ENGINE=InnoDB;
126 --disable_warnings
127 DROP TABLE IF EXISTS t2;
128 --enable_warnings
129 DROP TABLE IF EXISTS t1;