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.


Timo Stein, 03/11/2020 12:09

Thanks *a lot* for your help! I'm quite a beginner with SQL and found out a lot of things during the last 24 h. a) phpMyAdmin is not really 100 % compatible to MariaDB. b) Temporal data tables can not be backed up and restored using CREATEs and INSERTs. Instead, the local files belonging to the database need to be moved around. c) Bugs like the one you mention make it hard to backup and restore.

Here's what I did to move the tables MYTABLE within database MYDATABASE from a XAMPP Windows installation (MariaDB 10.4) to Ubuntu Server (MariaDB 10.4):

(source machine)

  • ALTER TABLE MYDATABASE.MYTABLE FORCE; (for each of the tables)
  • SHOW CREATE TABLE `MYDATABASE`.`MYTABLE`; (for each of the tables)
  • this will get you all the information to restore the blank database/table structure. I wrote a PHP script to merge the results of those queries into a ready-to-use SQL query.
  • mariabackup –backup –user MYUSERNAME –password MYPASSWORD –target-dir C:\backups\mariadb
  • mariabackup –prepare –export –databases MYDATABASE –user MYUSERNAME –password MYPASSWORD –target-dir C:\backups\mariadb
  • copy the directory C:\backups\mariadb to ~/mariadb on the target machine

(target machine)

  • apply the SQL queries that were returend by SHOW CREATE DATABASE and SHOW CREATE TABLE to restore the database/table structure
  • sudo cp ~/mariadb/*.* /var/lib/mysql/MYDATABASE
  • sudo chown -R mysql:mysql /var/lib/mysql/MYDATABASE

This worked for me and restored all temporal data as well. Took me nearly a day to figure this out… I don't understand why temporal data can't be backed up/restored via mysqldump in the first place. Gracías a tí, Juanga!

Timo Stein, 03/11/2020 13:05

Bullet point no. three on the target machine should be: sudo cp ~/mariadb/MYDATABASE/*.* /var/lib/mysql/MYDATABASE

Eventually, one wants to backup only the database in question. In this case, one could add –databases MYDATABASE also within the fifth bullet point on the source machine: mariabackup –backup –databases MYDATABASE –user MYUSERNAME –password MYPASSWORD –target-dir C:\backups\mariadb

Juanga Covas, 11/04/2021 19:24

Glad to see it helped! Thanks for your comments

Escribe el comentario. Se permite la sintaxis wiki:
linux/howtos/mariadb-104-mariabackup-partial-backup.txt · Última modificación: 09/09/2021 06:07 por Juanga Covas