Cannot convert type: decimal.Decimal

  • 【TiDB 版本】:5.7.25-TiDB-v3.0.5

  • 【问题描述】:使用DM从mysql(版本5.7.24)实时同步10张表至tidb,同步中出现如下错误:

          "subTaskStatus": [
              {
                  "name": "test",
                  "stage": "Paused",
                  "unit": "Sync",
                  "result": {
                      "isCanceled": false,
                      "errors": [
                          {
                              "Type": "ExecSQL",
                              "msg": "[code=10006:class=database:scope=not-set:level=high] execute statement failed: REPLACE INTO `test`.`xxxxxx` (`id`,`order_id`,`user_id`,`channel`,.....) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);: cannot convert type: decimal.Decimal\ngithub.com/pingcap/dm/pkg/terror.(*Error).Delegate\n\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\n\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/conn/baseconn.go:159\ngithub.com/pingcap/dm/syncer.(*DBConn).executeSQLWithIgnore.func2\n\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/db.go:257\ngithub.com/pingcap/dm/pkg/retry.(*FiniteRetryStrategy).Apply\n\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\n\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/conn/baseconn.go:180\ngithub.com/pingcap/dm/syncer.(*DBConn).executeSQLWithIgnore\n\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/db.go:252\ngithub.com/pingcap/dm/syncer.(*DBConn).executeSQL\n\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/db.go:279\ngithub.com/pingcap/dm/syncer.(*Syncer).sync.func3\n\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/syncer.go:968\ngithub.com/pingcap/dm/syncer.(*Syncer).sync\n\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/syncer.go:1010\ngithub.com/pingcap/dm/syncer.(*Syncer).Run.func2\n\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/syncer.go:1079\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1357",
                              "error": null
                          },
    

出现类型转换错误cannot convert type: decimal.Decimal,不知道是什么原因?mysql中这个表有两个字段是decimal类型,记录经纬度的。这张表数据目前已经同步完成90%以上的数据

你好麻烦把上下游中这张表的表结构发出来

上游mysql表结构:

CREATE TABLE xxxxxx ( id int(11) NOT NULL AUTO_INCREMENT, order_id bigint(11) NOT NULL COMMENT ‘’, user_id int(11) NOT NULL COMMENT ‘’, channel varchar(255) DEFAULT ‘’ COMMENT ‘’, cps_name varchar(255) DEFAULT ‘’ COMMENT ‘’, tsmcid varchar(255) DEFAULT ‘’ COMMENT ‘’, lat decimal(9,6) DEFAULT ‘0.000000’ COMMENT ‘’, lng decimal(9,6) DEFAULT ‘0.000000’ COMMENT ‘’, ip varchar(25) DEFAULT ‘0’ COMMENT ‘’, s varchar(32) DEFAULT ‘’ COMMENT ‘’, guid varchar(50) DEFAULT ‘’, tp_uid varchar(255) DEFAULT NULL COMMENT ‘’, spm varchar(128) DEFAULT NULL, device_type tinyint(4) DEFAULT ‘0’ COMMENT ‘’, delete_flag tinyint(4) DEFAULT ‘0’ COMMENT ‘’, is_settle tinyint(2) DEFAULT ‘0’ COMMENT ‘’, settle_time int(11) DEFAULT ‘0’ COMMENT ‘’, merchant_note varchar(512) DEFAULT ‘’ COMMENT ‘’, updated_at int(11) NOT NULL COMMENT ‘’, last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, sub_data varchar(5000) DEFAULT ‘{}’ COMMENT ‘’, created_at int(11) DEFAULT ‘0’ COMMENT ‘’, price_diff int(11) DEFAULT ‘0’ COMMENT ‘’, solicitation_time int(11) DEFAULT ‘0’ COMMENT ‘’, premium_income int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘’, deductible_amount int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘’, sham_total_price int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘’, mg_order_id varchar(128) NOT NULL DEFAULT ‘0’ COMMENT ‘’, PRIMARY KEY (id), KEY orderId (order_id), KEY IDX_CHANNEL (channel,cps_name), KEY idx_mg_order_id (mg_order_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

下游tidb表是自动生成的,结构如下:

CREATE TABLE xxxxxx ( id int(11) NOT NULL AUTO_INCREMENT, order_id bigint(11) NOT NULL COMMENT ‘’, user_id int(11) NOT NULL COMMENT ‘’, channel varchar(255) DEFAULT ‘’ COMMENT ‘’, cps_name varchar(255) DEFAULT ‘’ COMMENT ‘’, tsmcid varchar(255) DEFAULT ‘’ COMMENT ‘’, lat decimal(9,6) DEFAULT ‘0.000000’ COMMENT ‘’, lng decimal(9,6) DEFAULT ‘0.000000’ COMMENT ‘’, ip varchar(25) DEFAULT ‘0’ COMMENT ‘’, s varchar(32) DEFAULT ‘’ COMMENT ‘’, guid varchar(50) DEFAULT ‘’, tp_uid varchar(255) DEFAULT NULL COMMENT ‘’, spm varchar(128) DEFAULT NULL, device_type tinyint(4) DEFAULT ‘0’ COMMENT ‘’, delete_flag tinyint(4) DEFAULT ‘0’ COMMENT ‘’, is_settle tinyint(2) DEFAULT ‘0’ COMMENT ‘’, settle_time int(11) DEFAULT ‘0’ COMMENT ‘’, merchant_note varchar(512) DEFAULT ‘’ COMMENT ‘’, updated_at int(11) NOT NULL COMMENT ‘’, last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, sub_data varchar(5000) DEFAULT ‘{}’ COMMENT ‘’, created_at int(11) DEFAULT ‘0’ COMMENT ‘’, price_diff int(11) DEFAULT ‘0’ COMMENT ‘’, solicitation_time int(11) DEFAULT ‘0’ COMMENT ‘’, premium_income int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘’, deductible_amount int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘’, sham_total_price int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘’, mg_order_id varchar(128) NOT NULL DEFAULT ‘0’ COMMENT ‘’, PRIMARY KEY (id), KEY orderId (order_id), KEY IDX_CHANNEL (channel,cps_name), KEY idx_mg_order_id (mg_order_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

@sh-hanson 请问能提供一下 DM-worker.log 里在同步中断前,对应的 log 吗?

log 里的关键词是

execute statement failed

造成这个问题的原因是:

在我们 fix 之前,可能需要临时降级 DM 到 v1.0.1 才行

好的,我先降级继续测试评估

文档中没看到有降级的操作 是不是要在中控机上下载v1.0.1版本的dm-ansible,然后执行全新安装?

配置下新版本后,用滚动升级脚本就可以

@sh-hanson 不好意思,再确认一下。你之前遇到问题的版本,是 v1.0.2?还是某个未正式发布的 v1.1.0-alpha?

我们确认了一下这个问题的影响范围,应该并没有影响到 v1.0.2,只影响到了最近的 v1.1.0-alpha(11 月 19 号之后)

刚我检查了下,确实不是v1.0.2,版本是v1.1.0-alpha

我用latest版本下载的(dm-ansible-latest.tar.gz),我以为这里的latest就是最新release版本v1.0.2。

那v1.0.2版本我后面再测试一下,现在已经换成v1.0.1版本在同步了,目前没有出现问题

:+1::+1::+1: