MySQL Bugs: #92099: provide an option to record redo log for bulk loading

Bug #92099	provide an option to record redo log for bulk loading
Submitted:	21 Aug 2018 8:14	Modified:	14 Nov 2019 15:12
Reporter:	Fungo Wang (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	5.7, 8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	alter, Backup, meb, PXB

Description:
MySQL 5.7 introduced bulk loading optimization for index creation, detail in this worklog https://dev.mysql.com/worklog/task/?id=7277.

Redo log recording is disabled for bulk loading index creation, which is unsafe for physical online backup, both PXB and MEB. These tools rely on redo log and will fail with error:

```
An optimized (without  redo logging) DDLoperation  has been performed. All  modified pages may not have  been flushed to the disk yet.
```

PXB has a option `--lock-del-per-table` for such issue https://blueprints.launchpad.net/percona-xtrabackup/+spec/mdl-lock-tables, but this solution is not perfect, see details at:
https://jira.percona.com/browse/PXB-1539
https://jira.percona.com/browse/PXB-793

The latest MariaDB 10.3.9 has a option `innodb_log_optimize_ddl` to enable redo log recording for bulk load. This option can be used by PXB to enable redo recording during backup, and get a safe backup.

https://mariadb.com/kb/en/library/mariadb-1039-release-notes/
https://jira.mariadb.org/browse/MDEV-16809

Maybe MySQL can also provide such option to enable redo log recording, for physical backup (PXB or MEB).

How to repeat:
1. read the code in recv_parse_or_apply_log_rec_body()
or
2. take a physical backup and create index during backup, we will get failed backup.

Suggested fix:
Like what MariaDB does, provide an option to enable redo log recording, for physical backup (PXB or MEB).

Hi Mr. Wang,

Thank you for your feature request.

I think that this request is justified.

Verified as reported.

BTW, have you tried our Clone Plugin. It could do what you are asking for.

Hi Sinisa,

Thanks for your verification.

Clone plugin is a great feature in MySQL 8.0, but we have not employed it in our pro env yet.

And MySQL 5.7 does not have clone plugin :(

Currently we use the solution as I suggested in my initial FR report. We ported the redo recording for bulk load feature from MariaDB, and PXB works well with it.

Well, the fact that you are using 5.7 is irrelevant, since it is closed for new features long time ago.

So this feature, if implemented in future, will go into 8.0 only, or both 5.7 and 8.0?

Definitely not in 5.7.

8.0 or higher. 5.7 is closed for new features for couple of years.

Regarding other versions or releases, we do not know as we are not involved, in any manner, in the scheduling of the new features.

Okay, thanks :)

BTW, I just added 8.0 into the affected versions.