273 lines
9.5 KiB
Plaintext
273 lines
9.5 KiB
Plaintext
######################################################################
|
|
# Test restoring backups with preserving padding in char/bin conversions
|
|
######################################################################
|
|
|
|
-- source include/have_ndb.inc
|
|
-- source suite/ndb/include/backup_restore_setup.inc
|
|
|
|
# mysqld's configuration is not relevant to this test
|
|
|
|
# Turn off STRICT sql_mode since test intentionally truncates
|
|
# data when preparing the test data(those offending statements
|
|
# are within enable/disable_warnings)
|
|
--source suite/ndb/include/turn_off_strict_sql_mode.inc
|
|
|
|
--echo ************************************************************
|
|
--echo * Creating table with char+binary types
|
|
--echo ************************************************************
|
|
|
|
CREATE TABLE t3(
|
|
c1 char(32),
|
|
b1 binary(32)
|
|
) CHARACTER SET latin1 ENGINE=NDB;
|
|
|
|
INSERT INTO t3 VALUES('aaaaaaaa', 'bbbbbbbb');
|
|
SELECT length(c1), length(b1) FROM t3;
|
|
|
|
--echo ************************************************************
|
|
--echo * Backing up table with char+binary types
|
|
--echo ************************************************************
|
|
|
|
--source include/ndb_backup.inc
|
|
|
|
# command shortcuts
|
|
--let $restore_cmd=$NDB_RESTORE -b $the_backup_id
|
|
--let $restore_cmd=$restore_cmd -r --backup_path=$NDB_BACKUPS-$the_backup_id
|
|
|
|
--echo ************************************************************
|
|
--echo * Restoring table with unchanged char+binary types (sanity check):
|
|
--echo * char(32) --> char(32)
|
|
--echo * binary(32) --> binary(32)
|
|
--echo ************************************************************
|
|
|
|
# create a MyISAM table from NDB table, against which to compare data
|
|
CREATE TABLE t3_myisam ENGINE=MYISAM AS SELECT * FROM t3;
|
|
|
|
# restore table
|
|
DELETE FROM t3;
|
|
|
|
# for debugging:
|
|
# --exec $NDB_RESTORE -b $the_backup_id -n 1 -L -r --print $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
|
|
# --exec $NDB_RESTORE -b $the_backup_id -n 2 -L -r --print $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
|
|
--exec $restore_cmd -n 1 --print > /dev/null
|
|
--exec $restore_cmd -n 2 --print > /dev/null
|
|
SELECT length(c1), length(b1) FROM t3;
|
|
|
|
# verify data
|
|
SELECT COUNT(*) FROM t3 NATURAL JOIN t3_myisam;
|
|
DROP TABLE t3_myisam;
|
|
|
|
--echo ************************************************************
|
|
--echo * Restoring table with promoted char+binary types preserving padding:
|
|
--echo * char(32) --> varchar(32)
|
|
--echo * binary(32) --> varbinary(32)
|
|
--echo ************************************************************
|
|
|
|
# demote char+binary type attibutes
|
|
--disable_warnings
|
|
ALTER TABLE t3 MODIFY c1 varchar(32), MODIFY b1 varbinary(32);
|
|
--enable_warnings
|
|
SELECT length(c1), length(b1) FROM t3;
|
|
|
|
# create a MyISAM table from NDB table, against which to compare data
|
|
CREATE TABLE t3_myisam ENGINE=MYISAM AS SELECT * FROM t3;
|
|
|
|
# restore table
|
|
DELETE FROM t3;
|
|
--exec $restore_cmd -n 1 --print -A -P > /dev/null
|
|
--exec $restore_cmd -n 2 --print -A -P > /dev/null
|
|
SELECT length(c1), length(b1) FROM t3;
|
|
|
|
# verify data
|
|
SELECT COUNT(*) FROM t3 NATURAL JOIN t3_myisam;
|
|
DROP TABLE t3_myisam;
|
|
|
|
--echo ************************************************************
|
|
--echo * Restoring table with promoted char+binary types preserving padding:
|
|
--echo * char(32) --> varchar(64)
|
|
--echo * binary(32) --> varbinary(64)
|
|
--echo ************************************************************
|
|
|
|
# demote char+binary type attibutes
|
|
--disable_warnings
|
|
ALTER TABLE t3 MODIFY c1 varchar(64), MODIFY b1 varbinary(64);
|
|
--enable_warnings
|
|
SELECT length(c1), length(b1) FROM t3;
|
|
|
|
# create a MyISAM table from NDB table, against which to compare data
|
|
CREATE TABLE t3_myisam ENGINE=MYISAM AS SELECT * FROM t3;
|
|
|
|
# restore table
|
|
DELETE FROM t3;
|
|
--exec $restore_cmd -n 1 --print -A -P > /dev/null
|
|
--exec $restore_cmd -n 2 --print -A -P > /dev/null
|
|
SELECT length(c1), length(b1) FROM t3;
|
|
|
|
# verify data
|
|
SELECT COUNT(*) FROM t3 NATURAL JOIN t3_myisam;
|
|
DROP TABLE t3_myisam;
|
|
|
|
--echo ************************************************************
|
|
--echo * Restoring table with promoted char+binary types preserving padding:
|
|
--echo * char(32) --> varchar(512)
|
|
--echo * binary(32) --> varbinary(512)
|
|
--echo ************************************************************
|
|
|
|
# demote char+binary type attibutes
|
|
--disable_warnings
|
|
ALTER TABLE t3 MODIFY c1 varchar(512), MODIFY b1 varbinary(512);
|
|
--enable_warnings
|
|
SELECT length(c1), length(b1) FROM t3;
|
|
|
|
# create a MyISAM table from NDB table, against which to compare data
|
|
CREATE TABLE t3_myisam ENGINE=MYISAM AS SELECT * FROM t3;
|
|
|
|
# restore table
|
|
DELETE FROM t3;
|
|
--exec $restore_cmd -n 1 --print -A -P > /dev/null
|
|
--exec $restore_cmd -n 2 --print -A -P > /dev/null
|
|
SELECT length(c1), length(b1) FROM t3;
|
|
|
|
# verify data
|
|
SELECT COUNT(*) FROM t3 NATURAL JOIN t3_myisam;
|
|
DROP TABLE t3_myisam;
|
|
|
|
--echo ************************************************************
|
|
--echo * Restoring table with demoted char+binary types preserving padding:
|
|
--echo * char(32) --> varchar(16)
|
|
--echo * binary(32) --> varbinary(16)
|
|
--echo ************************************************************
|
|
|
|
# demote char+binary type attibutes
|
|
--disable_warnings
|
|
ALTER TABLE t3 MODIFY c1 varchar(16), MODIFY b1 varbinary(16);
|
|
--enable_warnings
|
|
SELECT length(c1), length(b1) FROM t3;
|
|
|
|
# create a MyISAM table from NDB table, against which to compare data
|
|
CREATE TABLE t3_myisam ENGINE=MYISAM AS SELECT * FROM t3;
|
|
|
|
# restore table
|
|
DELETE FROM t3;
|
|
--exec $restore_cmd -n 1 --print -L -P > /dev/null
|
|
--exec $restore_cmd -n 2 --print -L -P > /dev/null
|
|
SELECT length(c1), length(b1) FROM t3;
|
|
|
|
# verify data
|
|
SELECT COUNT(*) FROM t3 NATURAL JOIN t3_myisam;
|
|
DROP TABLE t3_myisam;
|
|
|
|
--echo ************************************************************
|
|
--echo * Restoring table with promoted char+binary types discarding padding:
|
|
--echo * char(32) --> varchar(32)
|
|
--echo * binary(32) --> varbinary(32)
|
|
--echo ************************************************************
|
|
|
|
# demote char+binary type attibutes
|
|
--disable_warnings
|
|
DELETE FROM t3;
|
|
ALTER TABLE t3 MODIFY c1 varchar(32), MODIFY b1 varbinary(32);
|
|
INSERT INTO t3 VALUES('aaaaaaaa', 'bbbbbbbb');
|
|
--enable_warnings
|
|
SELECT length(c1), length(b1) FROM t3;
|
|
|
|
# create a MyISAM table from NDB table, against which to compare data
|
|
CREATE TABLE t3_myisam ENGINE=MYISAM AS SELECT * FROM t3;
|
|
|
|
# restore table
|
|
DELETE FROM t3;
|
|
--exec $restore_cmd -n 1 --print -A > /dev/null
|
|
--exec $restore_cmd -n 2 --print -A > /dev/null
|
|
SELECT length(c1), length(b1) FROM t3;
|
|
|
|
# verify data
|
|
SELECT COUNT(*) FROM t3 NATURAL JOIN t3_myisam;
|
|
DROP TABLE t3_myisam;
|
|
|
|
--echo ************************************************************
|
|
--echo * Restoring table with promoted char+binary types discarding padding:
|
|
--echo * char(32) --> varchar(64)
|
|
--echo * binary(32) --> varbinary(64)
|
|
--echo ************************************************************
|
|
|
|
# demote char+binary type attibutes
|
|
--disable_warnings
|
|
ALTER TABLE t3 MODIFY c1 varchar(64), MODIFY b1 varbinary(64);
|
|
--enable_warnings
|
|
SELECT length(c1), length(b1) FROM t3;
|
|
|
|
# create a MyISAM table from NDB table, against which to compare data
|
|
CREATE TABLE t3_myisam ENGINE=MYISAM AS SELECT * FROM t3;
|
|
|
|
# restore table
|
|
DELETE FROM t3;
|
|
--exec $restore_cmd -n 1 --print -A > /dev/null
|
|
--exec $restore_cmd -n 2 --print -A > /dev/null
|
|
SELECT length(c1), length(b1) FROM t3;
|
|
|
|
# verify data
|
|
SELECT COUNT(*) FROM t3 NATURAL JOIN t3_myisam;
|
|
DROP TABLE t3_myisam;
|
|
|
|
--echo ************************************************************
|
|
--echo * Restoring table with promoted char+binary types discarding padding:
|
|
--echo * char(32) --> varchar(512)
|
|
--echo * binary(32) --> varbinary(512)
|
|
--echo ************************************************************
|
|
|
|
# demote char+binary type attibutes
|
|
--disable_warnings
|
|
ALTER TABLE t3 MODIFY c1 varchar(512), MODIFY b1 varbinary(512);
|
|
--enable_warnings
|
|
SELECT length(c1), length(b1) FROM t3;
|
|
|
|
# create a MyISAM table from NDB table, against which to compare data
|
|
CREATE TABLE t3_myisam ENGINE=MYISAM AS SELECT * FROM t3;
|
|
|
|
# restore table
|
|
DELETE FROM t3;
|
|
--exec $restore_cmd -n 1 --print -A > /dev/null
|
|
--exec $restore_cmd -n 2 --print -A > /dev/null
|
|
SELECT length(c1), length(b1) FROM t3;
|
|
|
|
# verify data
|
|
SELECT COUNT(*) FROM t3 NATURAL JOIN t3_myisam;
|
|
DROP TABLE t3_myisam;
|
|
|
|
--echo ************************************************************
|
|
--echo * Restoring table with demoted char+binary types discarding padding:
|
|
--echo * char(32) --> varchar(16)
|
|
--echo * binary(32) --> varbinary(16)
|
|
--echo ************************************************************
|
|
|
|
# demote char+binary type attibutes
|
|
--disable_warnings
|
|
ALTER TABLE t3 MODIFY c1 varchar(16), MODIFY b1 varbinary(16);
|
|
--enable_warnings
|
|
SELECT length(c1), length(b1) FROM t3;
|
|
|
|
# create a MyISAM table from NDB table, against which to compare data
|
|
CREATE TABLE t3_myisam ENGINE=MYISAM AS SELECT * FROM t3;
|
|
|
|
# restore table
|
|
DELETE FROM t3;
|
|
--exec $restore_cmd -n 1 --print -L > /dev/null
|
|
--exec $restore_cmd -n 2 --print -L > /dev/null
|
|
SELECT length(c1), length(b1) FROM t3;
|
|
|
|
# verify data
|
|
SELECT COUNT(*) FROM t3 NATURAL JOIN t3_myisam;
|
|
DROP TABLE t3_myisam;
|
|
|
|
--echo ************************************************************
|
|
--echo * Deleting table with char+binary types
|
|
--echo ************************************************************
|
|
|
|
DROP TABLE t3;
|
|
|
|
|
|
|
|
--source suite/ndb/include/restore_sql_mode_after_turn_off_strict.inc
|
|
--source suite/ndb/include/backup_restore_cleanup.inc
|
|
--remove_file $NDB_TOOLS_OUTPUT
|