[MDEV-28020] CHECKSUM TABLE calculates different checksums Created: 2022-03-07  Updated: 2022-04-26  Resolved: 2022-04-26

Status: Closed
Project: MariaDB Server
Component/s: Replication
Affects Version/s: 10.5.15, 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9
Fix Version/s: 10.2.44, 10.3.35, 10.4.25, 10.5.16, 10.6.8, 10.7.4

Type: Bug Priority: Critical
Reporter: Marc Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None
Environment:

linux

Issue Links:
Relates
relates to MDEV-15519 CHECKSUM TABLE gives different result... Confirmed

 Description   

I have a master slave setup. And two tables calculate different checksums, while the data seems be in sync.

checksum table A, ASlave extended;

Table Checksum
A 1139480600
ASlave 1134561865 

(select * from ASlave ORDER BY ID ) except (select * from A ORDER BY ID);

Empty set (2.461 sec)

(select * from A ORDER BY ID ) except (select * from ASlave ORDER BY ID);

Empty set (2.418 sec)

Any other command to check for differences in tables?



 Comments   
Comment by Marc [ 2022-03-07 ]

made a copy of both tables and put them together in a new database - so I'm able to reproduce it, with the actual data.
Already tried ANALYZE, CHECK and OPTMIZE. The checksum changed but they never get equal.

Comment by Sergei Golubchik [ 2022-03-09 ]

Is there any way I can repeat it? Are your tables big, can you share them?

Comment by Marc [ 2022-03-09 ]

not really big: 23M, 31524 entries. The table contains user data (like username, password-hash, ..). So I can't share them.
Tried sth:

ls -la tblWebUser.MAD tblWebUserm.MAD
 
-rw-r----- 1 mysql mysql 24002560 Mär  7 11:26 tblWebUser.MAD
 
-rw-r----- 1 mysql mysql 23953408 Mär  7 11:26 tblWebUserm.MAD


create table a like tblWebUser;
 
create table b like tblWebUser;
 
insert ignore into a select * from tblWebUser ORDER BY ID;
 
insert ignore into a select * from tblWebUserm ORDER BY ID;
 
checksum table a, b extended;
 
+----------+-----------+
 
| Table    | Checksum  |
 
+----------+-----------+
 
| import.a | 522685522 |
 
| import.b | 384264246 |
 
+----------+-----------+


ls -la a.MAD b.MAD
 
-rw-rw---- 1 mysql mysql 23961600 Mär  9 19:02 a.MAD
 
-rw-rw---- 1 mysql mysql 23961600 Mär  9 19:02 b.MAD


(select * from a ORDER BY ID ) except (select * from b ORDER BY ID);
 
Empty set (2.405 sec)
 
 
 
(select * from b ORDER BY ID ) except (select * from a ORDER BY ID);
 
Empty set (2.407 sec)

Comment by Sergei Golubchik [ 2022-03-09 ]

Okay. Please, show the output of SHOW CREATE TABLE.

May be if you do UPDATE a SET colname='' for all user data columns you'll be able to get rid of personal data while still having checksum difference?

Comment by Sergei Golubchik [ 2022-03-10 ]

Hold on, we'll try to reproduce based on that

Comment by Lena Startseva [ 2022-03-24 ]

The problem is repeated in all active versions: 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9
Test:

CREATE TABLE t (
 
  a int,
 
  b int GENERATED ALWAYS AS (a) VIRTUAL,
 
  c text
 
) ENGINE=MyISAM CHECKSUM=1;
 
 
 
INSERT INTO t (a,c) VALUES (1,'foo'),(2,'bar');
 
 
 
CREATE TABLE t1 LIKE t;
 
CREATE TABLE t2 LIKE t;
 
INSERT IGNORE INTO t1 SELECT a, b, c FROM t ORDER BY a, b, c;
 
INSERT IGNORE INTO t2 SELECT a, b, c FROM t ORDER BY a, b, c;
 
 
 
CHECKSUM TABLE t1, t2 EXTENDED;
 
CHECKSUM TABLE t1, t2;
 
 
 
DROP TABLE t, t1, t2;

Failed with error:

mysqltest: At line 14: query 'CHECKSUM TABLE t1, t2 EXTENDED' failed: 2013: Lost connection to MySQL server during query
 
 
 
 
 
/10.5/sql/handler.cc:5505: virtual int handler::calculate_checksum(): Assertion `checksum_start + checksum_length == f->ptr' failed.
 
220324 18:51:49 [ERROR] mysqld got signal 6 ;
 
 
 
stack_bottom = 0x7f960f05fd78 thread_stack 0x49000
 
mysys/stacktrace.c:212(my_print_stacktrace)[0x5639a4f1c524]
 
sql/signal_handler.cc:225(handle_fatal_signal)[0x5639a45680d3]
 
addr2line: DWARF error: section .debug_info is larger than its filesize! (0x5b4757 vs 0x429c68)
 
??:0(__sigaction)[0x7f96186c9520]
 
??:0(pthread_kill)[0x7f961871d828]
 
??:0(raise)[0x7f96186c9476]
 
??:0(abort)[0x7f96186af7b7]
 
/lib/x86_64-linux-gnu/libc.so.6(+0x286db)[0x7f96186af6db]
 
??:0(__assert_fail)[0x7f96186c0e26]
 
sql/handler.cc:5506(handler::calculate_checksum())[0x5639a457bf15]
 
sql/sql_table.cc:11853(mysql_checksum_table(THD*, TABLE_LIST*, st_ha_check_opt*))[0x5639a42f90ba]
 
sql/sql_parse.cc:4442(mysql_execute_command(THD*))[0x5639a41e651b]
 
sql/sql_parse.cc:8100(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x5639a41f2f2f]
 
sql/sql_parse.cc:1894(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x5639a41de763]
 
sql/sql_parse.cc:1370(do_command(THD*))[0x5639a41dcedd]
 
sql/sql_connect.cc:1418(do_handle_one_connection(CONNECT*, bool))[0x5639a43973b2]
 
sql/sql_connect.cc:1314(handle_one_connection)[0x5639a4397062]
 
perfschema/pfs.cc:2203(pfs_spawn_thread)[0x5639a491f28a]
 
addr2line: DWARF error: section .debug_info is larger than its filesize! (0x5b4757 vs 0x429c68)
 
??:0(pthread_condattr_setpshared)[0x7f961871b947]
 
??:0(clone)[0x7f96187aba44]
 
 
 
Trying to get some variables.
 
Some pointers may be invalid and cause the dump to abort.
 
Query (0x7f95f8015300): CHECKSUM TABLE t1, t2 EXTENDED

Generated at Thu Feb 08 09:57:26 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.