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:
, ALGORITHM=copy
on each dangerous ALTER statement.SET SESSION alter_algorithm=copy
(see alter_algorithm system variable) before an ALTER that could cause this “schema mismatch” issue with MariaDB 10.4alter_algorithm=copy
on your server.cnf
configuration (check that it survives a mariadb restart using SELECT @@alter_algorithm;
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 versionsThe COPY algorithm is going to slow things down when doing ALTER on big tables, but you know…
Hope that helps.
~~DISCUSSION|Comentarios~~