MySQL Bugs: #78351: Redo log does not indicate when redo logging is skipped

Bug #78351	Redo log does not indicate when redo logging is skipped
Submitted:	7 Sep 2015 7:50	Modified:	9 Sep 2015 19:23
Reporter:	Marko Mäkelä	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.7.5	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
As part of WL#7277 Bulk Load for Create Index,
InnoDB disabled redo logging of index page writes during DDL operations.

InnoDB crash recovery is not affected by this, because there will be extra page flushes before the DDL operation is committed. But, external hot backup tools will have no way of detecting if the redo logging was disabled, and they can thus create a corrupted backup without noticing it, if there was concurrent DDL.

How to repeat:
CREATE TABLE t(a INT PRIMARY KEY, b INT) ENGINE=InnoDB;
[insert some data]

Repeatedly issue the following in a client connection while taking a hot backup:
ALTER TABLE t ADD INDEX(b); ALTER TABLE t DROP INDEX b;

After restoring the backup, try CHECK TABLE t;

To improve repeatability, use several tables and a large number of records in them.

You can also try the following:
SET GLOBAL innodb_file_per_table=OFF;
CREATE TABLE t(...) ENGINE=InnoDB;
[insert lots of data]

and then run OPTIMIZE TABLE t; in a loop while taking a backup. Note: the table will be copied from/to the system tablespace (ibdata1).

Suggested fix:
After completing the extra flush and before re-enabling redo logging, issue a new redo log record MLOG_INDEX_CREATE(index->space,index->page,index->id). When hot backup tools see this record, they can either retry copying the tablespace (if it is small) or abort the backup, asking the user to avoid concurrent DDL.

Posted by developer:
 
Fixed as of the upcoming 5.7.9, 5.8.0 release, and here's the changelog entry:

The redo log provided no indication that redo logging is disabled for
index page writes during concurrent DDL operations. As a result, external
hot backup tools could produce corrupt backups.