Debian Codegraph @

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

raw
1 # Bug#60196 - Setting lowercase_table_names to 2 on Windows causing
2 # Foreign Key problems after an engine is restarted.
3
4 # This test case needs InnoDB, a lowercase file system,
5 # lower-case-table-names=2
6 --source include/have_lowercase2.inc
7 --source include/have_case_insensitive_file_system.inc
8
9 #
10 # Create test data.
11 #
12 CREATE TABLE Bug_60196_FK1 (Primary_Key INT PRIMARY KEY) ENGINE=InnoDB;
13 CREATE TABLE Bug_60196_FK2 (Primary_Key INT PRIMARY KEY) ENGINE=InnoDB;
14 CREATE TABLE Bug_60196 (
15 FK1_Key INT NOT NULL,
16 FK2_Key INT NOT NULL,
17 PRIMARY KEY (FK2_Key, FK1_Key),
18 KEY FK1_Key (FK1_Key),
19 KEY FK2_Key (FK2_Key),
20 CONSTRAINT FK_FK1 FOREIGN KEY (FK1_Key)
21 REFERENCES Bug_60196_FK1 (Primary_Key)
22 ON DELETE CASCADE
23 ON UPDATE CASCADE,
24 CONSTRAINT FK_FK2 FOREIGN KEY (FK2_Key)
25 REFERENCES Bug_60196_FK2 (Primary_Key)
26 ON DELETE CASCADE
27 ON UPDATE CASCADE
28 ) ENGINE=InnoDB;
29 INSERT INTO Bug_60196_FK1 VALUES (1), (2), (3), (4), (5);
30 INSERT INTO Bug_60196_FK2 VALUES (1), (2), (3), (4), (5);
31 INSERT INTO Bug_60196 VALUES (1, 1);
32 INSERT INTO Bug_60196 VALUES (1, 2);
33 INSERT INTO Bug_60196 VALUES (1, 3);
34 --error ER_NO_REFERENCED_ROW_2
35 INSERT INTO Bug_60196 VALUES (1, 99);
36 --error ER_NO_REFERENCED_ROW_2
37 INSERT INTO Bug_60196 VALUES (99, 1);
38
39 SELECT * FROM bug_60196_FK1;
40 SELECT * FROM bug_60196_FK2;
41 SELECT * FROM bug_60196;
42
43 --source include/restart_mysqld.inc
44
45 --echo #
46 --echo # Try to insert more to the example table with foreign keys.
47 --echo # Bug60196 causes the foreign key file not to be found after
48 --echo # the resstart above.
49 --echo #
50 SELECT * FROM Bug_60196;
51 INSERT INTO Bug_60196 VALUES (2, 1);
52 INSERT INTO Bug_60196 VALUES (2, 2);
53 INSERT INTO Bug_60196 VALUES (2, 3);
54 SELECT * FROM Bug_60196;
55
56 --echo
57 --echo # Clean up.
58 DROP TABLE Bug_60196;
59 DROP TABLE Bug_60196_FK1;
60 DROP TABLE Bug_60196_FK2;
61
62
63 # Bug#60309/12356829
64 # MYSQL 5.5.9 FOR MAC OSX HAS BUG WITH FOREIGN KEY CONSTRAINTS
65 # This testcase is different from that for Bug#60196 in that the
66 # referenced table contains a secondary key. When the engine is
67 # restarted, the referenced table is opened by the purge thread,
68 # which does not notice that lower_case_table_names == 2.
69
70 #
71 # Create test data.
72 #
73 CREATE TABLE Bug_60309_FK (
74 ID INT PRIMARY KEY,
75 ID2 INT,
76 KEY K2(ID2)
77 ) ENGINE=InnoDB;
78 CREATE TABLE Bug_60309 (
79 ID INT PRIMARY KEY,
80 FK_ID INT,
81 KEY (FK_ID),
82 CONSTRAINT FK FOREIGN KEY (FK_ID) REFERENCES Bug_60309_FK (ID)
83 ) ENGINE=InnoDB;
84
85 INSERT INTO Bug_60309_FK (ID, ID2) VALUES (1, 1), (2, 2), (3, 3);
86 INSERT INTO Bug_60309 VALUES (1, 1);
87 --error ER_NO_REFERENCED_ROW_2
88 INSERT INTO Bug_60309 VALUES (2, 99);
89
90 SELECT * FROM Bug_60309_FK;
91 SELECT * FROM Bug_60309;
92
93 --source include/restart_mysqld.inc
94
95 --echo #
96 --echo # Try to insert more to the example table with foreign keys.
97 --echo # Bug60309 causes the foreign key file not to be found after
98 --echo # the resstart above.
99 --echo #
100 SELECT * FROM Bug_60309;
101 INSERT INTO Bug_60309 VALUES (2, 2);
102 INSERT INTO Bug_60309 VALUES (3, 3);
103 SELECT * FROM Bug_60309;
104
105 --echo
106 --echo # Clean up.
107 DROP TABLE Bug_60309;
108 DROP TABLE Bug_60309_FK;