--source setup.inc --source suite/ndb/include/backup_restore_setup.inc # # Case 1: Basic schema detection + synchronization test using # the ndb_metadata_sync option. This is a duplicate of # case 1 in the metadata_sync test. # - Cause mismatch between NDB Dictionary and DD # - Check if these mismatches are detected properly by # Ndb_metadata_change_monitor thread # - Check if these mismatches detected are synchronized # # Create logfile group, tablespace, and tables in addition to # those created in setup.inc 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; USE ndb_ddl_test; CREATE TABLE t10 ( a INT PRIMARY KEY, b VARCHAR(255) ) ENGINE NDB TABLESPACE ts1 STORAGE DISK; INSERT INTO t10 VALUES(1, 'Beggars can\'t be choosers'); CREATE DATABASE ndb_ddl_test3; USE ndb_ddl_test3; CREATE TABLE t1 ( a INT, b INT, PRIMARY KEY(a,b) ) ENGINE NDB; CREATE TABLE t2( a INT PRIMARY KEY, b VARCHAR(255) ) ENGINE NDB; # backup --disable_query_log --source include/ndb_backup.inc --enable_query_log # Create a few more databases and tests CREATE DATABASE ndb_ddl_test4; USE ndb_ddl_test4; CREATE TABLE t1 ( a INT, b INT, PRIMARY KEY(a,b) ) ENGINE NDB; CREATE TABLE t2( a INT PRIMARY KEY, b VARCHAR(255) ) ENGINE NDB; CREATE DATABASE ndb_ddl_test5; USE ndb_ddl_test5; CREATE TABLE t1 ( a INT, b INT, PRIMARY KEY(a,b) ) ENGINE NDB; USE ndb_ddl_test; # Drop half the tables from ndb_ddl_test database --let $counter = 6 while ($counter <= 10) { let $tx=t$counter; --eval DROP TABLE $tx --inc $counter } ALTER TABLESPACE ts1 DROP DATAFILE 'ts1_datafile.dat'; DROP TABLESPACE ts1; DROP LOGFILE GROUP lg1 ENGINE NDB; # Drop all tables in ndb_ddl_test3 USE ndb_ddl_test3; DROP TABLE t1; DROP TABLE t2; # Restore into NDB, skip tables in ndb_ddl_test database that already exist --exec $NDB_RESTORE -b $the_backup_id -n 1 -m -r --exclude-tables=ndb_ddl_test.t1,ndb_ddl_test.t2,ndb_ddl_test.t3,ndb_ddl_test.t4,ndb_ddl_test.t5 $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --exclude-tables=ndb_ddl_test.t1,ndb_ddl_test.t2,ndb_ddl_test.t3,ndb_ddl_test.t4,ndb_ddl_test.t5 $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT # Drop tables from NDB only to cause mismatch --exec $NDB_DROP_TABLE -d ndb_ddl_test4 t1 >> $NDB_TOOLS_OUTPUT --exec $NDB_DROP_TABLE -d ndb_ddl_test5 t1 >> $NDB_TOOLS_OUTPUT # Wait until the changes have been synced --let $max_wait = 60 --source wait_immediate_metadata_sync.inc # Check if the metadata is as expected after synchronization SHOW DATABASES; USE ndb_ddl_test; SHOW TABLES; USE ndb_ddl_test3; SHOW TABLES; USE ndb_ddl_test4; SHOW TABLES; USE ndb_ddl_test5; SHOW TABLES; # Run some DDL statements and queries on the tables synced --sorted_result SELECT * FROM ndb_ddl_test.t6; --sorted_result SELECT * FROM ndb_ddl_test.t8; --sorted_result SELECT * FROM ndb_ddl_test.t10; ALTER TABLE ndb_ddl_test3.t1 ADD COLUMN c VARCHAR(255); INSERT INTO ndb_ddl_test3.t1 VALUES(1, 1, 'Donkey\'s praise'); SELECT * FROM ndb_ddl_test3.t1; --error ER_NO_SUCH_TABLE SHOW CREATE TABLE ndb_ddl_test4.t1; --error ER_NO_SUCH_TABLE SHOW CREATE TABLE ndb_ddl_test5.t1; # Check if tablespace and logfile group work fine after sync CREATE TABLESPACE ts2 ADD DATAFILE 'ts2_datafile.dat' USE LOGFILE GROUP lg1 INITIAL_SIZE 2M ENGINE NDB; ALTER TABLESPACE ts1 ADD DATAFILE 'ts1_datafile2.dat'; CREATE TABLE ndb_ddl_test4.t1 ( a INT PRIMARY KEY, b INT ) ENGINE NDB; INSERT INTO ndb_ddl_test4.t1 VALUES(1,1); SELECT * FROM ndb_ddl_test4.t1; # Cleanup DROP DATABASE ndb_ddl_test3; DROP DATABASE ndb_ddl_test4; DROP DATABASE ndb_ddl_test5; --source cleanup.inc ALTER TABLESPACE ts1 DROP DATAFILE 'ts1_datafile.dat'; ALTER TABLESPACE ts1 DROP DATAFILE 'ts1_datafile2.dat'; DROP TABLESPACE ts1; ALTER TABLESPACE ts2 DROP DATAFILE 'ts2_datafile.dat'; DROP TABLESPACE ts2; DROP LOGFILE GROUP lg1 ENGINE NDB; # # Case 2: Automatic synchronization of schemata. # This is a duplicate of case 2 in the metadata_sync test # - Cause mismatch in terms of schema between NDB Dictionary and DD # - Check if the schema mismatch is detected # - Check if the schema mismatch is synchronized # # db1 - Only NDB tables CREATE DATABASE db1; USE db1; CREATE TABLE t1_ndb ( a INT PRIMARY KEY, b INT ) ENGINE NDB; CREATE TABLE t2_ndb ( a INT PRIMARY KEY, b VARCHAR(255) ) ENGINE NDB; # db2 - Only NDB tables CREATE DATABASE db2; USE db2; CREATE TABLE t1_ndb ( a INT PRIMARY KEY, b INT ) ENGINE NDB; CREATE TABLE t2_ndb ( a INT PRIMARY KEY, b VARCHAR(255) ) ENGINE NDB; # db3 - Only InnoDB tables CREATE DATABASE db3; USE db3; CREATE TABLE t1_innodb ( a INT PRIMARY KEY, b INT ); CREATE TABLE t2_innodb ( a INT PRIMARY KEY, b VARCHAR(255) ); # db4 - Both NDB and InnoDB tables CREATE DATABASE db4; USE db4; CREATE TABLE t1_ndb ( a INT PRIMARY KEY, b INT ) ENGINE NDB; CREATE TABLE t2_ndb ( a INT PRIMARY KEY, b VARCHAR(255) ) ENGINE NDB; CREATE TABLE t1_innodb ( a INT PRIMARY KEY, b INT ); CREATE TABLE t2_innodb ( a INT PRIMARY KEY, b VARCHAR(255) ); # backup --disable_query_log --source include/ndb_backup.inc --enable_query_log # Cause mismatch by dropping db1 and db4 and restoring metadata DROP DATABASE db1; DROP DATABASE db4; --exec $NDB_RESTORE -b $the_backup_id -n 1 -m --exclude-databases=db2 $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT # # At this point the state of the schemata is as follows: # - db1: Used in NDB Dictionary, non-existent in DD # - db2: Used in NDB Dictionary, exists in DD # - db3: Not used in NDB Dictionary, exists in DD # - db4: Used in NDB Dictionary, non-existent in DD # # The auto sync mechanism will create db1 and db4 in the DD. # It will also synchronize all NDB tables contained in db1 and db4 # # Wait until the changes have been synced --let $max_wait = 60 --source wait_immediate_metadata_sync.inc # Check if the objects have been synchronized as expected SHOW DATABASES LIKE 'db%'; USE db1; SHOW TABLES; SHOW CREATE TABLE t1_ndb; SHOW CREATE TABLE t2_ndb; USE db2; SHOW TABLES; USE db3; SHOW TABLES; USE db4; SHOW TABLES; SHOW CREATE TABLE t1_ndb; SHOW CREATE TABLE t2_ndb; # Clean up DROP DATABASE db1; DROP DATABASE db2; DROP DATABASE db3; DROP DATABASE db4; # # Case 3: Automatic synchronization of tables restored # with the --disable-indexes option. # This is a variant of the metadata_sync_restore_disable_index test # - Cause mismatch by restoring tables with --disable-indexes # - Wait until the tables are excluded after 10 unsuccessful tries # - Rebuild indexes and discover tables # - Clear the excluded objects list # # Create tables with various indexes USE test; CREATE TABLE t1 ( a INT PRIMARY KEY, b INT NOT NULL, c INT NOT NULL, UNIQUE ib(b), INDEX ic(c) ) ENGINE NDB; INSERT INTO t1 VALUES (1,2,3),(2,3,5),(3,4,6),(4,5,8),(5,6,2),(6,7,2); CREATE TABLE parent ( c1 INT PRIMARY KEY ) ENGINE NDB; INSERT INTO parent VALUES (1),(2); CREATE TABLE child ( c1 INT PRIMARY KEY, c2 INT, INDEX xc2(c2), FOREIGN KEY(c2) REFERENCES parent(c1) ON DELETE CASCADE ) ENGINE NDB; INSERT INTO child VALUES (1,1),(2,2); --echo Backup --source include/ndb_backup.inc DROP TABLE t1,child,parent; # Suppress expected errors --disable_query_log call mtr.add_suppression("Metadata check has failed"); call mtr.add_suppression("The NDB Dictionary table definition is not identical to the DD table definition"); call mtr.add_suppression("Diff in 'index_count'"); --enable_query_log --echo Restore with --disable-indexes --exec $NDB_RESTORE -b $the_backup_id -n 1 -m -r --disable-indexes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT --exec $NDB_RESTORE -b $the_backup_id -n 2 -r $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT # Wait until the changes have been synced --let $max_wait = 60 --source wait_immediate_metadata_sync.inc --sorted_result SELECT schema_name, name, type FROM performance_schema.ndb_sync_excluded_objects; --echo Rebuild indexes in NDB --exec $NDB_RESTORE -b $the_backup_id -n 1 --rebuild-indexes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT --echo Retry sync now that the indexes have been created --let $max_wait = 60 --source wait_immediate_metadata_sync.inc # Check that the tables have been synced SHOW TABLES; --sorted_result SELECT * FROM t1; --sorted_result SELECT * FROM parent; --sorted_result SELECT * FROM child; # Verify that no objects are excluded SELECT COUNT(*) FROM performance_schema.ndb_sync_excluded_objects; # Clean-up DROP TABLE t1,child,parent; # # Case 4: Mock tables created with foreign_key_checks set to 0. # Mock tables are created when foreign_key_checks is set to 0 # and a child table has a foreign key referencing a parent table # that "does not exist". These tables should not be automatically # synced. # SET @old_foreign_key_checks = @@foreign_key_checks; SET foreign_key_checks=0; # Create child table referencing non-existent parent table CREATE TABLE child ( c1 INT PRIMARY KEY, c2 INT, INDEX xc2(c2), FOREIGN KEY(c2) REFERENCES parent(c1) ON DELETE CASCADE ) ENGINE NDB; INSERT INTO child VALUES (1,1),(2,2); # Wait until all changes have been synced --let $max_wait = 30 --source wait_immediate_metadata_sync.inc # Sync of mock tables fails every time so we check if any such # sync was attempted by looking at the ndb_sync_excluded_objects # table. The table should contain zero rows which indicates that # no mock tables were picked up for sync. SELECT CASE COUNT(*) WHEN 0 THEN 'YES' ELSE 'NO' END AS mock_tables_sync_skipped FROM performance_schema.ndb_sync_excluded_objects; # Clean-up DROP TABLE child; SET foreign_key_checks = @old_foreign_key_checks; --source suite/ndb/include/backup_restore_cleanup.inc --remove_file $NDB_TOOLS_OUTPUT