JuangaCovas.info

La página personal de Juan Gabriel Covas

Herramientas de usuario

Herramientas del sitio


linux:howtos:mariadb-104-mariabackup-partial-backup

¡Esta es una revisión vieja del documento!


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.

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 a [Mariadb-10.4] section of your server.cnf configuration (check that it survives a mariadb restart using SELECT @@alter_algorithm;

Hope that helps.

~~DISCUSSION|Comentarios~~

linux/howtos/mariadb-104-mariabackup-partial-backup.1594395513.txt.bz2 · Última modificación: 10/07/2020 17:38 por Juanga Covas