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.
Marc
added a comment - 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.
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 711:26 tblWebUser.MAD
-rw-r----- 1 mysql mysql 23953408 Mär 711: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 919:02 a.MAD
-rw-rw---- 1 mysql mysql 23961600 Mär 919: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)
Marc
added a comment - - edited 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)
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?
Sergei Golubchik
added a comment - 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?
Lena Startseva
added a comment - 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
People
Sergei Golubchik
Marc
Votes:
0Vote for this issue
Watchers:
4Start watching this issue
Dates
Created:
Updated:
Resolved:
Git Integration
Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.
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.