github.com/pingcap/tiflow@v0.0.0-20240520035814-5bf52d54e205/tests/integration_tests/csv_storage_multi_tables_ddl/data/test.sql (about)

     1  drop database if exists `multi_tables_ddl_test`;
     2  create database `multi_tables_ddl_test`;
     3  use `multi_tables_ddl_test`;
     4  
     5  create table t1 (
     6   value64  bigint unsigned  not null,
     7   primary key(value64)
     8  );
     9  insert into t1 values(17156792991891826145);
    10  insert into t1 values(91891826145);
    11  delete from t1 where value64=17156792991891826145;
    12  update t1 set value64=17156792991891826;
    13  update t1 set value64=56792991891826;
    14  
    15  rename table t1 to t1_1;
    16  
    17  create table t2 (
    18   value64  bigint unsigned  not null,
    19   primary key(value64)
    20  );
    21  insert into t2 values(17156792991891826145);
    22  insert into t2 values(91891826145);
    23  delete from t2 where value64=91891826145;
    24  update t2 set value64=17156792991891826;
    25  update t2 set value64=56792991891826;
    26  
    27  rename table t2 to t2_2;
    28  
    29  create table t1 (
    30   value64  bigint unsigned  not null,
    31   value32  integer          not null,
    32   primary key(value64, value32)
    33  );
    34  
    35  create table t2 (
    36   value64  bigint unsigned  not null,
    37   value32  integer          not null,
    38   primary key(value64, value32)
    39  );
    40  
    41  create table t3 (
    42   value64  bigint unsigned  not null,
    43   value32  integer          not null,
    44   primary key(value64, value32)
    45  );
    46  
    47  create table t4 (
    48   value64  bigint unsigned  not null,
    49   value32  integer          not null,
    50   primary key(value64, value32)
    51  );
    52  
    53  /*use by error changefeed*/
    54  create table t5 (
    55   value64  bigint unsigned  not null,
    56   value32  integer          not null,
    57   primary key(value64, value32)
    58  );
    59  
    60  /*use by error1 changefeed*/
    61  create table t6 (
    62   value64  bigint unsigned  not null,
    63   value32  integer          not null,
    64   primary key(value64, value32)
    65  );
    66  
    67  /*use by error1 changefeed*/
    68  create table t7 (
    69   value64  bigint unsigned  not null,
    70   value32  integer          not null,
    71   primary key(value64, value32)
    72  );
    73  
    74  /*use by error1 changefeed*/
    75  create table t8 (
    76   value64  bigint unsigned  not null,
    77   value32  integer          not null,
    78   primary key(value64, value32)
    79  );
    80  
    81  /*use by error2 changefeed*/
    82  create table t10 (
    83   value64  bigint unsigned  not null,
    84   value32  integer          not null,
    85   primary key(value64, value32)
    86  );
    87  
    88  /*use by error2 changefeed*/
    89  create table t11 (
    90   value64  bigint unsigned  not null,
    91   value32  integer          not null,
    92   primary key(value64, value32)
    93  );
    94  
    95  insert into t1 values(17156792991891826145, 1);
    96  insert into t1 values( 9223372036854775807, 2);
    97  insert into t2 values(17156792991891826145, 3);
    98  insert into t2 values( 9223372036854775807, 4);
    99  
   100  insert into t5 values(17156792991891826145, 1);
   101  insert into t6 values( 9223372036854775807, 2);
   102  insert into t7 values(17156792991891826145, 3);
   103  insert into t8 values( 9223372036854775807, 4);
   104  
   105  
   106  rename table t1 to t1_7, t2 to t2_7;
   107  
   108  insert into t1_7 values(91891826145, 5);
   109  insert into t1_7 values(685477580, 6);
   110  insert into t2_7 values(1715679991826145, 7);
   111  insert into t2_7 values(2036854775807, 8);
   112  
   113  insert into t3 select * from t1_7;
   114  insert into t4 select * from t2_7;
   115  drop table t3, t4;
   116  
   117  
   118  
   119  /* cf_err1, filter.rules = ["multi_tables_ddl_test.t5", "multi_tables_ddl_test.t6", "multi_tables_ddl_test.t7, "multi_tables_ddl_test.t8"] */
   120  /* replicate successful, they are all in `filter.rule` */
   121  rename table t5 to t55, t6 to t66;
   122  /* discard by cdc totally, they are all not in `filter.rule` */
   123  rename table t55 to t555, t66 to t666;
   124  /* replicate successful, since t8 in `filter.rule` */
   125  rename table t8 to t88;
   126  /* discard, t88 and t888 both not in `filter.rule` */
   127  rename table t88 to t888;
   128  
   129  
   130  /* cf_err2, filter.rules = ["multi_tables_ddl_test.t9", "multi_tables_ddl_test.t10"] */
   131  /* replicate successful, since t10 in `filter.rule` */
   132  rename table t10 to t9;
   133  /* replicate successful, since t9 in `filter.rule` */
   134  rename table t9 to t13;
   135  /* discard, t13 and t14 both not in `filter.rule` */
   136  rename table t13 to t14;
   137  /* error, t11 not match `filter.rule` and t9 match `filter.rule` */
   138  rename table t11 to t9;
   139  create table finish_mark(id int primary key);