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

'Incorrect key file' on ALTER TABLE

    XMLWordPrintable

    Details

    • Sprint:
      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

            Activity

              People

              Assignee:
              jplindst Jan Lindström
              Reporter:
              thomas.mischke Thomas Mischke
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: