WL#9508: InnoDB: Support CREATE/ALTER/DROP UNDO TABLESPACE

Affects: Server-8.0   —   Status: Complete 

Requirements
============
1) Create SQL commands to CREATE and DROP undo tablespaces to fulfill the
primary requirement: "Users of InnoDB should be able to add and drop
undo tablespaces through a SQL interface at runtime so that the location of each
Undo Tablespace can be chosen individually".

2) Stop creating undo tablespaces through the innodb_undo_tablespaces setting so
that there is only one way to add or drop undo tablespaces. 

SYNTAX
======

CREATE UNDO TABLESPACE
----------------------
MySQL should support the UNDO keyword in the following syntax:

   CREATE UNDO TABLESPACE `undo99` ADD DATAFILE 'undo99.ibu';

The datafile can be a file name only or it can be a full absolute path. But it
must end with the suffix ".ibu".

If a simple file name is used will be put into the location specified by
--innodb-undo-directory which is a read-only startup variable.  If
--innodb-undo-directory is not explicitly set, the datadir is used.

Any absolute path location must be in the list of known directories defined by
--datadir, --innodb-directories, --innodb-home-directory & --innodb-undo-directory.

But the datafile may not be a relative path since there would be confusion as to
what it is relative to; the undo directory or the datadir where all other
relative paths originate from.

If you want to use an undo datafile location other than the datadir but you do
not want the stored filename to be an absolute path, you can set
--innodb-undo-directory to a relative path. It will be relative to the datadir.
A simple file name in the ADD DATAFILE parameter will be added to that path. 
Then the filepath shown in information_schema.files and the path stored in the
DD will be a relative path.

DROP UNDO TABLESPACE
--------------------
Any undo tablespace created by the above statement should be dropped using:

   DROP UNDO TABLESPACE `undo99`;

ALTER UNDO TABLESPACE
---------------------
In order to DROP any existing tablespace it must be empty in the current design.
Likewise, in order to drop an undo tablespace, it must also be empty. To
accomplish this, we will introduce a command that will mark the UNDO tablespace
as 'inactive'.

   ALTER UNDO TABLESPACE 'undo99' SET INACTIVE;

This command will mark the undo tablespace so that it will no longer be used to
assign rollback segments to new transactions. In order for that tablespace to
become empty, all the active transactions that used undo logs in the rollback
segments in that tablespace must be completed, as well as any transaction that
was started before those transactions completed.  Then the purge thread must
clean up all undo logs and undo log segments in this tablespaces' rollback
segments.  Finally, when the rollback segments are no longer used, it is fully
inactive and empty and can be dropped.

This same process is currently being used to truncate undo tablespaces. The
purge thread does the work to clean them up and to truncate them. It will do the
same for undo tablespaces that have been explicitly marked as 'inactive'. It
will not delete the tablespace because instead of dropping it, the user may do:

   ALTER UNDO TABLESPACE 'undo99' SET ACTIVE;

This could be done at any time by the user instead of DROP UNDO TABLESPACE.  

If DROP UNDO TABLESPACE is done at any time before the tablespace has been
truncated, then it will return HA_ERR_TABLESPACE_IS_NOT_EMPTY as InnoDB does now
for a normal tablespace.


INNODB_UNDO_TABLESPACES
=======================
WL#10498 changed the default value of innodb_undo_tablespaces to 2.
WL#10583 then changed the minimum value of innodb_undo_tablespaces to 2.
With 2 undo tablespaces, it is possible to use innodb_undo_log_truncate=ON.
So WL#10499 changed the default value of innodb_undo_log_truncate to ON.

We would like to not use innodb_undo_tablespaces anymore and instead rely on SQL
to create undo tablespaces. But we need to have rollback segments created
somewhere when the instance is initialized.  This location for rollback segments
must exist before any SQL can be accepted.  The pre-8.0 engine creates one
rollback segment in the system tablespace very early during startup.  WL9507
stopped using that rollback segment and created rollback segments in separate
undo tablespaces instead.  We want to continue using separate undo tablespaces
that can be truncated because we want to move away from any dependence on the
system tablespace. So we should create at least 2 undo tablespaces by default
during initialization.

Instead of having 2 ways of creating and dropping undo tablespaces, we should
stop using and deprecate innodb_undo_tablespaces. Then we will create 2 default
independent undo tablespaces as part of each instance.  These undo tablespaces
files are currently named 'undo_001' & 'undo_002' with no extension.  This was
done in WL#9507. Since the explicit undo tablespaces will have a file extension
of '.ibu', it will be beneficial to leave the current naming of these implicit
undo tablespaces the same so that there will be no filename conflicts. 

