Avoiding the "An optimized (without redo logging) DDL operation has been performed" Error with Percona XtraBackup

This blog discusses newly added options for Percona XtraBackup 2.4.8 and how they can impact your database backups.

To avoid issues with MySQL 5.7 skipping the redo log for DDL, Percona XtraBackup has implemented three new options ( xtrabackup --lock-ddl, xtrabackup --lock-ddl-timeout, xtrabackup --lock-ddl-per-table) that can be used to place MDL locks on tables while they are copied.

So why we need those options? Let’s discuss the process used to get there.

Originally, we found problems while running DDLs: Percona XtraBackup produced corrupted backups as described in two reports:

• #1555626
• #1532878

After experimenting, it was clear that the core cause of those fails was MySQL 5.7 skipping redo logging for some DDLs. This is a newly added feature to MySQL named Sorted Index Builds. You can read more from following links:

• Sorted Index Builds
• https://dev.mysql.com/worklog/task/?id=7277

To prevent this we introduced a solution: wWhen Percona XtraBackup detects skipping the redo log), it aborts the backup to prevent creating a corrupted backup.

The scary error message you get with this fix is:

[FATAL] InnoDB: An optimized(without redo logging) DDLoperation has been performed. All modified pages may not have been flushed to the disk yet.
Percona XtraBackup will not be able to take a consistent backup. Retry the backup operation

We need to avoid aborting backup with this message. So how do we do that? Let’s create a test case first and reproduce the issue.

Prepare two tables:

Create a test.sh file and place it in the sandbox:

Run the script in a loop while the backup is taken:

Try to take a backup:

You will likely get something like:

Ok, now we have reproduced the error. To avoid this error, XtraBackup has the new options as mentioned above.

Using --lock-ddl:

The new thing you should notice is:

And the backup status:

Another new option is --lock-ddl-per-table:

The new output will look like this:

The result of the backup:

The another thing I should add here is about using --lock-ddl with non-Percona Server for MySQL servers. For example., using it with MariaDB:

But you can use --lock-ddl-per-table with any server. Use --lock-ddl-per-table with caution, it can block updates to tables for highly loaded servers under some circumstances. Let’s explore one:

If one connection holds an MDL lock, and another connection does ALTER TABLE (CREATE INDEX is mapped to an ALTER TABLE statement to create indexes), then updates to that table are blocked.

Testing this with the backup process is quite easy:

Sample table:

So our “connection 1:” is an  xtrabackup command:

So after running the backup command and doing the same steps for “connection 2” and “connection 3,” the result is something like this in processlist:

Updates are only completed after the backup, as described. It should be clear now why you should use it with caution.

The last thing we should discuss is if you do not want to use any “hacks” with xtrabackup , you can do things on the MySQL side such as:

1. Avoiding bad DDLs 🙂
2. Enabling old_alter_table. When this variable is enabled, the server does not use the optimized method of processing an ALTER TABLE operation. It reverts to using a temporary table, copying over the data and then renaming the temporary table to the original, as used by MySQL 5.0 and earlier. So it is going to use ALGORITHM=COPY for alters.

In conclusion, we do not have a panacea for this issue but, you can use some of the described tricks to get rid of this problem. Thanks for reading!