Debian Codegraph @

mysql-test/suite/ndb_ddl/tablespace_mdl.test

raw
1 --source include/have_ndb.inc
2
3 #
4 # Test MDL on tablespaces. Locks on tablespaces are taken in the following
5 # cases:
6 #
7 # 1. Tablespace DDL - X lock
8 # 2. Disk data table DDL - IX lock
9 # 3. Metadata change detection - SHARED lock
10 #
11
12 #
13 # Testcase 1: Parallel disk data table DDL queries using the same tablespace
14 #
15
16 # Create logfile group
17 CREATE LOGFILE GROUP lg1
18 ADD UNDOFILE 'lg1_undofile.dat'
19 INITIAL_SIZE 1M
20 UNDO_BUFFER_SIZE = 1M
21 ENGINE NDB;
22
23 # Create tablespace using the logfile group
24 CREATE TABLESPACE ts1
25 ADD DATAFILE 'ts1_datafile.dat'
26 USE LOGFILE GROUP lg1
27 INITIAL_SIZE 16M
28 ENGINE NDB;
29
30 # Procedure p1 communicates through the `procedure_control` table
31 CREATE TABLE procedure_control(
32 stop_flag INT
33 ) ENGINE NDB;
34
35 DELIMITER //;
36 CREATE PROCEDURE p1()
37 BEGIN
38 DECLARE done INT DEFAULT 0;
39 REPEAT
40 CREATE TABLE t1 (
41 a INT PRIMARY KEY,
42 b VARCHAR(255)
43 )
44 ENGINE NDB TABLESPACE ts1 STORAGE DISK;
45 DROP TABLE t1;
46 SELECT stop_flag INTO done FROM procedure_control;
47 UNTIL done END REPEAT;
48 END//
49 DELIMITER ;//
50
51 INSERT INTO procedure_control VALUE(0);
52
53 # Run CREATE + DROP TABLE queries in parallel across 2 connections
54 --echo Start background load of CREATE + DROP TABLE t1 which uses tablespace ts1
55 --send CALL p1()
56
57 # Create another connection
58 --connect (con2,127.0.0.1,root,,test,$MASTER_MYPORT,)
59 --connection con2
60 --echo Start CREATE + DROP TABLE t2 which uses tablespace ts1
61 --disable_query_log
62 --let $count = 1
63 while ($count <= 30)
64 {
65 CREATE TABLE t2 (
66 a INT PRIMARY KEY,
67 b VARCHAR(255)
68 )
69 ENGINE NDB TABLESPACE ts1 STORAGE DISK;
70 DROP TABLE t2;
71 --inc $count
72 }
73 --enable_query_log
74
75 # Signal procedure to stop background queries
76 UPDATE procedure_control SET stop_flag=1;
77
78 --connection default
79 --echo Wait for background load to complete
80 --reap
81
82 # Cleanup
83 DROP PROCEDURE p1;
84 DROP TABLE procedure_control;
85 ALTER TABLESPACE ts1
86 DROP DATAFILE 'ts1_datafile.dat';
87 DROP TABLESPACE ts1;
88 DROP LOGFILE GROUP lg1
89 ENGINE NDB;
90
91 #
92 # Testcase 2: Parallel tablespace and disk data table DDL queries
93 #
94
95 # Create logfile group
96 CREATE LOGFILE GROUP lg1
97 ADD UNDOFILE 'lg1_undofile.dat'
98 INITIAL_SIZE 1M
99 UNDO_BUFFER_SIZE = 1M
100 ENGINE NDB;
101
102 # Create tablespace using the logfile group
103 CREATE TABLESPACE ts1
104 ADD DATAFILE 'ts1_datafile.dat'
105 USE LOGFILE GROUP lg1
106 INITIAL_SIZE 16M
107 ENGINE NDB;
108
109 # Procedure p1 communicates through 'procedure_control' table
110 CREATE TABLE procedure_control(
111 stop_flag INT
112 ) ENGINE NDB;
113
114 # The query of the 'dummy' table in procedure p1() helps
115 # speed up the test by creating more opportunities to the
116 # CREATE/DROP TABLE statement to acquire the global schema
117 # lock. These statements are often the ones 'victimized'
118 # by deadlock detection and lose out in terms of contention
119 # for the GSL.
120 CREATE TABLE dummy (
121 a INT PRIMARY KEY
122 ) ENGINE NDB;
123 INSERT INTO dummy VALUES (1), (2), (3), (4), (5);
124
125 DELIMITER //;
126 CREATE PROCEDURE p1()
127 BEGIN
128 DECLARE done INT DEFAULT 0;
129 DECLARE dummy_var INT DEFAULT 5;
130 REPEAT
131 ALTER TABLESPACE ts1
132 ADD DATAFILE 'ts1_datafile2.dat';
133 ALTER TABLESPACE ts1
134 DROP DATAFILE 'ts1_datafile2.dat';
135 SELECT stop_flag INTO done FROM procedure_control;
136 SELECT a INTO dummy_var FROM dummy WHERE a > 4;
137 UNTIL done END REPEAT;
138 END//
139 DELIMITER ;//
140
141 INSERT INTO procedure_control VALUE(0);
142
143 # Run CREATE + DROP TABLE and ALTER TABLESPACE queries in parallel
144 --echo Start background load of ALTER TABLESPACE ts1 ADD/DROP DATAFILE
145 --send CALL p1()
146
147 --connection con2
148 --echo Start CREATE + DROP TABLE t1 which uses tablespace ts1
149 --disable_query_log
150 --let $count = 1
151 while ($count <= 30)
152 {
153 # 0 - No error
154 # ER_LOCK_DEADLOCK - Transaction encountered deadlock
155 # ER_TABLE_EXISTS_ERROR - In case the previous DROP TABLE failed
156 --error 0,ER_LOCK_DEADLOCK,ER_TABLE_EXISTS_ERROR
157 CREATE TABLE t1 (
158 a INT PRIMARY KEY,
159 b VARCHAR(255)
160 )
161 ENGINE NDB TABLESPACE ts1 STORAGE DISK;
162 # 0 - No error
163 # ER_LOCK_DEADLOCK - Transaction encountered deadlock
164 # ER_BAD_TABLE_ERROR - In case the previous CREATE TABLE failed
165 --error 0,ER_LOCK_DEADLOCK,ER_BAD_TABLE_ERROR
166 DROP TABLE t1;
167 --inc $count
168 }
169 --enable_query_log
170
171 # Signal procedure to stop background queries
172 UPDATE procedure_control SET stop_flag=1;
173
174 --connection default
175 --echo Wait for background load to complete
176 --error 0,ER_LOCK_DEADLOCK
177 --reap
178
179 # Cleanup
180 DROP PROCEDURE p1;
181 DROP TABLE procedure_control;
182 ALTER TABLESPACE ts1
183 DROP DATAFILE 'ts1_datafile.dat';
184 DROP TABLESPACE ts1;
185 DROP LOGFILE GROUP lg1
186 ENGINE NDB;
187
188 #
189 # Testcase 3: Parallel tablespace DDL queries
190 #
191
192 # Create logfile group
193 CREATE LOGFILE GROUP lg1
194 ADD UNDOFILE 'lg1_undofile.dat'
195 INITIAL_SIZE 1M
196 UNDO_BUFFER_SIZE = 1M
197 ENGINE NDB;
198
199 # Create tablespace using the logfile group
200 CREATE TABLESPACE ts1
201 ADD DATAFILE 'ts1_datafile.dat'
202 USE LOGFILE GROUP lg1
203 INITIAL_SIZE 16M
204 ENGINE NDB;
205
206 # Procedure p1 communicates through 'procedure_control' table
207 CREATE TABLE procedure_control(
208 stop_flag INT
209 ) ENGINE NDB;
210
211 DELIMITER //;
212 CREATE PROCEDURE p1()
213 BEGIN
214 DECLARE done INT DEFAULT 0;
215 REPEAT
216 ALTER TABLESPACE ts1
217 ADD DATAFILE 'ts1_datafile2.dat';
218 ALTER TABLESPACE ts1
219 DROP DATAFILE 'ts1_datafile2.dat';
220 SELECT stop_flag INTO done FROM procedure_control;
221 UNTIL done END REPEAT;
222 END//
223 DELIMITER ;//
224
225 INSERT INTO procedure_control VALUE(0);
226
227 # Run ALTER TABLESPACE queries in parallel
228 --echo Start background load of ALTER TABLESPACE ts1 ADD/DROP DATAFILE
229 --send CALL p1()
230
231 --connection con2
232 --echo Start ALTER TABLESPACE ts1 ADD/DROP DATAFILE
233 --disable_query_log
234 --let $count = 1
235 while ($count <= 30)
236 {
237 ALTER TABLESPACE ts1
238 ADD DATAFILE 'ts1_datafile3.dat';
239 ALTER TABLESPACE ts1
240 DROP DATAFILE 'ts1_datafile3.dat';
241 --inc $count
242 }
243 --enable_query_log
244
245 # Signal procedure to stop background queries
246 UPDATE procedure_control SET stop_flag=1;
247
248 --connection default
249 --echo Wait for background load to complete
250 --reap
251
252 # Cleanup
253 DROP PROCEDURE p1;
254 DROP TABLE procedure_control,dummy;
255 ALTER TABLESPACE ts1
256 DROP DATAFILE 'ts1_datafile.dat';
257 DROP TABLESPACE ts1;
258 DROP LOGFILE GROUP lg1
259 ENGINE NDB;