Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-28020

CHECKSUM TABLE calculates different checksums

Details

    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?

      Attachments

        Issue Links

          Activity

            mokraemer 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.

            mokraemer 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.

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

            serg Sergei Golubchik added a comment - Is there any way I can repeat it? Are your tables big, can you share them?
            mokraemer 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)
            

            mokraemer 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?

            serg 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?

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

            serg Sergei Golubchik added a comment - Hold on, we'll try to reproduce based on that

            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
            

            lstartseva 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

              serg Sergei Golubchik
              mokraemer Marc
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start 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.