There is a lot of problems with mariabackup, if DDL statements are executed in parallel to backup.
Some of them show up even if --lock-ddl-per-table is used.
A fundamental problem with lock-ddl-per-table is that only those tables, that exist at the start of backup, are MDL-locked. Another problem is that mariabackup will deadlock during backup's "FLUSH TABLE WITH READ LOCKS", if concurrent DDL statements run. To resolve this deadlock, we either KILL user's DDL query, or user has the option to omit FTWRL with unsafe --no-lock option (
So, there are problems which we currently have with backup in presence of Innodb DDL.
- Tables created during backup are not there after prepare (with/without lock-ddl-per-table)
- Tables dropped during backup might show up after prepare (without lock-ddl-per-table), or backup fails if file that was at the start of backup, is not found during copy.
- If lock-ddl-per-table is used, acquiring MDL lock will fail, if the table is concurrently being dropped or renamed.
- Tables that are renamed during backup, do not show up after prepare, if rename happens after table was copied.
- Tables that are recreated (dropped, and created under the same name) during backup,after tablespace copy, break prepare due to different tablespace id.
- Backup fails with "ALTER TABLE or OPTIMIZE TABLE was executed during backup"
- Concurrent multi-rename is prone to race condition, for example table t1 can be missed from backup, if RENAME TABLE t1 to t2, t3 to t1 runs concurrently.
A possible fix to some of those problems would be to
- Tolerate missing files during innodb copy - missing files can happen when DROP or RENAME runs in parallel. Also allow MDL lock failures in --lock-ddl-per-table.
- At the end of backup, under protection of FTWRL, during the stage when frm and MyISAM files are copied, rescan data directory, looking for Innodb tablespaces, and copy those that are not already in backup. This will pick up tables from parallel CREATE or RENAME.
- We might need to remove orphan (frm-less) .ibd files in prepare - there can be some left due to RENAME, or DROP.
- Recreated (DROP/CREATE) tablespaces, that have changed tablespace ids at the end of backup. We might need to copy them to backup second time. This is be tricky for streaming backup, since xbstream format does not support multiple copies of the same file , and does not have any "delete" command. One way to workaround it, it to give second copy special extension, e.g "ibd.new" , so that "prepare" would know it can replace old copy with the new one.
Maybe some of the above can be taken care by Innodb recovery, I'm not really sure how this would work. Marko Mäkelä, any idea on that?
"ALTER TABLE or OPTIMIZE TABLE was executed during backup" must be taken care by Innodb however.