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

InnoDB: Table ... contains <n> indexes inside InnoDB, which is different from the number of indexes <n> defined in the MariaDB

Details

    Description

      Customer reported the following error when running alter table <x> add index.

      [10.3.18]
      		 
      2020-03-15 1:00:03 1573540 [ERROR] InnoDB: Table status/currenteustatus20200315 contains 5 indexes inside InnoDB, which is different from the number of indexes 4 defined in the MariaDB Have you mixed up .frm files from different installations? See https://mariadb.com/kb/en/innodb-troubleshooting/

      this is exactly like MDEV-21669, but it wasn't fixed in 10.3.18. When I ran repro with 10.4.12, it returned the warning instead of error. In order to reproduce this, I've set data directory to read-only mode in windows and then it returned the following warning.

      [10.4.12]
      		 
      INNODB: TABLE test/currenteustatus20200315 CONTAINS 7 INDEXES inside INNODB, which IS different FROM the NUMBER OF INDEXES 6 defined IN the MariaDB

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-17567 Atomic DDL

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          links to

          Web Link dba stack exhange question of the same form

          Activity

            Ascending order - Click to sort in descending order
            marko Marko Mäkelä added a comment -

            allen.lee@mariadb.com, I tried but failed to repeat this on GNU/Linux with the following test case:

            --source include/have_innodb.inc
            		 
            CREATE TABLE t1(id SERIAL, LastModified DATETIME) ENGINE=INNODB;
            		 
             
            		 
            let $datadir=`select @@datadir`;
            		 
            exec chmod -w $datadir/test;
            		 
            ALTER TABLE t1 ADD INDEX i_lastmodified2 (lastmodified);
            		 
            exec chmod u+w $datadir/test;
            		 
            CHECK TABLE t1;
            		 
            DROP TABLE t1;

            On Windows, the chmod may have to be replaced with something compatible. For me, it fails as follows:

            10.3 7e07e38cf687ccd7fa3bd3a35c1eb7e4b307ca5f

            		 
            mysqltest: At line 6: query 'ALTER TABLE t1 ADD INDEX i_lastmodified2 (lastmodified)' failed: 1005: Can't create table `test`.`#sql-f6c0_9` (errno: 13 "Permission denied")

            I also tried with the described version:

            mariadb-10.3.18

            		 
            mysqltest: At line 6: query 'ALTER TABLE t1 ADD INDEX i_lastmodified2 (lastmodified)' failed: 1005: Can't create table `test`.`#sql-11247_9` (errno: 13 "Permission denied")

            It could be that this bug only affects the Windows platform, which I did not use.

            I suspect that what happens is the following:

            1. The ADD INDEX operation in InnoDB will modify the .ibd file only, like in any DML operation.
            2. But, any ALTER TABLE operation would create a new .frm file, rename .frm files and finally delete the original .frm files. This should be prevented by the permissions.
            3. On Windows, we would probably keep using the old .frm file and flag the mismatch later inside InnoDB.
            marko Marko Mäkelä added a comment - allen.lee@mariadb.com , I tried but failed to repeat this on GNU/Linux with the following test case: --source include/have_innodb.inc CREATE TABLE t1(id SERIAL, LastModified DATETIME) ENGINE=INNODB;   let $datadir=` select @@datadir`; exec chmod -w $datadir/test; ALTER TABLE t1 ADD INDEX i_lastmodified2 (lastmodified); exec chmod u+w $datadir/test; CHECK TABLE t1; DROP TABLE t1; On Windows, the chmod may have to be replaced with something compatible. For me, it fails as follows: 10.3 7e07e38cf687ccd7fa3bd3a35c1eb7e4b307ca5f mysqltest: At line 6: query 'ALTER TABLE t1 ADD INDEX i_lastmodified2 (lastmodified)' failed: 1005: Can't create table `test`.`#sql-f6c0_9` (errno: 13 "Permission denied") I also tried with the described version: mariadb-10.3.18 mysqltest: At line 6: query 'ALTER TABLE t1 ADD INDEX i_lastmodified2 (lastmodified)' failed: 1005: Can't create table `test`.`#sql-11247_9` (errno: 13 "Permission denied") It could be that this bug only affects the Windows platform, which I did not use. I suspect that what happens is the following: The ADD INDEX operation in InnoDB will modify the .ibd file only, like in any DML operation. But, any ALTER TABLE operation would create a new .frm file, rename .frm files and finally delete the original .frm files. This should be prevented by the permissions. On Windows, we would probably keep using the old .frm file and flag the mismatch later inside InnoDB.
            marko Marko Mäkelä added a comment -

            I believe that this failure scenario is specific to Windows. On Windows, apparently making a file read-only will prevent renaming.

            But, the .frm file would only be renamed after the ALTER TABLE operation was already committed inside the storage engine.

            I think that this failure scenario should be considered in MDEV-17567. Ideally, we need a distributed transaction where both the file system operations and the storage engine operations are participating.

            marko Marko Mäkelä added a comment - I believe that this failure scenario is specific to Windows. On Windows, apparently making a file read-only will prevent renaming. But, the .frm file would only be renamed after the ALTER TABLE operation was already committed inside the storage engine. I think that this failure scenario should be considered in MDEV-17567 . Ideally, we need a distributed transaction where both the file system operations and the storage engine operations are participating.
            wlad Vladislav Vaintroub added a comment -

            The fix I'm providing is merely a workaround. my_rename will do a couple of retries if file to be renamed is in use.
            The presumed scenario is 3rd party software like backup or antivirus, which might open files for a short moment, so for that scenario retrying is a viable workaround.

            For a better solution, we'd need atomic DDL.

            wlad Vladislav Vaintroub added a comment - The fix I'm providing is merely a workaround. my_rename will do a couple of retries if file to be renamed is in use. The presumed scenario is 3rd party software like backup or antivirus, which might open files for a short moment, so for that scenario retrying is a viable workaround. For a better solution, we'd need atomic DDL.

            People

              wlad Vladislav Vaintroub
              allen.lee@mariadb.com Allen Lee (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              5 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.