| 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; |