Debian Codegraph @

mysql-test/suite/ndb/t/ndb_dd_disk2memory.test

raw
1 ########################################
2 # Author: JBM
3 # Date: 2006-01-12
4 # Purpose: To test using ndb memory and
5 # disk tables together.
6 ########################################
7
8 -- source include/have_ndb.inc
9
10 ############ Test Setup Section #############
11 -- echo **** Test Setup Section ****
12
13 CREATE LOGFILE GROUP log_group1
14 ADD UNDOFILE './log_group1/undofile.dat'
15 INITIAL_SIZE 16M
16 UNDO_BUFFER_SIZE = 1M
17 ENGINE=NDB;
18
19 CREATE TABLESPACE table_space1
20 ADD DATAFILE './table_space1/datafile.dat'
21 USE LOGFILE GROUP log_group1
22 INITIAL_SIZE 28M
23 EXTENT_SIZE 256K
24 ENGINE NDB;
25
26
27 CREATE TABLE test.t1
28 (pk1 INT NOT NULL PRIMARY KEY, b INT NOT NULL, c INT NOT NULL)
29 TABLESPACE table_space1 STORAGE DISK
30 ENGINE=NDB;
31
32 CREATE TABLE test.t2
33 (pk2 INT NOT NULL PRIMARY KEY, b2 INT NOT NULL, c2 INT NOT NULL)
34 ENGINE=NDB;
35
36 --echo
37 ##################### Data load for first test ####################
38 --echo **** Data load for first test ****
39
40 INSERT INTO test.t1 VALUES
41 (1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),
42 (6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10),
43 (11,11,11),(12,12,12),(13,13,13),(14,14,14),(15,15,15),
44 (16,16,16),(17,17,17),(18,18,18),(19,19,19),(20,20,20),
45 (21,21,21),(22,22,22),(23,23,23),(24,24,24),(25,25,25),
46 (26,26,26),(27,27,27),(28,28,28),(29,29,29),(30,30,30),
47 (31,31,31),(32,32,32),(33,33,33),(34,34,34),(35,35,35),
48 (36,36,36),(37,37,37),(38,38,38),(39,39,39),(40,40,40),
49 (41,41,41),(42,42,42),(43,43,43),(44,44,44),(45,45,45),
50 (46,46,46),(47,47,47),(48,48,48),(49,49,49),(50,50,50),
51 (51,51,51),(52,52,52),(53,53,53),(54,54,54),(55,55,55),
52 (56,56,56),(57,57,57),(58,58,58),(59,59,59),(60,60,60),
53 (61,61,61),(62,62,62),(63,63,63),(64,64,64),(65,65,65),
54 (66,66,66),(67,67,67),(68,68,68),(69,69,69),(70,70,70),
55 (71,71,71),(72,72,72),(73,73,73),(74,74,74),(75,75,75);
56
57
58 INSERT INTO test.t2 VALUES
59 (1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),
60 (6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10),
61 (11,11,11),(12,12,12),(13,13,13),(14,14,14),(15,15,15),
62 (16,16,16),(17,17,17),(18,18,18),(19,19,19),(20,20,20),
63 (21,21,21),(22,22,22),(23,23,23),(24,24,24),(25,25,25),
64 (26,26,26),(27,27,27),(28,28,28),(29,29,29),(30,30,30),
65 (31,31,31),(32,32,32),(33,33,33),(34,34,34),(35,35,35),
66 (36,36,36),(37,37,37),(38,38,38),(39,39,39),(40,40,40),
67 (41,41,41),(42,42,42),(43,43,43),(44,44,44),(45,45,45),
68 (46,46,46),(47,47,47),(48,48,48),(49,49,49),(50,50,50),
69 (51,51,51),(52,52,52),(53,53,53),(54,54,54),(55,55,55),
70 (56,56,56),(57,57,57),(58,58,58),(59,59,59),(60,60,60),
71 (61,61,61),(62,62,62),(63,63,63),(64,64,64),(65,65,65),
72 (66,66,66),(67,67,67),(68,68,68),(69,69,69),(70,70,70),
73 (71,71,71),(72,72,72),(73,73,73),(74,74,74),(75,75,75);
74
75 --echo
76 ##################### Test 1 Section Begins ###############
77 --echo *** Test 1 Section Begins ***
78 SELECT COUNT(*) FROM test.t2 LEFT JOIN test.t1 ON pk2=pk1 WHERE b2 IN (4);
79 SELECT * FROM test.t2 LEFT JOIN test.t1 ON pk2=pk1 WHERE b2 IN (4);
80 SELECT COUNT(*) FROM test.t1 LEFT JOIN test.t2 ON pk1=pk2 WHERE b IN (4);
81 SELECT COUNT(*) FROM test.t1 LEFT JOIN test.t2 ON b=b2 WHERE pk1 IN (75);
82 SELECT b, c FROM test.t1 LEFT JOIN test.t2 ON pk1=pk2 ORDER BY b;
83 --echo
84 ####################### Test 1 Section End ################
85
86 ##################### Setup for test 2 ####################
87 --echo *** Setup for test 2 ****
88 DELETE FROM test.t1;
89 INSERT INTO test.t1 VALUES
90 (1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),
91 (6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10),
92 (11,11,11),(12,12,12),(13,13,13),(14,14,14),(15,15,15),
93 (16,16,16),(17,17,17),(18,18,18),(19,19,19),(20,20,20),
94 (21,21,21),(22,22,22),(23,23,23),(24,24,24),(25,25,25),
95 (26,26,26),(27,27,27),(28,28,28),(29,29,29),(30,30,30),
96 (31,31,31),(32,32,32),(33,33,33),(34,34,34),(35,35,35),
97 (36,36,36),(37,37,37),(38,38,38),(39,39,39),(40,40,40),
98 (41,41,41),(42,42,42),(43,43,43),(44,44,44),(45,45,45);
99 --echo
100 ############################# Test Section 2 ###############
101 --echo **** Test Section 2 ****
102 SELECT b, c FROM test.t1 LEFT JOIN test.t2 ON pk1=pk2 ORDER BY b;
103 SELECT COUNT(*) FROM test.t1 LEFT JOIN test.t2 ON b=b2;
104 SELECT COUNT(*) FROM test.t1 RIGHT JOIN test.t2 ON b=b2;
105 --disable_warnings
106 SHOW CREATE TABLE test.t2;
107 SHOW CREATE TABLE test.t1;
108 --enable_warnings
109 ALTER TABLE test.t2 TABLESPACE table_space1 STORAGE DISK
110 ENGINE=NDB;
111 --disable_warnings
112 SHOW CREATE TABLE test.t2;
113 --enable_warnings
114 ALTER TABLE test.t1 STORAGE MEMORY ENGINE=NDBCLUSTER;
115 --disable_warnings
116 SHOW CREATE TABLE test.t1;
117 --enable_warnings
118 --echo
119 ######################### End Test Section 2 #################
120 DROP TABLE test.t1;
121 DROP TABLE test.t2;
122 ##################### Setup for Test Section 3 ###############
123 --echo *** Setup for Test Section 3 ***
124 CREATE TABLE test.t1 (
125 usr_id INT unsigned NOT NULL,
126 uniq_id INT unsigned NOT NULL AUTO_INCREMENT,
127 start_num INT unsigned NOT NULL DEFAULT 1,
128 increment INT unsigned NOT NULL DEFAULT 1,
129 PRIMARY KEY (uniq_id),
130 INDEX usr_uniq_idx (usr_id, uniq_id),
131 INDEX uniq_usr_idx (uniq_id, usr_id))
132 TABLESPACE table_space1 STORAGE DISK
133 ENGINE=NDB;
134
135
136 CREATE TABLE test.t2 (
137 id INT unsigned NOT NULL DEFAULT 0,
138 usr2_id INT unsigned NOT NULL DEFAULT 0,
139 max INT unsigned NOT NULL DEFAULT 0,
140 c_amount INT unsigned NOT NULL DEFAULT 0,
141 d_max INT unsigned NOT NULL DEFAULT 0,
142 d_num INT unsigned NOT NULL DEFAULT 0,
143 orig_time INT unsigned NOT NULL DEFAULT 0,
144 c_time INT unsigned NOT NULL DEFAULT 0,
145 active ENUM ("no","yes") NOT NULL,
146 PRIMARY KEY (id,usr2_id),
147 INDEX id_idx (id),
148 INDEX usr2_idx (usr2_id))
149 ENGINE=NDB;
150
151 INSERT INTO test.t1 VALUES (3,NULL,0,50),(3,NULL,0,200),(3,NULL,0,25),(3,NULL,0,84676),(3,NULL,0,235),(3,NULL,0,10),(3,NULL,0,3098),(3,NULL,0,2947),(3,NULL,0,8987),(3,NULL,0,8347654),(3,NULL,0,20398),(3,NULL,0,8976),(3,NULL,0,500),(3,NULL,0,198);
152
153 --echo
154 ###################### Test Section 3 ######################
155 --echo **** Test Section 3 ****
156 SELECT test.t1.usr_id,test.t1.uniq_id,test.t1.increment,
157 test.t2.usr2_id,test.t2.c_amount,test.t2.max
158 FROM test.t1
159 LEFT JOIN test.t2 ON test.t2.id = test.t1.uniq_id
160 WHERE test.t1.uniq_id = 4
161 ORDER BY test.t2.c_amount;
162
163 INSERT INTO test.t2 VALUES (2,3,3000,6000,0,0,746584,837484,'yes');
164 INSERT INTO test.t2 VALUES (4,3,3000,6000,0,0,746584,837484,'yes');
165 INSERT INTO test.t2 VALUES (7,3,1000,2000,0,0,746294,937484,'yes');
166
167 SELECT test.t1.usr_id,test.t1.uniq_id,test.t1.increment,
168 test.t2.usr2_id,test.t2.c_amount,test.t2.max
169 FROM test.t1
170 LEFT JOIN test.t2 ON test.t2.id = test.t1.uniq_id
171 WHERE test.t1.uniq_id = 4
172 ORDER BY test.t2.c_amount;
173 --echo
174 ####################### End Section 3 #########################
175 DROP TABLE test.t1;
176 DROP TABLE test.t2;
177
178 ########## Test that use to be in ndb_dd_advance.test #########
179 ########## ndb_dd_advance.test is now ndb_dd_alter.test #######
180 #### Use group by asc and dec; Use having; Use order by. ####
181
182 CREATE TABLE test.t1 (
183 a1 smallint NOT NULL,
184 a2 int NOT NULL,
185 a3 bigint NOT NULL,
186 a4 char(10),
187 a5 decimal(5,1),
188 a6 time,
189 a7 date,
190 a8 datetime,
191 a9 VARCHAR(255),
192 a10 blob,
193 PRIMARY KEY(a1)
194 ) TABLESPACE table_space1 STORAGE DISK ENGINE=NDB;
195
196 CREATE TABLE test.t2 (
197 b1 smallint NOT NULL,
198 b2 int NOT NULL,
199 b3 bigint NOT NULL,
200 b4 char(10),
201 b5 decimal(5,1),
202 b6 time,
203 b7 date,
204 b8 datetime,
205 b9 VARCHAR(255),
206 b10 blob,
207 PRIMARY KEY(b1)
208 ) ENGINE=NDB;
209
210 ### ADD Some DATA to tables ######
211
212
213 let $1=20;
214 --disable_query_log
215 while ($1)
216 {
217 eval insert into test.t1 values($1, $1+1, $1+2000000000, "aaa$1", 34.2, '4:3:2', '2006-1-1', '1971-5-28 16:55:03', "bbbbbbbbbbbbb$1", "binary data");
218 eval insert into test.t2 values($1+2, $1+3, $1+3000000000, "aaa$1", 35.2, '4:3:2', '2006-1-1', '1971-5-28 16:55:03', "bbbbbbbbbbbbb$1", "binary data");
219 dec $1;
220 }
221 --enable_query_log
222
223 ALTER TABLE test.t1 ADD INDEX (a2), ADD INDEX (a3), ADD INDEX (a8);
224 ALTER TABLE test.t2 ADD INDEX (b2), ADD INDEX (b3), ADD INDEX (b8);
225 --disable_warnings
226 SHOW CREATE TABLE test.t1;
227 SHOW CREATE TABLE test.t2;
228 --enable_warnings
229 SELECT * FROM test.t1 order by a1;
230 SELECT * FROM test.t2 order by b1;
231 SELECT COUNT(a1), a1, COUNT(a1)*a1 FROM test.t1 GROUP BY a1;
232 SELECT COUNT(a2), (a2+1), COUNT(a2)*(a2+0) FROM test.t1 GROUP BY a2;
233
234 DROP TABLE test.t1;
235 DROP TABLE test.t2;
236
237 CREATE TABLE test.t1 (a INT NOT NULL,
238 b CHAR(5), c TEXT, d INT NOT NULL PRIMARY KEY) TABLESPACE
239 table_space1 STORAGE DISK ENGINE=NDB;
240
241 insert into test.t1 (a,b,c,d) values (1,'a','Jonas',1),(2,'b','Tomas',2),
242 (3,'c','Pekka',3),(4,'d','Martin',4),(5,'e','Stewart',5),(6,'f','Jeb',6),
243 (7,'g','Serge',7),(8,'h','Clavin',8);
244 select distinct a from test.t1 group by b,a having a > 4 order by a desc;
245 select distinct a,c from test.t1 group by b,c,a having a > 2 order by a desc;
246 select distinct a from test.t1 group by b,a having a > 1 order by a asc;
247 select distinct a,c from test.t1 group by b,c,a having a > 3 order by a asc;
248 drop table test.t1;
249
250 create table test.t1 (a char(1), key(a)) TABLESPACE table_space1
251 STORAGE DISK ENGINE=NDB;
252 insert into test.t1 values('1'),('1'),('1'),('2'),('2'),('3'),('3');
253 select * from test.t1 where a >= '1' order by a;
254 select distinct a from test.t1 order by a desc;
255 select distinct a from test.t1 where a >= '1' order by a desc;
256 select distinct a from test.t1 where a >= '1' order by a asc;
257 drop table test.t1;
258
259 CREATE TABLE test.t1 (email varchar(50), infoID BIGINT, dateentered DATETIME)
260 TABLESPACE table_space1 STORAGE DISK ENGINE=NDB;
261 CREATE TABLE test.t2 (infoID BIGINT, shipcode varchar(10)) ENGINE=NDB;
262
263 INSERT INTO test.t1 (email, infoID, dateentered) VALUES
264 ('[email protected]', 1, '2002-07-30 22:56:38'),
265 ('[email protected]', 1, '2002-07-27 22:58:16'),
266 ('[email protected]', 1, '2002-06-19 15:22:19'),
267 ('[email protected]', 2, '2002-06-18 14:23:47'),
268 ('[email protected]', 1, '2002-05-19 22:17:32');
269
270 INSERT INTO test.t2(infoID, shipcode) VALUES
271 (1, 'Z001'),
272 (2, 'R002');
273
274 SELECT DISTINCTROW email, shipcode FROM test.t1, test.t2
275 WHERE t1.infoID=t2.infoID order by email, shipcode;
276
277 # Change sql_mode to allow the two queries below
278 # which uses "DISTINCT with an ORDER BY on column which
279 # is not in the select list"
280 --source include/turn_off_only_full_group_by.inc
281 SELECT DISTINCTROW email FROM test.t1 ORDER BY dateentered DESC;
282 SELECT DISTINCTROW email, shipcode FROM test.t1, test.t2
283 WHERE test.t1.infoID=test.t2.infoID ORDER BY dateentered DESC;
284 --source include/restore_sql_mode_after_turn_off_only_full_group_by.inc
285
286 DROP TABLE test.t1,test.t2;
287
288
289 #####################################################
290 # Testing In-Memory Dynamic Columns with Disk Columns
291 ####################################################
292
293 --echo #
294 --echo # Bug#22195588 : SETTING NULL TO A DYNAMIC IN-MEMORY COLUMN CORRUPTS DISK COLUMN VALUES
295 --echo #
296
297 --echo # Setting up tables
298 create table t1(
299 a int unsigned auto_increment primary key,
300 b int,
301 c int default null column_format dynamic storage memory,
302 d varchar(100) default null column_format dynamic storage memory
303 )tablespace table_space1 storage disk engine=ndbcluster;
304
305 --echo # Populate t1
306 --echo # Setting all the dynamic columns to NULL and varying disk parts
307 insert into t1 () values();
308 insert into t1 (b) values (10);
309
310 --echo # Populating values to single dynamic column and varying disk parts
311 insert into t1 (c) values (20);
312 insert into t1 (b, c) values (10, 20);
313
314 insert into t1 (d) values (repeat('0123456789', 3));
315 insert into t1 (d) values (repeat('0123456789', 10));
316 insert into t1 (b, d) values (10, repeat('0123456789',3));
317 insert into t1 (b, d) values (10, repeat('0123456789',10));
318
319 --echo # Both dynamic columns have non NULL values and disk columns varied
320 insert into t1 (c, d) values (20, repeat('0123456789',3));
321 insert into t1 (c, d) values (20, repeat('0123456789',10));
322 insert into t1 (b, c, d) values (10, 20, repeat('0123456789',3));
323 insert into t1 (b, c, d) values (10, 20, repeat('0123456789',10));
324
325 --echo # Verify that they are properly stored and retrieved
326 select * from t1 order by a;
327
328 --echo # Update some rows to verify that the varpart is reallocating properly
329 --echo # Updating NULL dynamic columns and assigning NON-NULL values
330 update t1 set c = 10 where a in (1,2);
331 select * from t1 where a in (1,2) order by a;
332
333 --echo # Updating and setting NULL to NON-NULL dynamic columns
334 update t1 set c = null where a in (1,2);
335 select * from t1 where a in (1,2) order by a;
336
337 --echo # Updating NULL dynamic var columns and assigning NON-NULL values
338 update t1 set d = repeat('0123456789', 5) where a in (1,2);
339 select * from t1 where a in (1,2) order by a;
340
341 --echo # Updating NULL to NON-NULL and vice versa in dynamic columns
342 update t1 set c = null, d = repeat('0123456789', 10) where a in (3,4);
343 select * from t1 where a in (3,4) order by a;
344
345 --echo # Updating and setting NULL to NON-NULL dynamic var columns
346 update t1 set c = 20, d = null where a in (5,6,7,8);
347 select * from t1 where a in (5,6,7,8) order by a;
348
349 --echo # Update all dynamic columns to NULL
350 update t1 set c = null, d = null where a > 8;
351 select * from t1 where a > 8 order by a;
352
353 --echo # Update all dynamic columns to NON-NULL values
354 update t1 set c = 20, d = '0123456789' where a > 8;
355 select * from t1 where a > 8 order by a;
356
357 select * from t1 order by a;
358
359 --echo # Now do some transactions with multiple operations
360
361 # Record var memory usage at start
362 --source ndb_var_mem_usage.inc
363
364 --echo # Add more data to dynamic varchar and then set to NULL
365 --echo # Meanwhile insert value into int column
366 --echo # This should reduce the var memory usage
367 select * from t1 where a = 1;
368 begin;
369 update t1 set d = repeat('0123456789', 10) where a = 1;
370 update t1 set b = 20 where a = 1;
371 update t1 set d = NULL where a = 1;
372 update t1 set c = 30 where a = 1;
373 commit;
374 select * from t1 where a = 1;
375
376 --echo # Check if the var mem has decreased
377 --source ndb_var_mem_usage.inc
378 --disable_query_log
379 select if (@old_var_used_bytes > @var_used_bytes, 'Yes', 'No')
380 as 'Has var part memory usage decreased?';
381 --enable_query_log
382
383 --echo # Setting up next transaction - make dynamic columns NULL
384 update t1 set c = NULL where a = 1;
385
386 --echo # Checking var memory and number of var parts before transaction
387 --source ndb_var_mem_usage.inc
388
389 --echo # Insert and later set null to dynamic columns in the same transaction
390 --echo # This should not change memory usage
391 select * from t1 where a = 1;
392 begin;
393 update t1 set c = 30 where a = 1;
394 update t1 set b = 30 where a = 1;
395 update t1 set d = repeat('0123456789', 10) where a = 1;
396 update t1 set b = 45 where a = 1;
397 update t1 set d = repeat('0123456789', 5) where a = 1;
398 update t1 set c = NULL where a = 1;
399 update t1 set d = NULL where a = 1;
400 commit;
401 select * from t1 where a = 1;
402
403 --echo # Checking if the var mem is the same
404 --source ndb_var_mem_usage.inc
405 --disable_query_log
406 select if (@old_var_used_bytes = @var_used_bytes, 'Yes', 'No')
407 as 'Is var part memory usage unchanged?';
408 --enable_query_log
409
410 --echo # Grow the dynamic varchar and then shrink it but still having more data than when it was at start.
411 --echo # Insert alternatively the other dynamic column with NULL but back to a NON-NULL value at end
412 --echo # This should be reflected correctly in the memory
413 select * from t1 where a = 12;
414 begin;
415 update t1 set c = 42 where a = 12;
416 update t1 set d = repeat('0123456789', 5) where a = 12;
417 update t1 set c = NULL where a = 12;
418 update t1 set d = repeat('0123456789', 10) where a = 12;
419 update t1 set c = 30 where a = 12;
420 update t1 set d = repeat('0123456789', 2) where a = 12;
421 commit;
422 select * from t1 where a = 12;
423
424 --echo # Check if the var mem has increased
425 --source ndb_var_mem_usage.inc
426 --disable_query_log
427 select if (@old_var_used_bytes < @var_used_bytes, 'Yes', 'No')
428 as 'Has var part memory usage increased?';
429 --enable_query_log
430
431 --echo # Cleanup
432 drop table t1;
433
434 #################
435 # Test Cleanup
436 #################
437
438 ALTER TABLESPACE table_space1
439 DROP DATAFILE './table_space1/datafile.dat';
440
441 DROP TABLESPACE table_space1;
442
443 DROP LOGFILE GROUP log_group1
444 ENGINE =NDB;
445
446 #End 5.1 test case
447