search

pingcap / tidb-tools

tidb-tools are some useful tool collections for TiDB.
Apache License 2.0
286 stars 191 forks source link

【Question】json data check failed using sync_diff_inspector; #124

Open jianhaiqing opened 5 years ago

jianhaiqing commented 5 years ago

json data doesn't replicate with correct order using syncer.

CREATE TABLE `dynamic` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `uid` varchar(32) NOT NULL COMMENT '业务uid',
  `type` int(11) NOT NULL DEFAULT '1' ,
  `title` varchar(256) NOT NULL DEFAULT '' COMMENT '标题',
  `content` varchar(8192) DEFAULT NULL,
  `picture_url` varchar(2048) NOT NULL DEFAULT '' COMMENT '图片地址字符串，多个地址用","分隔',
  `audio_url` varchar(128) NOT NULL DEFAULT '' COMMENT '音频url',
  `audio_duration` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '音频时长',
  `web_content` varchar(1024) NOT NULL DEFAULT '' COMMENT '外链内容',
  `need_reply` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否需要回复，0是不需要，1是需要',
  `reply_expired_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '回复截止时间',
  `show_role` tinyint(2) unsigned NOT NULL DEFAULT '0' COMMENT '可见的角色',
  `creator_uid` varchar(32) NOT NULL DEFAULT '' COMMENT '创建者uid',
  `created_at` datetime NOT NULL COMMENT '创建时间',
  `updated_at` datetime NOT NULL COMMENT '更新时间',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '删除标识',
  `like_count` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '点赞数',
  `audios` json DEFAULT NULL COMMENT '存储音频列表',
  `videos` json DEFAULT NULL COMMENT '存储视频列表',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_uid` (`uid`) USING BTREE,
  KEY `idx_type` (`type`)
) ENGINE=InnoDB AUTO_INCREMENT=5542058 DEFAULT CHARSET=utf8;
id: 5541955
               uid: 3b047b3fe8604026ac12084770e2a609
              type: 1
             title: 111
           content: 555
       picture_url:
         audio_url:
    audio_duration: 0
       web_content:
        need_reply: 1
reply_expired_time: 2018-11-16 11:03:46
         show_role: 1
       creator_uid: ced40d39cc0a4d23a28f47c4d218e5a0
        created_at: 2018-11-16 11:03:24
        updated_at: 2018-11-16 11:03:24
        is_deleted: 0
        like_count: 0
            audios: [{"audioUrl": "111", "audioDuration": 111}]
            videos: NULL
id: 5541955
               uid: 3b047b3fe8604026ac12084770e2a609
              type: 1
             title: 111
           content: 555
       picture_url:
         audio_url:
    audio_duration: 0
       web_content:
        need_reply: 1
reply_expired_time: 2018-11-16 11:03:46
         show_role: 1
       creator_uid: ced40d39cc0a4d23a28f47c4d218e5a0
        created_at: 2018-11-16 11:03:24
        updated_at: 2018-11-16 11:03:24
        is_deleted: 0
        like_count: 0
            audios: [{"audioDuration": 111, "audioUrl": "111"}]
            videos: NULL

Release Version: v1.0.0-48-g4ac1cfc Git Commit Hash: 4ac1cfc7c1f9ac256c37733d831ce7dd1643b3fa Git Branch: master UTC Build Time: 2018-11-15 09:15:13 Go Version: go version go1.11 linux/amd64

july2993 commented 5 years ago

it just simple compare the raw data, so check fail for JSON Type, we will update to compare logically

jianhaiqing commented 5 years ago

Yes, I agree. Please let me know if you update sync_diff_inspector.
Thank you so much.

WangXiangUSTC commented 5 years ago

you can ignore this column now, you can see https://github.com/pingcap/tidb-tools/blob/94e36e48b3d1f6efae9de5d449ea053ccaa0518b/sync_diff_inspector/config.toml#L80

jianhaiqing commented 5 years ago

ERRO[0031] check chunk data equal failed, error Error 1054: Unknown column 'title' in 'field list' github.com/pingcap/errors.AddStack /home/jenkins/workspace/build_tidb_tools_master/go/pkg/mod/github.com/pingcap/errors@v0.11.0/errors.go:174 github.com/pingcap/errors.Trace /home/jenkins/workspace/build_tidb_tools_master/go/pkg/mod/github.com/pingcap/errors@v0.11.0/juju_adaptor.go:12 github.com/pingcap/tidb-tools/pkg/dbutil.GetCRC32Checksum /home/jenkins/workspace/build_tidb_tools_master/go/src/github.com/pingcap/tidb-tools/pkg/dbutil/common.go:363 github.com/pingcap/tidb-tools/pkg/diff.(TableDiff).getSourceTableChecksum /home/jenkins/workspace/build_tidb_tools_master/go/src/github.com/pingcap/tidb-tools/pkg/diff/diff.go:225 github.com/pingcap/tidb-tools/pkg/diff.(TableDiff).checkChunkDataEqual /home/jenkins/workspace/build_tidb_tools_master/go/src/github.com/pingcap/tidb-tools/pkg/diff/diff.go:244 github.com/pingcap/tidb-tools/pkg/diff.(*TableDiff).EqualTableData.func1 /home/jenkins/workspace/build_tidb_tools_master/go/src/github.com/pingcap/tidb-tools/pkg/diff/diff.go:192 runtime.goexit /usr/local/go/src/runtime/asm_amd64.s:1333

jianhaiqing commented 5 years ago 
CREATE TABLE test_float (
  uuid char(32) NOT NULL,
  average float DEFAULT '0',
  PRIMARY KEY (uuid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into test_float value('0a6f9d012f98467f8e671e9870044528', 208.867);

SELECT
  CONCAT_WS( ',', average)
FROM
  test_float
WHERE
  uuid = '0a6f9d012f98467f8e671e9870044528';
IANTHEREAL commented 5 years ago

@WangXiangUSTC how about this problem?

WangXiangUSTC commented 5 years ago

Unknown column 'title' in 'field list', it seems the data struct is not equal, and do you set ignore-struct-check=true in config file? @jianhaiqing

WangXiangUSTC commented 5 years ago

there is a issue in tidb https://github.com/pingcap/tidb/issues/7529, we will fix it. @jianhaiqing

dveeden commented 2 years ago

Can we close this as some of this may be covered by #350 and/or https://github.com/pingcap/tidb/issues/7529 ?