Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.11.6
-
None
-
Ubuntu 20.04.3 LTS x86_64; MariaDB installed via official MariaDB repo
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"