MySQL Bugs: #93712: Growing, then shrinking # of InnoDB Undo Tablespaces does not clean up old files

Bug #93712	Growing, then shrinking # of InnoDB Undo Tablespaces does not clean up old files
Submitted:	21 Dec 2018 8:41	Modified:	9 Jan 2019 13:01
Reporter:	Roel Van de Paar	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	8.0.13	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
8.0.13>SET @@GLOBAL.innodb_undo_tablespaces=100;
Query OK, 0 rows affected (27.97 sec)

8.0.13>SET @@GLOBAL.innodb_undo_tablespaces=10;
Query OK, 0 rows affected (0.00 sec)

Yet 100 files are left in data dir

How to repeat:
SET @@GLOBAL.innodb_undo_tablespaces=100;
SET @@GLOBAL.innodb_undo_tablespaces=10;

Hello Roel. In version 8.0.13, the ability to increase and decrease the number of undo tablespaces is implemented by changing the value of setting innodb-undo-tablespaces, as you report. This was implemented in WL9507 and was put into version 8.0.2.

The end goal was to allow the number of undo tablespaces to be changed online using SQL with CREATE/ALTER/DROP UNDO TABLESPACE commands. This was completed in WL8508 for the soon to be released version 8.0.14.

You may notice in the documentation that innodb-undo-tablespaces has been deprecated. In 8.0.14, that setting will be ignored. And undo tablespaces will be dropped using the DROP UNDO TABLESPACE command.

So this bug, as you report, is a temporary inconvenience that was a technical problem intentionally left for WL9508. An undo tablespace cannot be deleted until it is completely empty of undo logs. And that job is done by the purge thread in the background. So the code to delete an old undo tablespace was left out of WL9507 and completed in WL9508.

As a workaround in 8.0.13, you can delete the files yourself. But you have no way to see if these unused undo tablespaces still have any active undo logs in them. After a slow shutdown, they should be empty. But if there were open transactions at shutdown, then they might not. So it might be safe to wait a couple days. Obviously, this is not a good workaround.

In 8.0.14, you will first make the undo tablespace inactive with ALTER UNDO TABLESPACE name SET INACTIVE. Then monitor the status of it using a new 'state' column in information_schema.innodb_tablespaces. When it is empty, you will be able to delete the file using DROP UNDO TABLESPACE name.

The documentation for 8.0.14 will fully explain the use of the new SQL used to control undo tablespaces.

Thank you for the bug report.

Thank you Kevin for the detailed analysis/info.

Posted by developer:
 
This bug is fixed in 8.0.14 as described above. Moving it to documentation so that the inability to delete an undo tablespace by InnoDB in 8.0.x before 8.0.14 is documented.

Posted by developer:
 
The documentation was updated as follows:

https://dev.mysql.com/doc/refman/8.0/en/innodb-undo-tablespaces.html

"Prior to MySQL 8.0.14, deactivated undo tablespaces cannot be removed.
Manual removal of undo tablespace files is possible after a slow shutdown
but is not recommended, as deactivated undo tablespaces may contain active
undo logs for some time after the server is restarted if open transactions
were present when shutting down the server. As of MySQL 8.0.14, undo
tablespaces can be dropped using DROP UNDO TABALESPACE syntax."

Thank you for the bug report.