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

MariaDB 10.3.10-10.3.13 corrupts table and refuses to start with assertion in row0sel.cc 2986

Details

    Description

      Zabbix Servers with MariaDB comes up after restart with corrupted Zabbix items table. It always affects the same table. All other tables are good.

      Because of the corrupted table, MariaDB Server refuses to start with assertion in row0sel.cc line 2968.

      Trying to recover the corrupted tables according to https://mariadb.com/kb/en/library/innodb-recovery-modes/ didn't work for all recovery modes from 1-6.

      my.cnf:
      -------
      [client]
      port = 3306
      socket = /var/run/mysqld/mysqld.sock
      [mysqld_safe]
      socket = /var/run/mysqld/mysqld.sock
      nice = 0
      [mysqld]
      pid-file = /var/run/mysqld/mysqld.pid
      socket = /var/run/mysqld/mysqld.sock
      port = 3306
      basedir = /usr
      datadir = /var/lib/mysql
      tmpdir = /tmp
      lc_messages_dir = /usr/share/mysql
      lc_messages = en_US
      skip-external-locking
      max_connections = 100
      connect_timeout = 5
      wait_timeout = 600
      max_allowed_packet = 16M
      thread_cache_size = 128
      sort_buffer_size = 4M
      bulk_insert_buffer_size = 16M
      tmp_table_size = 32M
      max_heap_table_size = 32M
      myisam_recover_options = BACKUP
      key_buffer_size = 128M
      table_open_cache = 400
      myisam_sort_buffer_size = 512M
      concurrent_insert = 2
      read_buffer_size = 2M
      read_rnd_buffer_size = 1M
      query_cache_limit = 128K
      query_cache_size = 64M
      long_query_time = 10
      expire_logs_days = 10
      max_binlog_size = 100M
      default_storage_engine = InnoDB
      innodb_log_file_size = 1250M
      innodb_log_files_in_group = 4
      innodb_log_buffer_size = 1250M
      innodb_flush_log_at_trx_commit = 1
      innodb_buffer_pool_size = 5G
      innodb_buffer_pool_instances = 4
      innodb_buffer_pool_chunk_size = 1250M
      innodb_file_per_table = 1
      innodb_open_files = 400
      innodb_io_capacity = 400
      innodb_flush_method = O_DIRECT
      [galera]
      [mysqldump]
      quick
      quote-names
      max_allowed_packet = 16M
      [mysql]
      [isamchk]
      key_buffer = 16M
      !include /etc/mysql/mariadb.cnf
      !includedir /etc/mysql/conf.d/

      Attachments

        Issue Links

          Activity

            wdoekes Walter Doekes added a comment - - edited

            Hi there. We just ran into this problem. It was reproducible apparently, because we recently imported a dump and the problem persisted over the imports.

            Because of the corrupted table, MariaDB Server refuses to start with assertion in row0sel.cc line 2968.

            In our case, we start just fine, but we have trouble with table accesses.

            Source server: mariadb 10.3.12 (origin of the db dump)

            Problematic server:

            Server version: 10.3.16-MariaDB-1:10.3.16+maria~bionic-log mariadb.org binary distribution
            

            MariaDB [acceptance1]> check table xxx_xxxxx; 
            +-----------------------+-------+----------+----------------------------------------------------------------------------------------------------+
            | Table                 | Op    | Msg_type | Msg_text                                                                                           |
            +-----------------------+-------+----------+----------------------------------------------------------------------------------------------------+
            | acceptance1.xxx_xxxxx | check | Warning  | InnoDB: The B-tree of index PRIMARY is corrupted.                                                  |
            | acceptance1.xxx_xxxxx | check | Warning  | InnoDB: Index 'xxx_xxxxx_xxxxxxx_id_index' contains 30399 entries, should be 18446744073709551615. |
            | acceptance1.xxx_xxxxx | check | error    | Corrupt                                                                                            |
            +-----------------------+-------+----------+----------------------------------------------------------------------------------------------------+
            3 rows in set (0.379 sec)
             
            MariaDB [(none)]> select count(*) from acceptance1.xxx_xxxxx;
            +----------+
            | count(*) |
            +----------+
            |    30399 |
            +----------+
            

            In the source DB, this table is fine.

            Any UPDATE or ANALYZE TABLE on this table causes the assertion as mentioned:

            2019-10-18 09:05:40 0x7f1bf05ad700  InnoDB: Assertion failure in file /home/buildbot/buildbot/build/mariadb-10.3.16/storage/innobase/row/row0sel.cc line 2986
            InnoDB: Failing assertion: prebuilt->trx->isolation_level == TRX_ISO_READ_UNCOMMITTED
            InnoDB: We intentionally generate a memory trap.
            ...
            /lib/x86_64-linux-gnu/libc.so.6(abort+0x141)[0x7f1d43ac5801]
            mysqld(+0x49f856)[0x557d83a93856]
            mysqld(+0x49ec8c)[0x557d83a92c8c]
            mysqld(+0x49ef89)[0x557d83a92f89]
            mysqld(+0x9bb30a)[0x557d83faf30a]
            mysqld(+0x8df357)[0x557d83ed3357]
            mysqld(_ZN7handler11ha_rnd_nextEPh+0x79)[0x557d83d4bd29]
            mysqld(_Z13rr_sequentialP11READ_RECORD+0x22)[0x557d83e5bcd2]
            mysqld(_Z12mysql_updateP3THDP10TABLE_LISTR4ListI4ItemES6_PS4_jP8st_orderybPySA_+0xe9e)[0x557d83c085de]
            mysqld(_Z21mysql_execute_commandP3THD+0x3cb0)[0x557d83b6ab40]
            mysqld(_Z11mysql_parseP3THDPcjP12Parser_statebb+0x20a)[0x557d83b6f13a]
            mysqld(+0x57b9e6)[0x557d83b6f9e6]
            mysqld(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0x133f)[0x557d83b711cf]
            mysqld(_Z10do_commandP3THD+0x178)[0x557d83b72908]
            mysqld(_Z24do_handle_one_connectionP7CONNECT+0x212)[0x557d83c44a12]
            mysqld(handle_one_connection+0x3d)[0x557d83c44bed]
            

            If we're trying to allocate values close to (uint64_t)-1, then you'd expect failure indeed:

                            /* NOTE: if we are retrieving a big BLOB, we may
                            already run out of memory in the next call, which
                            causes an assert */
             
                            data = btr_rec_copy_externally_stored_field(
                                    rec, offsets,
                                    dict_table_page_size(prebuilt->table),
                                    field_no, &len, heap);
             
                            if (UNIV_UNLIKELY(!data)) {
                                /* assertion is here, as you already pointed out */
            

            Data has been synced using:

            • nightly mariabackup 1:10.3.12+maria~bionic
            • this data was then prepared on the source host (mariabackup --prepare --target-dir=/tank/mysql/restore)
            • this data was then copied (zfs send/recv) to the destination host
            • and started with mariadb 10.3.16

            If I come up with any other info, I'll amend.

            wdoekes Walter Doekes added a comment - - edited Hi there. We just ran into this problem. It was reproducible apparently, because we recently imported a dump and the problem persisted over the imports. Because of the corrupted table, MariaDB Server refuses to start with assertion in row0sel.cc line 2968. In our case, we start just fine, but we have trouble with table accesses. Source server: mariadb 10.3.12 (origin of the db dump) Problematic server: Server version: 10.3.16-MariaDB-1:10.3.16+maria~bionic-log mariadb.org binary distribution MariaDB [acceptance1]> check table xxx_xxxxx; +-----------------------+-------+----------+----------------------------------------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +-----------------------+-------+----------+----------------------------------------------------------------------------------------------------+ | acceptance1.xxx_xxxxx | check | Warning | InnoDB: The B-tree of index PRIMARY is corrupted. | | acceptance1.xxx_xxxxx | check | Warning | InnoDB: Index 'xxx_xxxxx_xxxxxxx_id_index' contains 30399 entries, should be 18446744073709551615. | | acceptance1.xxx_xxxxx | check | error | Corrupt | +-----------------------+-------+----------+----------------------------------------------------------------------------------------------------+ 3 rows in set (0.379 sec)   MariaDB [(none)]> select count(*) from acceptance1.xxx_xxxxx; +----------+ | count(*) | +----------+ | 30399 | +----------+ In the source DB, this table is fine. Any UPDATE or ANALYZE TABLE on this table causes the assertion as mentioned: 2019-10-18 09:05:40 0x7f1bf05ad700 InnoDB: Assertion failure in file /home/buildbot/buildbot/build/mariadb-10.3.16/storage/innobase/row/row0sel.cc line 2986 InnoDB: Failing assertion: prebuilt->trx->isolation_level == TRX_ISO_READ_UNCOMMITTED InnoDB: We intentionally generate a memory trap. ... /lib/x86_64-linux-gnu/libc.so.6(abort+0x141)[0x7f1d43ac5801] mysqld(+0x49f856)[0x557d83a93856] mysqld(+0x49ec8c)[0x557d83a92c8c] mysqld(+0x49ef89)[0x557d83a92f89] mysqld(+0x9bb30a)[0x557d83faf30a] mysqld(+0x8df357)[0x557d83ed3357] mysqld(_ZN7handler11ha_rnd_nextEPh+0x79)[0x557d83d4bd29] mysqld(_Z13rr_sequentialP11READ_RECORD+0x22)[0x557d83e5bcd2] mysqld(_Z12mysql_updateP3THDP10TABLE_LISTR4ListI4ItemES6_PS4_jP8st_orderybPySA_+0xe9e)[0x557d83c085de] mysqld(_Z21mysql_execute_commandP3THD+0x3cb0)[0x557d83b6ab40] mysqld(_Z11mysql_parseP3THDPcjP12Parser_statebb+0x20a)[0x557d83b6f13a] mysqld(+0x57b9e6)[0x557d83b6f9e6] mysqld(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0x133f)[0x557d83b711cf] mysqld(_Z10do_commandP3THD+0x178)[0x557d83b72908] mysqld(_Z24do_handle_one_connectionP7CONNECT+0x212)[0x557d83c44a12] mysqld(handle_one_connection+0x3d)[0x557d83c44bed] If we're trying to allocate values close to (uint64_t)-1, then you'd expect failure indeed: /* NOTE: if we are retrieving a big BLOB, we may already run out of memory in the next call, which causes an assert */   data = btr_rec_copy_externally_stored_field( rec, offsets, dict_table_page_size(prebuilt->table), field_no, &len, heap);   if (UNIV_UNLIKELY(!data)) { /* assertion is here, as you already pointed out */ Data has been synced using: nightly mariabackup 1:10.3.12+maria~bionic this data was then prepared on the source host (mariabackup --prepare --target-dir=/tank/mysql/restore) this data was then copied (zfs send/recv) to the destination host and started with mariadb 10.3.16 If I come up with any other info, I'll amend.

            msc, I believe that the problems that you are seeing are related to using instant ADD COLUMN. Unfortunately, we have been unable to reproduce that so far, despite adding consistency checks to debug builds. Let us track that one in MDEV-19783.

            marko Marko Mäkelä added a comment - msc , I believe that the problems that you are seeing are related to using instant ADD COLUMN . Unfortunately, we have been unable to reproduce that so far, despite adding consistency checks to debug builds. Let us track that one in MDEV-19783 .

            wdoekes, by data dump, do you mean a logical dump, which consists of SQL statements? If so, that could help us repeat the problem.

            A physical dump (used with ALTER TABLE…IMPORT TABLESPACE) would not help, because the steps between the time the corruption was introduced and it was noticed would be unknown. We have been trying hard to reproduce this with a sequence of SQL statements, but we have so far been unsuccessful. If I had such a sequence, this bug should be trivial to locate and fix.

            You can upload files to ftp.mariadb.com. It would only be accessible to some employees of MariaDB Corporation. You can obfuscate as much of the data as possible, while still keeping it repeatable. I think that for this bug, the PRIMARY KEY values must be preserved. For any other columns, the data should not matter (except that for any variable-length columsn, the lengths should be preserved).

            Once again, I am not interested in a physical dump (.ibd file).

            marko Marko Mäkelä added a comment - wdoekes , by data dump, do you mean a logical dump, which consists of SQL statements? If so, that could help us repeat the problem. A physical dump (used with ALTER TABLE…IMPORT TABLESPACE ) would not help, because the steps between the time the corruption was introduced and it was noticed would be unknown. We have been trying hard to reproduce this with a sequence of SQL statements, but we have so far been unsuccessful. If I had such a sequence, this bug should be trivial to locate and fix. You can upload files to ftp.mariadb.com. It would only be accessible to some employees of MariaDB Corporation. You can obfuscate as much of the data as possible, while still keeping it repeatable. I think that for this bug, the PRIMARY KEY values must be preserved. For any other columns, the data should not matter (except that for any variable-length columsn, the lengths should be preserved). Once again, I am not interested in a physical dump ( .ibd file).
            wdoekes Walter Doekes added a comment -

            Hi Marko,

            thanks for the reply.

            I don't know if this table was altered with instant-columns. But the "fix" you wrote in https://jira.mariadb.org/browse/MDEV-19783?focusedCommentId=132884&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-132884 does not appear to work.

            Dropping the index on the FK succeeded, but the corruption persisted on the PK.

            MariaDB [acceptance1]> check table xxx_xxxxx;
            +-----------------------+-------+----------+---------------------------------------------------+
            | Table                 | Op    | Msg_type | Msg_text                                          |
            +-----------------------+-------+----------+---------------------------------------------------+
            | acceptance1.xxx_xxxxx | check | Warning  | InnoDB: The B-tree of index PRIMARY is corrupted. |
            | acceptance1.xxx_xxxxx | check | error    | Corrupt                                           |
            +-----------------------+-------+----------+---------------------------------------------------+
            2 rows in set (0.335 sec)
            

            Suggested fix from MDEV-19783:

            MariaDB [acceptance1]> ALTER TABLE xxx_xxxxx FORCE;
            command terminated with exit code 137
            

            No luck. Is it expected to crash on ALTER TABLE FORCE? Or is this new to you?

            do you mean a logical dump, which consists of SQL statements? If so, that could help us repeat the problem.

            I'm sorry, no such luck. Only ibd files.

            We'll see if we can poke at it from another angle in the mean time. The productions servers have been up and running since Feb. So after reading some bug comments, I'm becoming a bit nervous they'll get in trouble after a restart.

            wdoekes Walter Doekes added a comment - Hi Marko, thanks for the reply. I don't know if this table was altered with instant-columns. But the "fix" you wrote in https://jira.mariadb.org/browse/MDEV-19783?focusedCommentId=132884&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-132884 does not appear to work. Dropping the index on the FK succeeded, but the corruption persisted on the PK. MariaDB [acceptance1]> check table xxx_xxxxx; +-----------------------+-------+----------+---------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +-----------------------+-------+----------+---------------------------------------------------+ | acceptance1.xxx_xxxxx | check | Warning | InnoDB: The B-tree of index PRIMARY is corrupted. | | acceptance1.xxx_xxxxx | check | error | Corrupt | +-----------------------+-------+----------+---------------------------------------------------+ 2 rows in set (0.335 sec) Suggested fix from MDEV-19783 : MariaDB [acceptance1]> ALTER TABLE xxx_xxxxx FORCE; command terminated with exit code 137 No luck. Is it expected to crash on ALTER TABLE FORCE? Or is this new to you? do you mean a logical dump, which consists of SQL statements? If so, that could help us repeat the problem. I'm sorry, no such luck. Only ibd files. We'll see if we can poke at it from another angle in the mean time. The productions servers have been up and running since Feb. So after reading some bug comments, I'm becoming a bit nervous they'll get in trouble after a restart.
            wdoekes Walter Doekes added a comment -

            So. We got ourselves a cleaner environment, and recreated the crash with only this table. Using the original MariaDB 10.3.12.

            DROP TABLE didn't cause a crash. And reloading the data from a mysqldump fixed it in development. That sounds like a viable fix on production then. (Unfortunately that won't bring this ticket any closer to a resolution.)

            wdoekes Walter Doekes added a comment - So. We got ourselves a cleaner environment, and recreated the crash with only this table. Using the original MariaDB 10.3.12. DROP TABLE didn't cause a crash. And reloading the data from a mysqldump fixed it in development. That sounds like a viable fix on production then. (Unfortunately that won't bring this ticket any closer to a resolution.)

            People

              marko Marko Mäkelä
              msc Martin Schroeter
              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.