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

'Incorrect key file' on ALTER TABLE

    XMLWordPrintable

Details

    • 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

              jplindst Jan Lindström (Inactive)
              thomas.mischke Thomas Mischke
              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.