v3.0.1的怎么定位drainer报错ddl的commitTs

为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。

  • 【TiDB 版本】:v3.0.1
  • 【问题描述】:

drainer同步数据的时候遇到了下游rds执行报错的ddl语句,按照官网的做法,需要找出报错的ddl的commitTs,然后在drainer.toml文件中加上ignore-txn-commit-ts = {报错的ddl的commitTS},但是我现在在drainer.log中找不到报错ddl的commitTs,但是在v3.0.4中能够找到,请问大佬,在这种情况下我需要怎么处理这个问题(ps:生产环境不敢轻举妄动)。现将drainer报错日志贴下:

[2019/12/16 11:33:50.615 +08:00] [INFO] [syncer.go:249] ["write save point"] [ts=413261423625109546] [2019/12/16 11:33:53.689 +08:00] [INFO] [syncer.go:249] ["write save point"] [ts=413261424595042366] [2019/12/16 11:33:56.696 +08:00] [INFO] [syncer.go:249] ["write save point"] [ts=413261425315938351] [2019/12/16 11:33:59.740 +08:00] [INFO] [syncer.go:249] ["write save point"] [ts=413261426259656706] [2019/12/16 11:34:02.874 +08:00] [INFO] [syncer.go:249] ["write save point"] [ts=413261427085410318] [2019/12/16 11:34:06.003 +08:00] [INFO] [syncer.go:249] ["write save point"] [ts=413261428029128768] [2019/12/16 11:34:09.093 +08:00] [INFO] [syncer.go:249] ["write save point"] [ts=413261428854882322] [2019/12/16 11:34:10.490 +08:00] [INFO] [client.go:758] ["recycle idle connection"] [target=10.10.10.143:20160] [2019/12/16 11:34:10.504 +08:00] [INFO] [collector.go:280] ["get ddl job"] [job="ID:1038, Type:create table, State:synced, SchemaState:public, SchemaID:44, TableID:1037, RowCount:0, ArgLen:0, start time: 2019-12-16 11:34:10.091 +0800 CST, Err:<nil>, ErrCount:0, SnapshotVersion:0"] [2019/12/16 11:34:12.146 +08:00] [INFO] [syncer.go:249] ["write save point"] [ts=413261429824815139] [2019/12/16 11:34:15.165 +08:00] [INFO] [syncer.go:249] ["write save point"] [ts=413261430545711176] [2019/12/16 11:34:16.593 +08:00] [ERROR] [util.go:153] [execDDL] [error="Error 1071: Specified key was too long; max key length is 767 bytes"] [2019/12/16 11:34:17.599 +08:00] [ERROR] [util.go:153] [execDDL] [error="Error 1071: Specified key was too long; max key length is 767 bytes"] [2019/12/16 11:34:18.604 +08:00] [ERROR] [util.go:153] [execDDL] [error="Error 1071: Specified key was too long; max key length is 767 bytes"] [2019/12/16 11:34:19.610 +08:00] [ERROR] [util.go:153] [execDDL] [error="Error 1071: Specified key was too long; max key length is 767 bytes"] [2019/12/16 11:34:20.616 +08:00] [ERROR] [util.go:153] [execDDL] [error="Error 1071: Specified key was too long; max key length is 767 bytes"] [2019/12/16 11:34:21.616 +08:00] [ERROR] [load.go:557] ["exec failed"] [sql="CREATE TABLEtc_financial_newest_report(\ idbigint(20) ************(此处省略) PRIMARY KEY (id),\ KEYindex(payment_date,merchant_id),\ KEYindex2(payment_month,merchant_id),\ KEYindex3(payment_date,merchant_name),\ KEYindex4(payment_month,merchant_name),\ KEYmerchantId_status_type_index(merchant_id,type,status),\ KEYmerchantName_status_type_index(merchant_name,type,status),\ UNIQUE KEYmerchant_id(merchant_id)\ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1 COMMENT='最新的财务报表数据'"] [error="Error 1071: Specified key was too long; max key length is 767 bytes"] [errorVerbose="Error 1071: Specified key was too long; max key length is 767 bytes\ github.com/pingcap/errors.AddStack\ \t/home/jenkins/workspace/release_tidb_3.0/go/pkg/mod/github.com/pingcap/errors@v0.11.4/errors.go:174\ github.com/pingcap/errors.Trace\ \t/home/jenkins/workspace/release_tidb_3.0/go/pkg/mod/github.com/pingcap/errors@v0.11.4/juju_adaptor.go:15\ github.com/pingcap/tidb-binlog/pkg/util.RetryOnError\ \t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/pkg/util/util.go:157\ github.com/pingcap/tidb-binlog/pkg/loader.(*loaderImpl).execDDL\ \t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/pkg/loader/load.go:278\ github.com/pingcap/tidb-binlog/pkg/loader.(*batchManager).execDDL\ \t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/pkg/loader/load.go:555\ github.com/pingcap/tidb-binlog/pkg/loader.(*batchManager).put\ \t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/pkg/loader/load.go:578\ github.com/pingcap/tidb-binlog/pkg/loader.(*loaderImpl).Run\ \t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/pkg/loader/load.go:420\ github.com/pingcap/tidb-binlog/drainer/sync.(*MysqlSyncer).run\ \t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/drainer/sync/mysql.go:117\ runtime.goexit\ \t/usr/local/go/src/runtime/asm_amd64.s:1337"] [2019/12/16 11:34:21.616 +08:00] [INFO] [load.go:402] ["Run()... in Loader quit"] [2019/12/16 11:34:21.616 +08:00] [INFO] [mysql.go:113] ["Successes chan quit"] [2019/12/16 11:34:21.617 +08:00] [INFO] [syncer.go:249] ["write save point"] [ts=413261430834069618]

