La página personal de Juan Gabriel Covas

Herramientas de usuario

Herramientas del sitio


Problem with MariaDB 10.4 and partial backup restore (IMPORT TABLESPACE)

The default alter_algorithm in MariaDB 10.4 is DEFAULT which tries to use the fastest way, including the new INSTANT algorithm.

Problem: if you DROP or ADD a column on a table, not being the last one, you'll be later unable to IMPORT TABLESPACE when restoring from a partial backup taken with the mariabackup tool, getting a schema error like the following:

ERROR 1808 (HY000) at line 1: Schema mismatch (Index field name xxxxxx doesn't match tablespace metadata field name yyyyyy for field position X) 

This belongs to this MDEV-18543 official issue of MariaDB -where I added a comment- and where you can read: “IMPORT TABLESPACE currently fails to read the mapping of clustered index fields to columns from the hidden metadata record”. So the .cfg files that mariabackup –export generate, are not going to be valid until the bug gets resolved, which is said “not trivial to do”.

Anyway I came up with a solution after reading those slides and documentation from MariaDB on the new INSTANT ALTERs and new features of MariaDB versions 10.3 and 10.4.

You can rebuild the metadata of the schema using this command:

ALTER TABLE dbname.dbtable FORCE;

Then you can grab again a backup (or manually transport the InnoDB table), --prepare and --export again, and IMPORT TABLESPACE should work now. Note that this seems not being replicated to any slaves.

A simple way to rebuild the metadata for all tables (so we fix them), note that it's simple but not quick, depending on your table data:

Getting a list of InnoDB tables on a given “dbname”, with the commands to “FORCE” (fix them)

mysql --batch --silent --skip-column-names -e \
"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' LOCK=EXCLUSIVE, FORCE;') FROM information_schema.tables \
WHERE table_schema = 'dbame' AND engine = 'InnoDB'"

Passing the command list to mysql:

mysql --batch --silent --skip-column-names -e \
"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' LOCK=EXCLUSIVE, FORCE;') FROM information_schema.tables \
WHERE table_schema = 'dbame' AND engine = 'InnoDB'" | mysql

Problem is that future ALTERs (while the bug is not solved), will again render the schema bugged for the “IMPORT TABLESPACE”, so you can:

  • Enforce the use of , ALGORITHM=copy on each dangerous ALTER statement.
  • or SET SESSION alter_algorithm=copy (see alter_algorithm system variable) before an ALTER that could cause this “schema mismatch” issue with MariaDB 10.4
  • or just setting alter_algorithm=copy on your server.cnf configuration (check that it survives a mariadb restart using SELECT @@alter_algorithm;
  • 2020 - Also you can use innodb_instant_alter_column_allowed=add_last in server config. as seen here since an INSTANT add_last column works, the problematic ALTER changes are column drop and reorder (reorder being applied when adding a column which will not be the last). This new server variable is only valid starting with MariaDB 10.4.14, 10.5.3, and later versions

The COPY algorithm is going to slow things down when doing ALTER on big tables, but you know…

Hope that helps.


linux/howtos/mariadb-104-mariabackup-partial-backup.txt · Última modificación: 09/09/2021 06:07 por Juanga Covas