MySQL Bugs: #110832: undo truncation in 5.7 is not crash safe

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

Description:
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:
N/A

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.

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.

regards,
Umesh

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.

Is there any progress recently?