Debian Codegraph @

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

raw
1 Warnings:
2 Warning 3135 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
3 set sql_mode=(select replace(@@sql_mode,'NO_ZERO_DATE',''));
4 Warnings:
5 Warning 3135 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
6 CREATE DATABASE mysqltest;
7 USE mysqltest;
8 ***************************************************************
9 * BASIC SQL STATEMENT TEST FOR NDB NATIVE DEFAULT VALUE SUPPORT
10 ***************************************************************
11 CREATE TABLE t1(
12 i INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
13 j INT DEFAULT 6,
14 f FLOAT NOT NULL DEFAULT 6.6,
15 d DOUBLE DEFAULT 8.8,
16 d2 DOUBLE NOT NULL, #d2 gets 'data-type-specific default', i.e. 0.
17 ch CHAR(19) DEFAULT "aaa",
18 vch VARCHAR(19) DEFAULT "bbb",
19 b BINARY(19) DEFAULT "ccc",
20 vb VARBINARY(19) DEFAULT "ddd",
21 blob1 BLOB,
22 text1 TEXT,
23 timestamp_c TIMESTAMP DEFAULT CURRENT_TIMESTAMP
24 )ENGINE=NDB PARTITION BY KEY() PARTITIONS 8;
25 INSERT INTO t1 VALUES(),();
26 INSERT INTO t1 VALUES(
27 10, 10, 10.0, 10.0, 10.0,
28 "nnnnn", "nnnnn", "nnnnn", "nnnnn", "nnnnn", "nnnnn",
29 "2008-11-16 08:13:32");
30 INSERT INTO t1(i, ch) VALUES(11, "mmm");
31 SELECT i, j, f, d, d2, ch, vch, HEX(b), HEX(vb), HEX(blob1), text1, timestamp_c FROM t1 ORDER BY i;
32 i j f d d2 ch vch HEX(b) HEX(vb) HEX(blob1) text1 timestamp_c
33 1 6 6.6 8.8 0 aaa bbb 63636300000000000000000000000000000000 646464 NULL NULL CURRENT_TIMESTAMP
34 2 6 6.6 8.8 0 aaa bbb 63636300000000000000000000000000000000 646464 NULL NULL CURRENT_TIMESTAMP
35 10 10 10 10 10 nnnnn nnnnn 6E6E6E6E6E0000000000000000000000000000 6E6E6E6E6E 6E6E6E6E6E nnnnn CURRENT_TIMESTAMP
36 11 6 6.6 8.8 0 mmm bbb 63636300000000000000000000000000000000 646464 NULL NULL CURRENT_TIMESTAMP
37 UPDATE t1 SET ch = "xxx" WHERE i = 10;
38 SELECT i, j, f, d, d2, ch, vch, HEX(b), HEX(vb), HEX(blob1), text1, timestamp_c FROM t1 ORDER BY i;
39 i j f d d2 ch vch HEX(b) HEX(vb) HEX(blob1) text1 timestamp_c
40 1 6 6.6 8.8 0 aaa bbb 63636300000000000000000000000000000000 646464 NULL NULL CURRENT_TIMESTAMP
41 2 6 6.6 8.8 0 aaa bbb 63636300000000000000000000000000000000 646464 NULL NULL CURRENT_TIMESTAMP
42 10 10 10 10 10 xxx nnnnn 6E6E6E6E6E0000000000000000000000000000 6E6E6E6E6E 6E6E6E6E6E nnnnn CURRENT_TIMESTAMP
43 11 6 6.6 8.8 0 mmm bbb 63636300000000000000000000000000000000 646464 NULL NULL CURRENT_TIMESTAMP
44 UPDATE t1 SET blob1 = "yyy" WHERE j = 10;
45 SELECT i, j, f, d, d2, ch, vch, HEX(b), HEX(vb), HEX(blob1), text1, timestamp_c FROM t1 ORDER BY i;
46 i j f d d2 ch vch HEX(b) HEX(vb) HEX(blob1) text1 timestamp_c
47 1 6 6.6 8.8 0 aaa bbb 63636300000000000000000000000000000000 646464 NULL NULL CURRENT_TIMESTAMP
48 2 6 6.6 8.8 0 aaa bbb 63636300000000000000000000000000000000 646464 NULL NULL CURRENT_TIMESTAMP
49 10 10 10 10 10 xxx nnnnn 6E6E6E6E6E0000000000000000000000000000 6E6E6E6E6E 797979 nnnnn CURRENT_TIMESTAMP
50 11 6 6.6 8.8 0 mmm bbb 63636300000000000000000000000000000000 646464 NULL NULL CURRENT_TIMESTAMP
51 REPLACE INTO t1(i, j, ch) VALUES(1, 1, "zzz");
52 REPLACE INTO t1(i, j, ch) VALUES(20, 20, "www");
53 SELECT i, j, f, d, d2, ch, vch, HEX(b), HEX(vb), HEX(blob1), text1, timestamp_c FROM t1 ORDER BY i;
54 i j f d d2 ch vch HEX(b) HEX(vb) HEX(blob1) text1 timestamp_c
55 1 1 6.6 8.8 0 zzz bbb 63636300000000000000000000000000000000 646464 NULL NULL CURRENT_TIMESTAMP
56 2 6 6.6 8.8 0 aaa bbb 63636300000000000000000000000000000000 646464 NULL NULL CURRENT_TIMESTAMP
57 10 10 10 10 10 xxx nnnnn 6E6E6E6E6E0000000000000000000000000000 6E6E6E6E6E 797979 nnnnn CURRENT_TIMESTAMP
58 11 6 6.6 8.8 0 mmm bbb 63636300000000000000000000000000000000 646464 NULL NULL CURRENT_TIMESTAMP
59 20 20 6.6 8.8 0 www bbb 63636300000000000000000000000000000000 646464 NULL NULL CURRENT_TIMESTAMP
60 DELETE FROM t1 WHERE i > 9;
61 SELECT i, j, f, d, d2, ch, vch, HEX(b), HEX(vb), HEX(blob1), text1, timestamp_c FROM t1 ORDER BY i;
62 i j f d d2 ch vch HEX(b) HEX(vb) HEX(blob1) text1 timestamp_c
63 1 1 6.6 8.8 0 zzz bbb 63636300000000000000000000000000000000 646464 NULL NULL CURRENT_TIMESTAMP
64 2 6 6.6 8.8 0 aaa bbb 63636300000000000000000000000000000000 646464 NULL NULL CURRENT_TIMESTAMP
65 CREATE TABLE bit1(
66 pk INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
67 b1 BIT(3) DEFAULT B'111',
68 b2 BIT(9) DEFAULT B'101',
69 b3 BIT(23) DEFAULT B'110',
70 b4 BIT(37) DEFAULT B'011',
71 b5 BIT(63) DEFAULT B'101011'
72 )ENGINE=NDB PARTITION BY KEY() PARTITIONS 8;
73 INSERT INTO bit1 VALUES();
74 INSERT INTO bit1(b1,b4) VALUES(B'101',B'111');
75 SELECT pk,BIN(b1),BIN(b2),BIN(b3),BIN(b4),BIN(b5) FROM bit1 ORDER BY pk;
76 pk BIN(b1) BIN(b2) BIN(b3) BIN(b4) BIN(b5)
77 1 111 101 110 11 101011
78 2 101 101 110 111 101011
79 UPDATE bit1 SET b5=B'11111' WHERE pk = 1;
80 REPLACE INTO bit1(pk, b3) VALUES(2, B'1');
81 REPLACE INTO bit1(pk, b3) VALUES(6, B'101');
82 SELECT pk,BIN(b1),BIN(b2),BIN(b3),BIN(b4),BIN(b5) FROM bit1 ORDER BY pk;
83 pk BIN(b1) BIN(b2) BIN(b3) BIN(b4) BIN(b5)
84 1 111 101 110 11 11111
85 2 111 101 1 11 101011
86 6 111 101 101 11 101011
87 DELETE FROM bit1 WHERE pk = 2;
88 SELECT pk,BIN(b1),BIN(b2),BIN(b3),BIN(b4),BIN(b5) FROM bit1 ORDER BY pk;
89 pk BIN(b1) BIN(b2) BIN(b3) BIN(b4) BIN(b5)
90 1 111 101 110 11 11111
91 6 111 101 101 11 101011
92 ********************************************************
93 * Alter table to add column with default value
94 ********************************************************
95 ALTER TABLE t1 ADD COLUMN ch2 CHAR(30) DEFAULT "alter table";
96 SELECT i, j, f, d, d2, ch, vch, HEX(b), HEX(vb), HEX(blob1), text1, timestamp_c, ch2 FROM t1 ORDER BY i;
97 i j f d d2 ch vch HEX(b) HEX(vb) HEX(blob1) text1 timestamp_c ch2
98 1 1 6.6 8.8 0 zzz bbb 63636300000000000000000000000000000000 646464 NULL NULL CURRENT_TIMESTAMP alter table
99 2 6 6.6 8.8 0 aaa bbb 63636300000000000000000000000000000000 646464 NULL NULL CURRENT_TIMESTAMP alter table
100 INSERT INTO t1 VALUES();
101 SELECT i, j, f, d, d2, ch, vch, HEX(b), HEX(vb), HEX(blob1), text1, timestamp_c, ch2 FROM t1 ORDER BY i;
102 i j f d d2 ch vch HEX(b) HEX(vb) HEX(blob1) text1 timestamp_c ch2
103 1 1 6.6 8.8 0 zzz bbb 63636300000000000000000000000000000000 646464 NULL NULL CURRENT_TIMESTAMP alter table
104 2 6 6.6 8.8 0 aaa bbb 63636300000000000000000000000000000000 646464 NULL NULL CURRENT_TIMESTAMP alter table
105 21 6 6.6 8.8 0 aaa bbb 63636300000000000000000000000000000000 646464 NULL NULL CURRENT_TIMESTAMP alter table
106 ********************************************************
107 * Alter table with default value can fail safely
108 ********************************************************
109 ALTER TABLE t1 ADD COLUMN ch2 CHAR(30) DEFAULT "alter table";
110 ERROR 42S21: Duplicate column name 'ch2'
111 ALTER TABLE t1 ADD COLUMN ch3 CHAR(3) DEFAULT "alter table";
112 ERROR 42000: Invalid default value for 'ch3'
113 INSERT INTO t1 VALUES();
114 SELECT i, j, f, d, d2, ch, vch, HEX(b), HEX(vb), HEX(blob1), text1, timestamp_c, ch2 FROM t1 ORDER BY i;
115 i j f d d2 ch vch HEX(b) HEX(vb) HEX(blob1) text1 timestamp_c ch2
116 1 1 6.6 8.8 0 zzz bbb 63636300000000000000000000000000000000 646464 NULL NULL CURRENT_TIMESTAMP alter table
117 2 6 6.6 8.8 0 aaa bbb 63636300000000000000000000000000000000 646464 NULL NULL CURRENT_TIMESTAMP alter table
118 21 6 6.6 8.8 0 aaa bbb 63636300000000000000000000000000000000 646464 NULL NULL CURRENT_TIMESTAMP alter table
119 22 6 6.6 8.8 0 aaa bbb 63636300000000000000000000000000000000 646464 NULL NULL CURRENT_TIMESTAMP alter table
120 ********************************************************
121 * The tables with default values BACKUP and RESTORE test
122 ********************************************************
123 DROP TABLE t1, bit1;
124 SET @old_ndb_metadata_check_server2 = @@global.ndb_metadata_check;
125 SET GLOBAL ndb_metadata_check = false;
126 SET @old_ndb_metadata_check_server1 = @@global.ndb_metadata_check;
127 SET GLOBAL ndb_metadata_check = false;
128 ********************************************************
129 * Begin to restore data from backup
130 ********************************************************
131 -- t1 --
132 Version: Any
133 Fragment type: HashMapPartition
134 K Value: 6
135 Min load factor: 78
136 Max load factor: 80
137 Temporary table: no
138 Number of attributes: 13
139 Number of primary keys: 1
140 Length of frm data: XXX
141 Max Rows: 0
142 Row Checksum: 1
143 Row GCI: 1
144 SingleUserMode: 0
145 ForceVarPart: 1
146 PartitionCount: 8
147 FragmentCount: 8
148 PartitionBalance: SPECIFIC
149 ExtraRowGciBits: 0
150 ExtraRowAuthorBits: 0
151 TableStatus: Retrieved
152 Table options:
153 HashMap: DEFAULT-HASHMAP-3840-8
154 -- Attributes --
155 i Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY AUTO_INCR
156 j Int NULL AT=FIXED ST=MEMORY DEFAULT 6
157 f Float NOT NULL AT=FIXED ST=MEMORY DEFAULT 6.600000
158 d Double NULL AT=FIXED ST=MEMORY DEFAULT 8.800000
159 d2 Double NOT NULL AT=FIXED ST=MEMORY
160 ch Char(76;utf8mb4_0900_ai_ci) NULL AT=FIXED ST=MEMORY DEFAULT "aaa"
161 vch Varchar(76;utf8mb4_0900_ai_ci) NULL AT=SHORT_VAR ST=MEMORY DEFAULT "bbb"
162 b Binary(19) NULL AT=FIXED ST=MEMORY DEFAULT 0x63636300000000000000000000000000000000
163 vb Varbinary(19) NULL AT=SHORT_VAR ST=MEMORY DEFAULT 0x646464
164 blob1 Blob(256,2000,0) NULL AT=MEDIUM_VAR ST=MEMORY BV=2 BT=NDB$BLOB_XX_9
165 text1 Text(256,2000,0;utf8mb4_0900_ai_ci) NULL AT=MEDIUM_VAR ST=MEMORY BV=2 BT=NDB$BLOB_XX_10
166 timestamp_c Timestamp2(0) NULL AT=FIXED ST=MEMORY
167 ch2 Char(120;utf8mb4_0900_ai_ci) NULL AT=FIXED ST=MEMORY DEFAULT "alter table"
168 -- Indexes --
169 PRIMARY KEY(i) - UniqueHashIndex
170 PRIMARY(i) - OrderedIndex
171 -- bit1 --
172 Version: Any
173 Fragment type: HashMapPartition
174 K Value: 6
175 Min load factor: 78
176 Max load factor: 80
177 Temporary table: no
178 Number of attributes: 6
179 Number of primary keys: 1
180 Length of frm data: XXX
181 Max Rows: 0
182 Row Checksum: 1
183 Row GCI: 1
184 SingleUserMode: 0
185 ForceVarPart: 1
186 PartitionCount: 8
187 FragmentCount: 8
188 PartitionBalance: SPECIFIC
189 ExtraRowGciBits: 0
190 ExtraRowAuthorBits: 0
191 TableStatus: Retrieved
192 Table options:
193 HashMap: DEFAULT-HASHMAP-3840-8
194 -- Attributes --
195 pk Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY AUTO_INCR
196 b1 Bit(3) NULL AT=FIXED ST=MEMORY DEFAULT H'0x7
197 b2 Bit(9) NULL AT=FIXED ST=MEMORY DEFAULT H'0x5
198 b3 Bit(23) NULL AT=FIXED ST=MEMORY DEFAULT H'0x6
199 b4 Bit(37) NULL AT=FIXED ST=MEMORY DEFAULT H'0x3
200 b5 Bit(63) NULL AT=FIXED ST=MEMORY DEFAULT H'0x2B
201 -- Indexes --
202 PRIMARY KEY(pk) - UniqueHashIndex
203 PRIMARY(pk) - OrderedIndex
204 SELECT pk,BIN(b1),BIN(b2),BIN(b3),BIN(b4),BIN(b5) FROM bit1 ORDER BY pk;
205 pk BIN(b1) BIN(b2) BIN(b3) BIN(b4) BIN(b5)
206 1 111 101 110 11 11111
207 6 111 101 101 11 101011
208 SELECT i, j, f, d, d2, ch, vch, HEX(b), HEX(vb), HEX(blob1), text1, timestamp_c, ch2 FROM t1 ORDER BY i;
209 i j f d d2 ch vch HEX(b) HEX(vb) HEX(blob1) text1 timestamp_c ch2
210 1 1 6.6 8.8 0 zzz bbb 63636300000000000000000000000000000000 646464 NULL NULL CURRENT_TIMESTAMP alter table
211 2 6 6.6 8.8 0 aaa bbb 63636300000000000000000000000000000000 646464 NULL NULL CURRENT_TIMESTAMP alter table
212 21 6 6.6 8.8 0 aaa bbb 63636300000000000000000000000000000000 646464 NULL NULL CURRENT_TIMESTAMP alter table
213 22 6 6.6 8.8 0 aaa bbb 63636300000000000000000000000000000000 646464 NULL NULL CURRENT_TIMESTAMP alter table
214 INSERT INTO t1(i, ch) VALUES(99, "restore");
215 INSERT INTO bit1(pk, b5) VALUES(99, B'11111111');
216 SELECT pk,BIN(b1),BIN(b2),BIN(b3),BIN(b4),BIN(b5) FROM bit1 ORDER BY pk;
217 pk BIN(b1) BIN(b2) BIN(b3) BIN(b4) BIN(b5)
218 1 111 101 110 11 11111
219 6 111 101 101 11 101011
220 99 111 101 110 11 11111111
221 SELECT i, j, f, d, d2, ch, vch, HEX(b), HEX(vb), HEX(blob1), text1, timestamp_c, ch2 FROM t1 ORDER BY i;
222 i j f d d2 ch vch HEX(b) HEX(vb) HEX(blob1) text1 timestamp_c ch2
223 1 1 6.6 8.8 0 zzz bbb 63636300000000000000000000000000000000 646464 NULL NULL CURRENT_TIMESTAMP alter table
224 2 6 6.6 8.8 0 aaa bbb 63636300000000000000000000000000000000 646464 NULL NULL CURRENT_TIMESTAMP alter table
225 21 6 6.6 8.8 0 aaa bbb 63636300000000000000000000000000000000 646464 NULL NULL CURRENT_TIMESTAMP alter table
226 22 6 6.6 8.8 0 aaa bbb 63636300000000000000000000000000000000 646464 NULL NULL CURRENT_TIMESTAMP alter table
227 99 6 6.6 8.8 0 restore bbb 63636300000000000000000000000000000000 646464 NULL NULL CURRENT_TIMESTAMP alter table
228 DROP TABLE t1, bit1;
229 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;
230 i j f d d2 ch vch HEX(b) HEX(vb) HEX(blob1) text1 timestamp_c ch2
231 MAX_VALUE 6 6.6 8.8 0 aaa bbb 63636300000000000000000000000000000000 646464 NULL NULL CURRENT_TIMESTAMP alter table
232 DROP TABLE t1, bit1;
233 DROP DATABASE mysqltest;
234 ******************************************************************************
235 * Restore the backup from 6.3 or 6.4, which don't support native default value
236 ******************************************************************************
237 -- t1 --
238 Version: Any
239 Fragment type: DistrKeyHash
240 K Value: 6
241 Min load factor: 78
242 Max load factor: 80
243 Temporary table: no
244 Number of attributes: 12
245 Number of primary keys: 1
246 Length of frm data: XXX
247 Max Rows: 0
248 Row Checksum: 1
249 Row GCI: 1
250 SingleUserMode: 0
251 ForceVarPart: 1
252 PartitionCount: 2
253 FragmentCount: 2
254 PartitionBalance: FOR_RP_BY_LDM
255 ExtraRowGciBits: 0
256 ExtraRowAuthorBits: 0
257 TableStatus: Retrieved
258 Table options:
259 -- Attributes --
260 i Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY AUTO_INCR
261 j Int NULL AT=FIXED ST=MEMORY
262 f Float NOT NULL AT=FIXED ST=MEMORY
263 d Double NULL AT=FIXED ST=MEMORY
264 d2 Double NOT NULL AT=FIXED ST=MEMORY
265 ch Char(19;latin1_swedish_ci) NULL AT=FIXED ST=MEMORY
266 vch Varchar(19;latin1_swedish_ci) NULL AT=SHORT_VAR ST=MEMORY
267 b Binary(19) NULL AT=FIXED ST=MEMORY
268 vb Varbinary(19) NULL AT=SHORT_VAR ST=MEMORY
269 blob1 Blob(256,2000,0) NULL AT=MEDIUM_VAR ST=MEMORY BV=2 BT=NDB$BLOB_XX_9
270 text1 Text(256,2000,0;latin1_swedish_ci) NULL AT=MEDIUM_VAR ST=MEMORY BV=2 BT=NDB$BLOB_XX_10
271 timestamp_c Timestamp NOT NULL AT=FIXED ST=MEMORY
272 -- Indexes --
273 PRIMARY KEY(i) - UniqueHashIndex
274 PRIMARY(i) - OrderedIndex
275 -- bit1 --
276 Version: Any
277 Fragment type: DistrKeyHash
278 K Value: 6
279 Min load factor: 78
280 Max load factor: 80
281 Temporary table: no
282 Number of attributes: 6
283 Number of primary keys: 1
284 Length of frm data: XXX
285 Max Rows: 0
286 Row Checksum: 1
287 Row GCI: 1
288 SingleUserMode: 0
289 ForceVarPart: 1
290 PartitionCount: 2
291 FragmentCount: 2
292 PartitionBalance: FOR_RP_BY_LDM
293 ExtraRowGciBits: 0
294 ExtraRowAuthorBits: 0
295 TableStatus: Retrieved
296 Table options:
297 -- Attributes --
298 pk Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY AUTO_INCR
299 b1 Bit(3) NULL AT=FIXED ST=MEMORY
300 b2 Bit(9) NULL AT=FIXED ST=MEMORY
301 b3 Bit(23) NULL AT=FIXED ST=MEMORY
302 b4 Bit(37) NULL AT=FIXED ST=MEMORY
303 b5 Bit(63) NULL AT=FIXED ST=MEMORY
304 -- Indexes --
305 PRIMARY KEY(pk) - UniqueHashIndex
306 PRIMARY(pk) - OrderedIndex
307 USE test;
308 SHOW CREATE TABLE bit1;
309 Table Create Table
310 bit1 CREATE TABLE `bit1` (
311 `pk` int NOT NULL AUTO_INCREMENT,
312 `b1` bit(3) DEFAULT b'111',
313 `b2` bit(9) DEFAULT b'101',
314 `b3` bit(23) DEFAULT b'110',
315 `b4` bit(37) DEFAULT b'11',
316 `b5` bit(63) DEFAULT b'101011',
317 PRIMARY KEY (`pk`)
318 ) ENGINE=ndbcluster AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
319 SELECT i, j, f, d, d2, ch, vch, HEX(b), HEX(vb), HEX(blob1), text1, timestamp_c FROM t1 ORDER BY i;
320 ERROR 42S02: Table 'test.t1' doesn't exist
321 SHOW WARNINGS;
322 Level Code Message
323 Warning 10923 Table definition contains obsolete data types such as old temporal or decimal types
324 Warning 1296 Failed to discover table 't1' from NDB, could not upgrade table with extra metadata version 1
325 Error 1146 Table 'test.t1' doesn't exist
326 SELECT pk,BIN(b1),BIN(b2),BIN(b3),BIN(b4),BIN(b5) FROM bit1 ORDER BY pk;
327 pk BIN(b1) BIN(b2) BIN(b3) BIN(b4) BIN(b5)
328 1 111 101 110 11 101011
329 2 101 101 110 111 101011
330 INSERT INTO bit1 VALUES();
331 UPDATE bit1 SET b5=b'1111111' WHERE pk = 1;
332 REPLACE INTO bit1(pk, b3) VALUES(6, B'110011');
333 SELECT pk,BIN(b1),BIN(b2),BIN(b3),BIN(b4),BIN(b5) FROM bit1 ORDER BY pk;
334 pk BIN(b1) BIN(b2) BIN(b3) BIN(b4) BIN(b5)
335 1 111 101 110 11 1111111
336 2 101 101 110 111 101011
337 3 111 101 110 11 101011
338 6 111 101 110011 11 101011
339 DROP TABLE bit1;
340 *************************************************************
341 * Test adding a unique index to a column with a default value
342 *************************************************************
343 CREATE TABLE t2(
344 i INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
345 j INT DEFAULT 6,
346 f FLOAT NOT NULL DEFAULT 6.6,
347 d DOUBLE DEFAULT 8.8,
348 UNIQUE INDEX t2_unique_index(j)
349 )ENGINE=NDB PARTITION BY KEY() PARTITIONS 8;
350 INSERT INTO t2 VALUES();
351 INSERT INTO t2 VALUES();
352 ERROR 23000: Duplicate entry '6' for key 't2.t2_unique_index'
353 INSERT INTO t2 VALUES(10, 10, 10.0, 10.0);
354 SELECT * FROM t2 ORDER BY i;
355 i j f d
356 1 6 6.6 8.8
357 10 10 10 10
358 *************************************************************
359 * Test offline alter of default values
360 *************************************************************
361 ALTER TABLE t2 MODIFY COLUMN j INT DEFAULT 666;
362 -- t2 --
363 Version: Any
364 Fragment type: HashMapPartition
365 K Value: 6
366 Min load factor: 78
367 Max load factor: 80
368 Temporary table: no
369 Number of attributes: 4
370 Number of primary keys: 1
371 Length of frm data: XXX
372 Max Rows: 0
373 Row Checksum: 1
374 Row GCI: 1
375 SingleUserMode: 0
376 ForceVarPart: 1
377 PartitionCount: 8
378 FragmentCount: 8
379 PartitionBalance: SPECIFIC
380 ExtraRowGciBits: 0
381 ExtraRowAuthorBits: 0
382 TableStatus: Retrieved
383 Table options:
384 HashMap: DEFAULT-HASHMAP-3840-8
385 -- Attributes --
386 i Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY AUTO_INCR
387 j Int NULL AT=FIXED ST=MEMORY DEFAULT 666
388 f Float NOT NULL AT=FIXED ST=MEMORY DEFAULT 6.600000
389 d Double NULL AT=FIXED ST=MEMORY DEFAULT 8.800000
390 -- Indexes --
391 PRIMARY KEY(i) - UniqueHashIndex
392 PRIMARY(i) - OrderedIndex
393 t2_unique_index(j) - OrderedIndex
394 t2_unique_index$unique(j) - UniqueHashIndex
395 INSERT INTO t2 VALUES();
396 SELECT * FROM t2 ORDER BY i;
397 i j f d
398 1 6 6.6 8.8
399 10 10 10 10
400 11 666 6.6 8.8
401 *************************************************************
402 * Test that online alter of default values fails
403 *************************************************************
404 ALTER TABLE t2 algorithm=inplace, MODIFY COLUMN j INT DEFAULT 888;
405 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Altering default value is not supported. Try ALGORITHM=COPY.
406 DROP TABLE t2;
407 **************************************************************
408 * Test not null-after-defaults example that failed previously
409 **************************************************************
410 CREATE TABLE t1 (
411 a int primary key,
412 b int default 12,
413 c char not null
414 )ENGINE=NDB PARTITION BY KEY() PARTITIONS 8;
415 -- t1 --
416 Version: Any
417 Fragment type: HashMapPartition
418 K Value: 6
419 Min load factor: 78
420 Max load factor: 80
421 Temporary table: no
422 Number of attributes: 3
423 Number of primary keys: 1
424 Length of frm data: XXX
425 Max Rows: 0
426 Row Checksum: 1
427 Row GCI: 1
428 SingleUserMode: 0
429 ForceVarPart: 1
430 PartitionCount: 8
431 FragmentCount: 8
432 PartitionBalance: SPECIFIC
433 ExtraRowGciBits: 0
434 ExtraRowAuthorBits: 0
435 TableStatus: Retrieved
436 Table options:
437 HashMap: DEFAULT-HASHMAP-3840-8
438 -- Attributes --
439 a Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
440 b Int NULL AT=FIXED ST=MEMORY DEFAULT 12
441 c Char(4;utf8mb4_0900_ai_ci) NOT NULL AT=FIXED ST=MEMORY
442 -- Indexes --
443 PRIMARY KEY(a) - UniqueHashIndex
444 PRIMARY(a) - OrderedIndex
445 DROP TABLE t1;
446 **************************************************************
447 * Test mix of null, not-null, default etc..
448 **************************************************************
449 CREATE TABLE t1 (a int primary key,
450 b int default 12,
451 c char not null,
452 d varchar(6) default 'Daniel',
453 e char(3) default 'Stu',
454 f enum('NBFry','Kebab') default 'NBFry',
455 g set('Chips','Pie','Fish') default 'Fish,Chips',
456 h enum('Pig','Lion') not null,
457 i char(2) default '66')
458 engine = ndb
459 partition by key() partitions 8;
460 -- t1 --
461 Version: Any
462 Fragment type: HashMapPartition
463 K Value: 6
464 Min load factor: 78
465 Max load factor: 80
466 Temporary table: no
467 Number of attributes: 9
468 Number of primary keys: 1
469 Length of frm data: XXX
470 Max Rows: 0
471 Row Checksum: 1
472 Row GCI: 1
473 SingleUserMode: 0
474 ForceVarPart: 1
475 PartitionCount: 8
476 FragmentCount: 8
477 PartitionBalance: SPECIFIC
478 ExtraRowGciBits: 0
479 ExtraRowAuthorBits: 0
480 TableStatus: Retrieved
481 Table options:
482 HashMap: DEFAULT-HASHMAP-3840-8
483 -- Attributes --
484 a Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
485 b Int NULL AT=FIXED ST=MEMORY DEFAULT 12
486 c Char(4;utf8mb4_0900_ai_ci) NOT NULL AT=FIXED ST=MEMORY
487 d Varchar(24;utf8mb4_0900_ai_ci) NULL AT=SHORT_VAR ST=MEMORY DEFAULT "Daniel"
488 e Char(12;utf8mb4_0900_ai_ci) NULL AT=FIXED ST=MEMORY DEFAULT "Stu"
489 f Char(1;binary) NULL AT=FIXED ST=MEMORY DEFAULT "0x01"
490 g Char(1;binary) NULL AT=FIXED ST=MEMORY DEFAULT "0x05"
491 h Char(1;binary) NOT NULL AT=FIXED ST=MEMORY
492 i Char(8;utf8mb4_0900_ai_ci) NULL AT=FIXED ST=MEMORY DEFAULT "66"
493 -- Indexes --
494 PRIMARY KEY(a) - UniqueHashIndex
495 PRIMARY(a) - OrderedIndex
496 DROP TABLE t1;
497 ******************************************
498 * Test binary default with null char value
499 ******************************************
500 CREATE TABLE t1 (a int primary key,
501 b binary(10) default 0x4142430045464748494a,
502 c varbinary(100) default 0x4142430045464748494a)
503 engine = ndb
504 partition by key() partitions 8;
505 SHOW CREATE TABLE t1;
506 Table Create Table
507 t1 CREATE TABLE `t1` (
508 `a` int NOT NULL,
509 `b` binary(10) DEFAULT 'ABC\0EFGHIJ',
510 `c` varbinary(100) DEFAULT 'ABC\0EFGHIJ',
511 PRIMARY KEY (`a`)
512 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
513 /*!50100 PARTITION BY KEY ()
514 PARTITIONS 8 */
515 -- t1 --
516 Version: Any
517 Fragment type: HashMapPartition
518 K Value: 6
519 Min load factor: 78
520 Max load factor: 80
521 Temporary table: no
522 Number of attributes: 3
523 Number of primary keys: 1
524 Length of frm data: XXX
525 Max Rows: 0
526 Row Checksum: 1
527 Row GCI: 1
528 SingleUserMode: 0
529 ForceVarPart: 1
530 PartitionCount: 8
531 FragmentCount: 8
532 PartitionBalance: SPECIFIC
533 ExtraRowGciBits: 0
534 ExtraRowAuthorBits: 0
535 TableStatus: Retrieved
536 Table options:
537 HashMap: DEFAULT-HASHMAP-3840-8
538 -- Attributes --
539 a Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
540 b Binary(10) NULL AT=FIXED ST=MEMORY DEFAULT 0x4142430045464748494A
541 c Varbinary(100) NULL AT=SHORT_VAR ST=MEMORY DEFAULT 0x4142430045464748494A
542 -- Indexes --
543 PRIMARY KEY(a) - UniqueHashIndex
544 PRIMARY(a) - OrderedIndex
545 DROP TABLE t1;
546 ***********************************
547 * Test timestamp column weirdness
548 http://dev.mysql.com/doc/refman/5.1/en/timestamp.html
549 ***********************************
550 Timestamp updated on insert + update
551 CREATE TABLE variant (a int primary key,
552 b timestamp)
553 engine = ndb
554 partition by key() partitions 8;
555 SHOW CREATE TABLE variant;
556 Table Create Table
557 variant CREATE TABLE `variant` (
558 `a` int NOT NULL,
559 `b` timestamp NULL DEFAULT NULL,
560 PRIMARY KEY (`a`)
561 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
562 /*!50100 PARTITION BY KEY ()
563 PARTITIONS 8 */
564 -- variant --
565 Version: Any
566 Fragment type: HashMapPartition
567 K Value: 6
568 Min load factor: 78
569 Max load factor: 80
570 Temporary table: no
571 Number of attributes: 2
572 Number of primary keys: 1
573 Length of frm data: XXX
574 Max Rows: 0
575 Row Checksum: 1
576 Row GCI: 1
577 SingleUserMode: 0
578 ForceVarPart: 1
579 PartitionCount: 8
580 FragmentCount: 8
581 PartitionBalance: SPECIFIC
582 ExtraRowGciBits: 0
583 ExtraRowAuthorBits: 0
584 TableStatus: Retrieved
585 Table options:
586 HashMap: DEFAULT-HASHMAP-3840-8
587 -- Attributes --
588 a Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
589 b Timestamp2(0) NULL AT=FIXED ST=MEMORY
590 -- Indexes --
591 PRIMARY KEY(a) - UniqueHashIndex
592 PRIMARY(a) - OrderedIndex
593 INSERT INTO variant (a) VALUES (1);
594 SELECT * from variant;
595 a b
596 1 CURRENT_TIMESTAMP
597 DROP TABLE variant;
598 Full syntax for update on insert + update
599 CREATE TABLE variant (a int primary key,
600 b timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)
601 engine = ndb
602 partition by key() partitions 8;
603 SHOW CREATE TABLE variant;
604 Table Create Table
605 variant CREATE TABLE `variant` (
606 `a` int NOT NULL,
607 `b` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
608 PRIMARY KEY (`a`)
609 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
610 /*!50100 PARTITION BY KEY ()
611 PARTITIONS 8 */
612 -- variant --
613 Version: Any
614 Fragment type: HashMapPartition
615 K Value: 6
616 Min load factor: 78
617 Max load factor: 80
618 Temporary table: no
619 Number of attributes: 2
620 Number of primary keys: 1
621 Length of frm data: XXX
622 Max Rows: 0
623 Row Checksum: 1
624 Row GCI: 1
625 SingleUserMode: 0
626 ForceVarPart: 1
627 PartitionCount: 8
628 FragmentCount: 8
629 PartitionBalance: SPECIFIC
630 ExtraRowGciBits: 0
631 ExtraRowAuthorBits: 0
632 TableStatus: Retrieved
633 Table options:
634 HashMap: DEFAULT-HASHMAP-3840-8
635 -- Attributes --
636 a Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
637 b Timestamp2(0) NULL AT=FIXED ST=MEMORY
638 -- Indexes --
639 PRIMARY KEY(a) - UniqueHashIndex
640 PRIMARY(a) - OrderedIndex
641 INSERT INTO variant (a) VALUES (1);
642 SELECT * from variant;
643 a b
644 1 CURRENT_TIMESTAMP
645 DROP TABLE variant;
646 Default on insert only
647 CREATE TABLE variant (a int primary key,
648 b timestamp DEFAULT CURRENT_TIMESTAMP)
649 engine = ndb
650 partition by key() partitions 8;
651 SHOW CREATE TABLE variant;
652 Table Create Table
653 variant CREATE TABLE `variant` (
654 `a` int NOT NULL,
655 `b` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
656 PRIMARY KEY (`a`)
657 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
658 /*!50100 PARTITION BY KEY ()
659 PARTITIONS 8 */
660 -- variant --
661 Version: Any
662 Fragment type: HashMapPartition
663 K Value: 6
664 Min load factor: 78
665 Max load factor: 80
666 Temporary table: no
667 Number of attributes: 2
668 Number of primary keys: 1
669 Length of frm data: XXX
670 Max Rows: 0
671 Row Checksum: 1
672 Row GCI: 1
673 SingleUserMode: 0
674 ForceVarPart: 1
675 PartitionCount: 8
676 FragmentCount: 8
677 PartitionBalance: SPECIFIC
678 ExtraRowGciBits: 0
679 ExtraRowAuthorBits: 0
680 TableStatus: Retrieved
681 Table options:
682 HashMap: DEFAULT-HASHMAP-3840-8
683 -- Attributes --
684 a Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
685 b Timestamp2(0) NULL AT=FIXED ST=MEMORY
686 -- Indexes --
687 PRIMARY KEY(a) - UniqueHashIndex
688 PRIMARY(a) - OrderedIndex
689 INSERT INTO variant (a) VALUES (1);
690 SELECT * from variant;
691 a b
692 1 CURRENT_TIMESTAMP
693 DROP TABLE variant;
694 Set on update only
695 CREATE TABLE variant (a int primary key,
696 b timestamp DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP,
697 c int)
698 engine = ndb
699 partition by key() partitions 8;
700 SHOW CREATE TABLE variant;
701 Table Create Table
702 variant CREATE TABLE `variant` (
703 `a` int NOT NULL,
704 `b` timestamp NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
705 `c` int DEFAULT NULL,
706 PRIMARY KEY (`a`)
707 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
708 /*!50100 PARTITION BY KEY ()
709 PARTITIONS 8 */
710 -- variant --
711 Version: Any
712 Fragment type: HashMapPartition
713 K Value: 6
714 Min load factor: 78
715 Max load factor: 80
716 Temporary table: no
717 Number of attributes: 3
718 Number of primary keys: 1
719 Length of frm data: XXX
720 Max Rows: 0
721 Row Checksum: 1
722 Row GCI: 1
723 SingleUserMode: 0
724 ForceVarPart: 1
725 PartitionCount: 8
726 FragmentCount: 8
727 PartitionBalance: SPECIFIC
728 ExtraRowGciBits: 0
729 ExtraRowAuthorBits: 0
730 TableStatus: Retrieved
731 Table options:
732 HashMap: DEFAULT-HASHMAP-3840-8
733 -- Attributes --
734 a Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
735 b Timestamp2(0) NULL AT=FIXED ST=MEMORY DEFAULT 0
736 c Int NULL AT=FIXED ST=MEMORY
737 -- Indexes --
738 PRIMARY KEY(a) - UniqueHashIndex
739 PRIMARY(a) - OrderedIndex
740 INSERT INTO variant (a,c) VALUES (1,1);
741 SELECT * from variant;
742 a b c
743 1 0000-00-00 00:00:00 1
744 UPDATE variant SET c=2;
745 SELECT * from variant;
746 a b c
747 1 CURRENT_TIMESTAMP 2
748 DROP TABLE variant;
749 No auto-set default 0
750 CREATE TABLE variant (a int primary key,
751 b timestamp DEFAULT 0)
752 engine = ndb
753 partition by key() partitions 8;
754 SHOW CREATE TABLE variant;
755 Table Create Table
756 variant CREATE TABLE `variant` (
757 `a` int NOT NULL,
758 `b` timestamp NULL DEFAULT '0000-00-00 00:00:00',
759 PRIMARY KEY (`a`)
760 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
761 /*!50100 PARTITION BY KEY ()
762 PARTITIONS 8 */
763 -- variant --
764 Version: Any
765 Fragment type: HashMapPartition
766 K Value: 6
767 Min load factor: 78
768 Max load factor: 80
769 Temporary table: no
770 Number of attributes: 2
771 Number of primary keys: 1
772 Length of frm data: XXX
773 Max Rows: 0
774 Row Checksum: 1
775 Row GCI: 1
776 SingleUserMode: 0
777 ForceVarPart: 1
778 PartitionCount: 8
779 FragmentCount: 8
780 PartitionBalance: SPECIFIC
781 ExtraRowGciBits: 0
782 ExtraRowAuthorBits: 0
783 TableStatus: Retrieved
784 Table options:
785 HashMap: DEFAULT-HASHMAP-3840-8
786 -- Attributes --
787 a Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
788 b Timestamp2(0) NULL AT=FIXED ST=MEMORY DEFAULT 0
789 -- Indexes --
790 PRIMARY KEY(a) - UniqueHashIndex
791 PRIMARY(a) - OrderedIndex
792 INSERT INTO variant (a) VALUES (1);
793 SELECT * from variant;
794 a b
795 1 0000-00-00 00:00:00
796 DROP TABLE variant;
797 No auto-set default non-zero
798 CREATE TABLE variant (a int primary key,
799 b timestamp DEFAULT 19770623000001)
800 engine = ndb
801 partition by key() partitions 8;
802 SHOW CREATE TABLE variant;
803 Table Create Table
804 variant CREATE TABLE `variant` (
805 `a` int NOT NULL,
806 `b` timestamp NULL DEFAULT '1977-06-23 00:00:01',
807 PRIMARY KEY (`a`)
808 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
809 /*!50100 PARTITION BY KEY ()
810 PARTITIONS 8 */
811 -- variant --
812 Version: Any
813 Fragment type: HashMapPartition
814 K Value: 6
815 Min load factor: 78
816 Max load factor: 80
817 Temporary table: no
818 Number of attributes: 2
819 Number of primary keys: 1
820 Length of frm data: XXX
821 Max Rows: 0
822 Row Checksum: 1
823 Row GCI: 1
824 SingleUserMode: 0
825 ForceVarPart: 1
826 PartitionCount: 8
827 FragmentCount: 8
828 PartitionBalance: SPECIFIC
829 ExtraRowGciBits: 0
830 ExtraRowAuthorBits: 0
831 TableStatus: Retrieved
832 Table options:
833 HashMap: DEFAULT-HASHMAP-3840-8
834 -- Attributes --
835 a Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
836 b Timestamp2(0) NULL AT=FIXED ST=MEMORY DEFAULT 235861201
837 -- Indexes --
838 PRIMARY KEY(a) - UniqueHashIndex
839 PRIMARY(a) - OrderedIndex
840 INSERT INTO variant (a) VALUES (1);
841 SELECT * from variant;
842 a b
843 1 1977-06-23 00:00:01
844 DROP TABLE variant;
845 Non-first timestamp default insert value
846 CREATE TABLE variant (a int primary key,
847 b timestamp DEFAULT 19770623000001,
848 c timestamp DEFAULT CURRENT_TIMESTAMP)
849 engine = ndb
850 partition by key() partitions 8;
851 SHOW CREATE TABLE variant;
852 Table Create Table
853 variant CREATE TABLE `variant` (
854 `a` int NOT NULL,
855 `b` timestamp NULL DEFAULT '1977-06-23 00:00:01',
856 `c` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
857 PRIMARY KEY (`a`)
858 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
859 /*!50100 PARTITION BY KEY ()
860 PARTITIONS 8 */
861 -- variant --
862 Version: Any
863 Fragment type: HashMapPartition
864 K Value: 6
865 Min load factor: 78
866 Max load factor: 80
867 Temporary table: no
868 Number of attributes: 3
869 Number of primary keys: 1
870 Length of frm data: XXX
871 Max Rows: 0
872 Row Checksum: 1
873 Row GCI: 1
874 SingleUserMode: 0
875 ForceVarPart: 1
876 PartitionCount: 8
877 FragmentCount: 8
878 PartitionBalance: SPECIFIC
879 ExtraRowGciBits: 0
880 ExtraRowAuthorBits: 0
881 TableStatus: Retrieved
882 Table options:
883 HashMap: DEFAULT-HASHMAP-3840-8
884 -- Attributes --
885 a Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
886 b Timestamp2(0) NULL AT=FIXED ST=MEMORY DEFAULT 235861201
887 c Timestamp2(0) NULL AT=FIXED ST=MEMORY
888 -- Indexes --
889 PRIMARY KEY(a) - UniqueHashIndex
890 PRIMARY(a) - OrderedIndex
891 INSERT INTO variant (a) VALUES (1);
892 SELECT * from variant;
893 a b c
894 1 1977-06-23 00:00:01 CURRENT_TIMESTAMP
895 DROP TABLE variant;
896 Non-first timestamp default update value
897 CREATE TABLE variant (a int primary key,
898 b timestamp DEFAULT 19770623000001,
899 c timestamp ON UPDATE CURRENT_TIMESTAMP)
900 engine = ndb
901 partition by key() partitions 8;
902 SHOW CREATE TABLE variant;
903 Table Create Table
904 variant CREATE TABLE `variant` (
905 `a` int NOT NULL,
906 `b` timestamp NULL DEFAULT '1977-06-23 00:00:01',
907 `c` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
908 PRIMARY KEY (`a`)
909 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
910 /*!50100 PARTITION BY KEY ()
911 PARTITIONS 8 */
912 -- variant --
913 Version: Any
914 Fragment type: HashMapPartition
915 K Value: 6
916 Min load factor: 78
917 Max load factor: 80
918 Temporary table: no
919 Number of attributes: 3
920 Number of primary keys: 1
921 Length of frm data: XXX
922 Max Rows: 0
923 Row Checksum: 1
924 Row GCI: 1
925 SingleUserMode: 0
926 ForceVarPart: 1
927 PartitionCount: 8
928 FragmentCount: 8
929 PartitionBalance: SPECIFIC
930 ExtraRowGciBits: 0
931 ExtraRowAuthorBits: 0
932 TableStatus: Retrieved
933 Table options:
934 HashMap: DEFAULT-HASHMAP-3840-8
935 -- Attributes --
936 a Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
937 b Timestamp2(0) NULL AT=FIXED ST=MEMORY DEFAULT 235861201
938 c Timestamp2(0) NULL AT=FIXED ST=MEMORY
939 -- Indexes --
940 PRIMARY KEY(a) - UniqueHashIndex
941 PRIMARY(a) - OrderedIndex
942 INSERT INTO variant (a) VALUES (1);
943 SELECT * from variant;
944 a b c
945 1 1977-06-23 00:00:01 NULL
946 UPDATE variant SET b=20100603000001;
947 SELECT * from variant;
948 a b c
949 1 2010-06-03 00:00:01 CURRENT_TIMESTAMP
950 DROP TABLE variant;
951 Non-first timestamp set on insert+update
952 CREATE TABLE variant (a int primary key,
953 b timestamp DEFAULT 19770623000001,
954 c timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)
955 engine = ndb
956 partition by key() partitions 8;
957 SHOW CREATE TABLE variant;
958 Table Create Table
959 variant CREATE TABLE `variant` (
960 `a` int NOT NULL,
961 `b` timestamp NULL DEFAULT '1977-06-23 00:00:01',
962 `c` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
963 PRIMARY KEY (`a`)
964 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
965 /*!50100 PARTITION BY KEY ()
966 PARTITIONS 8 */
967 -- variant --
968 Version: Any
969 Fragment type: HashMapPartition
970 K Value: 6
971 Min load factor: 78
972 Max load factor: 80
973 Temporary table: no
974 Number of attributes: 3
975 Number of primary keys: 1
976 Length of frm data: XXX
977 Max Rows: 0
978 Row Checksum: 1
979 Row GCI: 1
980 SingleUserMode: 0
981 ForceVarPart: 1
982 PartitionCount: 8
983 FragmentCount: 8
984 PartitionBalance: SPECIFIC
985 ExtraRowGciBits: 0
986 ExtraRowAuthorBits: 0
987 TableStatus: Retrieved
988 Table options:
989 HashMap: DEFAULT-HASHMAP-3840-8
990 -- Attributes --
991 a Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
992 b Timestamp2(0) NULL AT=FIXED ST=MEMORY DEFAULT 235861201
993 c Timestamp2(0) NULL AT=FIXED ST=MEMORY
994 -- Indexes --
995 PRIMARY KEY(a) - UniqueHashIndex
996 PRIMARY(a) - OrderedIndex
997 INSERT INTO variant (a) VALUES (1);
998 SELECT * from variant;
999 a b c
1000 1 1977-06-23 00:00:01 CURRENT_TIMESTAMP
1001 DROP TABLE variant;
1002 Nullable timestamp no default
1003 CREATE TABLE variant (a int primary key,
1004 b timestamp NULL)
1005 engine = ndb
1006 partition by key() partitions 8;
1007 SHOW CREATE TABLE variant;
1008 Table Create Table
1009 variant CREATE TABLE `variant` (
1010 `a` int NOT NULL,
1011 `b` timestamp NULL DEFAULT NULL,
1012 PRIMARY KEY (`a`)
1013 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1014 /*!50100 PARTITION BY KEY ()
1015 PARTITIONS 8 */
1016 -- variant --
1017 Version: Any
1018 Fragment type: HashMapPartition
1019 K Value: 6
1020 Min load factor: 78
1021 Max load factor: 80
1022 Temporary table: no
1023 Number of attributes: 2
1024 Number of primary keys: 1
1025 Length of frm data: XXX
1026 Max Rows: 0
1027 Row Checksum: 1
1028 Row GCI: 1
1029 SingleUserMode: 0
1030 ForceVarPart: 1
1031 PartitionCount: 8
1032 FragmentCount: 8
1033 PartitionBalance: SPECIFIC
1034 ExtraRowGciBits: 0
1035 ExtraRowAuthorBits: 0
1036 TableStatus: Retrieved
1037 Table options:
1038 HashMap: DEFAULT-HASHMAP-3840-8
1039 -- Attributes --
1040 a Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
1041 b Timestamp2(0) NULL AT=FIXED ST=MEMORY
1042 -- Indexes --
1043 PRIMARY KEY(a) - UniqueHashIndex
1044 PRIMARY(a) - OrderedIndex
1045 INSERT INTO variant (a) VALUES (1);
1046 SELECT * from variant;
1047 a b
1048 1 NULL
1049 DROP TABLE variant;
1050 Nullable timestamp default zero
1051 CREATE TABLE variant (a int primary key,
1052 b timestamp NULL DEFAULT 0)
1053 engine = ndb
1054 partition by key() partitions 8;
1055 SHOW CREATE TABLE variant;
1056 Table Create Table
1057 variant CREATE TABLE `variant` (
1058 `a` int NOT NULL,
1059 `b` timestamp NULL DEFAULT '0000-00-00 00:00:00',
1060 PRIMARY KEY (`a`)
1061 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1062 /*!50100 PARTITION BY KEY ()
1063 PARTITIONS 8 */
1064 -- variant --
1065 Version: Any
1066 Fragment type: HashMapPartition
1067 K Value: 6
1068 Min load factor: 78
1069 Max load factor: 80
1070 Temporary table: no
1071 Number of attributes: 2
1072 Number of primary keys: 1
1073 Length of frm data: XXX
1074 Max Rows: 0
1075 Row Checksum: 1
1076 Row GCI: 1
1077 SingleUserMode: 0
1078 ForceVarPart: 1
1079 PartitionCount: 8
1080 FragmentCount: 8
1081 PartitionBalance: SPECIFIC
1082 ExtraRowGciBits: 0
1083 ExtraRowAuthorBits: 0
1084 TableStatus: Retrieved
1085 Table options:
1086 HashMap: DEFAULT-HASHMAP-3840-8
1087 -- Attributes --
1088 a Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
1089 b Timestamp2(0) NULL AT=FIXED ST=MEMORY DEFAULT 0
1090 -- Indexes --
1091 PRIMARY KEY(a) - UniqueHashIndex
1092 PRIMARY(a) - OrderedIndex
1093 INSERT INTO variant (a) VALUES (1);
1094 SELECT * from variant;
1095 a b
1096 1 0000-00-00 00:00:00
1097 DROP TABLE variant;
1098 Nullable timestamp default non-zero
1099 CREATE TABLE variant (a int primary key,
1100 b timestamp NULL DEFAULT 19770623000001)
1101 engine = ndb
1102 partition by key() partitions 8;
1103 SHOW CREATE TABLE variant;
1104 Table Create Table
1105 variant CREATE TABLE `variant` (
1106 `a` int NOT NULL,
1107 `b` timestamp NULL DEFAULT '1977-06-23 00:00:01',
1108 PRIMARY KEY (`a`)
1109 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1110 /*!50100 PARTITION BY KEY ()
1111 PARTITIONS 8 */
1112 -- variant --
1113 Version: Any
1114 Fragment type: HashMapPartition
1115 K Value: 6
1116 Min load factor: 78
1117 Max load factor: 80
1118 Temporary table: no
1119 Number of attributes: 2
1120 Number of primary keys: 1
1121 Length of frm data: XXX
1122 Max Rows: 0
1123 Row Checksum: 1
1124 Row GCI: 1
1125 SingleUserMode: 0
1126 ForceVarPart: 1
1127 PartitionCount: 8
1128 FragmentCount: 8
1129 PartitionBalance: SPECIFIC
1130 ExtraRowGciBits: 0
1131 ExtraRowAuthorBits: 0
1132 TableStatus: Retrieved
1133 Table options:
1134 HashMap: DEFAULT-HASHMAP-3840-8
1135 -- Attributes --
1136 a Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
1137 b Timestamp2(0) NULL AT=FIXED ST=MEMORY DEFAULT 235861201
1138 -- Indexes --
1139 PRIMARY KEY(a) - UniqueHashIndex
1140 PRIMARY(a) - OrderedIndex
1141 INSERT INTO variant (a) VALUES (1);
1142 SELECT * from variant;
1143 a b
1144 1 1977-06-23 00:00:01
1145 DROP TABLE variant;
1146 Nullable timestamp auto insert val
1147 CREATE TABLE variant (a int primary key,
1148 b timestamp NULL DEFAULT CURRENT_TIMESTAMP)
1149 engine = ndb
1150 partition by key() partitions 8;
1151 SHOW CREATE TABLE variant;
1152 Table Create Table
1153 variant CREATE TABLE `variant` (
1154 `a` int NOT NULL,
1155 `b` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
1156 PRIMARY KEY (`a`)
1157 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1158 /*!50100 PARTITION BY KEY ()
1159 PARTITIONS 8 */
1160 -- variant --
1161 Version: Any
1162 Fragment type: HashMapPartition
1163 K Value: 6
1164 Min load factor: 78
1165 Max load factor: 80
1166 Temporary table: no
1167 Number of attributes: 2
1168 Number of primary keys: 1
1169 Length of frm data: XXX
1170 Max Rows: 0
1171 Row Checksum: 1
1172 Row GCI: 1
1173 SingleUserMode: 0
1174 ForceVarPart: 1
1175 PartitionCount: 8
1176 FragmentCount: 8
1177 PartitionBalance: SPECIFIC
1178 ExtraRowGciBits: 0
1179 ExtraRowAuthorBits: 0
1180 TableStatus: Retrieved
1181 Table options:
1182 HashMap: DEFAULT-HASHMAP-3840-8
1183 -- Attributes --
1184 a Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
1185 b Timestamp2(0) NULL AT=FIXED ST=MEMORY
1186 -- Indexes --
1187 PRIMARY KEY(a) - UniqueHashIndex
1188 PRIMARY(a) - OrderedIndex
1189 INSERT INTO variant (a) VALUES (1);
1190 SELECT * from variant;
1191 a b
1192 1 CURRENT_TIMESTAMP
1193 DROP TABLE variant;
1194 Nullable timestamp auto update val
1195 CREATE TABLE variant (a int primary key,
1196 b timestamp NULL ON UPDATE CURRENT_TIMESTAMP,
1197 c int)
1198 engine = ndb
1199 partition by key() partitions 8;
1200 SHOW CREATE TABLE variant;
1201 Table Create Table
1202 variant CREATE TABLE `variant` (
1203 `a` int NOT NULL,
1204 `b` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
1205 `c` int DEFAULT NULL,
1206 PRIMARY KEY (`a`)
1207 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1208 /*!50100 PARTITION BY KEY ()
1209 PARTITIONS 8 */
1210 -- variant --
1211 Version: Any
1212 Fragment type: HashMapPartition
1213 K Value: 6
1214 Min load factor: 78
1215 Max load factor: 80
1216 Temporary table: no
1217 Number of attributes: 3
1218 Number of primary keys: 1
1219 Length of frm data: XXX
1220 Max Rows: 0
1221 Row Checksum: 1
1222 Row GCI: 1
1223 SingleUserMode: 0
1224 ForceVarPart: 1
1225 PartitionCount: 8
1226 FragmentCount: 8
1227 PartitionBalance: SPECIFIC
1228 ExtraRowGciBits: 0
1229 ExtraRowAuthorBits: 0
1230 TableStatus: Retrieved
1231 Table options:
1232 HashMap: DEFAULT-HASHMAP-3840-8
1233 -- Attributes --
1234 a Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
1235 b Timestamp2(0) NULL AT=FIXED ST=MEMORY
1236 c Int NULL AT=FIXED ST=MEMORY
1237 -- Indexes --
1238 PRIMARY KEY(a) - UniqueHashIndex
1239 PRIMARY(a) - OrderedIndex
1240 INSERT INTO variant (a,c) VALUES (1,1);
1241 SELECT * from variant;
1242 a b c
1243 1 NULL 1
1244 UPDATE variant SET c=2;
1245 SELECT * from variant;
1246 a b c
1247 1 CURRENT_TIMESTAMP 2
1248 DROP TABLE variant;
1249 Nullable timestamp auto insert+update val
1250 CREATE TABLE variant (a int primary key,
1251 b timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)
1252 engine = ndb
1253 partition by key() partitions 8;
1254 SHOW CREATE TABLE variant;
1255 Table Create Table
1256 variant CREATE TABLE `variant` (
1257 `a` int NOT NULL,
1258 `b` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1259 PRIMARY KEY (`a`)
1260 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1261 /*!50100 PARTITION BY KEY ()
1262 PARTITIONS 8 */
1263 -- variant --
1264 Version: Any
1265 Fragment type: HashMapPartition
1266 K Value: 6
1267 Min load factor: 78
1268 Max load factor: 80
1269 Temporary table: no
1270 Number of attributes: 2
1271 Number of primary keys: 1
1272 Length of frm data: XXX
1273 Max Rows: 0
1274 Row Checksum: 1
1275 Row GCI: 1
1276 SingleUserMode: 0
1277 ForceVarPart: 1
1278 PartitionCount: 8
1279 FragmentCount: 8
1280 PartitionBalance: SPECIFIC
1281 ExtraRowGciBits: 0
1282 ExtraRowAuthorBits: 0
1283 TableStatus: Retrieved
1284 Table options:
1285 HashMap: DEFAULT-HASHMAP-3840-8
1286 -- Attributes --
1287 a Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
1288 b Timestamp2(0) NULL AT=FIXED ST=MEMORY
1289 -- Indexes --
1290 PRIMARY KEY(a) - UniqueHashIndex
1291 PRIMARY(a) - OrderedIndex
1292 INSERT INTO variant (a) VALUES (1);
1293 SELECT * from variant;
1294 a b
1295 1 CURRENT_TIMESTAMP
1296 DROP TABLE variant;
1297 *************************************************************
1298 * Restore data-only from old backup without native defaults *
1299 *************************************************************
1300 Create schema manually with differences for ndb_restore to
1301 deal with. See the backup (or above) for the original schema.
1302 - J changed from Int -> Bigint, and default changed from 6 to 6006
1303 requires --promote-attributes AND default ignoring
1304 - ch default changed from 'aaa' to 'aaaAAA', requires default ignoring
1305 - vch missing in DB schema, requires --exclude-missing-columns
1306 - timestamp_c default changed from CURRENT_TIMESTAMP to a const default (native)
1307 requires default ignoring
1308 - newOne is a new column with a default value, requires --exclude-missing-columns
1309 - newTwo is a new nullable column with no default value, requires --exclude-missing-columns
1310 CREATE TABLE t1 (
1311 `i` int(11) NOT NULL AUTO_INCREMENT,
1312 `j` bigint(20) NOT NULL DEFAULT '6006',
1313 `f` float NOT NULL DEFAULT '6.6',
1314 `d` double DEFAULT '8.8',
1315 `d2` double NOT NULL,
1316 `ch` char(19) DEFAULT 'aaaAAA',
1317 `b` binary(19) DEFAULT 'ccc\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
1318 `vb` varbinary(19) DEFAULT 'ddd',
1319 `blob1` blob,
1320 `text1` text,
1321 `timestamp_c` timestamp NOT NULL DEFAULT '2010-06-07 13:06:22',
1322 `newOne` varchar(255) DEFAULT 'Comment field default',
1323 `newTwo` bigint(20) DEFAULT NULL,
1324 PRIMARY KEY (`i`)
1325 ) ENGINE=ndbcluster AUTO_INCREMENT=18446744073709551615 DEFAULT CHARSET=latin1
1326 PARTITION BY KEY() PARTITIONS 8;
1327 Warnings:
1328 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1329 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1330 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1331 CREATE TABLE bit1 (
1332 `pk` int(11) NOT NULL AUTO_INCREMENT,
1333 `b1` bit(3) DEFAULT b'111',
1334 `b2` bit(9) DEFAULT b'101',
1335 `b3` bit(23) DEFAULT b'110',
1336 `b4` bit(37) DEFAULT b'11',
1337 `b5` bit(63) DEFAULT b'101011',
1338 PRIMARY KEY (`pk`)
1339 ) ENGINE=ndbcluster AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
1340 PARTITION BY KEY() PARTITIONS 8;
1341 Warnings:
1342 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1343 SHOW CREATE TABLE t1;
1344 Table Create Table
1345 t1 CREATE TABLE `t1` (
1346 `i` int NOT NULL AUTO_INCREMENT,
1347 `j` bigint NOT NULL DEFAULT '6006',
1348 `f` float NOT NULL DEFAULT '6.6',
1349 `d` double DEFAULT '8.8',
1350 `d2` double NOT NULL,
1351 `ch` char(19) DEFAULT 'aaaAAA',
1352 `b` binary(19) DEFAULT 'ccc\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
1353 `vb` varbinary(19) DEFAULT 'ddd',
1354 `blob1` blob,
1355 `text1` text,
1356 `timestamp_c` timestamp NOT NULL DEFAULT '2010-06-07 13:06:22',
1357 `newOne` varchar(255) DEFAULT 'Comment field default',
1358 `newTwo` bigint DEFAULT NULL,
1359 PRIMARY KEY (`i`)
1360 ) ENGINE=ndbcluster AUTO_INCREMENT=18446744073709551615 DEFAULT CHARSET=latin1
1361 /*!50100 PARTITION BY KEY ()
1362 PARTITIONS 8 */
1363 -- t1 --
1364 Version: Any
1365 Fragment type: HashMapPartition
1366 K Value: 6
1367 Min load factor: 78
1368 Max load factor: 80
1369 Temporary table: no
1370 Number of attributes: 13
1371 Number of primary keys: 1
1372 Length of frm data: XXX
1373 Max Rows: 0
1374 Row Checksum: 1
1375 Row GCI: 1
1376 SingleUserMode: 0
1377 ForceVarPart: 1
1378 PartitionCount: 8
1379 FragmentCount: 8
1380 PartitionBalance: SPECIFIC
1381 ExtraRowGciBits: 0
1382 ExtraRowAuthorBits: 0
1383 TableStatus: Retrieved
1384 Table options:
1385 HashMap: DEFAULT-HASHMAP-3840-8
1386 -- Attributes --
1387 i Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY AUTO_INCR
1388 j Bigint NOT NULL AT=FIXED ST=MEMORY DEFAULT 6006
1389 f Float NOT NULL AT=FIXED ST=MEMORY DEFAULT 6.600000
1390 d Double NULL AT=FIXED ST=MEMORY DEFAULT 8.800000
1391 d2 Double NOT NULL AT=FIXED ST=MEMORY
1392 ch Char(19;latin1_swedish_ci) NULL AT=FIXED ST=MEMORY DEFAULT "aaaAAA"
1393 b Binary(19) NULL AT=FIXED ST=MEMORY DEFAULT 0x63636300000000000000000000000000000000
1394 vb Varbinary(19) NULL AT=SHORT_VAR ST=MEMORY DEFAULT 0x646464
1395 blob1 Blob(256,2000,0) NULL AT=MEDIUM_VAR ST=MEMORY BV=2 BT=NDB$BLOB_XX_8
1396 text1 Text(256,2000,0;latin1_swedish_ci) NULL AT=MEDIUM_VAR ST=MEMORY BV=2 BT=NDB$BLOB_XX_9
1397 timestamp_c Timestamp2(0) NOT NULL AT=FIXED ST=MEMORY DEFAULT 1275905182
1398 newOne Varchar(255;latin1_swedish_ci) NULL AT=SHORT_VAR ST=MEMORY DEFAULT "Comment field default"
1399 newTwo Bigint NULL AT=FIXED ST=MEMORY
1400 -- Indexes --
1401 PRIMARY KEY(i) - UniqueHashIndex
1402 PRIMARY(i) - OrderedIndex
1403 SELECT i, j, f, d, d2, ch, HEX(b), HEX(vb), HEX(blob1), text1, timestamp_c, newOne, newTwo from t1 order by i;
1404 i j f d d2 ch HEX(b) HEX(vb) HEX(blob1) text1 timestamp_c newOne newTwo
1405 1 6 6.6 8.8 0 aaa 63636300000000000000000000000000000000 646464 NULL NULL 2008-12-18 17:41:02 Comment field default NULL
1406 2 6 6.6 8.8 0 aaa 63636300000000000000000000000000000000 646464 NULL NULL 2008-12-18 17:41:02 Comment field default NULL
1407 10 10 10 10 10 nnnnn 6E6E6E6E6E0000000000000000000000000000 6E6E6E6E6E 6E6E6E6E6E nnnnn 2008-11-16 08:13:32 Comment field default NULL
1408 11 6 6.6 8.8 0 mmm 63636300000000000000000000000000000000 646464 NULL NULL 2008-12-18 17:41:20 Comment field default NULL
1409 drop table bit1;
1410 Now backup the current data and restore data-only to a different schema
1411 drop table t1;
1412 CREATE TABLE t1 (
1413 `i` int(11) NOT NULL AUTO_INCREMENT,
1414 `j` bigint NOT NULL DEFAULT '6',
1415 `f` float NOT NULL DEFAULT '6.6',
1416 `d` double DEFAULT '8.8',
1417 `d2` double NOT NULL,
1418 `ch` char(19) DEFAULT 'aaa',
1419 `b` binary(19) DEFAULT 'ccc\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
1420 `vb` varbinary(19) DEFAULT 'ddd',
1421 `blob1` blob,
1422 `text1` text,
1423 `timestamp_c` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
1424 `newOne` varchar(256) DEFAULT 'Comment field default',
1425 PRIMARY KEY (`i`)
1426 ) ENGINE=ndbcluster AUTO_INCREMENT=18446744073709551615 DEFAULT CHARSET=latin1;
1427 Warnings:
1428 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1429 SELECT i, j, f, d, d2, ch, HEX(b), HEX(vb), HEX(blob1), text1, timestamp_c, newOne from t1 order by i;
1430 i j f d d2 ch HEX(b) HEX(vb) HEX(blob1) text1 timestamp_c newOne
1431 1 6 6.6 8.8 0 aaa 63636300000000000000000000000000000000 646464 NULL NULL 2008-12-18 17:41:02 Comment field default
1432 2 6 6.6 8.8 0 aaa 63636300000000000000000000000000000000 646464 NULL NULL 2008-12-18 17:41:02 Comment field default
1433 10 10 10 10 10 nnnnn 6E6E6E6E6E0000000000000000000000000000 6E6E6E6E6E 6E6E6E6E6E nnnnn 2008-11-16 08:13:32 Comment field default
1434 11 6 6.6 8.8 0 mmm 63636300000000000000000000000000000000 646464 NULL NULL 2008-12-18 17:41:20 Comment field default
1435 Now backup the current data then restore data-only to a schema with different defaults and no special ndb_restore options
1436 drop table t1;
1437 CREATE TABLE t1 (
1438 `i` int(11) NOT NULL AUTO_INCREMENT,
1439 `j` bigint NOT NULL DEFAULT '20',
1440 `f` float NOT NULL DEFAULT '6.66',
1441 `d` double DEFAULT '8.88',
1442 `d2` double NOT NULL DEFAULT '9.99',
1443 `ch` char(19) DEFAULT 'aaaZZZ',
1444 `b` binary(19) DEFAULT 'ccccc\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
1445 `vb` varbinary(19) DEFAULT 'dddDDDddd',
1446 `blob1` blob,
1447 `text1` text,
1448 `timestamp_c` timestamp NOT NULL DEFAULT 20100608133131,
1449 `newOne` varchar(256),
1450 PRIMARY KEY (`i`)
1451 ) ENGINE=ndbcluster AUTO_INCREMENT=18446744073709551615 DEFAULT CHARSET=latin1;
1452 Warnings:
1453 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1454 SELECT i, j, f, d, d2, ch, HEX(b), HEX(vb), HEX(blob1), text1, timestamp_c, newOne from t1 order by i;
1455 i j f d d2 ch HEX(b) HEX(vb) HEX(blob1) text1 timestamp_c newOne
1456 1 6 6.6 8.8 0 aaa 63636300000000000000000000000000000000 646464 NULL NULL 2008-12-18 17:41:02 Comment field default
1457 2 6 6.6 8.8 0 aaa 63636300000000000000000000000000000000 646464 NULL NULL 2008-12-18 17:41:02 Comment field default
1458 10 10 10 10 10 nnnnn 6E6E6E6E6E0000000000000000000000000000 6E6E6E6E6E 6E6E6E6E6E nnnnn 2008-11-16 08:13:32 Comment field default
1459 11 6 6.6 8.8 0 mmm 63636300000000000000000000000000000000 646464 NULL NULL 2008-12-18 17:41:20 Comment field default
1460 drop table t1;
1461 Bug#55121 error 839 'Illegal null attribute' from NDB for fields with default value
1462 Ensure that Ndb handler doesn't expect native defaults for Blobs.
1463 set @save_sql_mode = @@session.sql_mode;
1464 set sql_mode=STRICT_TRANS_TABLES;
1465 Warnings:
1466 Warning 3135 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
1467 CREATE TABLE t1 (
1468 fid smallint(6) unsigned NOT NULL DEFAULT '0',
1469 f01 text NOT NULL,
1470 f02 varchar(255) NOT NULL DEFAULT '',
1471 f03 text NOT NULL DEFAULT '',
1472 PRIMARY KEY (fid)
1473 ) engine=ndb;
1474 ERROR 42000: BLOB, TEXT, GEOMETRY or JSON column 'f03' can't have a default value
1475 set sql_mode=@save_sql_mode;
1476 Warnings:
1477 Warning 3135 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
1478 CREATE TABLE t1 (
1479 fid smallint(6) unsigned NOT NULL DEFAULT '0',
1480 f01 text NOT NULL,
1481 f02 varchar(255) NOT NULL DEFAULT '',
1482 f03 text NOT NULL DEFAULT '',
1483 PRIMARY KEY (fid)
1484 ) engine=ndb;
1485 show create table t1;
1486 Table Create Table
1487 t1 CREATE TABLE `t1` (
1488 `fid` smallint unsigned NOT NULL DEFAULT '0',
1489 `f01` text NOT NULL,
1490 `f02` varchar(255) NOT NULL DEFAULT '',
1491 `f03` text NOT NULL,
1492 PRIMARY KEY (`fid`)
1493 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1494 insert into t1(fid) value(100);
1495 Warnings:
1496 Warning 1364 Field 'f01' doesn't have a default value
1497 select fid, isnull(f01), isnull(f02), isnull(f03) from t1;
1498 fid isnull(f01) isnull(f02) isnull(f03)
1499 100 0 0 0
1500 drop table t1;
1501 SET GLOBAL ndb_metadata_check = @old_ndb_metadata_check_server2;
1502 SET GLOBAL ndb_metadata_check = @old_ndb_metadata_check_server1;