Debian Codegraph @

mysql-test/suite/ndb/r/ndb_dd_basic.result

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