Looks like the fix for MDEV-14795 didn't handle leaked undo pages (because even their extent has never been marked as freed). So MDEV-14795 assumes that it is used by server. This does NOT allow to shrink ibdata1 in a newer versions, even after a slow shutdown and even when UNDO is already moved to separate tablespaces (so instance re-creation is still required if the instance was ever affected by MDEV-30671.
I can repeat the issue.
Followed the steps in MDEV-31234 in affected version.
1) started the server with --innodb-immediate-scrub-data-uncompressed=1. --innodb_undo_tablespace=0.
2) Ran sysbench. did slow shutdown and ended up with 1528 to_purge undo log pages. (leaked undo log corrupt pages)
Start the latest 10.6 against the data directory, did slow shutdown
2024-07-04 14:29:50 0 [Note] InnoDB: Truncating system tablespace from 4864 to 2496 pages
2024-07-04 14:29:50 0 [Note] InnoDB: System tablespace truncated successfully
Ran innochecksum tool against shrinked system tablespace and we end up with same
So verified what was exist before 2496 page, it was all leaked undo pages and previous extent state has been marked as "XDES_FREE_FRAG" and 12 pages has been used in that extent.
#::2484 | Undo log page |, Will be freed in purge when all undodata in it is removed, -
#::2485 | Undo log page |, Will be freed in purge when all undodata in it is removed, -
#::2486 | Undo log page |, Will be freed in purge when all undodata in it is removed, -
#::2487 | Undo log page |, Will be freed in purge when all undodata in it is removed, -
#::2488 | Undo log page |, Will be freed in purge when all undodata in it is removed, -
#::2489 | Undo log page |, Will be freed in purge when all undodata in it is removed, -
#::2490 | Undo log page |, Will be freed in purge when all undodata in it is removed, -
#::2491 | Undo log page |, Will be freed in purge when all undodata in it is removed, -
#::2492 | Undo log page |, Will be freed in purge when all undodata in it is removed, -
#::2493 | Undo log page |, Will be freed in purge when all undodata in it is removed, -
#::2494 | Undo log page |, Will be freed in purge when all undodata in it is removed, -
#::2495 | Undo log page |, Will be freed in purge when all undodata in it is removed, -
MDEV-14795 didn't handle leaked undo pages because even their extent has never been marked as freed. So MDEV-14795 assumes that it is used by server. MDEV-14795 finds the last used extent in the system tablespace and truncates the remaining file.
Attached the ibdata1 of the leaky undo pages for future reference.
Thirunarayanan Balathandayuthapani
added a comment - - edited I can repeat the issue.
Followed the steps in MDEV-31234 in affected version.
1) started the server with --innodb-immediate-scrub-data-uncompressed=1. --innodb_undo_tablespace=0.
2) Ran sysbench. did slow shutdown and ended up with 1528 to_purge undo log pages. (leaked undo log corrupt pages)
Start the latest 10.6 against the data directory, did slow shutdown
2024-07-04 14:29:50 0 [Note] InnoDB: Truncating system tablespace from 4864 to 2496 pages
2024-07-04 14:29:50 0 [Note] InnoDB: System tablespace truncated successfully
Ran innochecksum tool against shrinked system tablespace and we end up with same
to_purge Undo page state: 0 active, 128 cached, 1528 to_purge, 0 prepared, 148 other
So verified what was exist before 2496 page, it was all leaked undo pages and previous extent state has been marked as "XDES_FREE_FRAG" and 12 pages has been used in that extent.
#::2484 | Undo log page |, Will be freed in purge when all undodata in it is removed, -
#::2485 | Undo log page |, Will be freed in purge when all undodata in it is removed, -
#::2486 | Undo log page |, Will be freed in purge when all undodata in it is removed, -
#::2487 | Undo log page |, Will be freed in purge when all undodata in it is removed, -
#::2488 | Undo log page |, Will be freed in purge when all undodata in it is removed, -
#::2489 | Undo log page |, Will be freed in purge when all undodata in it is removed, -
#::2490 | Undo log page |, Will be freed in purge when all undodata in it is removed, -
#::2491 | Undo log page |, Will be freed in purge when all undodata in it is removed, -
#::2492 | Undo log page |, Will be freed in purge when all undodata in it is removed, -
#::2493 | Undo log page |, Will be freed in purge when all undodata in it is removed, -
#::2494 | Undo log page |, Will be freed in purge when all undodata in it is removed, -
#::2495 | Undo log page |, Will be freed in purge when all undodata in it is removed, -
MDEV-14795 didn't handle leaked undo pages because even their extent has never been marked as freed. So MDEV-14795 assumes that it is used by server.
MDEV-14795 finds the last used extent in the system tablespace and truncates the remaining file.
Attached the ibdata1 of the leaky undo pages for future reference.
The undo page leak bug MDEV-31234 was fixed a long time ago. This fix is only freeing up the previously leaked pages in the system tablespace.
Marko Mäkelä
added a comment - The undo page leak bug MDEV-31234 was fixed a long time ago. This fix is only freeing up the previously leaked pages in the system tablespace.
origin/11.2-MDEV-34529 c1fc29443a213367c7b0c521d0d9c1c1636bf48b 2024-10-10T19:21:33+05:30
performed well in RQG testing. No new problems.
No objections against pushing the fix.
Matthias Leich
added a comment - - edited origin/11.2- MDEV-34529 c1fc29443a213367c7b0c521d0d9c1c1636bf48b 2024-10-10T19:21:33+05:30
performed well in RQG testing. No new problems.
No objections against pushing the fix.
Bug
Critical
MDEV-14795 InnoDB system tablespace cannot be shrunk
I can repeat the issue.
Followed the steps in
MDEV-31234in affected version.1) started the server with --innodb-immediate-scrub-data-uncompressed=1. --innodb_undo_tablespace=0.
2) Ran sysbench. did slow shutdown and ended up with 1528 to_purge undo log pages. (leaked undo log corrupt pages)
Start the latest 10.6 against the data directory, did slow shutdown
2024-07-04 14:29:50 0 [Note] InnoDB: Truncating system tablespace from 4864 to 2496 pages2024-07-04 14:29:50 0 [Note] InnoDB: System tablespace truncated successfullyRan innochecksum tool against shrinked system tablespace and we end up with same
to_purge Undo page state: 0 active, 128 cached, 1528 to_purge, 0 prepared, 148 otherSo verified what was exist before 2496 page, it was all leaked undo pages and previous extent state has been marked as "XDES_FREE_FRAG" and 12 pages has been used in that extent.
#::2484 | Undo log page |, Will be freed in purge when all undodata in it is removed, -#::2485 | Undo log page |, Will be freed in purge when all undodata in it is removed, -#::2486 | Undo log page |, Will be freed in purge when all undodata in it is removed, -#::2487 | Undo log page |, Will be freed in purge when all undodata in it is removed, -#::2488 | Undo log page |, Will be freed in purge when all undodata in it is removed, -#::2489 | Undo log page |, Will be freed in purge when all undodata in it is removed, -#::2490 | Undo log page |, Will be freed in purge when all undodata in it is removed, -#::2491 | Undo log page |, Will be freed in purge when all undodata in it is removed, -#::2492 | Undo log page |, Will be freed in purge when all undodata in it is removed, -#::2493 | Undo log page |, Will be freed in purge when all undodata in it is removed, -#::2494 | Undo log page |, Will be freed in purge when all undodata in it is removed, -#::2495 | Undo log page |, Will be freed in purge when all undodata in it is removed, -MDEV-14795didn't handle leaked undo pages because even their extent has never been marked as freed. SoMDEV-14795assumes that it is used by server.MDEV-14795finds the last used extent in the system tablespace and truncates the remaining file.Attached the ibdata1 of the leaky undo pages for future reference.