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

Unknown error 1877 (corruption) when opening table altered with ALTER TABLE LOCK=NONE that was imported using IMPORT TABLESPACE

    XMLWordPrintable

Details

    Description

      We noticed an easily reproducible table corruption after following these high-level steps

      1. ALTER a table to insert a new column with ALGORITHM=INPLACE, LOCK=NONE
      2. Export the table with FLUSH TABLES FOR EXPORT
      3. Import the table with IMPORT TABLE
      4. Restart mariadb

      After these 4 steps, trying to query the table will give this error:

      SELECT * FROM migrate_test.Test LIMIT 0,1;
      ERROR 1030 (HY000): Got error 1877 "Unknown error 1877" from storage engine InnoDB
      

      Workarounds

      It seems this error does not happen if we ALTER TABLE with ALGORITHM=COPY, LOCK=SHARED

      I suspect the instant altering is changing the table structure in a way that messes up the import.

      Full repro steps

      # Create a table
      CREATE DATABASE migrate_test;
      CREATE TABLE `migrate_test`.`Test` (`id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `name` VARCHAR(100) NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;
      INSERT INTO migrate_test.`Test` (`id`, `name`) VALUES (NULL, 'Harry'), (NULL, 'Peter');
       
      # Alter the table
      ALTER TABLE migrate_test.`Test` ADD `age` INT NOT NULL AFTER `id`, ALGORITHM=INPLACE, LOCK=NONE;
       
      # Export the table
      FLUSH TABLES migrate_test.Test FOR EXPORT;
       
      # In shell: copy out a backup
      # mysqldump migrate_test > ~/migrate_test.sql
      # rm -rf ~/migrate_test
      # cp -rf /var/lib/mysql/migrate_test ~
       
      UNLOCK TABLES;
       
      # Delete the database and recreate the table from scratch
      DROP DATABASE migrate_test;
      CREATE DATABASE migrate_test;
       
      # In shell: import the schema
      # mysql migrate_test < ~/migrate_test.sql
       
      ALTER TABLE migrate_test.Test DISCARD TABLESPACE;
       
      # In shell: copy over the table
      # cp -f ~/migrate_test/*.ibd ~/migrate_test/*.cfg /var/lib/mysql/migrate_test
      # chown mysql:mysql /var/lib/mysql/migrate_test/*
      # chmod 660 /var/lib/mysql/migrate_test/*
       
      ALTER TABLE migrate_test.Test IMPORT TABLESPACE;
      

      Expected

      1. Run `ALTER TABLE migrate_test.Test ADD blah INT NOT NULL AFTER id;` - it succeeds
      2. Restart MariaDB
      3. Run `SELECT * FROM migrate_test.Test LIMIT 0,1;` - it succeeds

      Actual

      1. Run `ALTER TABLE migrate_test.Test ADD blah INT NOT NULL AFTER id;` - errors with "ERROR 1034 (HY000): Index for table 'Test' is corrupt; try to repair it"
      2. Restart MariaDB
      3. Run `SELECT * FROM migrate_test.Test LIMIT 0,1;` - it errors with "ERROR 1030 (HY000): Got error 1877 "Unknown error 1877" from storage engine InnoDB"

      Attachments

        Activity

          People

            marko Marko Mäkelä
            hsroce Harry Yu
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.