Clear MySQL undo table space

Queries

Show undo table space with disk space usage.

SELECT * FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE LIKE 'UNDO LOG';

List active transactions

SELECT trx.trx_id,
       trx.trx_started,
       trx.trx_mysql_thread_id
FROM INFORMATION_SCHEMA.INNODB_TRX trx
         JOIN INFORMATION_SCHEMA.PROCESSLIST ps ON trx.trx_mysql_thread_id = ps.id
WHERE trx.trx_started < CURRENT_TIMESTAMP - INTERVAL 1 SECOND
  AND ps.user != 'system_user';

Clear undo files

Create a new empty undo file and empty the others

CREATE UNDO TABLESPACE temp_undo_003 ADD DATAFILE 'temp_undo_003.ibu';

Clear undo_001

ALTER UNDO TABLESPACE innodb_undo_001 SET INACTIVE;

Wait empty state

SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME = 'innodb_undo_001';

Make it active again

ALTER UNDO TABLESPACE innodb_undo_001 SET ACTIVE;

Same things with undo_002

ALTER UNDO TABLESPACE innodb_undo_002 SET INACTIVE;

SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME = 'innodb_undo_002';

ALTER UNDO TABLESPACE innodb_undo_002 SET ACTIVE;

Remove temp undo_003

ALTER UNDO TABLESPACE temp_undo_003 SET INACTIVE;
DROP UNDO TABLESPACE temp_undo_003;

---

Source: https://blog.raphaelpiccolo.com/fr/post/show/956