--source include/have_multi_ndb.inc --source suite/ndb/include/backup_restore_setup.inc # # More tests related to metadata upgrade including # upgrade of tables with different column types # and disk data tables # # The backup consists of: # CREATE TABLE t1 ( # c1 INT PRIMARY KEY AUTO_INCREMENT, # c2 INT UNSIGNED NOT NULL, # c3 FLOAT, # c4 VARCHAR(255), # c5 TIME, # c6 DATE, # c7 DATETIME, # c8 BLOB, # c9 CHAR(30), # c10 VARBINARY(255), # UNIQUE KEY xc2(c2) # ) ENGINE NDB; # # INSERT INTO t1 VALUES(1,1,2.5,'a','23:59:59','2018-01-30','2018-01-30 13:46:00','b','c','d'); # # CREATE TABLE t2 ( # c1 int PRIMARY KEY, # c2 int GENERATED ALWAYS AS (c1*c1) # ) ENGINE NDB; # # INSERT INTO t2(c1) VALUES(2); # # CREATE TABLE t3 ( # c1 int NOT NULL AUTO_INCREMENT PRIMARY KEY, # c2 varchar(100), # c3 varchar(100) # ) ENGINE NDB # COMMENT="NDB_TABLE=READ_BACKUP=0,PARTITION_BALANCE=FOR_RP_BY_NODE"; # # INSERT INTO t3 VALUES(1,'a','a'); # # Case 1: Metadata upgrade during schema synchronization --exec $NDB_RESTORE $NDB_RESTORE_NO_ENCR -b 1 -n 1 -m -r --disable-indexes $NDB_SAVED_BACKUPS/metadata_upgrade_advanced_backup >> $NDB_TOOLS_OUTPUT --exec $NDB_RESTORE $NDB_RESTORE_NO_ENCR -b 1 -n 2 -r $NDB_SAVED_BACKUPS/metadata_upgrade_advanced_backup >> $NDB_TOOLS_OUTPUT --exec $NDB_RESTORE $NDB_RESTORE_NO_ENCR -b 1 -n 1 --rebuild-indexes $NDB_SAVED_BACKUPS/metadata_upgrade_advanced_backup >> $NDB_TOOLS_OUTPUT --echo Backup from 7.5 restored --echo Restart server 1 to trigger schema synchronization let $mysqld_name=mysqld.1.1; --source include/restart_mysqld.inc connection server2; --echo Restart server 2 to trigger schema synchronization let $mysqld_name=mysqld.2.1; --source include/restart_mysqld.inc # Check schema and then perform DML and DDL on the tables SHOW TABLES; SHOW CREATE TABLE t1; --sorted_result SELECT * FROM t1; ALTER TABLE t1 DROP COLUMN c9; INSERT INTO t1 VALUES (2,2,2.5,'a','23:59:59','2018-01-30','2018-01-30 13:46:00','b','d'); --sorted_result SELECT * FROM t1; DROP TABLE t1; SHOW CREATE TABLE t2; --sorted_result SELECT * FROM t2; ALTER TABLE t2 RENAME t4; INSERT INTO t4(c1) VALUES (3); --sorted_result SELECT * FROM t4; DROP TABLE t4; SHOW CREATE TABLE t3; --sorted_result SELECT * FROM t3; ALTER TABLE t3 ALGORITHM=COPY, ADD COLUMN c4 int; INSERT INTO t3 VALUES (2,'b','b',2); --sorted_result SELECT * FROM t3; DROP TABLE t3; # Case 2: Metadata upgrade during discovery from NDB --exec $NDB_RESTORE $NDB_RESTORE_NO_ENCR -b 1 -n 1 -m -r --disable-indexes $NDB_SAVED_BACKUPS/metadata_upgrade_advanced_backup >> $NDB_TOOLS_OUTPUT --exec $NDB_RESTORE $NDB_RESTORE_NO_ENCR -b 1 -n 2 -r $NDB_SAVED_BACKUPS/metadata_upgrade_advanced_backup >> $NDB_TOOLS_OUTPUT --exec $NDB_RESTORE $NDB_RESTORE_NO_ENCR -b 1 -n 1 --rebuild-indexes $NDB_SAVED_BACKUPS/metadata_upgrade_advanced_backup >> $NDB_TOOLS_OUTPUT SHOW CREATE TABLE t1; --sorted_result SELECT * FROM t1; ALTER TABLE t1 DROP COLUMN c9; INSERT INTO t1 VALUES (2,2,2.5,'a','23:59:59','2018-01-30','2018-01-30 13:46:00','b','d'); --sorted_result SELECT * FROM t1; DROP TABLE t1; SHOW CREATE TABLE t2; --sorted_result SELECT * FROM t2; connection server2; SHOW CREATE TABLE t2; connection server1; ALTER TABLE t2 RENAME t4; INSERT INTO t4(c1) VALUES (3); --sorted_result SELECT * FROM t4; DROP TABLE t4; SHOW CREATE TABLE t3; --sorted_result SELECT * FROM t3; ALTER TABLE t3 ALGORITHM=COPY, ADD COLUMN c4 int; INSERT INTO t3 VALUES (2,'b','b',2); --sorted_result SELECT * FROM t3; DROP TABLE t3; # Case 3: Check that tables restored with the --disable-indexes option are # not automatically migrated. Synchronization of such tables # would lead to the wrong table definition being installed in the DD # and would render the table unusable until the indexes were restored # to NDB # Store initial counts of both detected and synchronized objects --let $initial_detected_count = query_get_value(SHOW STATUS LIKE 'Ndb_metadata_detected_count', Value, 1) --let $initial_synced_count = query_get_value(SHOW STATUS LIKE 'Ndb_metadata_synced_count', Value, 1) --echo Restore metadata and data but do not create indexes --exec $NDB_RESTORE $NDB_RESTORE_NO_ENCR -b 1 -n 1 -m -r --disable-indexes $NDB_SAVED_BACKUPS/metadata_upgrade_advanced_backup >> $NDB_TOOLS_OUTPUT --exec $NDB_RESTORE $NDB_RESTORE_NO_ENCR -b 1 -n 2 -r $NDB_SAVED_BACKUPS/metadata_upgrade_advanced_backup >> $NDB_TOOLS_OUTPUT --disable_query_log # Suppress expected warnings due to missing indexes CALL mtr.add_suppression("Metadata check has failed"); CALL mtr.add_suppression("The NDB Dictionary table definition is not identical"); CALL mtr.add_suppression("Diff in 'index_count'"); # Set low interval so changes are detected quickly SET @old_ndb_metadata_check = @@global.ndb_metadata_check; SET @old_ndb_metadata_check_interval = @@global.ndb_metadata_check_interval; SET GLOBAL ndb_metadata_check = true; SET GLOBAL ndb_metadata_check_interval = 5; --enable_query_log # Wait until the following 3 object changes are detected: # Table 'test.t1' # Table 'test.t2' # Table 'test.t3' --let $expected_changes = 3 --let $max_wait = 30 --source wait_metadata_changes_detected.inc --disable_query_log # Changes have been detected, reset values SET GLOBAL ndb_metadata_check = @old_ndb_metadata_check; SET GLOBAL ndb_metadata_check_interval = @old_ndb_metadata_check_interval; --enable_query_log # The above detected tables are never synced. They are instead treated # as temporary errors. This scenario can be detected by waiting for them # to be detected again which confirms that they've been removed from the # queue but not synced and are also not currently excluded --let $initial_detected_count = query_get_value(SHOW STATUS LIKE 'Ndb_metadata_detected_count', Value, 1) --disable_query_log # Set low interval so changes are detected quickly SET GLOBAL ndb_metadata_check = true; SET GLOBAL ndb_metadata_check_interval = 5; --enable_query_log # Wait until the following 3 object changes are detected: # Table 'test.t1' # Table 'test.t2' # Table 'test.t3' --let $expected_changes = 3 --let $max_wait = 30 --source wait_metadata_changes_detected.inc --disable_query_log # Changes have been detected, reset values SET GLOBAL ndb_metadata_check = @old_ndb_metadata_check; SET GLOBAL ndb_metadata_check_interval = @old_ndb_metadata_check_interval; --enable_query_log --echo Confirm that the tables haven't been synced SHOW TABLES; --echo Rebuild indexes in NDB --exec $NDB_RESTORE $NDB_RESTORE_NO_ENCR -b 1 -n 1 --rebuild-indexes $NDB_SAVED_BACKUPS/metadata_upgrade_advanced_backup >> $NDB_TOOLS_OUTPUT # Indexes have been rebuilt which means that the tables are now # ready to be synced --let $initial_detected_count = query_get_value(SHOW STATUS LIKE 'Ndb_metadata_detected_count', Value, 1) --disable_query_log # Set low interval so changes are detected quickly SET GLOBAL ndb_metadata_check = true; SET GLOBAL ndb_metadata_check_interval = 0; --enable_query_log # Wait until the following 3 object changes are detected: # Table 'test.t1' # Table 'test.t2' # Table 'test.t3' --let $expected_changes = 3 --let $max_wait = 30 --source wait_metadata_changes_detected.inc --disable_query_log # Changes have been detected, reset values SET GLOBAL ndb_metadata_check = @old_ndb_metadata_check; SET GLOBAL ndb_metadata_check_interval = @old_ndb_metadata_check_interval; --enable_query_log --let $max_wait = 30 --source wait_metadata_synced.inc --echo Confirm that the tables have been synced SHOW TABLES; --sorted_result SELECT * FROM t1; --sorted_result SELECT * FROM t2; --sorted_result SELECT * FROM t3; DROP TABLE t1,t2,t3; # # Metadata upgrade of a disk data table # The backup consists of: # # CREATE LOGFILE GROUP lg1 # ADD UNDOFILE 'lg1_undofile.dat' # INITIAL_SIZE 1M # UNDO_BUFFER_SIZE = 1M # ENGINE NDB; # # CREATE TABLESPACE ts1 # ADD DATAFILE 'ts1_datafile.dat' # USE LOGFILE GROUP lg1 # INITIAL_SIZE 2M # ENGINE NDB; # # CREATE TABLE t1 ( # a INT PRIMARY KEY, # b VARCHAR(255) # ) ENGINE NDB # TABLESPACE ts1 # STORAGE DISK; # # INSERT INTO t1 VALUES (1,'Sad Cypress'); # # Case 4: Metadata upgrade of a disk data table --connection server1 --exec $NDB_RESTORE $NDB_RESTORE_NO_ENCR -b 1 -n 1 -m -r --disable-indexes $NDB_SAVED_BACKUPS/metadata_upgrade_disk_data_backup >> $NDB_TOOLS_OUTPUT --exec $NDB_RESTORE $NDB_RESTORE_NO_ENCR -b 1 -n 2 -r $NDB_SAVED_BACKUPS/metadata_upgrade_disk_data_backup >> $NDB_TOOLS_OUTPUT --exec $NDB_RESTORE $NDB_RESTORE_NO_ENCR -b 1 -n 1 --rebuild-indexes $NDB_SAVED_BACKUPS/metadata_upgrade_disk_data_backup >> $NDB_TOOLS_OUTPUT --echo Backup from 7.6 restored --echo Restart server 1 to trigger schema synchronization let $mysqld_name=mysqld.1.1; --source include/restart_mysqld.inc connection server2; --echo Restart server 2 to trigger schema synchronization let $mysqld_name=mysqld.2.1; --source include/restart_mysqld.inc connection server1; # Check schema and then perform DML and DDL on the tables SHOW TABLES; SHOW CREATE TABLE t1; --sorted_result SELECT * FROM t1; ALTER TABLE t1 DROP COLUMN b; INSERT INTO t1 VALUES (2); --sorted_result SELECT * FROM t1; DROP TABLE t1; ALTER TABLESPACE ts1 DROP DATAFILE 'ts1_datafile.dat'; DROP TABLESPACE ts1; DROP LOGFILE GROUP lg1 ENGINE NDB; --remove_file $NDB_TOOLS_OUTPUT