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

     1  #!/bin/bash
     2  
     3  # [DISCRIPTION]
     4  # This test is for when upstream server is set to non-default sql mode.
     5  # We test NO_BACKSLASH_ESCAPES mode and ANSI_QUOTES here.
     6  # We will test the following cases:
     7  # 1. upstream server is set to NO_BACKSLASH_ESCAPES mode. downstream server is set to NO_BACKSLASH_ESCAPES mode.
     8  # 2. upstream server is set to ANSI_QUOTES mode. downstream server is set to ANSI_QUOTES mode.
     9  #
    10  # for two cases, we will insert some dml / ddl influenced by sql mode, and check the result.
    11  # case 1
    12  # use test;
    13  # create table t20(id bigint primary key, a text, b text as ((regexp_replace(a, "^[1-9]\d{9,29}$", "aaaaa"))), c text);
    14  # insert into t20 (id, a, c) values(1,123456, 'ab\\\\c');
    15  # insert into t20 (id, a, c) values(2,1234567890123, 'ab\\c');
    16  
    17  # case 2
    18  # use test;
    19  #create table t1(id bigint primary key, a date);
    20  #insert into t1 values(1, '2023-02-08');
    21  
    22  # When the downstream is mysql or tidb, we need to check the result in downstream server.
    23  # Otherwise, we just need to check it works without error.
    24  
    25  set -xeu
    26  
    27  CUR=$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)
    28  source $CUR/../_utils/test_prepare
    29  WORK_DIR=$OUT_DIR/$TEST_NAME
    30  CDC_BINARY=cdc.test
    31  SINK_TYPE=$1
    32  
    33  CDC_COUNT=3
    34  DB_COUNT=4
    35  
    36  # case 1
    37  rm -rf $WORK_DIR && mkdir -p $WORK_DIR
    38  start_tidb_cluster --workdir $WORK_DIR
    39  trap stop_tidb_cluster EXIT
    40  
    41  run_sql "set global sql_mode='NO_BACKSLASH_ESCAPES';" ${UP_TIDB_HOST} ${UP_TIDB_PORT}
    42  run_sql "set global sql_mode='NO_BACKSLASH_ESCAPES';" ${DOWN_TIDB_HOST} ${DOWN_TIDB_PORT}
    43  
    44  cd $WORK_DIR
    45  start_ts=$(run_cdc_cli_tso_query ${UP_PD_HOST_1} ${UP_PD_PORT_1})
    46  run_cdc_server --workdir $WORK_DIR --binary $CDC_BINARY
    47  
    48  SINK_URI="mysql://root@127.0.0.1:3306/?max-txn-row=1"
    49  run_cdc_cli changefeed create --start-ts=$start_ts --sink-uri="$SINK_URI" --changefeed-id="test-1"
    50  
    51  run_sql "use test; create table t1(id bigint primary key, a text, b text as ((regexp_replace(a, '^[1-9]\d{9,29}$', 'aaaaa'))), c text); insert into t1 (id, a, c) values(1,123456, 'ab\\\\\\\\c'); insert into t1 (id, a, c) values(2,1234567890123, 'ab\\\\c');" ${UP_TIDB_HOST} ${UP_TIDB_PORT}
    52  
    53  if [ "$SINK_TYPE" == "mysql" ]; then
    54  	check_table_exists "test.t1" ${DOWN_TIDB_HOST} ${DOWN_TIDB_PORT}
    55  	sleep 10
    56  	run_sql "SELECT * from test.t1" ${DOWN_TIDB_HOST} ${DOWN_TIDB_PORT} &&
    57  		check_contains "b: 123456" &&
    58  		check_contains "b: aaaaa" &&
    59  		check_contains "c: ab\\\\\\\\c" &&
    60  		check_contains "c: ab\\\\c"
    61  fi
    62  
    63  stop_tidb_cluster
    64  start_tidb_cluster --workdir $WORK_DIR
    65  
    66  ## case 2
    67  run_sql "set global sql_mode='ANSI_QUOTES';" ${UP_TIDB_HOST} ${UP_TIDB_PORT}
    68  run_sql "set global sql_mode='ANSI_QUOTES';" ${DOWN_TIDB_HOST} ${DOWN_TIDB_PORT}
    69  
    70  start_ts=$(run_cdc_cli_tso_query ${UP_PD_HOST_1} ${UP_PD_PORT_1})
    71  run_cdc_server --workdir $WORK_DIR --binary $CDC_BINARY
    72  
    73  SINK_URI="mysql://root@127.0.0.1:3306/?max-txn-row=1"
    74  run_cdc_cli changefeed create --start-ts=$start_ts --sink-uri="$SINK_URI" --changefeed-id="test-2"
    75  
    76  run_sql "use test; create table t2(id bigint primary key, a date); insert into t2 values(1, '2023-02-08');" ${UP_TIDB_HOST} ${UP_TIDB_PORT}
    77  
    78  if [ "$SINK_TYPE" == "mysql" ]; then
    79  	check_table_exists "test.t2" ${DOWN_TIDB_HOST} ${DOWN_TIDB_PORT}
    80  	sleep 10
    81  	run_sql "SELECT * from test.t2" ${DOWN_TIDB_HOST} ${DOWN_TIDB_PORT} &&
    82  		check_contains "a: 2023-02-08"
    83  fi
    84  
    85  stop_tidb_cluster