-- source include/have_multi_ndb.inc -- source include/have_debug_sync.inc --echo Create an extra connection to server1 connect (server1con2,127.0.0.1,root,,test,$MASTER_MYPORT,); --connection server1 --echo Show that copying alter has some ability to detect when concurrent --echo changes are occuring to a source table as part of an alter. --echo This causes the alter to fail, avoiding silent data loss/corruption. --echo Concurrent modifications are made on a separate MySQLD to avoid --echo MDL effects. --echo Also show that harmless read-only references to the base table --echo succeed and do not cause the ALTER to fail use test; create table cambridge (a int primary key, b int, c int, d int) engine=ndb; insert into cambridge values (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4), (5,5,5,5); create table oxford(a int primary key, b int, c text, d int) engine=ndb; insert into oxford values (1,1,repeat('B', 1000), 1), (2,2,repeat('B', 1000), 2), (3,3,repeat('B', 1000), 3), (4,4,repeat('B', 1000), 4), (5,5,repeat('B', 1000), 5); --echo ------------------------------------------------------- --echo Test various concurrent modifications that should cause --echo copy failure for a copying alter drop column. --echo ------------------------------------------------------- let alter_statement=ALTER TABLE test.cambridge drop column d; let other_statement=UPDATE test.cambridge set c=c+1; # scanning let expected_alter_error=ER_TABLE_DEF_CHANGED; let expected_other_error=0; -- source suite/ndb/t/ndb_alter_table_copy_check.inc let alter_statement=ALTER TABLE test.cambridge drop column d; let other_statement=UPDATE test.cambridge set c=20 where a=2; # by pk let expected_alter_error=ER_TABLE_DEF_CHANGED; let expected_other_error=0; -- source suite/ndb/t/ndb_alter_table_copy_check.inc let alter_statement=ALTER TABLE test.cambridge drop column d; let other_statement=INSERT into test.cambridge values (6,6,6,6); let expected_alter_error=ER_TABLE_DEF_CHANGED; let expected_other_error=0; -- source suite/ndb/t/ndb_alter_table_copy_check.inc let alter_statement=ALTER TABLE test.cambridge drop column d; let other_statement=DELETE from test.cambridge where a=1; let expected_alter_error=ER_TABLE_DEF_CHANGED; let expected_other_error=0; -- source suite/ndb/t/ndb_alter_table_copy_check.inc --echo ------------------------------------------------------- --echo Test various concurrent modifications that should cause --echo copy failure for a copying pk change --echo ------------------------------------------------------- let alter_statement=ALTER TABLE test.cambridge drop primary key; let other_statement=UPDATE test.cambridge set c=c+1; # scanning let expected_alter_error=ER_TABLE_DEF_CHANGED; let expected_other_error=0; -- source suite/ndb/t/ndb_alter_table_copy_check.inc --echo -------------------------- --echo FK cascading modifications --echo -------------------------- --echo Ndb maintains inter-table FKs in the kernel, so that --echo changes in a different table can affect a table --echo undergoing a copying alter update cambridge set b=a; alter table cambridge add unique(b); # Setup FK via secondary keys allowing cascading update + delete... create table bristol (a int primary key, b int, c int, foreign key (b) references cambridge (b) on update cascade on delete cascade) engine=ndb; insert into bristol values (2,2,2), (3,3,3), (4,4,4), (5,5,5); # Check that cascading FK update op causes child alter to fail let alter_statement=ALTER TABLE test.bristol drop column c; let other_statement=UPDATE test.cambridge set b=50 where a=5; let expected_alter_error=ER_TABLE_DEF_CHANGED; let expected_other_error=0; -- source suite/ndb/t/ndb_alter_table_copy_check.inc # Check that cascading FK delete op causes child alter to fail let alter_statement=ALTER TABLE test.bristol drop column c; let other_statement=DELETE from test.cambridge where a=5; let expected_alter_error=ER_TABLE_DEF_CHANGED; let expected_other_error=0; -- source suite/ndb/t/ndb_alter_table_copy_check.inc drop table bristol; # TODO : More copying alters... # Partitioning, --echo ----- --echo Blobs --echo ----- # A more interesting test would use NdbApi to update only the # parts rows of a blob, and check that this is treated as # a main table update. # MySQLD currently always overwrites the whole value let alter_statement=ALTER TABLE test.oxford drop column d; let other_statement=UPDATE test.oxford SET c=REPEAT('E',1000); let expected_alter_error=ER_TABLE_DEF_CHANGED; let expected_other_error=0; -- source suite/ndb/t/ndb_alter_table_copy_check.inc --echo ------------------------------------------------------- --echo Test various concurrent operations that should not cause --echo copy failure for a copying alter drop column. --echo ------------------------------------------------------- --echo Scanning SELECTs let alter_statement=ALTER TABLE test.cambridge drop column d; let other_statement=SELECT a,b from test.cambridge order by a; let expected_alter_error=0; let expected_other_error=0; -- source suite/ndb/t/ndb_alter_table_copy_check.inc alter table test.cambridge add column d int; let alter_statement=ALTER TABLE test.cambridge drop column d; let other_statement=SELECT a,b from test.cambridge order by a lock in share mode; let expected_alter_error=0; let expected_other_error=0; -- source suite/ndb/t/ndb_alter_table_copy_check.inc alter table test.cambridge add column d int; let alter_statement=ALTER TABLE test.cambridge drop column d; let other_statement=SELECT a,b from test.cambridge order by a for update; let expected_alter_error=0; let expected_other_error=0; -- source suite/ndb/t/ndb_alter_table_copy_check.inc alter table test.cambridge add column d int; let alter_statement=ALTER TABLE test.oxford drop column d; let other_statement=SELECT a,length(c) from test.oxford order by a; let expected_alter_error=0; let expected_other_error=0; -- source suite/ndb/t/ndb_alter_table_copy_check.inc alter table test.oxford add column d int; let alter_statement=ALTER TABLE test.oxford drop column d; let other_statement=SELECT a,length(c) from test.oxford order by a lock in share mode; let expected_alter_error=0; let expected_other_error=0; -- source suite/ndb/t/ndb_alter_table_copy_check.inc alter table test.oxford add column d int; let alter_statement=ALTER TABLE test.oxford drop column d; let other_statement=SELECT a,length(c) from test.oxford order by a for update; let expected_alter_error=0; let expected_other_error=0; -- source suite/ndb/t/ndb_alter_table_copy_check.inc alter table test.oxford add column d int; --echo SELECT by PK let alter_statement=ALTER TABLE test.cambridge drop column d; let other_statement=SELECT a,b from test.cambridge where a=3; let expected_alter_error=0; let expected_other_error=0; -- source suite/ndb/t/ndb_alter_table_copy_check.inc alter table test.cambridge add column d int; let alter_statement=ALTER TABLE test.cambridge drop column d; let other_statement=SELECT a,b from test.cambridge where a=3 lock in share mode; let expected_alter_error=0; let expected_other_error=0; -- source suite/ndb/t/ndb_alter_table_copy_check.inc alter table test.cambridge add column d int; let alter_statement=ALTER TABLE test.cambridge drop column d; let other_statement=SELECT a,b from test.cambridge where a=3 for update; let expected_alter_error=0; let expected_other_error=0; -- source suite/ndb/t/ndb_alter_table_copy_check.inc alter table test.cambridge add column d int; let alter_statement=ALTER TABLE test.oxford drop column d; let other_statement=SELECT a,length(c) from test.oxford where a=3; let expected_alter_error=0; let expected_other_error=0; -- source suite/ndb/t/ndb_alter_table_copy_check.inc alter table test.oxford add column d int; let alter_statement=ALTER TABLE test.oxford drop column d; let other_statement=SELECT a,length(c) from test.oxford where a=3 lock in share mode; let expected_alter_error=0; let expected_other_error=0; -- source suite/ndb/t/ndb_alter_table_copy_check.inc alter table test.oxford add column d int; let alter_statement=ALTER TABLE test.oxford drop column d; let other_statement=SELECT a,length(c) from test.oxford where a=3 for update; let expected_alter_error=0; let expected_other_error=0; -- source suite/ndb/t/ndb_alter_table_copy_check.inc alter table test.oxford add column d int; --echo NULL effect modifications let alter_statement=ALTER TABLE test.cambridge drop column d; let other_statement=INSERT into test.cambridge values (2,2,2,2); # PK clash let expected_alter_error=0; let expected_other_error=ER_DUP_ENTRY; -- source suite/ndb/t/ndb_alter_table_copy_check.inc alter table test.cambridge add column d int; let alter_statement=ALTER TABLE test.cambridge drop column d; let other_statement=UPDATE test.cambridge set b=888 where a=888; # Null change let expected_alter_error=0; let expected_other_error=0; -- source suite/ndb/t/ndb_alter_table_copy_check.inc alter table test.cambridge add column d int; let alter_statement=ALTER TABLE test.cambridge drop column d; let other_statement=UPDATE test.cambridge set b=b; # Null change let expected_alter_error=0; let expected_other_error=0; -- source suite/ndb/t/ndb_alter_table_copy_check.inc alter table test.cambridge add column d int; let alter_statement=ALTER TABLE test.cambridge drop column d; let other_statement=DELETE from test.cambridge where a=888; # No such row let expected_alter_error=0; let expected_other_error=0; -- source suite/ndb/t/ndb_alter_table_copy_check.inc alter table test.cambridge add column d int; let alter_statement=ALTER TABLE test.oxford drop column d; let other_statement=INSERT into test.oxford values (2,2,repeat('A', 1000),2); # PK clash let expected_alter_error=0; let expected_other_error=ER_DUP_ENTRY; -- source suite/ndb/t/ndb_alter_table_copy_check.inc alter table test.oxford add column d int; let alter_statement=ALTER TABLE test.oxford drop column d; let other_statement=UPDATE test.oxford set b=888 where a=888; # Null change let expected_alter_error=0; let expected_other_error=0; -- source suite/ndb/t/ndb_alter_table_copy_check.inc alter table test.oxford add column d int; let alter_statement=ALTER TABLE test.oxford drop column d; let other_statement=UPDATE test.oxford set c=c; # Null change let expected_alter_error=0; let expected_other_error=0; -- source suite/ndb/t/ndb_alter_table_copy_check.inc alter table test.oxford add column d int; let alter_statement=ALTER TABLE test.oxford drop column d; let other_statement=DELETE from test.oxford where a=888; # No such row let expected_alter_error=0; let expected_other_error=0; -- source suite/ndb/t/ndb_alter_table_copy_check.inc alter table test.oxford add column d int; --echo ------------------------------------------------ --echo FK checks are unaffected by parent copying alter --echo ------------------------------------------------ update cambridge set b=a; # Setup FK via secondary keys allowing cascading update + delete... create table bristol (a int primary key, b int, c int, foreign key (b) references cambridge (b) on update cascade on delete cascade) engine=ndb; select a from cambridge order by a; insert into bristol select a,a,a from cambridge; # Add new child row let alter_statement=ALTER TABLE test.cambridge drop column d; let other_statement=INSERT INTO test.bristol values (40,4,40); let expected_alter_error=0; let expected_other_error=0; -- source suite/ndb/t/ndb_alter_table_copy_check.inc alter table test.cambridge add column d int; # Update child to refer to different parent row let alter_statement=ALTER TABLE test.cambridge drop column d; let other_statement=UPDATE test.bristol set b=4 where b=2; let expected_alter_error=0; let expected_other_error=0; -- source suite/ndb/t/ndb_alter_table_copy_check.inc alter table test.cambridge add column d int; # Delete child row(s) let alter_statement=ALTER TABLE test.cambridge drop column d; let other_statement=DELETE FROM test.bristol; let expected_alter_error=0; let expected_other_error=0; -- source suite/ndb/t/ndb_alter_table_copy_check.inc alter table test.cambridge add column d int; drop table bristol; --echo --------------------------------------------------------- --echo Show post-check exposure in copying alter check mechanism --echo --------------------------------------------------------- --echo Existing rows : update cambridge set c=a; select a,c from cambridge order by a; set DEBUG_SYNC= 'ndb.after_commit_count_check SIGNAL alter_waiting WAIT_FOR alter_go'; --send ALTER TABLE cambridge drop column d; --connection server1con2 set DEBUG_SYNC= 'now WAIT_FOR alter_waiting'; --connection server2 # Perform insert, update, delete insert into test.cambridge values (20,20,20,20), (21,21,21,21), (22,22,22,22), (23,23,23,23); update test.cambridge set c=1000; delete from test.cambridge where a=6; --echo Committed table content : select a,c from test.cambridge order by a; --connection server1con2 set DEBUG_SYNC= 'now SIGNAL alter_go'; --connection server1 # Expect error-free ALTER table --reap --echo Committed table content after ALTER --echo Changes have been lost select a,c from test.cambridge order by a; set DEBUG_SYNC='RESET'; # TODO NdbAPI drop table oxford; drop table cambridge; # Test behaviour where different fragment replicas have # different commit counts --echo Create RP, RB and FR tables create table mab_rp (a int primary key, b int, c int, d int) engine=ndb COMMENT='NDB_TABLE=READ_BACKUP=0'; create table mab_rb (a int primary key, b int, c int, d int) engine=ndb COMMENT='NDB_TABLE=READ_BACKUP=1'; create table mab_fr (a int primary key, b int, c int, d int) engine=ndb COMMENT='NDB_TABLE=FULLY_REPLICATED=1'; --echo Create some data in all table types insert into mab_rp values (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4), (5,5,5,5); insert into mab_rp select a+5, b+5, c+5, d+5 from mab_rp; insert into mab_rp select a+10, b+10, c+10, d+10 from mab_rp; insert into mab_rp select a+20, b+20, c+20, d+20 from mab_rp; insert into mab_rb select * from mab_rp; insert into mab_fr select * from mab_rp; --echo Perform some updates, so commit count != row count update mab_rp set b=b+1; update mab_rb set b=b+1; update mab_fr set b=b+1; --echo Check stable commit counts --echo 4 node cluster with 2 replicas ((1,2), (3,4)) --echo Split into two logical sub clusters with (at least) all fragments A (1,3), B (2,4) --echo Show how (sum of) commit counts in each sub cluster diverge over node restart select sum(tot_commits) into @rp_commits_a from ndbinfo.operations_per_fragment where fq_name='test/def/mab_rp' and (node_id=1 or node_id=3); select sum(tot_commits) = @rp_commits_a as rp_commit_counts_same from ndbinfo.operations_per_fragment where fq_name='test/def/mab_rp' and (node_id=2 or node_id=4); select sum(tot_commits) into @rb_commits_a from ndbinfo.operations_per_fragment where fq_name='test/def/mab_rb' and (node_id=1 or node_id=3); select sum(tot_commits) = @rb_commits_a as rb_commits_counts_same from ndbinfo.operations_per_fragment where fq_name='test/def/mab_rb' and (node_id=2 or node_id=4); select sum(tot_commits) into @fr_commits_a from ndbinfo.operations_per_fragment where fq_name='test/def/mab_fr' and (node_id=1 or node_id=3); select sum(tot_commits) = @fr_commits_a as fr_commit_counts_same from ndbinfo.operations_per_fragment where fq_name='test/def/mab_fr' and (node_id=2 or node_id=4); --echo Restart one data node to cause different replicas to --echo have different commit counts --exec $NDB_MGM -e "1 RESTART -n -I" --exec $NDB_WAITER --nowait-nodes=2,3,4 --not-started > /dev/null --exec $NDB_MGM -e "1 START" --exec $NDB_WAITER > /dev/null --echo Node restarted --echo Show commit counts on A changed select sum(tot_commits) = @rp_commits_a as rp_commits_a_same from ndbinfo.operations_per_fragment where fq_name='test/def/mab_rp' and (node_id=1 or node_id=3); select sum(tot_commits) = @rb_commits_a as rb_commits_a_same from ndbinfo.operations_per_fragment where fq_name='test/def/mab_rb' and (node_id=1 or node_id=3); select sum(tot_commits) = @fr_commits_a as fr_commits_a_same from ndbinfo.operations_per_fragment where fq_name='test/def/mab_fr' and (node_id=1 or node_id=3); --echo Show commit counts no longer the same A vs B select sum(tot_commits) into @rp_commits_a from ndbinfo.operations_per_fragment where fq_name='test/def/mab_rp' and (node_id=1 or node_id=3); select sum(tot_commits) = @rp_commits_a as rp_commit_counts_same from ndbinfo.operations_per_fragment where fq_name='test/def/mab_rp' and (node_id=2 or node_id=4); select sum(tot_commits) into @rb_commits_a from ndbinfo.operations_per_fragment where fq_name='test/def/mab_rb' and (node_id=1 or node_id=3); select sum(tot_commits) = @rb_commits_a as rb_commit_counts_same from ndbinfo.operations_per_fragment where fq_name='test/def/mab_rb' and (node_id=2 or node_id=4); select sum(tot_commits) into @fr_commits_a from ndbinfo.operations_per_fragment where fq_name='test/def/mab_fr' and (node_id=1 or node_id=3); select sum(tot_commits) = @fr_commits_a as fr_commits_counts_same from ndbinfo.operations_per_fragment where fq_name='test/def/mab_fr' and (node_id=2 or node_id=4); --echo Check that ALTER TABLE can handle this situation ALTER TABLE mab_rp DROP COLUMN d; ALTER TABLE mab_rb DROP COLUMN d; ALTER TABLE mab_fr DROP COLUMN d; --echo Check that there are no resources left in use select count(1) from ndbinfo.cluster_transactions; select count(1) from ndbinfo.cluster_operations; drop table mab_rp; drop table mab_rb; drop table mab_fr;