Bug #93712 Growing, then shrinking # of InnoDB Undo Tablespaces does not clean up old files
Submitted: 21 Dec 2018 8:41 Modified: 9 Jan 13:01
Reporter: Roel Van de Paar (OCA) 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

[21 Dec 2018 8:41] Roel Van de Paar
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;
[21 Dec 2018 17:00] Kevin Lewis
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.
[22 Dec 2018 20:39] Miguel Solorzano
Thank you for the bug report.
[25 Dec 2018 23:13] Roel Van de Paar
Thank you Kevin for the detailed analysis/info.
[4 Jan 15:47] Kevin Lewis
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.
[9 Jan 13:01] Daniel Price
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.