看起来是创建表的时候key太大了? CREATE TABLE tc_financial_newest_report 日志往前找一下创建表时的DDL信息,应该有可以跳过的binlog信息

没有的,在v3.0.4中有这中提示信息,这个就是drainer不同步数据之前的所有的报错日志信息了

建议在 Drainer 文件添加 log-level = "debug" 。再看下日志输出的信息。

麻烦提供下 Drainer 的版本

好的,3q

[2019/12/13 11:19:41.160 +08:00] [INFO] [version.go:50] [“Welcome to Drainer”] [“Release Version”=v3.0.1] [“Git Commit Hash”=c735661f1bcce646dbad9742f5b483260499f0f5] [“Build TS”=“2019-07-16 01:02:24”] [“Go Version”=go1.12] [“Go OS/Arch”=linux/amd64]

3.0.1 这个版本还不支持 ignore-txn-commit-ts 这个功能

:joy:,大佬,那我应该怎么处理这个问题?

  1. 查看下这个ddl的历史信息: admin show ddl jobs; 还有这个ddl的记录吗?

  2. 具体这个ddl的完整信息麻烦也发一下,多谢

  3. 下游drainer的当前checkpoint查一下.

admin show ddl jobs;这个是在information这个数据库里面执行吗?

不用,都可以,会记录所有的. 如果比较多,可以用客户端找一下,或者-e 输出到文本.

嗯嗯

ddl:

