[MDEV-23741] InnoDB: Table ... contains <n> indexes inside InnoDB, which is different from the number of indexes <n> defined in the MariaDB Created: 2020-09-16  Updated: 2021-04-19  Resolved: 2020-09-17

Status: Closed
Project: MariaDB Server
Component/s: Platform Windows
Affects Version/s: 10.3.18
Fix Version/s: 10.3.26, 10.4.16, 10.5.7

Type: Bug Priority: Major
Reporter: Allen Lee (Inactive) Assignee: Vladislav Vaintroub
Resolution: Fixed Votes: 0
Labels: None
Environment:

Windows server 2012

Issue Links:
Relates
relates to MDEV-17567 Atomic DDL Closed

 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



 Comments   
Comment by Marko Mäkelä [ 2020-09-16 ]

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.
Comment by Marko Mäkelä [ 2020-09-16 ]

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.

Comment by Vladislav Vaintroub [ 2020-09-17 ]

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.

Generated at Thu Feb 08 09:24:41 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.