同步报错两天了 sql也过不去

学员吉吉 · 2020 年8 月 13 日 03:40

[ERROR] [subtask.go:285] [“unit process error”] [subtask=pdd_product] [unit=Load] [“error information”="{“Type”:1,“msg”:"[code=10006:class=database:scope=downstream:level=high] file pdd_product_0.pdd_goods_2.000000006.sql: execute statement failed: INSERT INTO pdd_goods_x VALUES\ (407020929756250113,17915492,183874785,‘S999纯银项链女锁骨链韩版银饰品时尚心形吊坠情人节礼物送女友’,17452,‘项链’,CONVERT(’[17412, 17425, 17452]’ USING UTF8MB4),20,22512,22513,0,89.00,90.00,89.00,48,0,0,0,1,20.000,853046,1,‘https://omsproductionimg.yangkeduo.com/images/2018-01-23/337d5ac76a99ad56d1a277926c3a33a1.jpeg’,1002,CONVERT(’[24, 13]’ USING UTF8MB4),‘高’,‘高’,‘高’,1574970951,1596268293,CONVERT(’[10098]’ USING UTF8MB4)),\ (407020931375255552,17915510,1838171373,‘黑色发夹发饰韩国边夹短发刘海夹头饰日韩版四叶草一字夹鸭嘴夹女’,17440,‘发饰’,CONVERT(’[17412, 17417, 17440]’ USING UTF8MB4),20,22473,22474,0,9.90,11.90,9.90,1696,0,0,0,1,5.000,883298,1,‘https://t06img.yangkeduo.com/images/2018-06-17/08bf25d41fb0781a61f7caf9cf0724c5.jpeg’,1002,CONVERT(’[24, 13]’ USING UTF8MB4),‘高’,‘高’,‘高’,1574970952,1596268293,CONVERT(’[10098]’ USING UTF8MB4)),\ (407020932369305601,17915528,1838041529,‘即食香脆萝卜咸菜300g/\ufffd\ufffd…: Error 9005: Region is unavailable\ngithub.com/pingcap/dm/pkg/terror.(*Error).Delegate\ \t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/terror/terror.go:267\ngithub.com/pingcap/dm/pkg/conn.(*BaseConn).ExecuteSQLWithIgnoreError\ \t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/conn/baseconn.go:193\ngithub.com/pingcap/dm/pkg/conn.(*BaseConn).ExecuteSQL\ \t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/conn/baseconn.go:210\ngithub.com/pingcap/dm/loader.(*DBConn).executeSQL.func2\ \t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/loader/db.go:150\ngithub.com/pingcap/dm/pkg/retry.(*FiniteRetryStrategy).Apply\ \t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/retry/strategy.go:71\ngithub.com/pingcap/dm/pkg/conn.(*BaseConn).ApplyRetryStrategy\ \t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/conn/baseconn.go:216\ngithub.com/pingcap/dm/loader.(*DBConn).executeSQL\ \t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/loader/db.go:145\ngithub.com/pingcap/dm/loader.(*Worker).run.func2\ \t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/loader/loader.go:169\ngithub.com/pingcap/dm/loader.(*Worker).run.func3\ \t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/loader/loader.go:202\ runtime.goexit\ \t/usr/local/go/src/runtime/asm_amd64.s:1357",“error”:{“ErrCode”:10006,“ErrClass”:1,“ErrScope”:2,“ErrLevel”:3,“Message”:"file pdd_product_0.pdd_goods_2.000000006.sql: execute statement failed: INSERT INTO pdd_goods_x VALUES\ (407020929756250113,17915492,183874785,‘S999纯银项链女锁骨链韩版银饰品时尚心形吊坠情人节礼物送女友’,17452,‘项链’,CONVERT(’[17412, 17425, 17452]’ USING UTF8MB4),20,22512,22513,0,89.00,90.00,89.00,48,0,0,0,1,20.000,853046,1,‘https://omsproductionimg.yangkeduo.com/images/2018-01-23/337d5ac76a99ad56d1a277926c3a33a1.jpeg’,1002,CONVERT(’[24, 13]’ USING UTF8MB4),‘高’,‘高’,‘高’,1574970951,1596268293,CONVERT(’[10098]’ USING UTF8MB4)),\ (407020931375255552,17915510,1838171373,‘黑色发夹发饰韩国边夹短发刘海夹头饰日韩版四叶草一字夹鸭嘴夹女’,17440,‘发饰’,CONVERT(’[17412, 17417, 17440]’ USING UTF8MB4),20,22473,22474,0,9.90,11.90,9.90,1696,0,0,0,1,5.000,883298,1,‘https://t06img.yangkeduo.com/images/2018-06-17/08bf25d41fb0781a61f7caf9cf0724c5.jpeg’,1002,CONVERT(’[24, 13]’ USING UTF8MB4),‘高’,‘高’,‘高’,1574970952,1596268293,CONVERT(’[10098]’ USING UTF8MB4)),\ (407020932369305601,17915528,1838041529,'即食香脆萝卜咸菜300g/\ufffd\ufffd…: Error 9005: Region is unavailable",“RawCause”:“Error 9005: Region is unavailable”}}"]

学员吉吉 · 2020 年8 月 13 日 03:43

[code=10006:class=database:scope=downstream:level=high] file pdd_product_0.pdd_goods_0.000000006.sql: execute statement failed: INSERT INTO pdd_goods_x VALUES\ (407042877466951680,18254196,1346206507,‘雨鞋雨靴短筒时尚防滑防水鞋水靴女厨房果冻透明胶鞋可爱套鞋夏季’,8537,‘雨鞋’,CONVERT(’[8509, 8523, 8537]’ USING UTF8MB4),3,22148,22149,0,15.12,18.80,15.12,45,0,0,0,1,1.000,548411,0,‘https://t00img.yangkeduo.com/goods/images/2020-03-28/902bdfd5-74a5-4a1f-a23e-b1617d08305f.jpeg’,1002,CONVERT(’[24, 13]’ USING UTF8MB4),‘低’,‘低’,‘低’,1574976184,1585512146,CONVERT(’[]’ USING UTF8MB4)),\ (407042878091907073,18254214,1346389672,‘广东昆竹牌柠檬酒低度果酒自酿女士酒饮品蜂蜜柠檬酒微醺13℃’,6762,‘配制酒/果酒’,CONVERT(’[6758, 6761, 6762]’ USING UTF8MB4),13,23194,23195,23213,20.00,22.00,20.00,0,0,0,0,1,5.000,208271,1,‘https://t03img.yangkeduo.com/images/2018-05-12/7c49b05a53da77a1614052497d0b4ff9.jpeg’,1010,CONVERT(’[24, 12]’ USING UTF8MB4),‘高’,‘高’,‘高’,1574976184,1596611842,CONVERT(’[10098]’ USING UTF8MB4)),\ (407042879048208385,18254232,1346338521,'巨净洗洁精10斤大桶5kg柠\ufffd\ufffd…: Error 9005: Region is unavailable\ngithub.com/pingcap/dm/pkg/terror.(*Error).Delegate\ \t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/terror/terror.go:267\ngithub.com/pingcap/dm/pkg/conn.(*BaseConn).ExecuteSQLWithIgnoreError\ \t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/conn/baseconn.go:193\ngithub.com/pingcap/dm/pkg/conn.(*BaseConn).ExecuteSQL\ \t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/conn/baseconn.go:210\ngithub.com/pingcap/dm/loader.(*DBConn).executeSQL.func2\ \t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/loader/db.go:150\ngithub.com/pingcap/dm/pkg/retry.(*FiniteRetryStrategy).Apply\ \t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/retry/strategy.go:71\ngithub.com/pingcap/dm/pkg/conn.(*BaseConn).ApplyRetryStrategy\ \t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/conn/baseconn.go:216\ngithub.com/pingcap/dm/loader.(*DBConn).executeSQL\ \t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/loader/db.go:145\ngithub.com/pingcap/dm/loader.(*Worker).run.func2\ \t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/loader/loader.go:169\ngithub.com/pingcap/dm/loader.(*Worker).run.func3\ \t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/loader/loader.go:202\ runtime.goexit\ \t/usr/local/go/src/runtime/asm_amd64.s:1357",

来了老弟 · 2020 年8 月 13 日 03:47

ERROR 9005 (HY000) : Region is unavailable
访问的 Region 不可用，某个 Raft Group 不可用，如副本数目不足，出现在 TiKV 比较繁忙或者是 TiKV 节点停机的时候，大概率是因为 tikv 比较繁忙，请检查 TiKV Server 状态/监控/日志。

可以提供下 task 文件中 load 相关配置，并且尝试在下游手动执行该 sql 是否可以成功

提供下完成的 query-stauts 的信息

学员吉吉 · 2020 年8 月 13 日 03:53

{
“result”: true,
“msg”: “”,
“workers”: [
{
“result”: true,
“worker”: “172.17.116.163:8265”,
“msg”: “”,
“subTaskStatus”: [
{
“name”: “pdd_product”,
“stage”: “Paused”,
“unit”: “Load”,
“result”: {
“isCanceled”: false,
“errors”: [
{
“Type”: “ExecSQL”,
“msg”: “[code=10006:class=database:scope=downstream:level=high] file pdd_product_0.pdd_goods_0.000000006.sql: execute statement failed: INSERT INTO pdd_goods_x VALUES\ (407042877466951680,18254196,1346206507,‘雨鞋雨靴短筒时尚防滑防水鞋水靴女厨房果冻透明胶鞋可爱套鞋夏季’,8537,‘雨鞋’,CONVERT(’[8509, 8523, 8537]’ USING UTF8MB4),3,22148,22149,0,15.12,18.80,15.12,45,0,0,0,1,1.000,548411,0,‘https://t00img.yangkeduo.com/goods/images/2020-03-28/902bdfd5-74a5-4a1f-a23e-b1617d08305f.jpeg’,1002,CONVERT(’[24, 13]’ USING UTF8MB4),‘低’,‘低’,‘低’,1574976184,1585512146,CONVERT(’[]’ USING UTF8MB4)),\ (407042878091907073,18254214,1346389672,‘广东昆竹牌柠檬酒低度果酒自酿女士酒饮品蜂蜜柠檬酒微醺13℃’,6762,‘配制酒/果酒’,CONVERT(’[6758, 6761, 6762]’ USING UTF8MB4),13,23194,23195,23213,20.00,22.00,20.00,0,0,0,0,1,5.000,208271,1,‘https://t03img.yangkeduo.com/images/2018-05-12/7c49b05a53da77a1614052497d0b4ff9.jpeg’,1010,CONVERT(’[24, 12]’ USING UTF8MB4),‘高’,‘高’,‘高’,1574976184,1596611842,CONVERT(’[10098]’ USING UTF8MB4)),\ (407042879048208385,18254232,1346338521,'巨净洗洁精10斤大桶5kg柠\ufffd\ufffd…: Error 9005: Region is unavailable\ngithub.com/pingcap/dm/pkg/terror.(*Error).Delegate\ \t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/terror/terror.go:267\ngithub.com/pingcap/dm/pkg/conn.(*BaseConn).ExecuteSQLWithIgnoreError\ \t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/conn/baseconn.go:193\ngithub.com/pingcap/dm/pkg/conn.(*BaseConn).ExecuteSQL\ \t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/conn/baseconn.go:210\ngithub.com/pingcap/dm/loader.(*DBConn).executeSQL.func2\ \t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/loader/db.go:150\ngithub.com/pingcap/dm/pkg/retry.(*FiniteRetryStrategy).Apply\ \t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/retry/strategy.go:71\ngithub.com/pingcap/dm/pkg/conn.(*BaseConn).ApplyRetryStrategy\ \t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/conn/baseconn.go:216\ngithub.com/pingcap/dm/loader.(*DBConn).executeSQL\ \t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/loader/db.go:145\ngithub.com/pingcap/dm/loader.(*Worker).run.func2\ \t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/loader/loader.go:169\ngithub.com/pingcap/dm/loader.(*Worker).run.func3\ \t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/loader/loader.go:202\ runtime.goexit\ \t/usr/local/go/src/runtime/asm_amd64.s:1357”,
“error”: null
}
],
“detail”: null
},
“unresolvedDDLLockID”: “”,
“load”: {
“finishedBytes”: “37050511467”,
“totalBytes”: “38784482898”,
“progress”: “95.53 %”,
“metaBinlog”: “(mysql-bin.015220, 213643743)”
}
}
],
“relayStatus”: {
“masterBinlog”: “(mysql-bin.015343, 395903932)”,
“masterBinlogGtid”: “56282596-0cf7-11ea-92e4-506b4bd40650:1-8514417770”,
“relaySubDir”: “56282596-0cf7-11ea-92e4-506b4bd40650.000001”,
“relayBinlog”: “(mysql-bin.015343, 395903932)”,
“relayBinlogGtid”: “56282596-0cf7-11ea-92e4-506b4bd40650:1-8514417770”,
“relayCatchUpMaster”: true,
“stage”: “Running”,
“result”: null
},
“sourceID”: “pdd_product”
}
]
}

学员吉吉 · 2020 年8 月 13 日 03:53

不是繁忙的问题第一个可能是但是status里面的不是这个卡2天了

学员吉吉 · 2020 年8 月 13 日 03:53

【TiDB 版本】：3.0.9
【DM版本】：v1.0.5 Git Commit Hash: a8e9f53f91e29756b09a22cdc37a6a6efcdfe55b

小王同学 · 2020 年8 月 13 日 06:40

您好，您这个问题本身不是 DM 的问题，报错信息是 DM 在下游执行失败返回的报错，本质还是要去检查下下游 tidb 的情况，麻烦按照下面的提供的方式检查下下游集群。

github.com

pingcap/tidb-map/blob/master/maps/diagnose-map.md#11-客户端报-region-is-unavailable-错误

# TiDB 集群问题导图

## 1. 服务不可用

### 1.1 客户端报 `"Region is Unavailable"` 错误

- 1.1.1 `"Region is Unavailable"` 一般是由于 region 在一段时间不可用导致（可能会遇到 `"TiKV server is busy"` 或者发送给 TiKV 的请求由于 `not leader` 或者 `epoch not match` 被打回，或者请求 TiKV 超时等），TiDB 内部会进行 `backoff` 重试机制，`backoff` 的时间超过了一定阈值（默认 20s），就会报错给客户端，如果 `backoff` 在阈值内该错误对于客户端是无感知的。

- 1.1.2 多台 TiKV 同时 OOM 导致 region 在一定时期内没有 leader ，见案例 [case-991](./diagnose-case-study/case991.md)。

- 1.1.3 TiKV 报 `TiKV server is busy`，超过 `backoff` 时间，参考 4.3 `TiKV server is busy` 属于内部流控机制，后续可能不计入 `backoff` 时间，正在改善。

- 1.1.4 多台 TiKV 启动不了导致 region 没有 leader。单台物理主机部署多个 TiKV 实例，一个物理机挂掉，由于 label 配置错了导致 region 没有 leader ，见案例 [case-228](./diagnose-case-study/case228.md)。

- 1.1.5 follower apply 落后，成为 leader 之后把收到的请求以 `epoch not match` 理由打回，见案例 [case-958](./diagnose-case-study/case958.md)（TiKV 内部需要优化改机制）。

### 1.2 PD 异常导致服务不可用，请查看 5 PD 问题

## 2. Latency 明显升高

该文件已被截断。显示原文

TTTIIIDDDBBB · 2020 年8 月 13 日 07:13

你好我们的tikv是偶数节点会和这个有关系吗

小王同学 · 2020 年8 月 13 日 07:46

偶数节点是指 2 个节点吗？如果是因为 not leader 导致，是有可能有关系的，如果只有 2 个 tikv 节点并且没有打 label，则只有 2 副本数据存在，此时投票选举可能会存在平票的问题，建议按照上面的 tidb-map 中查看一下下游的情况。

tao7 · 2020 年8 月 13 日 13:06

投票选举跟tikv节点数量有关？不是跟副本数量有关吗？

小王同学 · 2020 年8 月 13 日 13:58

是和副本数有关系的。