Bug #98628 create undo tablespace is deleting existing undo tablespace file
Submitted: 17 Feb 2020 7:10 Modified: 23 Mar 2020 13:31
Reporter: Rahul Malik (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:8.0.19 OS:Any
Assigned to: CPU Architecture:x86

[17 Feb 2020 7:10] Rahul Malik 
Description:
Create undo tablespace is deleting the existing undo tablespace file.

mysql> create undo tablespace tx1 add datafile "/dev/undo/tx1.ibu";                                                                                                                             2020-02-17T07:05:24.866666Z 10 [Note] [MY-012896] [InnoDB] Creating UNDO Tablespace /tmp/rahul/tx1.ibu
2020-02-17T07:05:24.866684Z 10 [Note] [MY-012897] [InnoDB] Setting file /dev/undo/tx1.ibu size to 10 MB
2020-02-17T07:05:24.866690Z 10 [Note] [MY-012898] [InnoDB] Physically writing the file full
2020-02-17T07:05:24.919572Z 10 [Note] [MY-013018] [InnoDB] Created 128 and tracked 128 new rollback segment(s) in undo tablespace number 3. 128 are now active.
Query OK, 0 rows affected (0.23 sec)

-rw-r----- 1 rahul.malik rahul.malik 10485760 Feb 17 07:05 undo_001
-rw-r----- 1 rahul.malik rahul.malik 10485760 Feb 17 07:05 tx1.ibu
-rw-r----- 1 rahul.malik rahul.malik 10485760 Feb 17 07:05 undo_002

mysql> create undo tablespace tx2 add datafile "tx1.ibu";                                                                                                                                        2020-02-17T07:05:50.854936Z 11 [ERROR] [MY-013039] [InnoDB] Can't create UNDO tablespace tx2  since '/dev/undo/tx1.ibu' already exists.
ERROR 1528 (HY000): Failed to create UNDO TABLESPACE tx2

and the file is gone

total 20480
-rw-r----- 1 rahul.malik rahul.malik 10485760 Feb 17 07:05 undo_002
-rw-r----- 1 rahul.malik rahul.malik 10485760 Feb 17 07:05 undo_001
{smm-12-d40aec o8 rahul}$

it is deleting the tx1.ibu file from disk while trying to create tx2. 

it works fine if
create undo tablespace tx1 add datafile tx1.ibu (success)
create undo tablespace tx2 add datafile tx1.ibu (failed but no delete of tx1.ibu from  disk) 

How to repeat:
database was started with --innodb_undo_directory "/dev/undo".

mysql> create undo tablespace tx1 add datafile "/dev/undo/tx1.ibu";                                                                                                                             2020-02-17T07:05:24.866666Z 10 [Note] [MY-012896] [InnoDB] Creating UNDO Tablespace /tmp/rahul/tx1.ibu
2020-02-17T07:05:24.866684Z 10 [Note] [MY-012897] [InnoDB] Setting file /dev/undo/tx1.ibu size to 10 MB
2020-02-17T07:05:24.866690Z 10 [Note] [MY-012898] [InnoDB] Physically writing the file full
2020-02-17T07:05:24.919572Z 10 [Note] [MY-013018] [InnoDB] Created 128 and tracked 128 new rollback segment(s) in undo tablespace number 3. 128 are now active.
Query OK, 0 rows affected (0.23 sec)

-rw-r----- 1 rahul.malik rahul.malik 10485760 Feb 17 07:05 undo_001
-rw-r----- 1 rahul.malik rahul.malik 10485760 Feb 17 07:05 tx1.ibu
-rw-r----- 1 rahul.malik rahul.malik 10485760 Feb 17 07:05 undo_002

mysql> create undo tablespace tx2 add datafile "tx1.ibu";                                                                                                                                        2020-02-17T07:05:50.854936Z 11 [ERROR] [MY-013039] [InnoDB] Can't create UNDO tablespace tx2  since '/dev/undo/tx1.ibu' already exists.
ERROR 1528 (HY000): Failed to create UNDO TABLESPACE tx2

and the file is gone

total 20480
-rw-r----- 1 rahul.malik rahul.malik 10485760 Feb 17 07:05 undo_002
-rw-r----- 1 rahul.malik rahul.malik 10485760 Feb 17 07:05 undo_001
{smm-12-d40aec o8 rahul}$
[17 Feb 2020 10:09] MySQL Verification Team 
Hello Rahul,

Thank you for the report and feedback.

regards,
Umesh
[23 Mar 2020 13:31] Daniel Price 
Posted by developer:
 
Fixed as of the upcoming 8.0.20 release, and here's the proposed changelog entry from the documentation team:

A CREATE UNDO TABLESPACE operation that specified an undo data file name
without specifying a path removed an existing undo data file of the same
name from the directory specified by innodb_undo_directory variable. The
file name conflict check was performed on the data directory instead of
the directory specified by the innodb_undo_directory variable.