Session SQL mode causes failures

[dveeden]

What did you do?

Setup

Setup two TiDB clusters and a MySQL Server.

tiup -T src playground --tiflash 0 --without-monitor v5.3.0
tiup -T dst playground --db.port 4001 --tiflash 0 --without-monitor v5.3.0
dbdeployer deploy single 8.0.27

Setup changefeeds to replicate data from "src" to "dst" (TiDB-to-TiDB) and "src" to "mysql" (TiDB-to-MySQL):

./bin/cdc server --pd=http://127.0.0.1:2379
./bin/cdc cli changefeed create --sink-uri="mysql://root@127.0.0.1:4001/" --changefeed-id="tidb-tidb-1"
./bin/cdc cli changefeed create --sink-uri="mysql://root:msandbox@127.0.0.1:8027/" --changefeed-id="tidb-mysql-1"

Test

On src:

CREATE SCHEMA cdctest;
USE cdctest;
CREATE TABLE t1(id int primary key);
INSERT INTO t1 VALUES(1);

Now check on dst and mysql, this should show the cdctest.t1 table with one row.

On src again:

SET sql_mode='';
CREATE TABLE t2 (id int primary key auto_increment, c1 timestamp not null);
INSERT INTO t2(c1) VALUES(CURRENT_TIMESTAMP);
INSERT INTO t2(c1) VALUES('0000-00-00 00:00:00');
TABLE t2;

What did you expect to see?

cdctest.t2 being replicated to the target databases with 2 rows.

What did you see instead?

[2021/12/09 10:27:51.692 +01:00] [WARN] [mysql.go:935] ["execute DMLs with error, retry later"] [error="[CDC:ErrMySQLTxnError]Error 1292: Incorrect datetime value: '0000-00-00 00:00:00' for column 'c1' at row 1: Error 1292: Incorrect datetime value: '0000-00-00 00:00:00' for column 'c1' at row 1"] [errorVerbose="[CDC:ErrMySQLTxnError]Error 1292: Incorrect datetime value: '0000-00-00 00:00:00' for column 'c1' at row 1: Error 1292: Incorrect datetime value: '0000-00-00 00:00:00' for column 'c1' at row 1\ngithub.com/pingcap/errors.AddStack\n\tgithub.com/pingcap/errors@v0.11.5-0.20210513014640-40f9a1999b3b/errors.go:174\ngithub.com/pingcap/errors.(*Error).GenWithStackByCause\n\tgithub.com/pingcap/errors@v0.11.5-0.20210513014640-40f9a1999b3b/normalize.go:302\ngithub.com/pingcap/ticdc/pkg/errors.WrapError\n\tgithub.com/pingcap/ticdc/pkg/errors/helper.go:30\ngithub.com/pingcap/ticdc/cdc/sink.(*mysqlSink).execDMLWithMaxRetries.func1.3\n\tgithub.com/pingcap/ticdc/cdc/sink/mysql.go:985\ngithub.com/pingcap/ticdc/cdc/sink.(*Statistics).RecordBatchExecution\n\tgithub.com/pingcap/ticdc/cdc/sink/statistics.go:112\ngithub.com/pingcap/ticdc/cdc/sink.(*mysqlSink).execDMLWithMaxRetries.func1\n\tgithub.com/pingcap/ticdc/cdc/sink/mysql.go:972\ngithub.com/pingcap/ticdc/pkg/retry.run\n\tgithub.com/pingcap/ticdc/pkg/retry/retry_with_opt.go:54\ngithub.com/pingcap/ticdc/pkg/retry.Do\n\tgithub.com/pingcap/ticdc/pkg/retry/retry_with_opt.go:32\ngithub.com/pingcap/ticdc/cdc/sink.(*mysqlSink).execDMLWithMaxRetries\n\tgithub.com/pingcap/ticdc/cdc/sink/mysql.go:964\ngithub.com/pingcap/ticdc/cdc/sink.(*mysqlSink).execDMLs\n\tgithub.com/pingcap/ticdc/cdc/sink/mysql.go:1125\ngithub.com/pingcap/ticdc/cdc/sink.(*mysqlSinkWorker).run.func3\n\tgithub.com/pingcap/ticdc/cdc/sink/mysql.go:820\ngithub.com/pingcap/ticdc/cdc/sink.(*mysqlSinkWorker).run\n\tgithub.com/pingcap/ticdc/cdc/sink/mysql.go:858\ngithub.com/pingcap/ticdc/cdc/sink.(*mysqlSink).createSinkWorkers.func1\n\tgithub.com/pingcap/ticdc/cdc/sink/mysql.go:661\nruntime.goexit\n\truntime/asm_amd64.s:1371"]

After `SET GLOBAL sql_mode='' on dst and mysql and waiting for retry the data was synced.

Versions of the cluster

TiDB: v5.3.0
MySQL: 8.0.27
CDC: v5.3.0

[dveeden]

With tiup client and TiDB Cloud "Web SQL Client" (both usql based) using a non-default SQL mode (It uses ANSI iirc, which includes PIPES_AS_CONCAT, etc. ) this could lead to issues. And applications setting a session SQL mode could also lead to issues. Looks like wordpress is doing this: https://developer.wordpress.org/reference/classes/wpdb/set_sql_mode/

[lance6716]

maybe need https://github.com/pingcap/ticdc/blob/9dad091c38323e8bdf4198afc1f6d53394b944cc/dm/pkg/utils/db.go#L616-L656