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(\nidbigint(20) ************(此处省略) PRIMARY KEY (id),\n KEYindex(payment_date,merchant_id),\n KEYindex2(payment_month,merchant_id),\n KEYindex3(payment_date,merchant_name),\n KEYindex4(payment_month,merchant_name),\n KEYmerchantId_status_type_index(merchant_id,type,status),\n KEYmerchantName_status_type_index(merchant_name,type,status),\n UNIQUE KEYmerchant_id(merchant_id)\n) 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\ngithub.com/pingcap/errors.AddStack\n\t/home/jenkins/workspace/release_tidb_3.0/go/pkg/mod/github.com/pingcap/errors@v0.11.4/errors.go:174\ngithub.com/pingcap/errors.Trace\n\t/home/jenkins/workspace/release_tidb_3.0/go/pkg/mod/github.com/pingcap/errors@v0.11.4/juju_adaptor.go:15\ngithub.com/pingcap/tidb-binlog/pkg/util.RetryOnError\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/pkg/util/util.go:157\ngithub.com/pingcap/tidb-binlog/pkg/loader.(*loaderImpl).execDDL\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/pkg/loader/load.go:278\ngithub.com/pingcap/tidb-binlog/pkg/loader.(*batchManager).execDDL\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/pkg/loader/load.go:555\ngithub.com/pingcap/tidb-binlog/pkg/loader.(*batchManager).put\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/pkg/loader/load.go:578\ngithub.com/pingcap/tidb-binlog/pkg/loader.(*loaderImpl).Run\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/pkg/loader/load.go:420\ngithub.com/pingcap/tidb-binlog/drainer/sync.(*MysqlSyncer).run\n\t/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/drainer/sync/mysql.go:117\nruntime.goexit\n\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_timetimestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', 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='最新的财务报表数据'

下游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: