github.com/pingcap/tiflow@v0.0.0-20240520035814-5bf52d54e205/dm/tests/shardddl_optimistic/run.sh (about)

     1  #!/bin/bash
     2  
     3  set -eu
     4  
     5  cur=$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)
     6  source $cur/../_utils/test_prepare
     7  WORK_DIR=$TEST_DIR/$TEST_NAME
     8  source $cur/../_utils/shardddl_lib.sh
     9  
    10  function DM_DIFFERENT_SCHEMA_FULL_CASE() {
    11  	run_sql_tidb_with_retry "select count(1) from ${shardddl}.${tb}" "count(1): 4"
    12  	run_sql_source1 "insert into ${shardddl1}.${tb1} values(5);"
    13  	run_sql_source1 "insert into ${shardddl1}.${tb2} values(6,'6');"
    14  	run_sql_source2 "insert into ${shardddl1}.${tb1} values(7,'77');"
    15  	run_sql_source2 "insert into ${shardddl1}.${tb2} values(8,'8','88');"
    16  
    17  	run_sql_source1 "alter table ${shardddl1}.${tb1} add column c text;"
    18  	# source1.tb1(a,c); source1.tb2(a,b); source2.tb1(a,c); source2.tb2(a,b,c)
    19  	run_sql_source1 "insert into ${shardddl1}.${tb1} values(9,'999');"
    20  	run_sql_source1 "insert into ${shardddl1}.${tb2} values(10,'1010');"
    21  	run_sql_source2 "insert into ${shardddl1}.${tb1} values(11,'111111');"
    22  	run_sql_source2 "insert into ${shardddl1}.${tb2} values(12,'1212','121212');"
    23  
    24  	run_sql_source2 "alter table ${shardddl1}.${tb2} drop column b;"
    25  	# source1.tb1(a,c); source1.tb2(a,b); source2.tb1(a,c); source2.tb2(a,c)
    26  	run_sql_source1 "insert into ${shardddl1}.${tb1} values(13,'131313');"
    27  	run_sql_source1 "insert into ${shardddl1}.${tb2} values(14,'1414');"
    28  	run_sql_source2 "insert into ${shardddl1}.${tb1} values(15,'151515');"
    29  	run_sql_source2 "insert into ${shardddl1}.${tb2} values(16,'161616');"
    30  
    31  	run_sql_source1 "alter table ${shardddl1}.${tb2} drop column b;"
    32  	# source1.tb1(a,c); source1.tb2(a); source2.tb1(a,c); source2.tb2(a,c)
    33  	run_sql_source1 "insert into ${shardddl1}.${tb1} values(17,'171717');"
    34  	run_sql_source1 "insert into ${shardddl1}.${tb2} values(18);"
    35  	run_sql_source2 "insert into ${shardddl1}.${tb1} values(19,'191919');"
    36  	run_sql_source2 "insert into ${shardddl1}.${tb2} values(20,'202020');"
    37  
    38  	run_sql_source1 "alter table ${shardddl1}.${tb2} add column c text;"
    39  	# source1.tb1(a,c); source1.tb2(a,c); source2.tb1(a,c); source2.tb2(a,c)
    40  	run_sql_source1 "insert into ${shardddl1}.${tb1} values(21,'212121');"
    41  	run_sql_source1 "insert into ${shardddl1}.${tb2} values(22,'222222');"
    42  	run_sql_source2 "insert into ${shardddl1}.${tb1} values(23,'232323');"
    43  	run_sql_source2 "insert into ${shardddl1}.${tb2} values(24,'242424');"
    44  
    45  	run_sql_tidb_with_retry "select count(1) from ${shardddl}.${tb}" "count(1): 24"
    46  	check_sync_diff $WORK_DIR $cur/conf/diff_config.toml
    47  }
    48  
    49  function DM_DIFFERENT_SCHEMA_FULL() {
    50  	# create table with different schema, init data, and create table in downstream manually
    51  	run_case DIFFERENT_SCHEMA_FULL "double-source-optimistic" \
    52  		"run_sql_source1 \"create table ${shardddl1}.${tb1} (a int primary key);\"; \
    53      run_sql_source1 \"create table ${shardddl1}.${tb2} (a int primary key, b varchar(10));\"; \
    54      run_sql_source2 \"create table ${shardddl1}.${tb1} (a int primary key, c text);\"; \
    55      run_sql_source2 \"create table ${shardddl1}.${tb2} (a int primary key, b varchar(10), c text);\"; \
    56      run_sql_source1 \"insert into ${shardddl1}.${tb1} values(1);\"; \
    57      run_sql_source1 \"insert into ${shardddl1}.${tb2} values(2,'22');\"; \
    58      run_sql_source2 \"insert into ${shardddl1}.${tb1} values(3,'333');\"; \
    59      run_sql_source2 \"insert into ${shardddl1}.${tb2} values(4,'44','444');\"; \
    60      run_sql_tidb \"create database if not exists ${shardddl};\"; \
    61      run_sql_tidb \"create table ${shardddl}.${tb} (a int primary key, b varchar(10), c text);\"" \
    62  		"clean_table" "optimistic"
    63  }
    64  
    65  function DM_DIFFERENT_SCHEMA_INCREMENTAL_CASE() {
    66  	run_sql_tidb_with_retry "select count(1) from ${shardddl}.${tb}" "count(1): 4"
    67  
    68  	# get checkpoint
    69  	source1_status=($(get_master_status $MYSQL_HOST1 $MYSQL_PORT1))
    70  	source2_status=($(get_master_status $MYSQL_HOST2 $MYSQL_PORT2))
    71  
    72  	run_dm_ctl $WORK_DIR "127.0.0.1:$MASTER_PORT" \
    73  		"pause-task test" \
    74  		"\"result\": true" 3
    75  
    76  	# save schema
    77  	curl -X GET http://127.0.0.1:8261/api/v1/tasks/test/sources/mysql-replica-01/schemas/${shardddl1}/${tb1} | jq -r .schema_create_sql >$WORK_DIR/schema11.sql
    78  	curl -X GET http://127.0.0.1:8261/api/v1/tasks/test/sources/mysql-replica-01/schemas/${shardddl1}/${tb2} | jq -r .schema_create_sql >$WORK_DIR/schema12.sql
    79  	curl -X GET http://127.0.0.1:8261/api/v1/tasks/test/sources/mysql-replica-02/schemas/${shardddl1}/${tb1} | jq -r .schema_create_sql >$WORK_DIR/schema21.sql
    80  	curl -X GET http://127.0.0.1:8261/api/v1/tasks/test/sources/mysql-replica-02/schemas/${shardddl1}/${tb2} | jq -r .schema_create_sql >$WORK_DIR/schema22.sql
    81  
    82  	run_dm_ctl $WORK_DIR "127.0.0.1:$MASTER_PORT" \
    83  		"stop-task test" \
    84  		"\"result\": true" 3
    85  
    86  	# incremental data
    87  	run_sql_source1 "insert into ${shardddl1}.${tb1} values(5);"
    88  	run_sql_source1 "insert into ${shardddl1}.${tb2} values(6,'6');"
    89  	run_sql_source2 "insert into ${shardddl1}.${tb1} values(7,'77');"
    90  	run_sql_source2 "insert into ${shardddl1}.${tb2} values(8,'8','88');"
    91  
    92  	run_sql_source1 "alter table ${shardddl1}.${tb1} add column c text;"
    93  	# source1.tb1(a,c); source1.tb2(a,b); source2.tb1(a,c); source2.tb2(a,b,c)
    94  	run_sql_source1 "insert into ${shardddl1}.${tb1} values(9,'999');"
    95  	run_sql_source1 "insert into ${shardddl1}.${tb2} values(10,'1010');"
    96  	run_sql_source2 "insert into ${shardddl1}.${tb1} values(11,'111111');"
    97  	run_sql_source2 "insert into ${shardddl1}.${tb2} values(12,'1212','121212');"
    98  
    99  	run_sql_source2 "alter table ${shardddl1}.${tb2} drop column b;"
   100  	# source1.tb1(a,c); source1.tb2(a,b); source2.tb1(a,c); source2.tb2(a,c)
   101  	run_sql_source1 "insert into ${shardddl1}.${tb1} values(13,'131313');"
   102  	run_sql_source1 "insert into ${shardddl1}.${tb2} values(14,'1414');"
   103  	run_sql_source2 "insert into ${shardddl1}.${tb1} values(15,'151515');"
   104  	run_sql_source2 "insert into ${shardddl1}.${tb2} values(16,'161616');"
   105  
   106  	run_sql_source1 "alter table ${shardddl1}.${tb2} drop column b;"
   107  	# source1.tb1(a,c); source1.tb2(a); source2.tb1(a,c); source2.tb2(a,c)
   108  	run_sql_source1 "insert into ${shardddl1}.${tb1} values(17,'171717');"
   109  	run_sql_source1 "insert into ${shardddl1}.${tb2} values(18);"
   110  	run_sql_source2 "insert into ${shardddl1}.${tb1} values(19,'191919');"
   111  	run_sql_source2 "insert into ${shardddl1}.${tb2} values(20,'202020');"
   112  
   113  	run_sql_source1 "alter table ${shardddl1}.${tb2} add column c text;"
   114  	# source1.tb1(a,c); source1.tb2(a,c); source2.tb1(a,c); source2.tb2(a,c)
   115  	run_sql_source1 "insert into ${shardddl1}.${tb1} values(21,'212121');"
   116  	run_sql_source1 "insert into ${shardddl1}.${tb2} values(22,'222222');"
   117  	run_sql_source2 "insert into ${shardddl1}.${tb1} values(23,'232323');"
   118  	run_sql_source2 "insert into ${shardddl1}.${tb2} values(24,'242424');"
   119  
   120  	# start task with current checkpoint
   121  	sed "s/pos-holder/${source1_status[1]}/g" $cur/conf/double-source-optimistic-incr.yaml >$WORK_DIR/task.yaml
   122  	sed -i "s/name-holder/${source1_status[0]}/g" $WORK_DIR/task.yaml
   123  	sed -i "s/gtid-holder/${source2_status[2]}/g" $WORK_DIR/task.yaml
   124  	run_dm_ctl $WORK_DIR "127.0.0.1:$MASTER_PORT" \
   125  		"start-task $WORK_DIR/task.yaml --remove-meta"
   126  
   127  	run_dm_ctl_with_retry $WORK_DIR "127.0.0.1:$MASTER_PORT" \
   128  		"query-status test" \
   129  		"Column count doesn't match" 2
   130  
   131  	run_dm_ctl $WORK_DIR "127.0.0.1:$MASTER_PORT" \
   132  		"operate-schema set -s mysql-replica-01 test -d ${shardddl1} -t ${tb1} $WORK_DIR/schema11.sql" \
   133  		"\"result\": true" 2
   134  	run_dm_ctl $WORK_DIR "127.0.0.1:$MASTER_PORT" \
   135  		"operate-schema set -s mysql-replica-01 test -d ${shardddl1} -t ${tb2} $WORK_DIR/schema12.sql" \
   136  		"\"result\": true" 2
   137  	run_dm_ctl $WORK_DIR "127.0.0.1:$MASTER_PORT" \
   138  		"operate-schema set -s mysql-replica-02 test -d ${shardddl1} -t ${tb1} $WORK_DIR/schema21.sql" \
   139  		"\"result\": true" 2
   140  	run_dm_ctl $WORK_DIR "127.0.0.1:$MASTER_PORT" \
   141  		"operate-schema set -s mysql-replica-02 test -d ${shardddl1} -t ${tb2} $WORK_DIR/schema22.sql" \
   142  		"\"result\": true" 2
   143  
   144  	run_dm_ctl $WORK_DIR "127.0.0.1:$MASTER_PORT" \
   145  		"resume-task test"
   146  
   147  	run_sql_tidb_with_retry "select count(1) from ${shardddl}.${tb}" "count(1): 24"
   148  	check_sync_diff $WORK_DIR $cur/conf/diff_config.toml
   149  }
   150  
   151  function DM_DIFFERENT_SCHEMA_INCREMENTAL() {
   152  	# create table with different schema, init data, and create table in downstream manually
   153  	run_case DIFFERENT_SCHEMA_INCREMENTAL "double-source-optimistic" \
   154  		"run_sql_source1 \"create table ${shardddl1}.${tb1} (a int primary key);\"; \
   155      run_sql_source1 \"create table ${shardddl1}.${tb2} (a int primary key, b varchar(10));\"; \
   156      run_sql_source2 \"create table ${shardddl1}.${tb1} (a int primary key, c text);\"; \
   157      run_sql_source2 \"create table ${shardddl1}.${tb2} (a int primary key, b varchar(10), c text);\"; \
   158      run_sql_source1 \"insert into ${shardddl1}.${tb1} values(1);\"; \
   159      run_sql_source1 \"insert into ${shardddl1}.${tb2} values(2,'22');\"; \
   160      run_sql_source2 \"insert into ${shardddl1}.${tb1} values(3,'333');\"; \
   161      run_sql_source2 \"insert into ${shardddl1}.${tb2} values(4,'44','444');\"; \
   162      run_sql_tidb \"create database if not exists ${shardddl};\"; \
   163      run_sql_tidb \"create table ${shardddl}.${tb} (a int primary key, b varchar(10), c text);\"" \
   164  		"clean_table" "optimistic"
   165  }
   166  
   167  function DM_RESTART_TASK_MASTER_WORKER_CASE() {
   168  	run_sql_tidb_with_retry "select count(1) from ${shardddl}.${tb}" "count(1): 4"
   169  	run_sql_source1 "insert into ${shardddl1}.${tb1} values(5);"
   170  	run_sql_source1 "insert into ${shardddl1}.${tb2} values(6,'6');"
   171  	run_sql_source2 "insert into ${shardddl1}.${tb1} values(7,'77');"
   172  	run_sql_source2 "insert into ${shardddl1}.${tb2} values(8,'8','88');"
   173  
   174  	run_sql_source1 "alter table ${shardddl1}.${tb1} add column c text;"
   175  	check_log_contain_with_retry "finish to handle ddls in optimistic shard mode.*alter table ${shardddl1}.${tb1} add column c text" \
   176  		$WORK_DIR/worker1/log/dm-worker.log $WORK_DIR/worker2/log/dm-worker.log
   177  	random_restart
   178  
   179  	# source1.tb1(a,c); source1.tb2(a,b); source2.tb1(a,c); source2.tb2(a,b,c)
   180  	run_sql_source1 "insert into ${shardddl1}.${tb1} values(9,'999');"
   181  	run_sql_source1 "insert into ${shardddl1}.${tb2} values(10,'1010');"
   182  	run_sql_source2 "insert into ${shardddl1}.${tb1} values(11,'111111');"
   183  	run_sql_source2 "insert into ${shardddl1}.${tb2} values(12,'1212','121212');"
   184  
   185  	run_sql_source2 "alter table ${shardddl1}.${tb2} drop column b;"
   186  	check_log_contain_with_retry "finish to handle ddls in optimistic shard mode.*alter table ${shardddl1}.${tb2} drop column b" \
   187  		$WORK_DIR/worker1/log/dm-worker.log $WORK_DIR/worker2/log/dm-worker.log
   188  	random_restart
   189  
   190  	# source1.tb1(a,c); source1.tb2(a,b); source2.tb1(a,c); source2.tb2(a,c)
   191  	run_sql_source1 "insert into ${shardddl1}.${tb1} values(13,'131313');"
   192  	run_sql_source1 "insert into ${shardddl1}.${tb2} values(14,'1414');"
   193  	run_sql_source2 "insert into ${shardddl1}.${tb1} values(15,'151515');"
   194  	run_sql_source2 "insert into ${shardddl1}.${tb2} values(16,'161616');"
   195  
   196  	run_sql_source1 "alter table ${shardddl1}.${tb2} drop column b;"
   197  	check_log_contain_with_retry "finish to handle ddls in optimistic shard mode.*alter table ${shardddl1}.${tb2} drop column b" \
   198  		$WORK_DIR/worker1/log/dm-worker.log
   199  	check_log_contain_with_retry "finish to handle ddls in optimistic shard mode.*alter table ${shardddl1}.${tb2} drop column b" \
   200  		$WORK_DIR/worker2/log/dm-worker.log
   201  	random_restart
   202  
   203  	# source1.tb1(a,c); source1.tb2(a); source2.tb1(a,c); source2.tb2(a,c)
   204  	run_sql_source1 "insert into ${shardddl1}.${tb1} values(17,'171717');"
   205  	run_sql_source1 "insert into ${shardddl1}.${tb2} values(18);"
   206  	run_sql_source2 "insert into ${shardddl1}.${tb1} values(19,'191919');"
   207  	run_sql_source2 "insert into ${shardddl1}.${tb2} values(20,'202020');"
   208  
   209  	run_sql_source1 "alter table ${shardddl1}.${tb2} add column c text;"
   210  	check_log_contain_with_retry "finish to handle ddls in optimistic shard mode.*alter table ${shardddl1}.${tb2} add column c text" \
   211  		$WORK_DIR/worker1/log/dm-worker.log $WORK_DIR/worker2/log/dm-worker.log
   212  	random_restart
   213  
   214  	# source1.tb1(a,c); source1.tb2(a,c); source2.tb1(a,c); source2.tb2(a,c)
   215  	run_sql_source1 "insert into ${shardddl1}.${tb1} values(21,'212121');"
   216  	run_sql_source1 "insert into ${shardddl1}.${tb2} values(22,'222222');"
   217  	run_sql_source2 "insert into ${shardddl1}.${tb1} values(23,'232323');"
   218  	run_sql_source2 "insert into ${shardddl1}.${tb2} values(24,'242424');"
   219  
   220  	run_sql_tidb_with_retry "select count(1) from ${shardddl}.${tb}" "count(1): 24"
   221  	check_sync_diff $WORK_DIR $cur/conf/diff_config.toml
   222  }
   223  
   224  function DM_RESTART_TASK_MASTER_WORKER() {
   225  	# create table with different schema, init data, and create table in downstream manually
   226  	run_case RESTART_TASK_MASTER_WORKER "double-source-optimistic" \
   227  		"run_sql_source1 \"create table ${shardddl1}.${tb1} (a int primary key);\"; \
   228     	run_sql_source1 \"create table ${shardddl1}.${tb2} (a int primary key, b varchar(10));\"; \
   229     	run_sql_source2 \"create table ${shardddl1}.${tb1} (a int primary key, c text);\"; \
   230     	run_sql_source2 \"create table ${shardddl1}.${tb2} (a int primary key, b varchar(10), c text);\"; \
   231     	run_sql_source1 \"insert into ${shardddl1}.${tb1} values(1);\"; \
   232     	run_sql_source1 \"insert into ${shardddl1}.${tb2} values(2,'22');\"; \
   233     	run_sql_source2 \"insert into ${shardddl1}.${tb1} values(3,'333');\"; \
   234     	run_sql_source2 \"insert into ${shardddl1}.${tb2} values(4,'44','444');\"; \
   235     	run_sql_tidb \"create database if not exists ${shardddl};\"; \
   236     	run_sql_tidb \"create table ${shardddl}.${tb} (a int primary key, b varchar(10), c text);\"" \
   237  		"clean_table" "optimistic"
   238  }
   239  
   240  function DM_STOP_TASK_FOR_A_SOURCE_CASE() {
   241  	run_sql_source1 "insert into ${shardddl1}.${tb1} values(1);"
   242  	run_sql_source1 "insert into ${shardddl1}.${tb2} values(2);"
   243  	run_sql_source2 "insert into ${shardddl1}.${tb1} values(3);"
   244  	run_sql_source2 "insert into ${shardddl1}.${tb2} values(4);"
   245  
   246  	run_sql_source1 "alter table ${shardddl1}.${tb1} add column b varchar(10);"
   247  	run_sql_source1 "alter table ${shardddl1}.${tb2} add column b varchar(10);"
   248  	run_sql_source2 "alter table ${shardddl1}.${tb1} add column b varchar(10);"
   249  	run_sql_source2 "alter table ${shardddl1}.${tb2} add column b varchar(10);"
   250  
   251  	run_sql_source1 "insert into ${shardddl1}.${tb1} values(5,'aaa');"
   252  	run_sql_source1 "insert into ${shardddl1}.${tb2} values(6,'bbb');"
   253  	run_sql_source2 "insert into ${shardddl1}.${tb1} values(7,'ccc');"
   254  	run_sql_source2 "insert into ${shardddl1}.${tb2} values(8,'ddd');"
   255  
   256  	run_sql_source1 "alter table ${shardddl1}.${tb1} add column c text;"
   257  	run_sql_source1 "insert into ${shardddl1}.${tb1} values(9,'eee','eee');"
   258  	run_sql_source1 "alter table ${shardddl1}.${tb2} drop column b;"
   259  	run_sql_source1 "insert into ${shardddl1}.${tb2} values(10);"
   260  	run_sql_source2 "alter table ${shardddl1}.${tb1} add column c text;"
   261  	run_sql_source2 "insert into ${shardddl1}.${tb1} values(11,'fff','fff');"
   262  	run_sql_source2 "alter table ${shardddl1}.${tb2} drop column b;"
   263  	run_sql_source2 "insert into ${shardddl1}.${tb2} values(12);"
   264  
   265  	run_sql_tidb_with_retry "select count(1) from ${shardddl}.${tb}" "count(1): 12"
   266  
   267  	run_dm_ctl $WORK_DIR "127.0.0.1:$MASTER_PORT" \
   268  		"stop-task test -s mysql-replica-02" \
   269  		"\"result\": true" 2
   270  
   271  	run_sql_source1 "alter table ${shardddl1}.${tb1} drop column b;"
   272  	run_sql_source1 "insert into ${shardddl1}.${tb1} values(13,'ggg');"
   273  	run_sql_source1 "alter table ${shardddl1}.${tb2} add column c text;"
   274  	run_sql_source1 "insert into ${shardddl1}.${tb2} values(14,'hhh');"
   275  
   276  	run_sql_tidb_with_retry "select count(1) from ${shardddl}.${tb}" "count(1): 14"
   277  	run_sql_tidb_with_retry "select count(1) from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='${shardddl}' AND TABLE_NAME='${tb}';" \
   278  		"count(1): 2"
   279  
   280  	run_dm_ctl $WORK_DIR "127.0.0.1:$MASTER_PORT" \
   281  		"start-task $cur/conf/double-source-optimistic.yaml -s mysql-replica-02" \
   282  		"\"result\": true" 2
   283  
   284  	run_sql_source1 "insert into ${shardddl1}.${tb1} values(15,'iii');"
   285  	run_sql_source1 "insert into ${shardddl1}.${tb1} values(16,'jjj');"
   286  	run_sql_source2 "alter table ${shardddl1}.${tb1} drop column b;"
   287  	run_sql_source2 "insert into ${shardddl1}.${tb1} values(17,'kkk');"
   288  	run_sql_source2 "alter table ${shardddl1}.${tb2} add column c text;"
   289  	run_sql_source2 "insert into ${shardddl1}.${tb2} values(18,'lll');"
   290  
   291  	check_sync_diff $WORK_DIR $cur/conf/diff_config.toml
   292  }
   293  
   294  function DM_STOP_TASK_FOR_A_SOURCE() {
   295  	run_case STOP_TASK_FOR_A_SOURCE "double-source-optimistic" "init_table 111 112 211 212" "clean_table" "optimistic"
   296  }
   297  
   298  function DM_UPDATE_BA_ROUTE_CASE() {
   299  	run_sql_source1 "insert into ${shardddl1}.${tb1} values(1);"
   300  	run_sql_source1 "insert into ${shardddl2}.${tb1} values(2);"
   301  	run_sql_source2 "insert into ${shardddl1}.${tb1} values(3);"
   302  	run_sql_source2 "insert into ${shardddl2}.${tb1} values(4);"
   303  
   304  	run_sql_source1 "alter table ${shardddl1}.${tb1} add column new_col1 int"
   305  	run_sql_source1 "alter table ${shardddl2}.${tb1} add column new_col1 int"
   306  	run_sql_source2 "alter table ${shardddl1}.${tb1} add column new_col1 int"
   307  	run_sql_source2 "alter table ${shardddl2}.${tb1} add column new_col1 int"
   308  
   309  	run_sql_source1 "insert into ${shardddl1}.${tb1} values(5,5);"
   310  	run_sql_source1 "insert into ${shardddl2}.${tb1} values(6,6);"
   311  	run_sql_source2 "insert into ${shardddl1}.${tb1} values(7,7);"
   312  	run_sql_source2 "insert into ${shardddl2}.${tb1} values(8,8);"
   313  
   314  	# source1 db2.tb1 add column and then drop column
   315  	run_sql_source1 "alter table ${shardddl2}.${tb1} add column new_col2 int"
   316  	run_sql_source1 "insert into ${shardddl2}.${tb1} values(9,9,9);"
   317  	run_sql_source1 "alter table ${shardddl2}.${tb1} drop column new_col2"
   318  	run_sql_source1 "insert into ${shardddl2}.${tb1} values(10,10);"
   319  
   320  	# source1.db1.tb1, source2.db2.tb1 add column
   321  	run_sql_source1 "alter table ${shardddl1}.${tb1} add column new_col3 int"
   322  	run_sql_source1 "insert into ${shardddl1}.${tb1} values(11,11,11);"
   323  	run_sql_source2 "alter table ${shardddl2}.${tb1} add column new_col3 int"
   324  	run_sql_source2 "insert into ${shardddl2}.${tb1} values(12,12,12);"
   325  
   326  	# source2 db1.tb1 drop column
   327  	run_sql_source2 "alter table ${shardddl1}.${tb1} drop column new_col1"
   328  	run_sql_source2 "insert into ${shardddl1}.${tb1} values(13);"
   329  
   330  	run_sql_tidb_with_retry "select count(1) from ${shardddl}.${tb}" "count(1): 13"
   331  
   332  	run_dm_ctl $WORK_DIR "127.0.0.1:$MASTER_PORT" \
   333  		"stop-task test" \
   334  		"\"result\": true" 3
   335  
   336  	sed 's/do-dbs: \["shardddl1","shardddl2"\]/do-dbs: \["shardddl1"\]/g' $cur/conf/double-source-optimistic.yaml >$WORK_DIR/task.yaml
   337  
   338  	# source1: db1.tb1(id,new_col1,new_col3)
   339  	# source2: db1.tb1(id)
   340  	run_dm_ctl $WORK_DIR "127.0.0.1:$MASTER_PORT" \
   341  		"start-task $WORK_DIR/task.yaml" \
   342  		"\"result\": true" 3
   343  
   344  	# no lock exist when task begin
   345  	run_dm_ctl_with_retry $WORK_DIR "127.0.0.1:$MASTER_PORT" \
   346  		"show-ddl-locks" \
   347  		"no DDL lock exists" 1
   348  
   349  	run_sql_source1 "insert into ${shardddl1}.${tb1} values(14,14,14);"
   350  	run_sql_source2 "insert into ${shardddl1}.${tb1} values(15);"
   351  	run_sql_tidb_with_retry "select count(1) from ${shardddl}.${tb};" "count(1): 15"
   352  
   353  	run_sql_source1 "alter table ${shardddl1}.${tb1} drop column new_col1"
   354  	run_sql_source2 "alter table ${shardddl1}.${tb1} add column new_col3 int"
   355  	run_sql_source1 "insert into ${shardddl1}.${tb1} values(16,16);"
   356  	run_sql_source2 "insert into ${shardddl1}.${tb1} values(17,17);"
   357  	run_sql_tidb_with_retry "select count(1) from ${shardddl}.${tb};" "count(1): 17"
   358  
   359  	run_dm_ctl $WORK_DIR "127.0.0.1:$MASTER_PORT" \
   360  		"stop-task test" \
   361  		"\"result\": true" 3
   362  	run_dm_ctl $WORK_DIR "127.0.0.1:$MASTER_PORT" \
   363  		"start-task $cur/conf/double-source-optimistic.yaml" \
   364  		"\"result\": true" 3
   365  
   366  	# source1: db1.tb1(id,new_col3), db2.tb1(id,new_col1)
   367  	# source2: db1.tb1(id,new_col3), db2.tb1(id,new_col1,new_col3)
   368  	run_sql_source1 "insert into ${shardddl1}.${tb1} values(18,18);"
   369  	run_sql_source1 "insert into ${shardddl2}.${tb1} values(19,19);"
   370  	run_sql_source2 "insert into ${shardddl1}.${tb1} values(20,20);"
   371  	run_sql_source2 "insert into ${shardddl2}.${tb1} values(21,21,21);"
   372  
   373  	run_dm_ctl_with_retry $WORK_DIR "127.0.0.1:$MASTER_PORT" \
   374  		"query-status test" \
   375  		"Unknown column 'new_col1' in 'field list'" 2
   376  
   377  	run_sql_tidb "alter table ${shardddl}.${tb} add column new_col1 int"
   378  
   379  	run_dm_ctl $WORK_DIR "127.0.0.1:$MASTER_PORT" \
   380  		"resume-task test"
   381  
   382  	run_sql_source1 "alter table ${shardddl2}.${tb1} drop column new_col1"
   383  	run_sql_source2 "alter table ${shardddl2}.${tb1} drop column new_col1"
   384  	run_sql_source1 "alter table ${shardddl2}.${tb1} add column new_col3 int"
   385  
   386  	run_sql_source1 "insert into ${shardddl1}.${tb1} values(22,22);"
   387  	run_sql_source1 "insert into ${shardddl2}.${tb1} values(23,23);"
   388  	run_sql_source2 "insert into ${shardddl1}.${tb1} values(24,24);"
   389  	run_sql_source2 "insert into ${shardddl2}.${tb1} values(25,25);"
   390  
   391  	check_sync_diff $WORK_DIR $cur/conf/diff_config.toml
   392  }
   393  
   394  function DM_UPDATE_BA_ROUTE() {
   395  	run_case UPDATE_BA_ROUTE "double-source-optimistic" "init_table 111 121 211 221" "clean_table" "optimistic"
   396  }
   397  
   398  function DM_CREATE_DROP_TABLE_CASE() {
   399  	run_sql_source1 "insert into ${shardddl1}.${tb1} values(1);"
   400  	run_sql_source1 "insert into ${shardddl2}.${tb1} values(2);"
   401  	run_sql_source2 "insert into ${shardddl1}.${tb1} values(3);"
   402  	run_sql_source2 "insert into ${shardddl2}.${tb1} values(4);"
   403  
   404  	# add source1.db1.table2
   405  	# drop source1.db1.table1
   406  	run_sql_source1 "create table ${shardddl1}.${tb2} (id int primary key);"
   407  	run_sql_source1 "insert into ${shardddl1}.${tb2} values(5);"
   408  	run_sql_source1 "drop table ${shardddl1}.${tb1};"
   409  
   410  	run_sql_source1 "alter table ${shardddl1}.${tb2} add column new_col1 int"
   411  	run_sql_source1 "alter table ${shardddl2}.${tb1} add column new_col1 int"
   412  	run_sql_source2 "alter table ${shardddl1}.${tb1} add column new_col1 int"
   413  	run_sql_source2 "alter table ${shardddl2}.${tb1} add column new_col1 int"
   414  
   415  	run_sql_source1 "insert into ${shardddl2}.${tb1} values(6,6);"
   416  	run_sql_source2 "insert into ${shardddl1}.${tb1} values(7,7);"
   417  	run_sql_source2 "insert into ${shardddl2}.${tb1} values(8,8);"
   418  	run_sql_source1 "insert into ${shardddl1}.${tb2} values(9,9);"
   419  
   420  	# add source2.db1.table2 with a smaller schema
   421  	# drop source2.db1.table1
   422  	run_sql_source2 "create table ${shardddl1}.${tb2} (id int primary key);"
   423  	run_sql_source2 "insert into ${shardddl1}.${tb2} values(10);"
   424  	run_sql_source2 "drop table ${shardddl1}.${tb1};"
   425  
   426  	run_sql_source2 "alter table ${shardddl2}.${tb1} drop column new_col1"
   427  
   428  	run_sql_source1 "insert into ${shardddl1}.${tb2} values(11,11);"
   429  	run_sql_source1 "insert into ${shardddl2}.${tb1} values(12,12);"
   430  	run_sql_source2 "insert into ${shardddl1}.${tb2} values(13);"
   431  	run_sql_source2 "insert into ${shardddl1}.${tb2} values(14);"
   432  
   433  	# drop source1.db2.table1
   434  	run_sql_source1 "drop table ${shardddl2}.${tb1};"
   435  	# all table has no new_col1
   436  	run_sql_source1 "alter table ${shardddl1}.${tb2} drop column new_col1"
   437  
   438  	run_sql_source1 "insert into ${shardddl1}.${tb2} values(15);"
   439  	run_sql_source2 "insert into ${shardddl1}.${tb2} values(16);"
   440  	run_sql_source2 "insert into ${shardddl2}.${tb1} values(17);"
   441  
   442  	run_sql_tidb_with_retry "select count(1) from ${shardddl}.${tb}" "count(1): 17"
   443  
   444  	run_sql_source1 "alter table ${shardddl1}.${tb2} add column new_col2 int"
   445  	run_sql_source2 "alter table ${shardddl1}.${tb2} add column new_col2 int"
   446  	run_sql_source2 "alter table ${shardddl2}.${tb1} add column new_col2 int"
   447  
   448  	run_sql_source1 "insert into ${shardddl1}.${tb2} values(18,18);"
   449  	run_sql_source2 "insert into ${shardddl1}.${tb2} values(19,19);"
   450  	run_sql_source2 "insert into ${shardddl2}.${tb1} values(20,20);"
   451  
   452  	run_sql_tidb_with_retry "select count(1) from ${shardddl}.${tb}" "count(1): 20"
   453  	run_sql_tidb_with_retry "select count(1) from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='${shardddl}' AND TABLE_NAME='${tb}';" \
   454  		"count(1): 2"
   455  	run_dm_ctl $WORK_DIR "127.0.0.1:$MASTER_PORT" \
   456  		"show-ddl-locks" \
   457  		"no DDL lock exists" 1
   458  }
   459  
   460  function DM_CREATE_DROP_TABLE() {
   461  	run_case CREATE_DROP_TABLE "double-source-optimistic" "init_table 111 121 211 221" "clean_table" "optimistic"
   462  }
   463  
   464  function run() {
   465  	init_cluster
   466  	init_database
   467  
   468  	DM_DIFFERENT_SCHEMA_FULL
   469  	DM_DIFFERENT_SCHEMA_INCREMENTAL
   470  	DM_RESTART_TASK_MASTER_WORKER
   471  	DM_STOP_TASK_FOR_A_SOURCE
   472  	DM_UPDATE_BA_ROUTE
   473  	DM_CREATE_DROP_TABLE
   474  }
   475  
   476  cleanup_data $shardddl
   477  cleanup_data $shardddl1
   478  cleanup_data $shardddl2
   479  # also cleanup dm processes in case of last run failed
   480  cleanup_process $*
   481  run $*
   482  cleanup_process $*
   483  
   484  echo "[$(date)] <<<<<< test case $TEST_NAME success! >>>>>>"