Debian Codegraph @

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

raw
1 --source include/have_innodb.inc
2 # Bug#60196 - Setting lowercase_table_names to 2 on Windows causing
3 # Foreign Key problems after an engine is restarted.
4
5 # This test case needs InnoDB, a lowercase file system,
6 # lower-case-table-names=2, and cannot use the embedded server
7 # because it restarts the server.
8 --source include/not_embedded.inc
9 --source include/have_lowercase2.inc
10 --source include/have_case_insensitive_file_system.inc
11
12 #
13 # Create test data.
14 #
15 CREATE TABLE Bug_60196_FK1 (Primary_Key INT PRIMARY KEY) ENGINE=InnoDB;
16 CREATE TABLE Bug_60196_FK2 (Primary_Key INT PRIMARY KEY) ENGINE=InnoDB;
17 CREATE TABLE Bug_60196 (
18 FK1_Key INT NOT NULL,
19 FK2_Key INT NOT NULL,
20 PRIMARY KEY (FK2_Key, FK1_Key),
21 KEY FK1_Key (FK1_Key),
22 KEY FK2_Key (FK2_Key),
23 CONSTRAINT FK_FK1 FOREIGN KEY (FK1_Key)
24 REFERENCES Bug_60196_FK1 (Primary_Key)
25 ON DELETE CASCADE
26 ON UPDATE CASCADE,
27 CONSTRAINT FK_FK2 FOREIGN KEY (FK2_Key)
28 REFERENCES Bug_60196_FK2 (Primary_Key)
29 ON DELETE CASCADE
30 ON UPDATE CASCADE
31 ) ENGINE=InnoDB;
32 INSERT INTO Bug_60196_FK1 VALUES (1), (2), (3), (4), (5);
33 INSERT INTO Bug_60196_FK2 VALUES (1), (2), (3), (4), (5);
34 INSERT INTO Bug_60196 VALUES (1, 1);
35 INSERT INTO Bug_60196 VALUES (1, 2);
36 INSERT INTO Bug_60196 VALUES (1, 3);
37 --error ER_NO_REFERENCED_ROW_2
38 INSERT INTO Bug_60196 VALUES (1, 99);
39 --error ER_NO_REFERENCED_ROW_2
40 INSERT INTO Bug_60196 VALUES (99, 1);
41
42 SELECT * FROM bug_60196_FK1;
43 SELECT * FROM bug_60196_FK2;
44 --sorted_result
45 SELECT * FROM bug_60196;
46
47 --echo # Stop server
48
49 # Write file to make mysql-test-run.pl wait for the server to stop
50 -- exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
51
52 # Send a shutdown request to the server
53 -- shutdown_server
54
55 # Call script that will poll the server waiting for it to disapear
56 -- source include/wait_until_disconnected.inc
57
58 --echo # Restart server.
59
60 # Write file to make mysql-test-run.pl start up the server again
61 --write_line restart $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
62
63 # Turn on reconnect
64 --enable_reconnect
65
66 # Call script that will poll the server waiting for it to be back online again
67 --source include/wait_until_connected_again.inc
68
69 # Turn off reconnect again
70 --disable_reconnect
71
72 --echo #
73 --echo # Try to insert more to the example table with foreign keys.
74 --echo # Bug60196 causes the foreign key file not to be found after
75 --echo # the resstart above.
76 --echo #
77 SELECT * FROM Bug_60196;
78 INSERT INTO Bug_60196 VALUES (2, 1);
79 INSERT INTO Bug_60196 VALUES (2, 2);
80 INSERT INTO Bug_60196 VALUES (2, 3);
81 --sorted_result
82 SELECT * FROM Bug_60196;
83
84 --echo
85 --echo # Clean up.
86 DROP TABLE Bug_60196;
87 DROP TABLE Bug_60196_FK1;
88 DROP TABLE Bug_60196_FK2;
89
90
91 # Bug#60309/12356829
92 # MYSQL 5.5.9 FOR MAC OSX HAS BUG WITH FOREIGN KEY CONSTRAINTS
93 # This testcase is different from that for Bug#60196 in that the
94 # referenced table contains a secondary key. When the engine is
95 # restarted, the referenced table is opened by the purge thread,
96 # which does not notice that lower_case_table_names == 2.
97
98 #
99 # Create test data.
100 #
101 CREATE TABLE Bug_60309_FK (
102 ID INT PRIMARY KEY,
103 ID2 INT,
104 KEY K2(ID2)
105 ) ENGINE=InnoDB;
106 CREATE TABLE Bug_60309 (
107 ID INT PRIMARY KEY,
108 FK_ID INT,
109 KEY (FK_ID),
110 CONSTRAINT FK FOREIGN KEY (FK_ID) REFERENCES Bug_60309_FK (ID)
111 ) ENGINE=InnoDB;
112
113 INSERT INTO Bug_60309_FK (ID, ID2) VALUES (1, 1), (2, 2), (3, 3);
114 INSERT INTO Bug_60309 VALUES (1, 1);
115 --error ER_NO_REFERENCED_ROW_2
116 INSERT INTO Bug_60309 VALUES (2, 99);
117
118 SELECT * FROM Bug_60309_FK;
119 SELECT * FROM Bug_60309;
120
121 --echo # Stop server
122
123 # Write file to make mysql-test-run.pl wait for the server to stop
124 -- exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
125
126 # Send a shutdown request to the server
127 -- shutdown_server
128
129 # Call script that will poll the server waiting for it to disapear
130 -- source include/wait_until_disconnected.inc
131
132 --echo # Restart server.
133
134 # Write file to make mysql-test-run.pl start up the server again
135 --write_line restart $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
136
137 # Turn on reconnect
138 --enable_reconnect
139
140 # Call script that will poll the server waiting for it to be back online again
141 --source include/wait_until_connected_again.inc
142
143 # Turn off reconnect again
144 --disable_reconnect
145
146 --echo #
147 --echo # Try to insert more to the example table with foreign keys.
148 --echo # Bug60309 causes the foreign key file not to be found after
149 --echo # the resstart above.
150 --echo #
151 SELECT * FROM Bug_60309;
152 INSERT INTO Bug_60309 VALUES (2, 2);
153 INSERT INTO Bug_60309 VALUES (3, 3);
154 SELECT * FROM Bug_60309;
155
156 --echo
157 --echo # Clean up.
158 DROP TABLE Bug_60309;
159 DROP TABLE Bug_60309_FK;