Debian Codegraph @

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

raw
1 -- source include/have_ndb.inc
2
3 #
4 # Bug#30322 Server crashes on selecting from i_s.columns when cluster is running -regression
5 #
6 --replace_column 1 #
7 select count(*) from information_schema.columns;
8
9 #
10 # some negative tests
11 # - (which assumes that MyISAM does not support
12 # tablespace or logfile group)
13 #
14
15 --error ER_ILLEGAL_HA_CREATE_OPTION
16 CREATE LOGFILE GROUP lg1
17 ADD UNDOFILE 'undofile.dat'
18 INITIAL_SIZE 16M
19 UNDO_BUFFER_SIZE = 1M
20 ENGINE=MYISAM;
21
22 --error ER_UNKNOWN_STORAGE_ENGINE
23 ALTER LOGFILE GROUP lg1
24 ADD UNDOFILE 'undofile02.dat'
25 INITIAL_SIZE = 4M
26 ENGINE=XYZ;
27
28 --error ER_ILLEGAL_HA_CREATE_OPTION
29 CREATE TABLESPACE ts1
30 ADD DATAFILE 'datafile.dat'
31 USE LOGFILE GROUP lg1
32 INITIAL_SIZE 12M
33 ENGINE=MYISAM;
34
35 #
36 # bug#29574
37 #
38 --error ER_CREATE_FILEGROUP_FAILED
39 CREATE LOGFILE GROUP lg1
40 ADD UNDOFILE 'undofile.dat'
41 INITIAL_SIZE 5K
42 UNDO_BUFFER_SIZE = 1M
43 ENGINE=NDB;
44
45 SHOW WARNINGS;
46
47 ##################################
48 # Basic test of disk tables for NDB
49 # Start by creating a logfile group
50 ##################################
51
52 # Note : This logfile group is used by
53 # most of the tests in this file
54 CREATE LOGFILE GROUP lg1
55 ADD UNDOFILE 'undofile.dat'
56 INITIAL_SIZE 16M
57 UNDO_BUFFER_SIZE = 1M
58 ENGINE=NDB;
59
60 ALTER LOGFILE GROUP lg1
61 ADD UNDOFILE 'undofile02.dat'
62 INITIAL_SIZE = 4M
63 ENGINE=NDB;
64
65 ###################################################
66 # Create a tablespace connected to the logfile group
67 ###################################################
68
69 CREATE TABLESPACE ts1
70 ADD DATAFILE 'datafile.dat'
71 USE LOGFILE GROUP lg1
72 INITIAL_SIZE 12M
73 ENGINE NDB;
74
75 ALTER TABLESPACE ts1
76 ADD DATAFILE 'datafile02.dat'
77 INITIAL_SIZE = 12M;
78
79 ########################################
80 # Create a table using this tablespace
81 # Specify number of partitions in order to make
82 # output from INFORMATION_SCHEMA.PARTITIONS
83 # independent of ndbd/ndbmtd and ThreadConfig.
84 ########################################
85
86 CREATE TABLE t1
87 (pk1 INT NOT NULL PRIMARY KEY, b INT NOT NULL, c INT NOT NULL)
88 TABLESPACE ts1 STORAGE DISK
89 ENGINE=NDB
90 PARTITION BY KEY() PARTITIONS 8;
91
92 SHOW CREATE TABLE t1;
93
94 INSERT INTO t1 VALUES (0, 0, 0);
95 SELECT * FROM t1;
96
97 INSERT INTO t1 VALUES
98 (1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),
99 (6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10),
100 (11,11,11),(12,12,12),(13,13,13),(14,14,14),(15,15,15),
101 (16,16,16),(17,17,17),(18,18,18),(19,19,19),(20,20,20),
102 (21,21,21),(22,22,22),(23,23,23),(24,24,24),(25,25,25),
103 (26,26,26),(27,27,27),(28,28,28),(29,29,29),(30,30,30),
104 (31,31,31),(32,32,32),(33,33,33),(34,34,34),(35,35,35),
105 (36,36,36),(37,37,37),(38,38,38),(39,39,39),(40,40,40),
106 (41,41,41),(42,42,42),(43,43,43),(44,44,44),(45,45,45),
107 (46,46,46),(47,47,47),(48,48,48),(49,49,49),(50,50,50),
108 (51,51,51),(52,52,52),(53,53,53),(54,54,54),(55,55,55),
109 (56,56,56),(57,57,57),(58,58,58),(59,59,59),(60,60,60),
110 (61,61,61),(62,62,62),(63,63,63),(64,64,64),(65,65,65),
111 (66,66,66),(67,67,67),(68,68,68),(69,69,69),(70,70,70),
112 (71,71,71),(72,72,72),(73,73,73),(74,74,74),(75,75,75),
113 (76,76,76),(77,77,77),(78,78,78),(79,79,79),(80,80,80),
114 (81,81,81),(82,82,82),(83,83,83),(84,84,84),(85,85,85),
115 (86,86,86),(87,87,87),(88,88,88),(89,89,89),(90,90,90),
116 (91,91,91),(92,92,92),(93,93,93),(94,94,94),(95,95,95),
117 (96,96,96),(97,97,97),(98,98,98),(99,99,99),(100,100,100),
118 (101,101,101),(102,102,102),(103,103,103),(104,104,104),(105,105,105),
119 (106,106,106),(107,107,107),(108,108,108),(109,109,109),(110,110,110),
120 (111,111,111),(112,112,112),(113,113,113),(114,114,114),(115,115,115),
121 (116,116,116),(117,117,117),(118,118,118),(119,119,119),(120,120,120),
122 (121,121,121),(122,122,122),(123,123,123),(124,124,124),(125,125,125),
123 (126,126,126),(127,127,127),(128,128,128),(129,129,129),(130,130,130),
124 (131,131,131),(132,132,132),(133,133,133),(134,134,134),(135,135,135),
125 (136,136,136),(137,137,137),(138,138,138),(139,139,139),(140,140,140),
126 (141,141,141),(142,142,142),(143,143,143),(144,144,144),(145,145,145),
127 (146,146,146),(147,147,147),(148,148,148),(149,149,149),(150,150,150),
128 (151,151,151),(152,152,152),(153,153,153),(154,154,154),(155,155,155),
129 (156,156,156),(157,157,157),(158,158,158),(159,159,159),(160,160,160),
130 (161,161,161),(162,162,162),(163,163,163),(164,164,164),(165,165,165),
131 (166,166,166),(167,167,167),(168,168,168),(169,169,169),(170,170,170),
132 (171,171,171),(172,172,172),(173,173,173),(174,174,174),(175,175,175),
133 (176,176,176),(177,177,177),(178,178,178),(179,179,179),(180,180,180),
134 (181,181,181),(182,182,182),(183,183,183),(184,184,184),(185,185,185),
135 (186,186,186),(187,187,187),(188,188,188),(189,189,189),(190,190,190),
136 (191,191,191),(192,192,192),(193,193,193),(194,194,194),(195,195,195),
137 (196,196,196),(197,197,197),(198,198,198),(199,199,199),(200,200,200),
138 (201,201,201),(202,202,202),(203,203,203),(204,204,204),(205,205,205),
139 (206,206,206),(207,207,207),(208,208,208),(209,209,209),(210,210,210),
140 (211,211,211),(212,212,212),(213,213,213),(214,214,214),(215,215,215),
141 (216,216,216),(217,217,217),(218,218,218),(219,219,219),(220,220,220),
142 (221,221,221),(222,222,222),(223,223,223),(224,224,224),(225,225,225),
143 (226,226,226),(227,227,227),(228,228,228),(229,229,229),(230,230,230),
144 (231,231,231),(232,232,232),(233,233,233),(234,234,234),(235,235,235),
145 (236,236,236),(237,237,237),(238,238,238),(239,239,239),(240,240,240),
146 (241,241,241),(242,242,242),(243,243,243),(244,244,244),(245,245,245),
147 (246,246,246),(247,247,247),(248,248,248),(249,249,249),(250,250,250),
148 (251,251,251),(252,252,252),(253,253,253),(254,254,254),(255,255,255),
149 (256,256,256),(257,257,257),(258,258,258),(259,259,259),(260,260,260),
150 (261,261,261),(262,262,262),(263,263,263),(264,264,264),(265,265,265),
151 (266,266,266),(267,267,267),(268,268,268),(269,269,269),(270,270,270),
152 (271,271,271),(272,272,272),(273,273,273),(274,274,274),(275,275,275),
153 (276,276,276),(277,277,277),(278,278,278),(279,279,279),(280,280,280),
154 (281,281,281),(282,282,282),(283,283,283),(284,284,284),(285,285,285),
155 (286,286,286),(287,287,287),(288,288,288),(289,289,289),(290,290,290),
156 (291,291,291),(292,292,292),(293,293,293),(294,294,294),(295,295,295),
157 (296,296,296),(297,297,297),(298,298,298),(299,299,299),(300,300,300),
158 (301,301,301),(302,302,302),(303,303,303),(304,304,304),(305,305,305),
159 (306,306,306),(307,307,307),(308,308,308),(309,309,309),(310,310,310),
160 (311,311,311),(312,312,312),(313,313,313),(314,314,314),(315,315,315),
161 (316,316,316),(317,317,317),(318,318,318),(319,319,319),(320,320,320),
162 (321,321,321),(322,322,322),(323,323,323),(324,324,324),(325,325,325),
163 (326,326,326),(327,327,327),(328,328,328),(329,329,329),(330,330,330),
164 (331,331,331),(332,332,332),(333,333,333),(334,334,334),(335,335,335),
165 (336,336,336),(337,337,337),(338,338,338),(339,339,339),(340,340,340),
166 (341,341,341),(342,342,342),(343,343,343),(344,344,344),(345,345,345),
167 (346,346,346),(347,347,347),(348,348,348),(349,349,349),(350,350,350),
168 (351,351,351),(352,352,352),(353,353,353),(354,354,354),(355,355,355),
169 (356,356,356),(357,357,357),(358,358,358),(359,359,359),(360,360,360),
170 (361,361,361),(362,362,362),(363,363,363),(364,364,364),(365,365,365),
171 (366,366,366),(367,367,367),(368,368,368),(369,369,369),(370,370,370),
172 (371,371,371),(372,372,372),(373,373,373),(374,374,374),(375,375,375),
173 (376,376,376),(377,377,377),(378,378,378),(379,379,379),(380,380,380),
174 (381,381,381),(382,382,382),(383,383,383),(384,384,384),(385,385,385),
175 (386,386,386),(387,387,387),(388,388,388),(389,389,389),(390,390,390),
176 (391,391,391),(392,392,392),(393,393,393),(394,394,394),(395,395,395),
177 (396,396,396),(397,397,397),(398,398,398),(399,399,399),(400,400,400),
178 (401,401,401),(402,402,402),(403,403,403),(404,404,404),(405,405,405),
179 (406,406,406),(407,407,407),(408,408,408),(409,409,409),(410,410,410),
180 (411,411,411),(412,412,412),(413,413,413),(414,414,414),(415,415,415),
181 (416,416,416),(417,417,417),(418,418,418),(419,419,419),(420,420,420),
182 (421,421,421),(422,422,422),(423,423,423),(424,424,424),(425,425,425),
183 (426,426,426),(427,427,427),(428,428,428),(429,429,429),(430,430,430),
184 (431,431,431),(432,432,432),(433,433,433),(434,434,434),(435,435,435),
185 (436,436,436),(437,437,437),(438,438,438),(439,439,439),(440,440,440),
186 (441,441,441),(442,442,442),(443,443,443),(444,444,444),(445,445,445),
187 (446,446,446),(447,447,447),(448,448,448),(449,449,449),(450,450,450),
188 (451,451,451),(452,452,452),(453,453,453),(454,454,454),(455,455,455),
189 (456,456,456),(457,457,457),(458,458,458),(459,459,459),(460,460,460),
190 (461,461,461),(462,462,462),(463,463,463),(464,464,464),(465,465,465),
191 (466,466,466),(467,467,467),(468,468,468),(469,469,469),(470,470,470),
192 (471,471,471),(472,472,472),(473,473,473),(474,474,474),(475,475,475),
193 (476,476,476),(477,477,477),(478,478,478),(479,479,479),(480,480,480),
194 (481,481,481),(482,482,482),(483,483,483),(484,484,484),(485,485,485),
195 (486,486,486),(487,487,487),(488,488,488),(489,489,489),(490,490,490),
196 (491,491,491),(492,492,492),(493,493,493),(494,494,494),(495,495,495),
197 (496,496,496),(497,497,497),(498,498,498),(499,499,499),(500, 500, 500);
198
199 SELECT COUNT(*) FROM t1;
200
201 ########################################
202 # WL#5568: Check disk space usage.
203 ########################################
204
205 # Turn off caching for information_schema, otherwise values
206 # will be cached and only updated after ANALYZE TABLE
207 SET SESSION information_schema_stats_expiry=0;
208
209 SELECT table_name,
210 SUM(table_rows),
211 SUM(data_length),
212 MAX(max_data_length),
213 SUM(data_free)
214 FROM INFORMATION_SCHEMA.partitions
215 WHERE table_name='t1'
216 GROUP BY table_name;
217
218 SELECT table_name, table_rows, data_length, max_data_length, data_free
219 FROM information_schema.tables
220 WHERE table_name='t1';
221
222 ####################################
223 # Test error cases with size numbers
224 ####################################
225 --error ER_WRONG_SIZE_NUMBER
226 CREATE LOGFILE GROUP lg2
227 ADD UNDOFILE 'x.dat'
228 INITIAL_SIZE 10y
229 ENGINE = NDB;
230
231 --error ER_WRONG_SIZE_NUMBER
232 CREATE LOGFILE GROUP lg2
233 ADD UNDOFILE 'x.dat'
234 INITIAL_SIZE 10MB
235 ENGINE = NDB;
236
237 --error ER_PARSE_ERROR
238 CREATE LOGFILE GROUP lg2
239 ADD UNDOFILE 'x.dat'
240 INITIAL_SIZE 10 MB
241 ENGINE = NDB;
242
243 --error ER_PARSE_ERROR
244 CREATE LOGFILE GROUP lg2
245 ADD UNDOFILE 'x.dat'
246 INITIAL_SIZE 10 M
247 ENGINE = NDB;
248
249 --error ER_SIZE_OVERFLOW_ERROR
250 CREATE LOGFILE GROUP lg2
251 ADD UNDOFILE 'x.dat'
252 INITIAL_SIZE 1000000000000K
253 ENGINE = NDB;
254
255 DROP TABLE t1;
256
257 # Create a table with a disk column having offset > max offset
258
259 --error ER_CANT_CREATE_TABLE
260 create table t1(
261 col1 int unsigned not null primary key,
262 col2 int unsigned not null,
263 col3 varchar(8176),
264 col4 int)
265 tablespace ts1 storage disk
266 charset = latin1 engine=ndbcluster;
267 show warnings;
268
269 #Verify that it isn't possible to create no logging table with disk fields
270 --error ER_ILLEGAL_HA_CREATE_OPTION
271 CREATE TABLE t1 (a bigint primary key, b bigint storage disk)
272 ENGINE ndb
273 COMMENT="NDB_TABLE=NOLOGGING=1"
274 TABLESPACE ts1 STORAGE DISK;
275
276 #
277 # Drop tablespace and datafiles
278 #
279 ALTER TABLESPACE ts1
280 DROP DATAFILE 'datafile.dat';
281 ALTER TABLESPACE ts1
282 DROP DATAFILE 'datafile02.dat';
283 DROP TABLESPACE ts1;
284
285
286 --echo #
287 --echo # Bug #21699 DROP last DATAFILE from TABLESPACE even though there are still tables in it
288 --echo #
289
290 --echo # Create the tablespace
291 create tablespace ts2
292 add datafile 'datafile2_1.dat'
293 use logfile group lg1
294 initial_size 12M
295 engine ndb;
296
297 --echo # Create tables on the tablespace
298 CREATE TABLE City (
299 ID int(11) NOT NULL AUTO_INCREMENT,
300 Name char(35) NOT NULL,
301 CountryCode char(3) NOT NULL,
302 District char(20) NOT NULL,
303 Population int(11) NOT NULL,
304 PRIMARY KEY (ID)
305 ) ENGINE=ndbcluster
306 tablespace ts2
307 storage disk;
308
309 create table t1 (a int primary key, b int)
310 engine = ndbcluster tablespace ts2 storage disk;
311 insert into t1 values (1,1);
312
313 --echo # Attempting to drop the datafile should fail now
314 --error ER_ALTER_FILEGROUP_FAILED
315 alter tablespace ts2
316 drop datafile 'datafile2_1.dat';
317
318 show warnings;
319
320 drop table t1;
321
322 alter tablespace ts2
323 drop datafile 'datafile2_1.dat';
324
325 --echo # Inserting into the table should now fail with error : NoDatafile in tablespace
326 --error ER_GET_ERRMSG
327 insert
328 into City (Name,CountryCode,District,Population)
329 values ('BeiJing','CN','Beijing',2000);
330
331 --echo # Dropping tablsepace before dropping table should fail
332 --error ER_TABLESPACE_IS_NOT_EMPTY
333 drop tablespace ts2;
334
335 --echo # Cleanup
336 drop table City;
337
338 drop tablespace ts2;
339
340 --echo ###########################
341 --echo # Test update of mm/dd part
342 --echo ###########################
343
344 --echo # Create the tablespaces
345 CREATE TABLESPACE ts1
346 ADD DATAFILE 'datafile.dat'
347 USE LOGFILE GROUP lg1
348 INITIAL_SIZE 8M
349 ENGINE NDB;
350
351 --echo # Create the tables with mm/dd parts and run tests
352 CREATE TABLE t1 (a INT PRIMARY KEY, b CHAR(4) NOT NULL, c CHAR(4) NOT NULL, KEY(b)) TABLESPACE ts1 STORAGE DISK ENGINE = NDB;
353 INSERT INTO t1 VALUES (1,'1','1'), (2,'2','2'), (3,'3','3');
354 BEGIN;
355 UPDATE t1 SET b = '2' WHERE a = 1;
356 SELECT b FROM t1 WHERE a = 1;
357 SELECT * FROM t1 WHERE a = 1;
358 UPDATE t1 SET c = '2' WHERE a = 1;
359 SELECT b FROM t1 WHERE a = 1;
360 SELECT * FROM t1 WHERE a = 1;
361 UPDATE t1 SET b = '3' WHERE a = 1;
362 SELECT b FROM t1 WHERE a = 1;
363 SELECT * FROM t1 WHERE a = 1;
364 COMMIT;
365 SELECT * FROM t1 ORDER BY 1;
366 BEGIN;
367 UPDATE t1 SET c = '3' WHERE a = 1;
368 SELECT b FROM t1 WHERE a = 1;
369 SELECT * FROM t1 WHERE a = 1;
370 UPDATE t1 SET b = '4' WHERE a = 1;
371 SELECT b FROM t1 WHERE a = 1;
372 SELECT * FROM t1 WHERE a = 1;
373 UPDATE t1 SET c = '4' WHERE a = 1;
374 SELECT b FROM t1 WHERE a = 1;
375 SELECT * FROM t1 WHERE a = 1;
376 COMMIT;
377 SELECT * FROM t1 ORDER BY 1;
378 UPDATE t1 SET b = '5' WHERE a = 1;
379 SELECT * FROM t1 ORDER BY 1;
380 UPDATE t1 SET b = '6' WHERE b = '5';
381 SELECT * FROM t1 ORDER BY 1;
382 UPDATE t1 SET b = '7'WHERE c = '4';
383 SELECT * FROM t1 ORDER BY 1;
384 UPDATE t1 SET c = '5' WHERE a = 1;
385 SELECT * FROM t1 ORDER BY 1;
386 UPDATE t1 SET c = '6' WHERE b = '7';
387 SELECT * FROM t1 ORDER BY 1;
388 UPDATE t1 SET c = '7' WHERE c = '6';
389 SELECT * FROM t1 ORDER BY 1;
390 DROP TABLE t1;
391
392 #####
393
394 CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(4) NOT NULL, c CHAR(4) NOT NULL, KEY(b)) TABLESPACE ts1 STORAGE DISK ENGINE NDB;
395 INSERT INTO t1 VALUE (1,'1','1'), (2,'2','2'), (3,'3','3');
396 BEGIN;
397 UPDATE t1 SET b = '2' WHERE a = 1;
398 SELECT b FROM t1 WHERE a = 1;
399 SELECT * FROM t1 WHERE a = 1;
400 UPDATE t1 SET c = '2' WHERE a = 1;
401 SELECT b FROM t1 WHERE a = 1;
402 SELECT * FROM t1 WHERE a = 1;
403 UPDATE t1 SET b = '3' WHERE a = 1;
404 SELECT b FROM t1 WHERE a = 1;
405 SELECT * FROM t1 WHERE a = 1;
406 COMMIT;
407 SELECT * FROM t1 ORDER BY 1;
408 BEGIN;
409 UPDATE t1 SET c = '3' WHERE a = 1;
410 SELECT b FROM t1 WHERE a = 1;
411 SELECT * FROM t1 WHERE a = 1;
412 UPDATE t1 SET b = '4' WHERE a = 1;
413 SELECT b FROM t1 WHERE a = 1;
414 SELECT * FROM t1 WHERE a = 1;
415 UPDATE t1 SET c = '4' WHERE a = 1;
416 SELECT b FROM t1 WHERE a = 1;
417 SELECT * FROM t1 WHERE a = 1;
418 COMMIT;
419 SELECT * FROM t1 ORDER BY 1;
420 UPDATE t1 SET b = '5' WHERE a = 1;
421 SELECT * FROM t1 ORDER BY 1;
422 UPDATE t1 SET b = '6' WHERE b = '5';
423 SELECT * FROM t1 ORDER BY 1;
424 UPDATE t1 SET b = '7' WHERE c = '4';
425 SELECT * FROM t1 ORDER BY 1;
426 UPDATE t1 SET c = '5' WHERE a = 1;
427 SELECT * FROM t1 ORDER BY 1;
428 UPDATE t1 SET c = '6' WHERE b = '7';
429 SELECT * FROM t1 ORDER BY 1;
430 UPDATE t1 SET c = '7' WHERE c = '6';
431 SELECT * FROM t1 ORDER BY 1;
432
433 DELETE FROM t1;
434 INSERT INTO t1 VALUES (3,'1','1');
435 BEGIN;
436 UPDATE t1 SET b = b + 2 WHERE A = 3;
437 DELETE FROM t1 WHERE A = 3;
438 INSERT INTO t1 VALUES (3,'0','0');
439 COMMIT;
440 SELECT * from t1 ORDER BY 1;
441
442 DROP TABLE t1;
443
444 --echo # Cleanup tablespace
445 ALTER TABLESPACE ts1
446 DROP DATAFILE 'datafile.dat';
447 DROP TABLESPACE ts1;
448
449 --echo ########################
450 --echo # Test for blobs...
451 --echo ########################
452
453 --echo # Create the tablespace
454 CREATE TABLESPACE ts1
455 ADD DATAFILE 'datafile.dat'
456 USE LOGFILE GROUP lg1
457 INITIAL_SIZE 12M
458 ENGINE NDB;
459
460 --echo # Create a table with blob column on it
461 CREATE TABLE t1 (
462 a INT NOT NULL PRIMARY KEY,
463 b TEXT NOT NULL
464 ) TABLESPACE ts1 STORAGE DISK ENGINE=NDBCLUSTER;
465
466 --echo # Generate the blob values to be inserted
467 # b1 length 2000+256 (blob part aligned)
468 #########################################
469 set @x0 = '01234567012345670123456701234567';
470 set @x0 = concat(@x0,@x0,@x0,@x0,@x0,@x0,@x0,@x0);
471 set @b1 = 'b1';
472 set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);
473 set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);
474 set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);
475 set @b1 = concat(@b1,@x0);
476 # b2 length 20000
477 ##########################################
478 set @b2 = 'b2';
479 set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);
480 set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);
481 set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);
482 set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);
483
484 --echo # Run the tests
485 INSERT INTO t1 VALUES(1,@b1);
486 INSERT INTO t1 VALUES(2,@b2);
487 SELECT a,length(b),substr(b,1+2*900,2) FROM t1 WHERE a=1;
488 SELECT a,length(b),substr(b,1+2*9000,2) FROM t1 WHERE a=2;
489 UPDATE t1 SET b=@b2 WHERE a=1;
490 UPDATE t1 SET b=@b1 WHERE a=2;
491 SELECT a,length(b),substr(b,1+2*9000,2) FROM t1 WHERE a=1;
492 SELECT a,length(b),substr(b,1+2*900,2) FROM t1 WHERE a=2;
493 UPDATE t1 SET b=concat(b,b) WHERE a=1;
494 UPDATE t1 SET b=concat(b,b) WHERE a=2;
495 SELECT a,length(b),substr(b,1+4*9000,2) FROM t1 WHERE a=1;
496 SELECT a,length(b),substr(b,1+4*900,2) FROM t1 WHERE a=2;
497 DELETE FROM t1 WHERE a=1;
498 DELETE FROM t1 WHERE a=2;
499 SELECT COUNT(*) FROM t1;
500
501 --echo # Cleanup
502 DROP TABLE t1;
503 ALTER TABLESPACE ts1
504 DROP DATAFILE 'datafile.dat';
505 DROP TABLESPACE ts1;
506
507 --echo #
508 --echo # Bug#20612 INS-DEL bug in TUP (not pgman bug)
509 --echo # found via disk data assert but is not pgman or disk data related
510 --echo #
511
512 --echo #Setup - Create tablespaces and table
513 CREATE TABLESPACE ts1
514 ADD DATAFILE 'datafile.dat'
515 USE LOGFILE GROUP lg1
516 INITIAL_SIZE 8M
517 ENGINE NDB;
518
519 CREATE TABLE t1 (
520 a int NOT NULL,
521 b varchar(4000) charset latin1, -- must use 2 pages undo
522 PRIMARY KEY using hash (a)
523 )
524 TABLESPACE ts1 STORAGE DISK ENGINE=NDBCLUSTER;
525
526 --echo # Run the tests
527 set autocommit = 0;
528 insert into t1 values(0,'x');
529 insert into t1 values(1,'x');
530 insert into t1 values(2,'x');
531 insert into t1 values(3,'x');
532 insert into t1 values(4,'x');
533 insert into t1 values(5,'x');
534 insert into t1 values(6,'x');
535 insert into t1 values(7,'x');
536 insert into t1 values(8,'x');
537 delete from t1 where a = 0;
538 commit;
539
540 delete from t1;
541 begin;
542 insert into t1 values (1, 'x');
543 select * from t1;
544 rollback;
545
546 set autocommit = 1;
547
548 --echo # Cleanup
549 drop table t1;
550 alter tablespace ts1
551 drop datafile 'datafile.dat';
552 drop tablespace ts1;
553
554 --echo #############################
555 --echo # Customer posted order by test case
556 --echo # Org in ndb_dd_advance.test
557 --echo #############################
558
559 --echo # Setup
560 create tablespace ts1
561 add datafile 'datafile.dat'
562 use logfile group lg1
563 initial_size 4M
564 engine ndb;
565 create table t1 (f1 varchar(50) primary key, f2 text,f3 int)
566 TABLESPACE ts1 STORAGE DISK engine=NDB;
567 --echo # Run the tests
568 insert into t1 (f1,f2,f3)VALUES("111111","aaaaaa",1);
569 insert into t1 (f1,f2,f3)VALUES("222222","bbbbbb",2);
570 select * from t1 order by f1;
571 select f1,f2 from t1 order by f2;
572 select f2 from t1 order by f2;
573 select f1,f2 from t1 order by f1;
574 --echo # Cleanup
575 drop table t1;
576 alter tablespace ts1
577 drop datafile 'datafile.dat';
578 drop tablespace ts1;
579
580 --echo #
581 --echo # bug#34118 hash index trigger and do not update any disk attr
582 --echo #
583
584 --echo # Setup - Create tablespace, table and fill it
585 CREATE TABLESPACE ts1
586 ADD DATAFILE 'datafile.dat'
587 USE LOGFILE GROUP lg1
588 INITIAL_SIZE 4M
589 ENGINE NDB;
590 CREATE TABLE t1 (
591 id int unsigned NOT NULL,
592 c1 int unsigned NOT NULL,
593 c2 int DEFAULT NULL,
594 PRIMARY KEY using hash (id),
595 UNIQUE KEY c1 using hash (c1))
596 TABLESPACE ts1 STORAGE DISK ENGINE=ndbcluster;
597 insert into t1 values(1, 1, 3);
598 insert into t1 values(2, 2, 3);
599
600 --echo # Confirm that the update fails
601 --error ER_DUP_ENTRY
602 update t1 set c1 = 1 where id = 2;
603
604 --echo # Cleanup
605 drop table t1;
606
607 ALTER TABLESPACE ts1
608 DROP DATAFILE 'datafile.dat';
609 DROP TABLESPACE ts1;
610
611 --echo # Also drop the logfile group lg1 created at the start of this file
612 DROP LOGFILE GROUP lg1
613 ENGINE =NDB;
614
615 # bug#31712 - datafile smaller than extent
616 --echo === bug#31712 ===
617
618 create logfile group lg1
619 add undofile 'undofile.dat'
620 initial_size 2M
621 undo_buffer_size 1M
622 engine ndb;
623
624 --error ER_CREATE_FILEGROUP_FAILED
625 create tablespace ts1
626 add datafile 'datafile.dat'
627 use logfile group lg1
628 initial_size 1M
629 extent_size 2M
630 engine ndb;
631 show warnings;
632
633 create tablespace ts1
634 add datafile 'datafile.dat'
635 use logfile group lg1
636 initial_size 2M
637 extent_size 2M
638 engine ndb;
639
640 --error ER_ALTER_FILEGROUP_FAILED
641 alter tablespace ts1
642 add datafile 'datafile02.dat'
643 initial_size 1M;
644 show warnings;
645
646 alter tablespace ts1
647 drop datafile 'datafile.dat';
648
649 drop tablespace ts1;
650
651 drop logfile group lg1
652 engine ndb;
653
654 # bug#31782 - tablespace with no files
655 --echo === bug#31782 ===
656
657 create logfile group lg1
658 add undofile 'undofile.dat'
659 initial_size 2M
660 undo_buffer_size 1M
661 engine ndb;
662
663 create tablespace ts1
664 add datafile 'datafile.dat'
665 use logfile group lg1
666 initial_size 2M
667 extent_size 2M
668 engine ndb;
669
670 select tablespace_name, file_type, file_name
671 from information_schema.files
672 where engine = 'ndbcluster' and file_type = 'DATAFILE';
673
674 alter tablespace ts1
675 drop datafile 'datafile.dat';
676
677 select tablespace_name, file_type, file_name
678 from information_schema.files
679 where engine = 'ndbcluster' and file_type = 'DATAFILE';
680
681 drop tablespace ts1;
682
683 drop logfile group lg1
684 engine ndb;
685
686 # bug#49709 - I_S discrepancies due to DICT vs LGMAN/TSMAN
687 --echo === bug#49709 ===
688
689 # undo buffer size rounded UP to page size (32k)
690 # 294913 = 9 * 32k + 1 => 10 * 32k = 327680
691 # undofile size rounded DOWN to page size (32k)
692 # 3276801 = 100 * 32k + 1 => 100 * 32k = 3276800
693 create logfile group lg1
694 add undofile 'undofile.dat'
695 initial_size 3276801
696 undo_buffer_size 294913
697 engine ndb;
698
699 # undofile size rounded DOWN to page size (32k)
700 # 3309567 = 101 * 32k - 1 => 100 * 32k = 3276800
701 alter logfile group lg1
702 add undofile 'undofile02.dat'
703 initial_size 3309567
704 engine ndb;
705
706 # extent size rounded UP to page size (32k)
707 # 294913 = 9 * 32k + 1 => 10 * 32k = 327680
708 # datafile size rounded DOWN to page size and UP to extent size
709 # 3276801 = 100 * 32k + 1 => 100 * 32k == 10 * EXT = 3276800
710 create tablespace ts1
711 add datafile 'datafile.dat'
712 use logfile group lg1
713 initial_size 3276801
714 extent_size 294913
715 engine ndb;
716
717 # datafile size rounded DOWN to page size and UP to extent size
718 # 3342335 = 102 * 32k - 1 => 110 * 32k == 11 * EXT = 3604480
719 alter tablespace ts1
720 add datafile 'datafile02.dat'
721 initial_size 3342335;
722
723 # check undofiles
724 select file_name, initial_size
725 from information_schema.files
726 where file_type = 'UNDO LOG' and file_name like '%dat'
727 order by file_name;
728
729 # check datafiles
730 select file_name, initial_size, extent_size, total_extents, free_extents
731 from information_schema.files
732 where file_type = 'DATAFILE'
733 order by file_name;
734
735 alter tablespace ts1
736 drop datafile 'datafile02.dat';
737
738 alter tablespace ts1
739 drop datafile 'datafile.dat';
740
741 drop tablespace ts1;
742
743 drop logfile group lg1
744 engine ndb;
745
746 # Bug 13116514: parser does not accept suffix 'M', etc for sizes
747 set character_set_client=utf8;
748 create logfile group lg1
749 add undofile 'undo1.log'
750 undo_buffer_size 2M
751 engine=ndb;
752
753 drop logfile group lg1
754 engine ndb;
755
756 #End 5.1 test case
757
758 #
759 # BUG#11762867
760 # - create logfile group with too large undo_buffer_size
761 # in order to show the improved ndb error message indicating
762 # "too large undo_buffer_size or too small GlobalSharedMemory"
763 #
764 --error ER_CREATE_FILEGROUP_FAILED
765 CREATE LOGFILE GROUP lg1
766 ADD UNDOFILE 'undofile.dat'
767 INITIAL_SIZE 16M
768 UNDO_BUFFER_SIZE = 2G
769 ENGINE=NDB;
770 SHOW WARNINGS;
771 SET SESSION information_schema_stats_expiry=default;