Debian Codegraph @

mysql-test/suite/parts/t/partition_special_innodb.test

raw
1 ################################################################################
2 # t/partition_special_innodb.test #
3 # #
4 # Purpose: #
5 # different Tests #
6 # InnoDB branch #
7 # #
8 #------------------------------------------------------------------------------#
9 # Original Author: HH #
10 # Original Date: 2006-08-01 #
11 # Change Author: MattiasJ #
12 # Change Date: 2008-08-20 #
13 # Change: added test for bug#34604 #
14 ################################################################################
15
16 #
17 # NOTE: PLEASE DO NOT ADD NOT INNODB SPECIFIC TESTCASES HERE !
18 # TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN
19 # THE SOURCED FILES ONLY.
20 #
21 # Please read the README at the end of inc/partition.pre before changing
22 # any of the variables.
23 #
24
25 #------------------------------------------------------------------------------#
26 # General not engine specific settings and requirements
27
28 ##### Options, for debugging support #####
29 let $debug= 0;
30
31 #------------------------------------------------------------------------------#
32 # Engine specific settings and requirements
33
34 ##### Storage engine to be tested
35 let $engine= 'InnoDB';
36
37 #------------------------------------------------------------------------------#
38 # Execute the tests to be applied to all storage engines
39 --source suite/parts/inc/partition_key_4col.inc
40 --source suite/parts/inc/partition_key_8col.inc
41 --source suite/parts/inc/partition_key_16col.inc
42 --source suite/parts/inc/partition_key_32col.inc
43
44 #------------------------------------------------------------------------------#
45 # Execute storage engine specific tests
46
47 --echo # Bug#34604 - Assertion 'inited==RND' failed in handler::ha_rnd_end
48
49 CREATE TABLE t1 (
50 a INT AUTO_INCREMENT,
51 b VARCHAR(255),
52 PRIMARY KEY (a))
53 ENGINE = InnoDB
54 PARTITION BY HASH (a)
55 PARTITIONS 2;
56
57 connect (con1, localhost, root,,);
58 connect (con2, localhost, root,,);
59
60 --connection con1
61 SET autocommit=OFF;
62 START TRANSACTION;
63 INSERT INTO t1 VALUES (NULL, 'first row t2');
64
65 --connection con2
66 SET autocommit=OFF;
67 SET SESSION lock_wait_timeout= 1;
68 --error ER_LOCK_WAIT_TIMEOUT
69 ALTER TABLE t1 AUTO_INCREMENT = 10;
70
71 --disconnect con2
72 --disconnect con1
73 --connection default
74 DROP TABLE t1;
75
76 --echo #
77 --echo # Bug#53676: Unexpected errors and possible table corruption on
78 --echo # ADD PARTITION and LOCK TABLE
79 --connect (con1,localhost,root,,)
80 CREATE TABLE t1 ( i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, f INT )
81 ENGINE = InnoDB PARTITION BY HASH(i) PARTITIONS 2;
82 INSERT INTO t1 VALUES (2, 2), (3, 3), (4, 4), (5, 5);
83
84 --connect (con2,localhost,root,,)
85 SET lock_wait_timeout = 2;
86
87 --connection con1
88 --echo #Connection 1 locks the table
89 LOCK TABLE t1 READ;
90
91 --connection con2
92 --echo # Connection 2 tries to add partitions:
93 --echo # First attempt: lock wait timeout (as expected)
94 --error ER_LOCK_WAIT_TIMEOUT
95 ALTER TABLE t1 ADD PARTITION PARTITIONS 2;
96 --echo # Second attempt: says that partition already exists
97 --error ER_LOCK_WAIT_TIMEOUT
98 ALTER TABLE t1 ADD PARTITION PARTITIONS 2;
99 --echo # Check that we only can select, not insert/update/delete.
100 --error ER_LOCK_WAIT_TIMEOUT
101 INSERT INTO t1 VALUES (NULL, 6), (NULL, 7), (10, 10), (11, 11);
102 --error ER_LOCK_WAIT_TIMEOUT
103 UPDATE t1 SET i = 5 WHERE f = 2;
104 --error ER_LOCK_WAIT_TIMEOUT
105 DELETE FROM t1 WHERE i = 10;
106 --sorted_result
107 SELECT * FROM t1;
108
109 --connection con1
110 --echo # Connection 1 unlocks the table and locks it again:
111 UNLOCK TABLES;
112 --sleep 1
113 LOCK TABLE t1 READ;
114
115 --connection con2
116 --echo # Connection 2 tries again to add partitions:
117 --echo # Third attempt: says that the table does not exist
118 --error ER_LOCK_WAIT_TIMEOUT
119 ALTER TABLE t1 ADD PARTITION PARTITIONS 2;
120 --echo # Check table returns the same (not after fixing bug#56172!)
121 CHECK TABLE t1;
122
123 --connection con1
124 UNLOCK TABLES;
125
126 --connection con2
127 DROP TABLE t1;
128
129 # End of Test1
130
131 # Test2
132
133 --connection con1
134
135 CREATE TABLE t2 ( i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, f INT )
136 ENGINE = InnoDB PARTITION BY HASH(i) PARTITIONS 2;
137
138 --connection con2
139 SET lock_wait_timeout = 2;
140
141 --connection con1
142 LOCK TABLE t2 READ;
143
144 --connection con2
145 --error ER_LOCK_WAIT_TIMEOUT
146 ALTER TABLE t2 ADD PARTITION PARTITIONS 2;
147 send ALTER TABLE t2 ADD PARTITION PARTITIONS 2;
148
149 --connection con1
150 UNLOCK TABLES;
151
152 --connection con2
153 --reap
154
155 --connect (con3,localhost,root,,)
156 CHECK TABLE t2;
157 SELECT * FROM t2;
158 DROP TABLE t2;
159
160 # End of Test2
161 # Test #3
162
163 --connection con1
164
165 CREATE TABLE t3 ( i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, f INT )
166 ENGINE = InnoDB PARTITION BY HASH(i) PARTITIONS 2;
167
168 --connection con2
169 SET lock_wait_timeout = 2;
170
171 --connection con1
172 --echo # Connection 1 locks the table
173 LOCK TABLE t3 READ;
174
175 --connection con2
176 --echo # Connection 2 tries to add partitions (timeout):
177 --error ER_LOCK_WAIT_TIMEOUT
178 ALTER TABLE t3 ADD PARTITION PARTITIONS 2;
179
180 --connection con3
181 SET lock_wait_timeout = 2;
182 --echo # Connection 3 tries to add partitions (partition already exists):
183 --error ER_LOCK_WAIT_TIMEOUT
184 ALTER TABLE t3 ADD PARTITION PARTITIONS 2;
185
186 --connect (con4,localhost,root,,)
187 --echo # Connection 4 tries to rename the table:
188 send RENAME TABLE t3 TO t4;
189
190 --connection con1
191 --sleep 1
192 --echo # Connection 1 unlocks the table:
193 UNLOCK TABLES;
194
195 --connection con4
196 --echo # Connection 4 gets error on rename:
197 --reap
198
199 --connect (con5,localhost,root,,)
200 --echo # SHOW TABLES returns the table (not renamed):
201 SHOW TABLES;
202 --echo # Connection 5 attempts to read from the table (table does not exist):
203 --error ER_NO_SUCH_TABLE
204 SELECT * FROM t3;
205 DROP TABLE t4;
206
207 --disconnect con5
208 --disconnect con4
209 --disconnect con3
210 --disconnect con2
211 --disconnect con1
212 --connection default
213 # End of Test #3
214
215 --echo # Test WRITE LOCK.
216 --connect (con1,localhost,root,,)
217 CREATE TABLE t1 ( i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, f INT )
218 ENGINE = InnoDB PARTITION BY HASH(i) PARTITIONS 2;
219 INSERT INTO t1 VALUES (3, 3), (4, 4);
220
221 --connect (con2,localhost,root,,)
222 SET lock_wait_timeout = 2;
223
224 --connection con1
225 --echo #Connection 1 locks the table
226 LOCK TABLE t1 WRITE;
227
228 --connection con2
229 --echo # Check that we still can SELECT, but not insert/update/delete.
230 --echo # Check that we only can select, not insert/update/delete.
231 --error ER_LOCK_WAIT_TIMEOUT
232 INSERT INTO t1 VALUES (NULL, 1), (NULL, 2), (10, 10), (11, 11);
233 --error ER_LOCK_WAIT_TIMEOUT
234 UPDATE t1 SET i = 5 WHERE f = 2;
235 --error ER_LOCK_WAIT_TIMEOUT
236 DELETE FROM t1 WHERE i = 10;
237 --error ER_LOCK_WAIT_TIMEOUT
238 SELECT * FROM t1;
239
240 --connection con1
241 UNLOCK TABLES;
242
243 --disconnect con1
244 --source include/wait_until_disconnected.inc
245
246 --connection con2
247 DROP TABLE t1;
248
249 --disconnect con2
250 --source include/wait_until_disconnected.inc
251
252 --connection default
253
254 --echo #
255 --echo # Bug#53770: Server crash at handler.cc:2076 on LOAD DATA
256 --echo # after timed out COALESCE PARTITION.
257 --echo # Rewritten to use InnoDB after WL#8971.
258 CREATE TABLE t1 ( i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, f INT )
259 ENGINE = InnoDB PARTITION BY HASH(i) PARTITIONS 3;
260
261 --echo # Connection 1 starts transaction and gets lock
262 START TRANSACTION;
263 SELECT * FROM t1;
264
265 --connect (con2,localhost,root,,)
266 SET lock_wait_timeout = 2;
267 --echo # Connection 2 tries to coalesce partitions (timeout):
268 --error ER_LOCK_WAIT_TIMEOUT
269 ALTER TABLE t1 COALESCE PARTITION 2;
270
271 --connect (con3,localhost,root,,)
272
273 --let $MYSQLD_DATADIR= `SELECT @@datadir`
274 --write_file $MYSQLD_DATADIR/test/load.in
275 1
276 2
277 3
278 EOF
279 --echo # Connection 3 tries to load into the table:
280 send LOAD DATA INFILE 'load.in' INTO TABLE t1 (f);
281
282 --connection default
283 --sleep 1
284 --echo # Connection 1 commits the transaction
285 COMMIT;
286
287 --connection con3
288 --echo # Connection 3...
289 --reap
290 DROP TABLE t1;
291
292 --disconnect con3
293 --source include/wait_until_disconnected.inc
294
295 --connection con2
296 --disconnect con2
297 --source include/wait_until_disconnected.inc
298
299 --connection default
300 --remove_file $MYSQLD_DATADIR/test/load.in