|
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 'MapEmbedMocks' 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"
|