Debian Codegraph @

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

raw
1 #############################################################
2 # Author: Guangbao Ni
3 # Date: 2008-12
4 # Purpose: ndb native default support test
5 ##############################################################
6 -- source include/have_multi_ndb.inc
7 -- source suite/ndb/include/backup_restore_setup.inc
8
9 # Turn off ndb_read_backup to avoid result diffs from ndb_desc
10 --source suite/ndb/include/turn_off_ndb_read_backup.inc
11
12 # Turn off STRICT mode since test intentionally generate warnings
13 # while inserting rows without default values.
14 # NOTE! one test at the end turns STRICT back on for a while
15 --source suite/ndb/include/turn_off_strict_sql_mode.inc
16 # Also turn off NO_ZERO_DATE to allow table with
17 # TIMSTAMP NO NULL to be created
18 set sql_mode=(select replace(@@sql_mode,'NO_ZERO_DATE',''));
19
20 CREATE DATABASE mysqltest;
21 USE mysqltest;
22 ###############################################################
23 # BASIC SQL STATEMENT TEST FOR NDB NATIVE DEFAULT VALUE SUPPORT
24 ###############################################################
25 # Create table with default values for some types.
26 # Create table for bit type.
27 # Test cases include:
28 # 1. Create table with default values
29 # 2. Insert statement:
30 # --Insert default values into, the default values can be inserted into table correctly
31 # --Insert record supplied by client, it can inserted correctly
32 # --Insert record (including default value and value supplied by client)
33 # 3. Update statement:
34 # --Update with primary key condition
35 # --Update with non-primary key condtion
36 # 4. Replace statement:
37 # --Replace with default values when the record isn't existed in table
38 # --Replace with part default values and part value supplied by client when the record isn't existed in table.
39 # --Replace with part default values and part value suppliced by client when the record already existed in table.
40 # 5. Delete statement:
41
42 --echo ***************************************************************
43 --echo * BASIC SQL STATEMENT TEST FOR NDB NATIVE DEFAULT VALUE SUPPORT
44 --echo ***************************************************************
45
46 CREATE TABLE t1(
47 i INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
48 j INT DEFAULT 6,
49 f FLOAT NOT NULL DEFAULT 6.6,
50 d DOUBLE DEFAULT 8.8,
51 d2 DOUBLE NOT NULL, #d2 gets 'data-type-specific default', i.e. 0.
52 ch CHAR(19) DEFAULT "aaa",
53 vch VARCHAR(19) DEFAULT "bbb",
54 b BINARY(19) DEFAULT "ccc",
55 vb VARBINARY(19) DEFAULT "ddd",
56 blob1 BLOB,
57 text1 TEXT,
58 timestamp_c TIMESTAMP DEFAULT CURRENT_TIMESTAMP
59 )ENGINE=NDB PARTITION BY KEY() PARTITIONS 8;
60
61 --disable_warnings
62 INSERT INTO t1 VALUES(),();
63
64 INSERT INTO t1 VALUES(
65 10, 10, 10.0, 10.0, 10.0,
66 "nnnnn", "nnnnn", "nnnnn", "nnnnn", "nnnnn", "nnnnn",
67 "2008-11-16 08:13:32");
68 INSERT INTO t1(i, ch) VALUES(11, "mmm");
69 --replace_column 12 CURRENT_TIMESTAMP
70 SELECT i, j, f, d, d2, ch, vch, HEX(b), HEX(vb), HEX(blob1), text1, timestamp_c FROM t1 ORDER BY i;
71
72 UPDATE t1 SET ch = "xxx" WHERE i = 10;
73 --replace_column 12 CURRENT_TIMESTAMP
74 SELECT i, j, f, d, d2, ch, vch, HEX(b), HEX(vb), HEX(blob1), text1, timestamp_c FROM t1 ORDER BY i;
75
76 UPDATE t1 SET blob1 = "yyy" WHERE j = 10;
77 --replace_column 12 CURRENT_TIMESTAMP
78 SELECT i, j, f, d, d2, ch, vch, HEX(b), HEX(vb), HEX(blob1), text1, timestamp_c FROM t1 ORDER BY i;
79
80 REPLACE INTO t1(i, j, ch) VALUES(1, 1, "zzz");
81 REPLACE INTO t1(i, j, ch) VALUES(20, 20, "www");
82 --enable_warnings
83 --replace_column 12 CURRENT_TIMESTAMP
84 SELECT i, j, f, d, d2, ch, vch, HEX(b), HEX(vb), HEX(blob1), text1, timestamp_c FROM t1 ORDER BY i;
85 DELETE FROM t1 WHERE i > 9;
86 --replace_column 12 CURRENT_TIMESTAMP
87 SELECT i, j, f, d, d2, ch, vch, HEX(b), HEX(vb), HEX(blob1), text1, timestamp_c FROM t1 ORDER BY i;
88
89 #Test BIT TYPE
90 CREATE TABLE bit1(
91 pk INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
92 b1 BIT(3) DEFAULT B'111',
93 b2 BIT(9) DEFAULT B'101',
94 b3 BIT(23) DEFAULT B'110',
95 b4 BIT(37) DEFAULT B'011',
96 b5 BIT(63) DEFAULT B'101011'
97 )ENGINE=NDB PARTITION BY KEY() PARTITIONS 8;
98
99 INSERT INTO bit1 VALUES();
100 INSERT INTO bit1(b1,b4) VALUES(B'101',B'111');
101 SELECT pk,BIN(b1),BIN(b2),BIN(b3),BIN(b4),BIN(b5) FROM bit1 ORDER BY pk;
102
103 UPDATE bit1 SET b5=B'11111' WHERE pk = 1;
104 REPLACE INTO bit1(pk, b3) VALUES(2, B'1');
105 REPLACE INTO bit1(pk, b3) VALUES(6, B'101');
106 SELECT pk,BIN(b1),BIN(b2),BIN(b3),BIN(b4),BIN(b5) FROM bit1 ORDER BY pk;
107
108 DELETE FROM bit1 WHERE pk = 2;
109 SELECT pk,BIN(b1),BIN(b2),BIN(b3),BIN(b4),BIN(b5) FROM bit1 ORDER BY pk;
110
111 #############################################################
112 # ALTER TABLE WITH DEFAULT VALUES TEST
113 #############################################################
114 --echo ********************************************************
115 --echo * Alter table to add column with default value
116 --echo ********************************************************
117 ALTER TABLE t1 ADD COLUMN ch2 CHAR(30) DEFAULT "alter table";
118 --replace_column 12 CURRENT_TIMESTAMP
119 SELECT i, j, f, d, d2, ch, vch, HEX(b), HEX(vb), HEX(blob1), text1, timestamp_c, ch2 FROM t1 ORDER BY i;
120 --disable_warnings
121 INSERT INTO t1 VALUES();
122 --enable_warnings
123 --replace_column 12 CURRENT_TIMESTAMP
124 SELECT i, j, f, d, d2, ch, vch, HEX(b), HEX(vb), HEX(blob1), text1, timestamp_c, ch2 FROM t1 ORDER BY i;
125
126 --echo ********************************************************
127 --echo * Alter table with default value can fail safely
128 --echo ********************************************************
129 --disable_warnings
130 --error ER_DUP_FIELDNAME
131 ALTER TABLE t1 ADD COLUMN ch2 CHAR(30) DEFAULT "alter table";
132 --error ER_INVALID_DEFAULT
133 ALTER TABLE t1 ADD COLUMN ch3 CHAR(3) DEFAULT "alter table";
134 INSERT INTO t1 VALUES();
135 --enable_warnings
136 --replace_column 12 CURRENT_TIMESTAMP
137 SELECT i, j, f, d, d2, ch, vch, HEX(b), HEX(vb), HEX(blob1), text1, timestamp_c, ch2 FROM t1 ORDER BY i;
138
139 ##############################################################
140 # BACKUP AND RESTORE TEST FOR TABLE WITH DEFAULT VALUES
141 ##############################################################
142 #the above two tables are backuped, they can restore correctly.
143 #The default values can be inserted correctly after restored.
144 --echo ********************************************************
145 --echo * The tables with default values BACKUP and RESTORE test
146 --echo ********************************************************
147 --source include/ndb_backup.inc
148 DROP TABLE t1, bit1;
149
150 # Turn off metadata check so it doesn't intefere with table discovery
151 --connection server2
152 SET @old_ndb_metadata_check_server2 = @@global.ndb_metadata_check;
153 SET GLOBAL ndb_metadata_check = false;
154 --connection server1
155 SET @old_ndb_metadata_check_server1 = @@global.ndb_metadata_check;
156 SET GLOBAL ndb_metadata_check = false;
157
158 --echo ********************************************************
159 --echo * Begin to restore data from backup
160 --echo ********************************************************
161 --exec $NDB_RESTORE -b $the_backup_id -n 1 -A -m -r --print --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
162 --exec $NDB_RESTORE -b $the_backup_id -n 2 -A -r --print --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
163
164 --let $ndb_describe_table= t1
165 --let $ndb_describe_db= mysqltest
166 --source suite/ndb/include/ndb_describe_table.inc
167
168 --let $ndb_describe_table= bit1
169 --let $ndb_describe_db= mysqltest
170 --source suite/ndb/include/ndb_describe_table.inc
171
172 SELECT pk,BIN(b1),BIN(b2),BIN(b3),BIN(b4),BIN(b5) FROM bit1 ORDER BY pk;
173 --replace_column 12 CURRENT_TIMESTAMP
174 SELECT i, j, f, d, d2, ch, vch, HEX(b), HEX(vb), HEX(blob1), text1, timestamp_c, ch2 FROM t1 ORDER BY i;
175
176 --disable_warnings
177 INSERT INTO t1(i, ch) VALUES(99, "restore");
178 --enable_warnings
179 INSERT INTO bit1(pk, b5) VALUES(99, B'11111111');
180 SELECT pk,BIN(b1),BIN(b2),BIN(b3),BIN(b4),BIN(b5) FROM bit1 ORDER BY pk;
181 --replace_column 12 CURRENT_TIMESTAMP
182 SELECT i, j, f, d, d2, ch, vch, HEX(b), HEX(vb), HEX(blob1), text1, timestamp_c, ch2 FROM t1 ORDER BY i;
183 DROP TABLE t1, bit1;
184
185
186 --exec $NDB_RESTORE -b $the_backup_id -n 1 -A -m -r --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
187 --exec $NDB_RESTORE -b $the_backup_id -n 2 -A -r --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
188
189 --replace_column 1 MAX_VALUE 12 CURRENT_TIMESTAMP
190 SELECT i, j, f, d, d2, ch, vch, HEX(b), HEX(vb), HEX(blob1), text1, timestamp_c, ch2 FROM t1 WHERE i >= (SELECT MAX(i) FROM t1) ORDER BY i;
191
192 DROP TABLE t1, bit1;
193 DROP DATABASE mysqltest;
194
195 ###############################################################################
196 # RESTORE THE BACKUP FROM 6.3 OR 6.4, WHICH DON'T SUPPORT NATIVE DEFAULT VALUE
197 # SO DEFAULT VALUES AREN'T STORED IN NDBD KERNEL
198 ###############################################################################
199 --echo ******************************************************************************
200 --echo * Restore the backup from 6.3 or 6.4, which don't support native default value
201 --echo ******************************************************************************
202
203 --exec $NDB_RESTORE $NDB_RESTORE_NO_ENCR -b 1 -n 1 -m -r $NDB_SAVED_BACKUPS/before_native_default >> $NDB_TOOLS_OUTPUT
204 --exec $NDB_RESTORE $NDB_RESTORE_NO_ENCR -b 1 -n 2 -r $NDB_SAVED_BACKUPS/before_native_default >> $NDB_TOOLS_OUTPUT
205
206 ####
207 # Bug# 53539 Ndb : MySQLD default values in frm embedded in backup not endian-converted
208 # Bug# 53818 Default values in .frm file not byte-order-independent
209 # Due to this, on big-endian platforms the backup file restored above
210 # has corrupt values for the endian-sensitive defaults (int, float, double)
211 # Until this is fixed we cannot robustly examine the MySQL schema.
212 # Workaround is to offline-alter the defaults to what they should be.
213 #
214
215 # Show that restored tables have no native defaults
216 --let $ndb_describe_table= t1
217 --source suite/ndb/include/ndb_describe_table.inc
218
219 --let $ndb_describe_table= bit1
220 --source suite/ndb/include/ndb_describe_table.inc
221
222 USE test;
223
224 # SHOW CREATE TABLE t1; # Disabled til bug#53539 fixed as it show junk on big-endian
225 --disable_warnings
226 SHOW CREATE TABLE bit1;
227 --replace_column 12 CURRENT_TIMESTAMP
228 --enable_warnings
229 # The `t1` table is not expected to be usable. It contains old temporal
230 # types which have been removed in 8.0. The upgrade of the table
231 # metadata is not supported
232 --disable_query_log
233 call mtr.add_suppression("Table upgrade required");
234 --enable_query_log
235 --error ER_NO_SUCH_TABLE
236 SELECT i, j, f, d, d2, ch, vch, HEX(b), HEX(vb), HEX(blob1), text1, timestamp_c FROM t1 ORDER BY i;
237 SHOW WARNINGS;
238 SELECT pk,BIN(b1),BIN(b2),BIN(b3),BIN(b4),BIN(b5) FROM bit1 ORDER BY pk;
239
240 # Show that table with MySQL, but no native defaults is still handled
241 # correctly.
242 # (This works as bit defaults have no endian problems)
243 INSERT INTO bit1 VALUES();
244 UPDATE bit1 SET b5=b'1111111' WHERE pk = 1;
245 REPLACE INTO bit1(pk, b3) VALUES(6, B'110011');
246 SELECT pk,BIN(b1),BIN(b2),BIN(b3),BIN(b4),BIN(b5) FROM bit1 ORDER BY pk;
247
248 --exec $NDB_DROP_TABLE -d test t1 >> $NDB_TOOLS_OUTPUT
249 DROP TABLE bit1;
250 --echo *************************************************************
251 --echo * Test adding a unique index to a column with a default value
252 --echo *************************************************************
253 CREATE TABLE t2(
254 i INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
255 j INT DEFAULT 6,
256 f FLOAT NOT NULL DEFAULT 6.6,
257 d DOUBLE DEFAULT 8.8,
258 UNIQUE INDEX t2_unique_index(j)
259 )ENGINE=NDB PARTITION BY KEY() PARTITIONS 8;
260 INSERT INTO t2 VALUES();
261 --error ER_DUP_ENTRY
262 INSERT INTO t2 VALUES();
263 INSERT INTO t2 VALUES(10, 10, 10.0, 10.0);
264 SELECT * FROM t2 ORDER BY i;
265
266 --echo *************************************************************
267 --echo * Test offline alter of default values
268 --echo *************************************************************
269 ALTER TABLE t2 MODIFY COLUMN j INT DEFAULT 666;
270
271 --let $ndb_describe_table= t2
272 --source suite/ndb/include/ndb_describe_table.inc
273
274 INSERT INTO t2 VALUES();
275 SELECT * FROM t2 ORDER BY i;
276
277 --echo *************************************************************
278 --echo * Test that online alter of default values fails
279 --echo *************************************************************
280 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
281 ALTER TABLE t2 algorithm=inplace, MODIFY COLUMN j INT DEFAULT 888;
282
283 DROP TABLE t2;
284
285 --echo **************************************************************
286 --echo * Test not null-after-defaults example that failed previously
287 --echo **************************************************************
288
289 CREATE TABLE t1 (
290 a int primary key,
291 b int default 12,
292 c char not null
293 )ENGINE=NDB PARTITION BY KEY() PARTITIONS 8;
294 --let $ndb_describe_table=t1
295 --source suite/ndb/include/ndb_describe_table.inc
296
297
298 DROP TABLE t1;
299
300 --echo **************************************************************
301 --echo * Test mix of null, not-null, default etc..
302 --echo **************************************************************
303
304 CREATE TABLE t1 (a int primary key,
305 b int default 12,
306 c char not null,
307 d varchar(6) default 'Daniel',
308 e char(3) default 'Stu',
309 f enum('NBFry','Kebab') default 'NBFry',
310 g set('Chips','Pie','Fish') default 'Fish,Chips',
311 h enum('Pig','Lion') not null,
312 i char(2) default '66')
313 engine = ndb
314 partition by key() partitions 8;
315 --let $ndb_describe_table=t1
316 --source suite/ndb/include/ndb_describe_table.inc
317
318 DROP TABLE t1;
319
320 --echo ******************************************
321 --echo * Test binary default with null char value
322 --echo ******************************************
323
324 CREATE TABLE t1 (a int primary key,
325 b binary(10) default 0x4142430045464748494a,
326 c varbinary(100) default 0x4142430045464748494a)
327 engine = ndb
328 partition by key() partitions 8;
329
330 --disable_warnings
331 SHOW CREATE TABLE t1;
332 --enable_warnings
333
334 --let $ndb_describe_table=t1
335 --source suite/ndb/include/ndb_describe_table.inc
336
337 DROP TABLE t1;
338
339 --echo ***********************************
340 --echo * Test timestamp column weirdness
341 --echo http://dev.mysql.com/doc/refman/5.1/en/timestamp.html
342 --echo ***********************************
343
344 --echo Timestamp updated on insert + update
345 CREATE TABLE variant (a int primary key,
346 b timestamp)
347 engine = ndb
348 partition by key() partitions 8;
349 --disable_warnings
350 SHOW CREATE TABLE variant;
351 --enable_warnings
352 --let $ndb_describe_table= variant
353 --source suite/ndb/include/ndb_describe_table.inc
354 INSERT INTO variant (a) VALUES (1);
355 --replace_column 2 CURRENT_TIMESTAMP
356 SELECT * from variant;
357 DROP TABLE variant;
358
359 --echo Full syntax for update on insert + update
360 CREATE TABLE variant (a int primary key,
361 b timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)
362 engine = ndb
363 partition by key() partitions 8;
364 --disable_warnings
365 SHOW CREATE TABLE variant;
366 --enable_warnings
367 --let $ndb_describe_table= variant
368 --source suite/ndb/include/ndb_describe_table.inc
369 INSERT INTO variant (a) VALUES (1);
370 --replace_column 2 CURRENT_TIMESTAMP
371 SELECT * from variant;
372 DROP TABLE variant;
373
374 --echo Default on insert only
375 CREATE TABLE variant (a int primary key,
376 b timestamp DEFAULT CURRENT_TIMESTAMP)
377 engine = ndb
378 partition by key() partitions 8;
379 --disable_warnings
380 SHOW CREATE TABLE variant;
381 --enable_warnings
382 --let $ndb_describe_table= variant
383 --source suite/ndb/include/ndb_describe_table.inc
384 INSERT INTO variant (a) VALUES (1);
385 --replace_column 2 CURRENT_TIMESTAMP
386 SELECT * from variant;
387 DROP TABLE variant;
388
389 --echo Set on update only
390 CREATE TABLE variant (a int primary key,
391 b timestamp DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP,
392 c int)
393 engine = ndb
394 partition by key() partitions 8;
395 --disable_warnings
396 SHOW CREATE TABLE variant;
397 --enable_warnings
398 --let $ndb_describe_table= variant
399 --source suite/ndb/include/ndb_describe_table.inc
400 INSERT INTO variant (a,c) VALUES (1,1);
401 SELECT * from variant;
402 UPDATE variant SET c=2;
403 --replace_column 2 CURRENT_TIMESTAMP
404 SELECT * from variant;
405 DROP TABLE variant;
406
407 --echo No auto-set default 0
408 CREATE TABLE variant (a int primary key,
409 b timestamp DEFAULT 0)
410 engine = ndb
411 partition by key() partitions 8;
412 --disable_warnings
413 SHOW CREATE TABLE variant;
414 --enable_warnings
415 --let $ndb_describe_table= variant
416 --source suite/ndb/include/ndb_describe_table.inc
417 INSERT INTO variant (a) VALUES (1);
418 SELECT * from variant;
419 DROP TABLE variant;
420
421 --echo No auto-set default non-zero
422 CREATE TABLE variant (a int primary key,
423 b timestamp DEFAULT 19770623000001)
424 engine = ndb
425 partition by key() partitions 8;
426 --disable_warnings
427 SHOW CREATE TABLE variant;
428 --enable_warnings
429 --let $ndb_describe_table= variant
430 --source suite/ndb/include/ndb_describe_table.inc
431 INSERT INTO variant (a) VALUES (1);
432 SELECT * from variant;
433 DROP TABLE variant;
434
435 --echo Non-first timestamp default insert value
436 CREATE TABLE variant (a int primary key,
437 b timestamp DEFAULT 19770623000001,
438 c timestamp DEFAULT CURRENT_TIMESTAMP)
439 engine = ndb
440 partition by key() partitions 8;
441 --disable_warnings
442 SHOW CREATE TABLE variant;
443 --enable_warnings
444 --let $ndb_describe_table= variant
445 --source suite/ndb/include/ndb_describe_table.inc
446 INSERT INTO variant (a) VALUES (1);
447 --replace_column 3 CURRENT_TIMESTAMP
448 SELECT * from variant;
449 DROP TABLE variant;
450
451 --echo Non-first timestamp default update value
452 CREATE TABLE variant (a int primary key,
453 b timestamp DEFAULT 19770623000001,
454 c timestamp ON UPDATE CURRENT_TIMESTAMP)
455 engine = ndb
456 partition by key() partitions 8;
457 --disable_warnings
458 SHOW CREATE TABLE variant;
459 --enable_warnings
460 --let $ndb_describe_table= variant
461 --source suite/ndb/include/ndb_describe_table.inc
462 INSERT INTO variant (a) VALUES (1);
463 SELECT * from variant;
464 UPDATE variant SET b=20100603000001;
465 --replace_column 3 CURRENT_TIMESTAMP
466 SELECT * from variant;
467 DROP TABLE variant;
468
469 --echo Non-first timestamp set on insert+update
470 CREATE TABLE variant (a int primary key,
471 b timestamp DEFAULT 19770623000001,
472 c timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)
473 engine = ndb
474 partition by key() partitions 8;
475 --disable_warnings
476 SHOW CREATE TABLE variant;
477 --enable_warnings
478 --let $ndb_describe_table= variant
479 --source suite/ndb/include/ndb_describe_table.inc
480 INSERT INTO variant (a) VALUES (1);
481 --replace_column 3 CURRENT_TIMESTAMP
482 SELECT * from variant;
483 DROP TABLE variant;
484
485 --echo Nullable timestamp no default
486 CREATE TABLE variant (a int primary key,
487 b timestamp NULL)
488 engine = ndb
489 partition by key() partitions 8;
490 --disable_warnings
491 SHOW CREATE TABLE variant;
492 --enable_warnings
493 --let $ndb_describe_table= variant
494 --source suite/ndb/include/ndb_describe_table.inc
495 INSERT INTO variant (a) VALUES (1);
496 SELECT * from variant;
497 DROP TABLE variant;
498
499 --echo Nullable timestamp default zero
500 CREATE TABLE variant (a int primary key,
501 b timestamp NULL DEFAULT 0)
502 engine = ndb
503 partition by key() partitions 8;
504 --disable_warnings
505 SHOW CREATE TABLE variant;
506 --enable_warnings
507 --let $ndb_describe_table= variant
508 --source suite/ndb/include/ndb_describe_table.inc
509 INSERT INTO variant (a) VALUES (1);
510 SELECT * from variant;
511 DROP TABLE variant;
512
513 --echo Nullable timestamp default non-zero
514 CREATE TABLE variant (a int primary key,
515 b timestamp NULL DEFAULT 19770623000001)
516 engine = ndb
517 partition by key() partitions 8;
518 --disable_warnings
519 SHOW CREATE TABLE variant;
520 --enable_warnings
521 --let $ndb_describe_table= variant
522 --source suite/ndb/include/ndb_describe_table.inc
523 INSERT INTO variant (a) VALUES (1);
524 SELECT * from variant;
525 DROP TABLE variant;
526
527 --echo Nullable timestamp auto insert val
528 CREATE TABLE variant (a int primary key,
529 b timestamp NULL DEFAULT CURRENT_TIMESTAMP)
530 engine = ndb
531 partition by key() partitions 8;
532 --disable_warnings
533 SHOW CREATE TABLE variant;
534 --enable_warnings
535 --let $ndb_describe_table= variant
536 --source suite/ndb/include/ndb_describe_table.inc
537 INSERT INTO variant (a) VALUES (1);
538 --replace_column 2 CURRENT_TIMESTAMP
539 SELECT * from variant;
540 DROP TABLE variant;
541
542 --echo Nullable timestamp auto update val
543 CREATE TABLE variant (a int primary key,
544 b timestamp NULL ON UPDATE CURRENT_TIMESTAMP,
545 c int)
546 engine = ndb
547 partition by key() partitions 8;
548 --disable_warnings
549 SHOW CREATE TABLE variant;
550 --enable_warnings
551 --let $ndb_describe_table= variant
552 --source suite/ndb/include/ndb_describe_table.inc
553 INSERT INTO variant (a,c) VALUES (1,1);
554 SELECT * from variant;
555 UPDATE variant SET c=2;
556 --replace_column 2 CURRENT_TIMESTAMP
557 SELECT * from variant;
558 DROP TABLE variant;
559
560 --echo Nullable timestamp auto insert+update val
561 CREATE TABLE variant (a int primary key,
562 b timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)
563 engine = ndb
564 partition by key() partitions 8;
565 --disable_warnings
566 SHOW CREATE TABLE variant;
567 --enable_warnings
568 --let $ndb_describe_table= variant
569 --source suite/ndb/include/ndb_describe_table.inc
570 INSERT INTO variant (a) VALUES (1);
571 --replace_column 2 CURRENT_TIMESTAMP
572 SELECT * from variant;
573 DROP TABLE variant;
574
575
576 --echo *************************************************************
577 --echo * Restore data-only from old backup without native defaults *
578 --echo *************************************************************
579
580 --echo Create schema manually with differences for ndb_restore to
581 --echo deal with. See the backup (or above) for the original schema.
582 --echo - J changed from Int -> Bigint, and default changed from 6 to 6006
583 --echo requires --promote-attributes AND default ignoring
584 --echo - ch default changed from 'aaa' to 'aaaAAA', requires default ignoring
585 --echo - vch missing in DB schema, requires --exclude-missing-columns
586 --echo - timestamp_c default changed from CURRENT_TIMESTAMP to a const default (native)
587 --echo requires default ignoring
588 --echo - newOne is a new column with a default value, requires --exclude-missing-columns
589 --echo - newTwo is a new nullable column with no default value, requires --exclude-missing-columns
590
591 CREATE TABLE t1 (
592 `i` int(11) NOT NULL AUTO_INCREMENT,
593 `j` bigint(20) NOT NULL DEFAULT '6006',
594 `f` float NOT NULL DEFAULT '6.6',
595 `d` double DEFAULT '8.8',
596 `d2` double NOT NULL,
597 `ch` char(19) DEFAULT 'aaaAAA',
598 `b` binary(19) DEFAULT 'ccc\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
599 `vb` varbinary(19) DEFAULT 'ddd',
600 `blob1` blob,
601 `text1` text,
602 `timestamp_c` timestamp NOT NULL DEFAULT '2010-06-07 13:06:22',
603 `newOne` varchar(255) DEFAULT 'Comment field default',
604 `newTwo` bigint(20) DEFAULT NULL,
605 PRIMARY KEY (`i`)
606 ) ENGINE=ndbcluster AUTO_INCREMENT=18446744073709551615 DEFAULT CHARSET=latin1
607 PARTITION BY KEY() PARTITIONS 8;
608
609 # Bit1 currently unchanged
610 CREATE TABLE bit1 (
611 `pk` int(11) NOT NULL AUTO_INCREMENT,
612 `b1` bit(3) DEFAULT b'111',
613 `b2` bit(9) DEFAULT b'101',
614 `b3` bit(23) DEFAULT b'110',
615 `b4` bit(37) DEFAULT b'11',
616 `b5` bit(63) DEFAULT b'101011',
617 PRIMARY KEY (`pk`)
618 ) ENGINE=ndbcluster AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
619 PARTITION BY KEY() PARTITIONS 8;
620
621
622 --disable_warnings
623 SHOW CREATE TABLE t1;
624 --enable_warnings
625
626 --let $ndb_describe_table= t1
627 --source suite/ndb/include/ndb_describe_table.inc
628
629 --exec $NDB_RESTORE $NDB_RESTORE_NO_ENCR -b 1 -n 1 -r --promote-attributes --exclude-missing-columns $NDB_SAVED_BACKUPS/before_native_default >> $NDB_TOOLS_OUTPUT
630 --exec $NDB_RESTORE $NDB_RESTORE_NO_ENCR -b 1 -n 2 -r --promote-attributes --exclude-missing-columns $NDB_SAVED_BACKUPS/before_native_default >> $NDB_TOOLS_OUTPUT
631
632 SELECT i, j, f, d, d2, ch, HEX(b), HEX(vb), HEX(blob1), text1, timestamp_c, newOne, newTwo from t1 order by i;
633
634 drop table bit1;
635
636 --echo Now backup the current data and restore data-only to a different schema
637
638 --source include/ndb_backup.inc
639 drop table t1;
640
641 CREATE TABLE t1 (
642 `i` int(11) NOT NULL AUTO_INCREMENT,
643 `j` bigint NOT NULL DEFAULT '6',
644 `f` float NOT NULL DEFAULT '6.6',
645 `d` double DEFAULT '8.8',
646 `d2` double NOT NULL,
647 `ch` char(19) DEFAULT 'aaa',
648 `b` binary(19) DEFAULT 'ccc\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
649 `vb` varbinary(19) DEFAULT 'ddd',
650 `blob1` blob,
651 `text1` text,
652 `timestamp_c` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
653 `newOne` varchar(256) DEFAULT 'Comment field default',
654 PRIMARY KEY (`i`)
655 ) ENGINE=ndbcluster AUTO_INCREMENT=18446744073709551615 DEFAULT CHARSET=latin1;
656
657 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --promote-attributes --exclude-missing-columns $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
658 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --promote-attributes --exclude-missing-columns $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
659
660 SELECT i, j, f, d, d2, ch, HEX(b), HEX(vb), HEX(blob1), text1, timestamp_c, newOne from t1 order by i;
661
662 --echo Now backup the current data then restore data-only to a schema with different defaults and no special ndb_restore options
663
664 --source include/ndb_backup.inc
665 drop table t1;
666
667 CREATE TABLE t1 (
668 `i` int(11) NOT NULL AUTO_INCREMENT,
669 `j` bigint NOT NULL DEFAULT '20',
670 `f` float NOT NULL DEFAULT '6.66',
671 `d` double DEFAULT '8.88',
672 `d2` double NOT NULL DEFAULT '9.99',
673 `ch` char(19) DEFAULT 'aaaZZZ',
674 `b` binary(19) DEFAULT 'ccccc\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
675 `vb` varbinary(19) DEFAULT 'dddDDDddd',
676 `blob1` blob,
677 `text1` text,
678 `timestamp_c` timestamp NOT NULL DEFAULT 20100608133131,
679 `newOne` varchar(256),
680 PRIMARY KEY (`i`)
681 ) ENGINE=ndbcluster AUTO_INCREMENT=18446744073709551615 DEFAULT CHARSET=latin1;
682
683 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
684 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
685
686 SELECT i, j, f, d, d2, ch, HEX(b), HEX(vb), HEX(blob1), text1, timestamp_c, newOne from t1 order by i;
687
688 drop table t1;
689
690 --echo Bug#55121 error 839 'Illegal null attribute' from NDB for fields with default value
691 --echo Ensure that Ndb handler doesn't expect native defaults for Blobs.
692
693 set @save_sql_mode = @@session.sql_mode;
694 set sql_mode=STRICT_TRANS_TABLES;
695 --error ER_BLOB_CANT_HAVE_DEFAULT
696 CREATE TABLE t1 (
697 fid smallint(6) unsigned NOT NULL DEFAULT '0',
698 f01 text NOT NULL,
699 f02 varchar(255) NOT NULL DEFAULT '',
700 f03 text NOT NULL DEFAULT '',
701 PRIMARY KEY (fid)
702 ) engine=ndb;
703 set sql_mode=@save_sql_mode;
704
705 --disable_warnings
706 CREATE TABLE t1 (
707 fid smallint(6) unsigned NOT NULL DEFAULT '0',
708 f01 text NOT NULL,
709 f02 varchar(255) NOT NULL DEFAULT '',
710 f03 text NOT NULL DEFAULT '',
711 PRIMARY KEY (fid)
712 ) engine=ndb;
713 --enable_warnings
714
715 show create table t1;
716
717 insert into t1(fid) value(100);
718
719 select fid, isnull(f01), isnull(f02), isnull(f03) from t1;
720
721 drop table t1;
722
723 --source suite/ndb/include/restore_sql_mode_after_turn_off_strict.inc
724
725 --source suite/ndb/include/restore_ndb_read_backup.inc
726 --source suite/ndb/include/backup_restore_cleanup.inc
727 --remove_file $NDB_TOOLS_OUTPUT
728
729 # Reset metadata check value
730 --connection server2
731 SET GLOBAL ndb_metadata_check = @old_ndb_metadata_check_server2;
732 --connection server1
733 SET GLOBAL ndb_metadata_check = @old_ndb_metadata_check_server1;