--source include/have_ndb.inc --result_format 2 # WL#15044 Configurable BLOB inline size ## CREATE and INSERT definitions of extended Blob feature BLOB_INLINE_SIZE CREATE TABLE test.defs( a int primary key, b blob, bm mediumblob, bl longblob, t text, tm mediumtext, tl longtext, j json) engine=ndb; ## Verify inline and partition sizes of test.defs SELECT inline_size, part_size FROM ndbinfo.blobs WHERE table_name = 'defs'; DROP TABLE test.defs; ## Create table with inline values of 0 CREATE TABLE test.zeroinline( a int primary key, b blob comment "NDB_COLUMN=BLOB_INLINE_SIZE=0", bm mediumblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=0", bl longblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=0", t text comment "NDB_COLUMN=BLOB_INLINE_SIZE=0", tm mediumtext comment "NDB_COLUMN=BLOB_INLINE_SIZE=0", tl longtext comment "NDB_COLUMN=BLOB_INLINE_SIZE=0", j json comment "NDB_COLUMN=BLOB_INLINE_SIZE=0") engine=ndb; ## Verify inline and partition sizes of test.zeroinline SELECT inline_size, part_size FROM ndbinfo.blobs WHERE table_name = 'zeroinline'; DROP TABLE test.zeroinline; ## Create table with less than default inline values CREATE TABLE test.shortinline( a int primary key, b blob comment "NDB_COLUMN=BLOB_INLINE_SIZE=10", bm mediumblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=10", bl longblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=10", t text comment "NDB_COLUMN=BLOB_INLINE_SIZE=10", tm mediumtext comment "NDB_COLUMN=BLOB_INLINE_SIZE=10", tl longtext comment "NDB_COLUMN=BLOB_INLINE_SIZE=10", j json comment "NDB_COLUMN=BLOB_INLINE_SIZE=10") engine=ndb; ## Verify inline and partition sizes of test.shortinline SELECT inline_size, part_size FROM ndbinfo.blobs WHERE table_name = 'shortinline'; ## Insert and validate test.shortinline INSERT INTO test.shortinline VALUES ( 1, repeat('J',60000), repeat('J',60000), repeat('J',60000), repeat(_utf32 0x1F47E,15000), repeat(_utf32 0x1F47E,60000), repeat(_utf32 0x1F47E,60000), "{\"f\": \"12\"}"); SELECT a, sha1(b), sha1(bm), sha1(bl), sha1(t), sha1(tm), sha1(tl), sha1(j) FROM test.shortinline; DROP TABLE test.shortinline; ## Create table with large inline values CREATE TABLE test.longinline( a int primary key, b blob comment "NDB_COLUMN=BLOB_INLINE_SIZE=3000", bm mediumblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=3000", bl longblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=3000", t text comment "NDB_COLUMN=BLOB_INLINE_SIZE=3000", tm mediumtext comment "NDB_COLUMN=BLOB_INLINE_SIZE=3000", tl longtext comment "NDB_COLUMN=BLOB_INLINE_SIZE=3000", j json comment "NDB_COLUMN=BLOB_INLINE_SIZE=6000") engine=ndb; ## Verify inline and partition sizes of test.longinline SELECT inline_size, part_size FROM ndbinfo.blobs WHERE table_name = 'longinline'; ## Insert and validate INSERT INTO test.longinline values ( 1, repeat('J',60000), repeat('J',60000), repeat('J',60000), repeat(_utf32 0x1F47E,15000), repeat(_utf32 0x1F47E,60000), repeat(_utf32 0x1F47E,60000), "{\"f\": \"12\"}"); SELECT a, sha1(b), sha1(bm), sha1(bl), sha1(t), sha1(tm), sha1(tl), sha1(j) FROM test.longinline; DROP TABLE test.longinline; ## Create table with some inline values below their default CREATE TABLE test.mixedinline( a int primary key, b blob comment "NDB_COLUMN=BLOB_INLINE_SIZE=20000", bm mediumblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=25", bl longblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=288", t text, tm mediumtext comment "Random other comment", tl longtext comment "NDB_COLUMN=BLOB_INLINE_SIZE=233", j json) engine=ndb; ## Verify inline and partition sizes of test.mixedinline SELECT inline_size, part_size FROM ndbinfo.blobs WHERE table_name = 'mixedinline'; ## Insert and validate test.mixedinline INSERT INTO test.mixedinline VALUES ( 1, repeat('J',60000), repeat('J',60000), repeat('J',60000), repeat(_utf32 0x1F47E,15000), repeat(_utf32 0x1F47E,60000), repeat(_utf32 0x1F47E,60000), "{\"f\": \"12\"}"); SELECT a, sha1(b), sha1(bm), sha1(bl), sha1(t), sha1(tm), sha1(tl), sha1(j) FROM test.mixedinline; DROP TABLE test.mixedinline; ## Create table with larger inline values and maximum partition size (= 13948) CREATE TABLE test.withmaxparts( a int primary key, b blob comment "NDB_COLUMN=BLOB_INLINE_SIZE=20000,MAX_BLOB_PART_SIZE=1", bm mediumblob comment "NDB_COLUMN=MAX_BLOB_PART_SIZE=1,BLOB_INLINE_SIZE=320", bl longblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=320", t text comment "NDB_COLUMN=BLOB_INLINE_SIZE=320,MAX_BLOB_PART_SIZE", tm mediumtext comment "NDB_COLUMN=MAX_BLOB_PART_SIZE=0", tl longtext comment "NDB_COLUMN=MAX_BLOB_PART_SIZE=1", j json comment "NDB_COLUMN=BLOB_INLINE_SIZE=8192") engine=ndb; ## Verify inline and partition sizes of test.withmaxparts SELECT inline_size, part_size FROM ndbinfo.blobs WHERE table_name = 'withmaxparts'; ## Insert and validate test.withmaxparts INSERT INTO test.withmaxparts VALUES ( 1, repeat('J',60000), repeat('J',60000), repeat('J',60000), repeat(_utf32 0x1F47E,15000), repeat(_utf32 0x1F47E,60000), repeat(_utf32 0x1F47E,60000), "{\"f\": \"12\"}"); SELECT a, sha1(b), sha1(bm), sha1(bl), sha1(t), sha1(tm), sha1(tl), sha1(j) FROM test.withmaxparts; DROP TABLE test.withmaxparts; ## Create tables with bad values CREATE TABLE test.neglen( a int primary key, b blob comment "NDB_COLUMN=BLOB_INLINE_SIZE=-20") engine=ndb; CREATE TABLE test.badchar( a int primary key, b blob comment "NDB_COLUMN=BLOB_INLINE_SIZE=ten") engine=ndb; ## Verify inline_size SELECT inline_size FROM ndbinfo.blobs WHERE table_name = 'neglen'; SELECT inline_size FROM ndbinfo.blobs WHERE table_name = 'badchar'; DROP TABLE test.neglen; DROP TABLE test.badchar; --error ER_CANT_CREATE_TABLE CREATE TABLE test.badlen( a int primary key, b blob comment "NDB_COLUMN=BLOB_INLINE_SIZE=40000") engine=ndb; SHOW WARNINGS; --error ER_CANT_CREATE_TABLE CREATE TABLE test.maxlen( a int primary key, b blob comment "NDB_COLUMN=BLOB_INLINE_SIZE=29981") engine=ndb; SHOW WARNINGS; ## Max len for inline Blob is 30000 - (4 + 16[header]) CREATE TABLE test.maxlen( a int primary key, b blob comment "NDB_COLUMN=BLOB_INLINE_SIZE=29980") engine=ndb; ## Verify inline of test.maxlen SELECT inline_size FROM ndbinfo.blobs WHERE table_name = 'maxlen'; DROP TABLE test.maxlen; ## TINYBLOB columns ignore the BLOB_INLINE_SIZE option CREATE TABLE test.bigtiny( a int primary key, tb tinyblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=512") engine=ndb; CREATE TABLE test.smalltiny( a int primary key, tb tinyblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=128") engine=ndb; DROP TABLE test.bigtiny; DROP TABLE test.smalltiny; --echo # WL#15044 Configurable BLOB inline size ## ALTER definitions of extended Blob feature BLOB_INLINE_SIZE CREATE TABLE test.inline( a int primary key, b longblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=10", t longtext comment "NDB_COLUMN=BLOB_INLINE_SIZE=10", j json comment "NDB_COLUMN=BLOB_INLINE_SIZE=10") engine=ndb; ## Verify inline and partition sizes of test.inline SELECT inline_size, part_size FROM ndbinfo.blobs WHERE table_name = 'inline'; ## ALTER with new blob inline size ALTER TABLE test.inline drop column b, drop column t, drop column j, add column bl longblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=512", add column tl longtext comment "NDB_COLUMN=BLOB_INLINE_SIZE=512", add column js json comment "NDB_COLUMN=BLOB_INLINE_SIZE=8192"; ## Verify updated test.inline with new BLOB_INLINE_SIZE SELECT inline_size FROM ndbinfo.blobs WHERE table_name = 'inline'; ## SHOW CREATE TABLE for new BLOB_INLINE_SIZE SHOW CREATE TABLE test.inline; --echo ## In-place ALTER TABLE rename column (same comment) ALTER TABLE test.inline ALGORITHM=inplace, change column bl b longblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=512", change column tl t longtext comment "NDB_COLUMN=BLOB_INLINE_SIZE=512", change column js j json comment "NDB_COLUMN=BLOB_INLINE_SIZE=8192"; ## In-place ALTER TABLE add column (non blob due to prevention of online partitioning) ALTER TABLE test.inline ALGORITHM=inplace, add column c char(32); --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON ## In-place ALTER TABLE change column BLOB_INLINE_SIZE will err ALTER TABLE test.inline ALGORITHM=inplace, change column b b longblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=1000"; SHOW WARNINGS; --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON ## In-place ALTER TABLE remove configured BLOB_INLINE_SIZE will err ALTER TABLE test.inline ALGORITHM=inplace, change column b b longblob; SHOW WARNINGS; --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON ## In-place ALTER TABLE change column comment variations for NDB_COLUMN will err ALTER TABLE test.inline ALGORITHM=inplace, change column b b longblob comment "randomNDB_COLUMN=BLOB_INLINE_SIZE=1000"; SHOW WARNINGS; --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON ALTER TABLE test.inline ALGORITHM=inplace, change column b b longblob comment "NDB_COLUMNrandom= BLOB_INLINE_SIZE=1000"; SHOW WARNINGS; ## Insert some data and verify that read data remains the same INSERT INTO test.inline (a, b, t, j) VALUES (1, repeat(0x424C4F, 512), repeat(0x424C4F, 512), "{\"a\": \"42\"}"); SELECT sha1(b), sha1(t), sha1(j) FROM test.inline; ## Copy ALTER TABLE change column BLOB_INLINE_SIZE changes inline_blob_size ALTER TABLE test.inline algorithm=copy, change column b b longblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=1000", change column t t longtext comment "NDB_COLUMN=BLOB_INLINE_SIZE=1000", change column j j json comment "NDB_COLUMN=BLOB_INLINE_SIZE=10000"; ## Verify updated test.inline SELECT inline_size FROM ndbinfo.blobs WHERE table_name = 'inline'; ## Verify data SELECT sha1(b), sha1(t), sha1(j) FROM test.inline; ## Copy ALTER TABLE change back BLOB_INLINE_SIZE to less ALTER TABLE test.inline algorithm=copy, change column b b longblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=10", change column t t longtext comment "NDB_COLUMN=BLOB_INLINE_SIZE=10", change column j j json comment "NDB_COLUMN=BLOB_INLINE_SIZE=25"; ## Verify updated test.inline with reduced BLOB_INLINE_SIZE SELECT inline_size FROM ndbinfo.blobs WHERE table_name = 'inline'; ## Verify data SELECT sha1(b), sha1(t), sha1(j) FROM test.inline; ## Copy ALTER TABLE change to zero inline size ALTER TABLE test.inline algorithm=copy, change column b b longblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=0", change column t t longtext comment "NDB_COLUMN=BLOB_INLINE_SIZE=0", change column j j json comment "NDB_COLUMN=BLOB_INLINE_SIZE=0"; ## Verify updated test.inline with zero inline size SELECT inline_size FROM ndbinfo.blobs WHERE table_name = 'inline'; ## Verify data SELECT sha1(b), sha1(t), sha1(j) FROM test.inline; ## Copy ALTER TABLE too big BLOB_INLINE_SIZE --error ER_CANT_CREATE_TABLE ALTER TABLE test.inline algorithm=copy, change column b b longblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=27949", change column t t longtext comment "NDB_COLUMM=BLOB_INLINE_SIZE=1000", change column j j json comment "NDB_COLUMN=BLOB_INLINE_SIZE=27949"; SHOW WARNINGS; ## ALTER TABLE random characters on COMMENT before NDB_COLUMN ALTER TABLE test.inline change column b b longblob comment "randomNDB_COLUMN=BLOB_INLINE_SIZE=15"; ## Verify altered test.inline SELECT inline_size FROM ndbinfo.blobs WHERE table_name = 'inline'; --error ER_ILLEGAL_HA_CREATE_OPTION ## ALTER TABLE random characters on COMMENT after NDB_COLUMN ALTER TABLE test.inline change column b b longblob comment "NDB_COLUMN=randomBLOB_INLINE_SIZE=25"; SHOW WARNINGS LIMIT 2; ## ALTER TABLE on table for TINYBLOB --error ER_DATA_TOO_LONG ALTER TABLE test.inline change column b tb tinyblob; ## Length value on b (1536) is still larger than what can fit in TINYBLOB length (255) ALTER TABLE test.inline change column b b longblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=255"; --error ER_DATA_TOO_LONG ALTER TABLE test.inline change column b tb tinyblob; ## Must update data to allow to fit TINYBLOB UPDATE test.inline SET b = repeat(0x424C4F, 64) WHERE a = 1; ALTER TABLE test.inline change column b tb tinyblob; SELECT a, length(tb) FROM test.inline; CREATE TABLE test.tiny ( a int primary key, tb tinyblob) engine = ndb; ## Always ignored for TINYBLOB ALTER TABLE test.tiny change column tb tb tinyblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=128"; DROP TABLE test.tiny; DROP TABLE test.inline; --echo # WL#15044 Configurable BLOB inline size ## Show Backup and Restore across BLOB inline sizes CREATE TABLE test.withmaxparts( a int primary key, b blob comment "NDB_COLUMN=BLOB_INLINE_SIZE=20000,MAX_BLOB_PART_SIZE=1", bm mediumblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=320", bl longblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=320", t text comment "NDB_COLUMN=BLOB_INLINE_SIZE=320", tm mediumtext comment "irrelevant", tl longtext comment "NDB_COLUMN=BLOB_INLINE_SIZE=320", j json comment "NDB_COLUMN=BLOB_INLINE_SIZE=8192") engine=ndb; INSERT INTO test.withmaxparts VALUES ( 1, repeat('J',60000), repeat('J',60000), repeat('J',60000), repeat(_utf32 0x1F47E,15000), repeat(_utf32 0x1F47E,60000), repeat(_utf32 0x1F47E,60000), "{\"f\": \"12\"}"); SELECT a, sha1(b), sha1(bm), sha1(bl), sha1(t), sha1(tm), sha1(tl), sha1(j) FROM test.withmaxparts; --source suite/ndb/include/backup_restore_setup.inc --source include/ndb_backup.inc DROP TABLE test.withmaxparts; # CREATE TABLE with diff sizes CREATE TABLE test.withmaxparts( a int primary key, b blob, bm mediumblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=6000,MAX_BLOB_PART_SIZE=1", bl longblob, t text comment "NDB_COLUMN=BLOB_INLINE_SIZE=10000,MAX_BLOB_PART_SIZE", tm mediumtext comment "NDB_COLUMN=BLOB_INLINE_SIZE=3000", tl longtext, j json comment "irrelevant") engine=ndb; --echo # restore data from backup --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --lossy-conversions --promote-attributes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --lossy-conversions --promote-attributes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT SELECT a, sha1(b), sha1(bm), sha1(bl), sha1(t), sha1(tm), sha1(tl), sha1(j) FROM test.withmaxparts; ## Verify that inline of test.withmaxparts are the new sizes SELECT inline_size FROM ndbinfo.blobs WHERE table_name = 'withmaxparts'; DROP TABLE test.withmaxparts; --source suite/ndb/include/backup_restore_cleanup.inc --remove_file $NDB_TOOLS_OUTPUT