CREATE TABLEtc_financial_newest_report( idbigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键,自增id', report_idvarchar(20) NOT NULL DEFAULT '' COMMENT '跑报表的批次id', mechanism_idvarchar(32) NOT NULL DEFAULT '' COMMENT '支付机构编号', agent_idvarchar(255) NOT NULL COMMENT'平台渠道号', agent_namevarchar(255) NOT NULL DEFAULT '' COMMENT '代理商名称', merchant_idvarchar(255) NOT NULL DEFAULT '' COMMENT '平台商户号', merchant_namevarchar(255)NOT NULL DEFAULT '' COMMENT '平台商户名称', payment_monthvarchar(20) NOT NULL DEFAULT '' COMMENT '交易月份', payment_datedate NOT NULL COMMENT '交易日期', typetinyint(4) NOT NULL DEFAULT '0' COMMENT '报表类型,0商户报表,1渠道报表', statustinyint(4) NOT NULL DEFAULT '0' COMMENT '状态 1正常 2废弃', payment_amountbigint(20) NOT NULL DEFAULT '0' COMMENT '支付总金额', refund_amountbigint(20) NOT NULL DEFAULT '0' COMMENT '退款金额', payment_hang_amountbigint(20) NOT NULL DEFAULT '0' COMMENT '支付挂帐总金额', payment_drop_amountbigint(20) NOT NULL DEFAULT '0' COMMENT '支付销账总金额', mch_feebigint(20) NOT NULL DEFAULT '0' COMMENT '系统平台收下游商户交易手续费', use_amountbigint(20) NOT NULL DEFAULT '0' COMMENT '垫资已用金额', advance_withdraw_amountbigint(20) NOT NULL DEFAULT '0' COMMENT '垫资提现金额', advance_withdraw_feebigint(20) NOT NULL DEFAULT '0' COMMENT '垫资提现手续费,当日垫资出款手续费', balance_withdraw_amountbigint(20) NOT NULL DEFAULT '0' COMMENT '余额提现金额', balance_withdraw_feebigint(20) NOT NULL DEFAULT '0' COMMENT '余额提现手续费', sum_advance_feebigint(20) NOT NULL DEFAULT '0' COMMENT '垫资提现手续费,当日垫资出款手续费', should_settlement_amountbigint(20) NOT NULL DEFAULT '0' COMMENT '应结算金额,D1日应结算金额(扣除垫资代付金额及手续费) ', real_settlement_amountbigint(20) NOT NULL DEFAULT '0' COMMENT '实结金额,D1实际结算金额(不含垫资代付提现失败第二天回退金额)', settlement_differencebigint(20) NOT NULL DEFAULT '0' COMMENT '结算差额', freeze_amountbigint(20) NOT NULL DEFAULT '0' COMMENT '冻结金额', unfreeze_amountbigint(20) NOT NULL DEFAULT '0' COMMENT '解冻金额', adjust_plus_amountbigint(20) NOT NULL DEFAULT '0' COMMENT '调帐加金额', adjust_minus_amountbigint(20) NOT NULL DEFAULT '0' COMMENT '调帐减金额', withdraw_fail_differencebigint(20) NOT NULL DEFAULT '0' COMMENT '提现失败差额,提现失败差额(资金流水提现失败-代付出款提现失败-代付出款拒绝提现)', balance_withdrawing_amountbigint(20) NOT NULL DEFAULT '0' COMMENT '余额提现中金额(包含手续费)', advance_withdrawing_amountbigint(20) NOT NULL COMMENT '垫资提现中金额(含手续费)', balance_withdraw_fail_unconfirmed_amountbigint(20) NOT NULL DEFAULT '0' COMMENT '余额提现失败未确认金额(包含手续费)', advance_withdraw_fail_unconfirmed_amountbigint(20) NOT NULL DEFAULT '0' COMMENT '垫资提现失败未确认金额(包含手续费)', withdraw_back_amountbigint(20) NOT NULL DEFAULT '0' COMMENT '非当日提现的回款金额', should_balancebigint(20) NOT NULL DEFAULT '0' COMMENT '应结余金额', end_balancebigint(20) NOT NULL DEFAULT '0' COMMENT '期末余额', difference_balancebigint(20) NOT NULL DEFAULT '0' COMMENT '余额差异', create_timetimestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (id), KEY index (payment_date,merchant_id), KEY index2 (payment_month,merchant_id), KEY index3 (payment_date,merchant_name), KEY index4 (payment_month,merchant_name), KEY merchantId_status_type_index (merchant_id,type,status), KEY merchantName_status_type_index (merchant_name,type,status), UNIQUE KEY merchant_id (merchant_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1 COMMENT='最新的财务报表数据'

下游drainer的当前checkpoint:

±--------------------±--------------------------------------------+
| clusterID | checkPoint |
±--------------------±--------------------------------------------+
| 6750102238872085307 | {“commitTS”:413261430834069618,“ts-map”:{}} |
±--------------------±--------------------------------------------+

admin show ddl jobs已经没有这个ddl的记录了

我下游使用的mysql5.6,这个ddl的字符集改为utf8就可以执行

之前使用的什么字符集? 这个表写数据了吗? 同步数据是否正常?

这个表之前使用的是utf8mb4,表已经写入数据了,下游表没创建成功,所以数据没有同步了

上游tidb中的报错ddl的表我可以清数据重建,但是现在卡在怎么跳过当前报错的ddl

建议可以升级drainer到3.0.2,这样重新启动报错信息里应该会包含ts信息,3.0.2也支持跳过

大佬,那我当前问题怎么解决呢?:joy:我这个是生产问题:cold_sweat: