Details

    Description

      We faced to index corruption in innodb table.

      2024-11-20  2:04:11 4299263 [ERROR] mariadbd: Index for table 'message' is corrupt; try to repair it
      2024-11-20  2:04:11 4299257 [ERROR] InnoDB: We detected index corruption in an InnoDB type table. You have to dump + drop + reimport the table or, in a case of widespread corruption, dump all InnoDB tables and recreate the whole tablespace. If the mariadbd server crashes after the startup or when you dump the tables. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery.
      

      We couldn't backup table with mysqldump, but we can drop table.
      So we drop table and recreate it using slave table.

      Is there any cases that index was corrupted?
      Do you know reason why table or index is corrupted?
      And How can we prevent corruption of index?

      Attachments

        1. my-2.cnf
          5 kB
        2. query.txt
          0.8 kB
        3. table.txt
          0.7 kB

        Activity

          MariaDB Server 10.11.3 was released in May 2023. Since then, there have been many bug fixes. One of them is MDEV-32511, which was a regression due to MDEV-26827. That bug alone could explain basically any corruption. It is somewhat hard to repeat; you would need to execute mariadb-backup --backup or kill the server just at the right moment, while a log checkpoint is in progress. That is just one example; there could be other bugs that could explain this.

          Can you reproduce this corruption with a more recent version of MariaDB Server, such as 10.11.10?

          marko Marko Mäkelä added a comment - MariaDB Server 10.11.3 was released in May 2023. Since then, there have been many bug fixes. One of them is MDEV-32511 , which was a regression due to MDEV-26827 . That bug alone could explain basically any corruption. It is somewhat hard to repeat; you would need to execute mariadb-backup --backup or kill the server just at the right moment, while a log checkpoint is in progress. That is just one example; there could be other bugs that could explain this. Can you reproduce this corruption with a more recent version of MariaDB Server, such as 10.11.10?
          supbaek baek seung ho added a comment -

          We didn't run backup and insert that table at the same time.
          The `message` table is batch table and batch is running at 02:00 and mariabackup is running at 20:00.
          Is there other reasons that table is corrupted?

          supbaek baek seung ho added a comment - We didn't run backup and insert that table at the same time. The `message` table is batch table and batch is running at 02:00 and mariabackup is running at 20:00. Is there other reasons that table is corrupted?
          supbaek baek seung ho added a comment - - edited

          After the index was corrupted on 2024-11-20, the index was corrupted again.
          It is the same table as last time, and it is a table used only in batches.

          2024-12-07  1:57:45 11642302 [ERROR] mariadbd: Index for table 'message' is corrupt; try to repair it
          2024-12-07  1:57:45 11642301 [ERROR] InnoDB: We detected index corruption in an InnoDB type table. You have to dump + drop + reimport the table or, in a case of widespread corruption, dump all InnoDB tables and recreate the whole tablespace. If the mariadbd server crashes after the startup or when you dump the tables. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery.
          

          I've attached the table creation syntax and query syntax.
          The number of variables used in the query is 320, the time required is less than 0.1 seconds, and it is performed a total of 5 times.

          For reference, in another service environment that uses the same table, 870 variables are used, but the index has not been corrupted yet.

          supbaek baek seung ho added a comment - - edited After the index was corrupted on 2024-11-20, the index was corrupted again. It is the same table as last time, and it is a table used only in batches. 2024 - 12 - 07 1 : 57 : 45 11642302 [ERROR] mariadbd: Index for table 'message' is corrupt; try to repair it 2024 - 12 - 07 1 : 57 : 45 11642301 [ERROR] InnoDB: We detected index corruption in an InnoDB type table. You have to dump + drop + reimport the table or, in a case of widespread corruption, dump all InnoDB tables and recreate the whole tablespace. If the mariadbd server crashes after the startup or when you dump the tables. Please refer to https: //mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery. I've attached the table creation syntax and query syntax. The number of variables used in the query is 320, the time required is less than 0.1 seconds, and it is performed a total of 5 times. For reference, in another service environment that uses the same table, 870 variables are used, but the index has not been corrupted yet.

          People

            Unassigned Unassigned
            supbaek baek seung ho
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.