Bug #110832 undo truncation in 5.7 is not crash safe
Submitted: 27 Apr 8:44 Modified: 16 May 6:43
Reporter: jie xu Email Updates:
Status: Can't repeat Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any

[27 Apr 8:44] jie xu
It's a variant of the bug https://bugs.mysql.com/bug.php?id=93170.

In 5.7. two checkpoints are made before and after undo tablespace truncate.

But it use log_make_checkpoint_at(LSN_MAX, TRUE) make two checkpoints, this operation flush as much as possible, but can't make sure checkpoint bigger or equal log_sys->lsn;
So there maybe some redo logs belong to the truncated area of undo tablespace.

How to repeat:

Suggested fix:
use log_make_checkpoint_at(log_get_lsn(), TRUE) make checkpoint before undo tablespace truncate.

It can make sure there hasn't redo logs belong to the truncated area of undo tablespace.
[3 May 5:33] MySQL Verification Team
Hello jie xu,

Thank you for the report and feedback.
With the slight modification(no test case changes but the way it is invoked), I tried the test case mentioned in https://bugs.mysql.com/bug.php?id=93170 but not seeing any issues.

Do you have a test case which confirms the issue? Thank you.


 ./mtr  innodb_undo.truncate_recover  --mysqld=--innodb_undo_tablespaces=2 --bootstrap=--innodb_undo_tablespaces=2 --big-test --debug-server
Logging: ./mtr  innodb_undo.truncate_recover --mysqld=--innodb_undo_tablespaces=2 --bootstrap=--innodb_undo_tablespaces=2 --big-test --debug-server
MySQL Version 5.7.42
Checking supported features...
 - SSL connections supported
 - binaries are debug compiled
Collecting tests...
Checking leftover processes...
 - found old pid 9778 in 'mysqld.1.pid', killing it...
   process did not exist!
Removing old var directory...
Creating var directory '/export/home/tmp/ushastry/mysql-5.7.42/mysql-test/var'...
Installing system database...
Using parallel: 1


TEST                                      RESULT   TIME (ms) or COMMENT

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
[ 50%] innodb_undo.truncate_recover             [ pass ]  94843
[100%] shutdown_report                          [ pass ]
The servers were restarted 0 times
Spent 94.843 of 102 seconds executing testcases

Completed: All 2 tests were successful.

[5 May 6:33] jie xu
I tried to reproduce this issue locally, finally it didn't occur.

But i did encounter this issue in our production environment.

A MySQL instance apply redo crashed when recovery.

I found this recovery checkpoint lsn is 43977226940520, but the undo truncate log lsn is 43977957543246; the checkpoint lsn is too small.

And MySQL 8.0 hasn't this bug, since it use log_make_latest_checkpoint() to make checkpoint when undo truncate; this func make sure make checkpoint to latest lsn.
[16 May 6:43] jie xu
Is there any progress recently?