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 # The server must support partitioning.
32 --source include/have_partition.inc
33
34 #------------------------------------------------------------------------------#
35 # Engine specific settings and requirements
36
37 ##### Storage engine to be tested
38 --source include/have_innodb.inc
39 let $engine= 'InnoDB';
40
41 #------------------------------------------------------------------------------#
42 # Execute the tests to be applied to all storage engines
43 --source suite/parts/inc/partition_key_4col.inc
44 --source suite/parts/inc/partition_key_8col.inc
45 --source suite/parts/inc/partition_key_16col.inc
46 --source suite/parts/inc/partition_key_32col.inc
47
48 #------------------------------------------------------------------------------#
49 # Execute storage engine specific tests
50
51 --echo # Bug#34604 - Assertion 'inited==RND' failed in handler::ha_rnd_end
52
53 CREATE TABLE t1 (
54 a INT AUTO_INCREMENT,
55 b VARCHAR(255),
56 PRIMARY KEY (a))
57 ENGINE = InnoDB
58 PARTITION BY HASH (a)
59 PARTITIONS 2;
60
61 connect (con1, localhost, root,,);
62 SET autocommit=OFF;
63 START TRANSACTION;
64 INSERT INTO t1 VALUES (NULL, 'first row t2');
65
66 connect (con2, localhost, root,,);
67 SET autocommit=OFF;
68 SET SESSION lock_wait_timeout= 0;
69 --error ER_LOCK_WAIT_TIMEOUT
70 ALTER TABLE t1 AUTO_INCREMENT = 10;
71
72 --disconnect con2
73 --disconnect con1
74 --connection default
75 DROP TABLE t1;
76
77 --echo #
78 --echo # Bug#53676: Unexpected errors and possible table corruption on
79 --echo # ADD PARTITION and LOCK TABLE
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 LOCK TABLE t1 READ;
85
86 --connect (con2,localhost,root,,)
87 SET lock_wait_timeout = 0;
88 --echo # First attempt: lock wait timeout (as expected)
89 --error ER_LOCK_WAIT_TIMEOUT
90 ALTER TABLE t1 ADD PARTITION PARTITIONS 2;
91 --echo # Second attempt: says that partition already exists
92 --error ER_LOCK_WAIT_TIMEOUT
93 ALTER TABLE t1 ADD PARTITION PARTITIONS 2;
94 --echo # Check that we only can select, not insert/update/delete.
95 --error ER_LOCK_WAIT_TIMEOUT
96 INSERT INTO t1 VALUES (NULL, 6), (NULL, 7), (10, 10), (11, 11);
97 --error ER_LOCK_WAIT_TIMEOUT
98 UPDATE t1 SET i = 5 WHERE f = 2;
99 --error ER_LOCK_WAIT_TIMEOUT
100 DELETE FROM t1 WHERE i = 10;
101 --sorted_result
102 SELECT * FROM t1;
103
104 --connection default
105 UNLOCK TABLES;
106 LOCK TABLE t1 READ;
107
108 --connection con2
109 --echo # Third attempt: says that the table does not exist
110 --error ER_LOCK_WAIT_TIMEOUT
111 ALTER TABLE t1 ADD PARTITION PARTITIONS 2;
112 --echo # Check table returns the same (not after fixing bug#56172!)
113 CHECK TABLE t1;
114
115 --connection default
116 UNLOCK TABLES;
117 # Avoid a MDL conflict between purge and the DROP TABLE below
118 SET GLOBAL innodb_max_purge_lag_wait=0;
119
120 --connection con2
121 DROP TABLE t1;
122
123 # End of Test1
124
125 # Test2
126
127 --connection default
128
129 CREATE TABLE t2 ( i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, f INT )
130 ENGINE = InnoDB PARTITION BY HASH(i) PARTITIONS 2;
131
132 LOCK TABLE t2 READ;
133
134 --connection con2
135 SET lock_wait_timeout = 0;
136 --error ER_LOCK_WAIT_TIMEOUT
137 ALTER TABLE t2 ADD PARTITION PARTITIONS 2;
138 SET lock_wait_timeout = 2;
139 send ALTER TABLE t2 ADD PARTITION PARTITIONS 2;
140
141 --connection default
142 UNLOCK TABLES;
143
144 --connection con2
145 --reap
146
147 --connect (con3,localhost,root,,)
148 CHECK TABLE t2;
149 SELECT * FROM t2;
150 DROP TABLE t2;
151
152 # End of Test2
153 # Test #3
154
155 --connection default
156
157 CREATE TABLE t3 ( i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, f INT )
158 ENGINE = InnoDB PARTITION BY HASH(i) PARTITIONS 2;
159
160 LOCK TABLE t3 READ;
161
162 --connection con2
163 SET lock_wait_timeout = 0;
164 --error ER_LOCK_WAIT_TIMEOUT
165 ALTER TABLE t3 ADD PARTITION PARTITIONS 2;
166
167 --connection con3
168 SET lock_wait_timeout = 0;
169 --error ER_LOCK_WAIT_TIMEOUT
170 ALTER TABLE t3 ADD PARTITION PARTITIONS 2;
171
172 --connect (con4,localhost,root,,)
173 send RENAME TABLE t3 TO t4;
174
175 --connection default
176 UNLOCK TABLES;
177
178 --connection con4
179 --reap
180
181 --connect (con5,localhost,root,,)
182 --echo # SHOW TABLES returns the table (not renamed):
183 SHOW TABLES;
184 --echo # Connection 5 attempts to read from the table (table does not exist):
185 --error ER_NO_SUCH_TABLE
186 SELECT * FROM t3;
187 DROP TABLE t4;
188
189 --disconnect con5
190 --disconnect con4
191 --disconnect con3
192 --disconnect con2
193 --connection default
194 # End of Test #3
195
196 # MDEV-21693 ALGORITHM=INSTANT does not work for partitioned tables
197 CREATE TABLE t1(
198 f1 INT, f2 VARCHAR(10) CHARSET ascii,
199 f3 CHAR(150) CHARSET ascii,
200 f4 TEXT CHARSET ascii)ENGINE=InnoDB
201 PARTITION BY RANGE(f1) (PARTITION p1 VALUES LESS THAN(10),
202 PARTITION p2 VALUES LESS THAN (100));
203 ALTER TABLE t1 convert to charset ascii collate ascii_bin, ALGORITHM=INSTANT;
204 DROP TABLE t1;
205
206 --echo # Test WRITE LOCK.
207 CREATE TABLE t1 ( i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, f INT )
208 ENGINE = InnoDB PARTITION BY HASH(i) PARTITIONS 2;
209 INSERT INTO t1 VALUES (3, 3), (4, 4);
210
211 LOCK TABLE t1 WRITE;
212
213 --connect (con2,localhost,root,,)
214 SET lock_wait_timeout = 0;
215 --echo # Check that we only can select, not insert/update/delete.
216 --error ER_LOCK_WAIT_TIMEOUT
217 INSERT INTO t1 VALUES (NULL, 1), (NULL, 2), (10, 10), (11, 11);
218 --error ER_LOCK_WAIT_TIMEOUT
219 UPDATE t1 SET i = 5 WHERE f = 2;
220 --error ER_LOCK_WAIT_TIMEOUT
221 DELETE FROM t1 WHERE i = 10;
222 --error ER_LOCK_WAIT_TIMEOUT
223 SELECT * FROM t1;
224
225 --connection default
226 UNLOCK TABLES;
227 # Avoid a MDL conflict between purge and the DROP TABLE below
228 SET GLOBAL innodb_max_purge_lag_wait=0;
229
230 --connection con2
231 DROP TABLE t1;
232
233 --disconnect con2
234 --connection default