The tablespace names for the 2 implicit undo tablespaces are 'innodb_undo_001'
and 'innodb_undo_002'.  Since the create tablespace process does not allow the
user to create a tablespace name that starts with 'innodb', these names also
will not conflict with explicit undo tablespace names. 

Implicit undo tablespaces are created in the default undo directory and they are
not permitted to be dropped.  But they can be made inactive with;
    ALTER UNDO TABLESPACE `innodb_undo_001` SET INACTIVE;
Both implicit undo tablespaces will be made active at bootstrap and remain
active until they are SET INACTIVE. There must be at least 2 explicit undo
tablespaces active before these two implicit undo tablespaces can be made inactive. 


UNDO TABLESPACE DISCOVERY
=========================
All undo tablespaces must be discovered and opened before redo recovery and
before any other datafile is opened so that uncommitted transactions can be
rolled back, including rollback of the data dictionary.
Even undo tablespaces that were not involved in redo recovery must be open and
available before recovery.

This means that the data dictionary cannot be used at startup for undo file
discovery.

Implicitly created undo tablespaces are found easily before recovery because
they are always in the default undo directory. But explicit SQL generated undo
tablespaces can be placed in separate locations specified in the ADD DATAFILE
statement. That location is saved in the data dictionary but these remote undo
tablespaces must be discovered and opened before the DD can be used.

The setting --innodb-undo-directory will be the default location for undo
datafiles but they will also be allowed in or under any of the known directories
in --innodb-directories.  In this way, InnoDB will know where to scan in order
to discover undo datafiles.  In order for discovery to work as implemented in
WL8619, we cannot allow ADD DATAFILE locations that are outside of these valid
directories. When InnoDB starts up, only the directories listed in --datadir,
--innodb-home-directory, --innodb-undo-directory and --innodb-directories will
be scanned.  

Any undo datafile that is not found in this scan cannot be used. The directory
scanning code implemented in WL#8619 will be used for this. This code discovers
all undo tablespaces from known directories before recovery. Thus if recovery
happens with missing undo tablespaces, any uncommitted transaction in that undo
tablespace will not get rolled back.  The database would be inconsistent.  This
worklog adds an error message to the error log if an undo tablespace known to
the DD is not found at startup. 

UNDO TABLESPACE PORTABILITY
===========================
Explicit undo tablespaces created with CREATE UNDO TABLESPACE can be moved while
offline to any location that is in or under one of the known directories
specified in --innodb-directories, --innodb-undo-directory, --datadir or the
current directory.  These files will be discovered at startup and assumed to be
the current undo tablespaces which were moved.

Implicit undo tablespaces must always be located in the --innodb-undo-directory.
If they are moved while offline, then mysqld must be started with that new
directory in --innodb-undo-directory.

INFORMATION_SCHEMA.INNODB_TABLESPACES
=====================================
In order to determine if an inactive undo tablespace is empty and ready to drop,
the user can query INFORMATION_SCHEMA.INNODB_TABLESPACES.  There will be a new
column called "STATE". This worklog will implement this display column. 

This state will be persisted in DD table mysql.tablespaces as part of
se_private_data. It will be another key/value pair of {state=""}.The key
{discard=<1/0>} will be removed as part of this worklog and state='discard' will
be used instead.

For undo tablespaces, the values can be 'active', 'inactive', and
'empty'. For general and file-per-table tablespaces the column may contain
either 'normal', 'discarded' or 'corrupted'.

The four undo tablespace states have the following meaning:
active:    These rollback segments can be allocated to new transactions.
inactive:  These rollback segments are no longer being used by new transactions.
           They are 'inactive'. The truncate process is happening. This undo
           tablespace was either selected by the purge thread implicitly or it
           was made inactive explicitly by an ALTER UNDO TABLESPACE statement
           with SET INACTIVE.
           When the truncation process is complete, the next state is ACTIVE If
           it was set implicitly, or EMPTY if it was set explicitly.
empty:     This undo tablespace has been truncated and is no longer active.
           It is ready to be either dropped or set active again explicitly with
           SET ACTIVE.

The three general and file-per-table tablespace states have the following meaning:
normal:    A normal active general or file-per-table tablespace.
discarded: A file-per-table tablespace that has been discarded using
           ALTER TABLE name DISCARD TABLESPACE;
corrupted: A general or file-per-table tablespace that has been identified by
           InnoDB as having been corrupted.


Copyright (c) 2000, 2025, Oracle Corporation and/or its affiliates. All rights reserved.