github.com/pingcap/tiflow@v0.0.0-20240520035814-5bf52d54e205/engine/test/integration_tests/dm_lightning_checkpoint/data/db2.prepare.sql (about)

     1  drop user if exists 'dm_full2';
     2  flush privileges;
     3  create user 'dm_full2'@'%' identified by '123456';
     4  grant all privileges on *.* to 'dm_full2'@'%';
     5  flush privileges;
     6  
     7  drop database if exists `dm_full2`;
     8  create database `dm_full2` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin;
     9  use `dm_full2`;
    10  create table t1 (
    11      id int,
    12      name varchar(20),
    13      ts timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    14      primary key(`id`));
    15  insert into t1 (id, name, ts) values (1, 'arya', now()), (2, 'catelyn', '2021-05-11 10:01:03');
    16  insert into t1 (id, name) values (3, 'Eddard
    17  Stark');
    18  update t1 set name = 'Arya S\\\\tark' where id = 1;
    19  update t1 set name = 'Catelyn S\"\n\ttark' where name = 'catelyn';
    20  
    21  -- test multi column index with generated column
    22  alter table t1 add column info json;
    23  alter table t1 add column gen_id int as (info->"$.id");
    24  alter table t1 add index multi_col(`id`, `gen_id`);
    25  insert into t1 (id, name, info) values (4, 'gentest', '{"id": 123}');
    26  insert into t1 (id, name, info) values (5, 'gentest', '{"id": 124}');
    27  update t1 set info = '{"id": 120}' where id = 1;
    28  update t1 set info = '{"id": 121}' where id = 2;
    29  update t1 set info = '{"id": 122}' where id = 3;
    30  
    31  -- test genColumnCache is reset after ddl
    32  alter table t1 add column info2 varchar(40);
    33  insert into t1 (id, name, info) values (6, 'gentest', '{"id": 125, "test cache": false}');
    34  alter table t1 add unique key gen_idx(`gen_id`);
    35  update t1 set name = 'gentestxx' where gen_id = 123;
    36  
    37  insert into t1 (id, name, info) values (7, 'gentest', '{"id": 126}');
    38  update t1 set name = 'gentestxxxxxx' where gen_id = 124;
    39  -- delete with unique key
    40  delete from t1 where gen_id > 124;