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

     1  drop user if exists 'dm_full';
     2  flush privileges;
     3  create user 'dm_full'@'%' identified by '123456';
     4  grant all privileges on *.* to 'dm_full'@'%';
     5  revoke replication slave, replication client, super on *.* from 'dm_full'@'%';
     6  revoke create temporary tables, lock tables, create routine, alter routine, event, create tablespace, file, shutdown, execute, process, index on *.* from 'dm_full'@'%'; # privileges not supported by TiDB
     7  flush privileges;
     8  
     9  drop database if exists dm_full_empty_db;
    10  create database dm_full_empty_db;
    11  
    12  drop database if exists dm_full_empty_table;
    13  create database dm_full_empty_table;
    14  create table dm_full_empty_table.t1 (id int primary key);
    15  
    16  drop database if exists dm_full_route_schema;
    17  create database dm_full_route_schema;
    18  create table dm_full_route_schema.t1 (id int primary key);
    19  insert into dm_full_route_schema.t1 values (1);
    20  
    21  drop database if exists `dm_full_special/ch"ar`;
    22  create database `dm_full_special/ch"ar`;
    23  create table `dm_full_special/ch"ar`.`t/b"1` (id int primary key, name varchar(255));
    24  insert into `dm_full_special/ch"ar`.`t/b"1` values (1, 'a');
    25  
    26  drop database if exists `dm_full`;
    27  create database `dm_full`;
    28  use `dm_full`;
    29  create table t1 (
    30      id int,
    31      name varchar(20),
    32      ts timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    33      primary key(`id`));
    34  insert into t1 (id, name, ts) values (1, 'arya', now()), (2, 'catelyn', '2021-05-11 10:01:03');
    35  insert into t1 (id, name) values (3, 'Eddard
    36  Stark');
    37  update t1 set name = 'Arya S\\\\tark' where id = 1;
    38  update t1 set name = 'Catelyn S\"\n\ttark' where name = 'catelyn';
    39  
    40  -- test multi column index with generated column
    41  alter table t1 add column info json;
    42  alter table t1 add column gen_id int as (info->"$.id");
    43  alter table t1 add index multi_col(`id`, `gen_id`);
    44  insert into t1 (id, name, info) values (4, 'gentest', '{"id": 123}');
    45  insert into t1 (id, name, info) values (5, 'gentest', '{"id": 124}');
    46  update t1 set info = '{"id": 120}' where id = 1;
    47  update t1 set info = '{"id": 121}' where id = 2;
    48  update t1 set info = '{"id": 122}' where id = 3;
    49  
    50  -- test genColumnCache is reset after ddl
    51  alter table t1 add column info2 varchar(40);
    52  insert into t1 (id, name, info) values (6, 'gentest', '{"id": 125, "test cache": false}');
    53  alter table t1 add unique key gen_idx(`gen_id`);
    54  update t1 set name = 'gentestxx' where gen_id = 123;
    55  
    56  insert into t1 (id, name, info) values (7, 'gentest', '{"id": 126}');
    57  update t1 set name = 'gentestxxxxxx' where gen_id = 124;
    58  -- delete with unique key
    59  delete from t1 where gen_id > 124;