Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.18, 10.0.23, 10.1.10, 10.0(EOL), 10.1(EOL)
-
None
-
Linux, Windows
-
10.0.24
Description
Somewhere between MariaDB 10.0.12 and MariaDB 10.0.21 the behavior of ALTER TABLE changed. We have one special ALTER TABLE statement that worked until version 10.0.12 (and maybe later) but stopped to work at version 10.0.21 (maybe earlier). We tried version 10.1.10 as well, which still produces the error.
How to reproduce:
First create a table like this:
CREATE TABLE `w_findispmon05u` (
`atpkey` INT(11) NOT NULL DEFAULT '0',
`atzo05` INT(11) NULL DEFAULT NULL,
`pos` BIGINT(21) NULL DEFAULT NULL,
`f5BnvB` INT(9) NULL DEFAULT NULL,
`f5atbvb` INT(11) NULL DEFAULT NULL,
`f5atbwmg` INT(11) NULL DEFAULT NULL,
`f5pBneu` BIGINT(12) NULL DEFAULT NULL,
`atbwdt` INT(11) NULL DEFAULT NULL,
`atbwzt` INT(11) NULL DEFAULT NULL,
`atbart` VARCHAR(10) NULL DEFAULT NULL
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
Next do an ALTER table like this:
ALTER TABLE `w_findispmon05u`
CHANGE COLUMN `atpkey` `f5atpkey` INT(11) NOT NULL DEFAULT '0' FIRST,
CHANGE COLUMN `atzo05` `f5atzo05` INT(11) NULL DEFAULT NULL AFTER `f5atpkey`,
CHANGE COLUMN `atbwdt` `f5atbwdt` INT(11) NULL DEFAULT NULL AFTER `f5pBneu`,
CHANGE COLUMN `atbwzt` `f5atbwzt` INT(11) NULL DEFAULT NULL AFTER `f5atbwdt`,
CHANGE COLUMN `atbart` `f5atbart` VARCHAR(10) NULL DEFAULT NULL AFTER `f5atbwzt`,
ADD INDEX `atpkey` (`f5atpkey`),
ADD INDEX `inatkey` (`f5atzo05`, `pos`),
ADD INDEX `pos` (`pos`, `f5atzo05`);
The message is 'Incorrect key file for table 'w_findispmon05u'; try to repair it'.
If the ALTER table is split into two statements as follows, the error disappears.
ALTER TABLE `w_findispmon05u`
CHANGE COLUMN `atpkey` `f5atpkey` INT(11) NOT NULL DEFAULT '0' FIRST,
CHANGE COLUMN `atzo05` `f5atzo05` INT(11) NULL DEFAULT NULL AFTER `f5atpkey`,
CHANGE COLUMN `atbwdt` `f5atbwdt` INT(11) NULL DEFAULT NULL AFTER `f5pBneu`,
CHANGE COLUMN `atbwzt` `f5atbwzt` INT(11) NULL DEFAULT NULL AFTER `f5atbwdt`,
CHANGE COLUMN `atbart` `f5atbart` VARCHAR(10) NULL DEFAULT NULL AFTER `f5atbwzt`;ALTER TABLE `w_findispmon05u`
ADD INDEX `atpkey` (`f5atpkey`),
ADD INDEX `inatkey` (`f5atzo05`, `pos`),
ADD INDEX `pos` (`pos`, `f5atzo05`);
We see this behavior with different operating systems (Windows 7 64Bit, various Linux variants), so we think that this does not matter.
To test it with the version 10.1.10, we just used the MSI installer for Windows 64 Bit with all default settings. The error can be reproduced this way.
Attachments
Issue Links
- causes
-
MDEV-13668 InnoDB unnecessarily rebuilds table when renaming a column and adding index
- Closed
- is duplicated by
-
MDEV-9548 Alter table (renaming and adding index) fails with "Incorrect key file for table"
- Closed
- relates to
-
MDEV-13640 ALTER TABLE CHANGE and ADD INDEX on auto_increment column fails with "Incorrect key file for table..."
- Closed
-
MDEV-13838 Wrong result after altering a partitioned table
- Closed