Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

the content is different in table A , B, but the check sql gets the same result.

Description

for example, we want to check table A and B. But there is a column with the same value in A, also there is a column with the same value in B. The value in A is diffferent with B.

the content is different,but the check sql gets the same result.

SELECT COUNT AS cnt,COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#',`create_time`,CONCAT(ISNULL(`create_time`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `A`

 

SELECT COUNT AS cnt,COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#',`create_time`,CONCAT(ISNULL(`create_time`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `B`

 

table A:

1 2020-10-31 12:00:00

2 2020-10-31 12:00:00

3 2020-10-31 12:00:00

table B:

1 2020-10-31 12:00:05

2 2020-10-31 12:00:05

3 2020-10-31 12:00:05

 

CREATE TABLE `A` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增id',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=71 DEFAULT CHARSET=utf8mb4;

 

Environment

None

Smart Checklist

Activity

Show:

Jira Bot March 4, 2021 at 1:56 PM

Hello ,
It's been 52 days since this issue went into Incomplete and we haven't heard
from you on this.

At this point, our policy is to Close this issue, to keep things from getting
too cluttered. If you have more information about this issue and wish to
reopen it, please reply with a comment containing "jira-bot=reopen".

Jira Bot February 24, 2021 at 1:56 PM

Hello ,
It's jira-bot again. Your bug report is important to us, but we haven't heard
from you since the previous notification. If we don't hear from you on
this in 7 days, the issue will be automatically closed.

Jira Bot February 9, 2021 at 12:56 PM

Hello ,
I'm jira-bot, Percona's automated helper script. Your bug report is important
to us but we've been unable to reproduce it, and asked you for more
information. If we haven't heard from you on this in 3 more weeks, the issue
will be automatically closed.

Lalit Choudhary January 11, 2021 at 12:44 PM

Hi

Thank you for the report.

Please provide more information on the issue with the reproduciable test case and pt command you are using. 

hechenyang October 27, 2020 at 12:39 PM

My advice: every check sql chooses the range with odd number. this can  avoid bit_xor' s exception.

For example:

SELECT COUNT AS cnt,COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#',`create_time`,CONCAT(ISNULL(`create_time`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `crc_test` where  id > 1 limit  5;

 

Incomplete

Details

Assignee

Reporter

Priority

Smart Checklist

Created October 27, 2020 at 12:32 PM
Updated February 29, 2024 at 8:56 PM
Resolved March 4, 2021 at 1:56 PM