WL#13065: Restrict All InnoDB Tablespaces To Known Directories

Affects: Server-8.0   —   Status: Complete

In v8.0, undo and general tablespaces are restricted to known directories
which are a combination of these four settings:
  datadir, innodb_data_home_dir, innodb_directories & innodb_undo_directory.

However, implicit tablespaces created for tables and partitions using the
DATA DIRECTORY phrase can be created anywhere.

This worklog proposes that both general and implicit tablespaces work the
same way.  There are two reasons for doing this;

1. In a cloud configuration, a DBA needs to be able to restrict where files can
be created.

2. Any implicit tablespace created in an unknown location in v8.0 cannot be
recovered in the case of a crash until that location is added to the
--innodb-directories setting.  Recovery is terminated by InnoDB if a file
referenced in the redo log has not previously been discovered.

In addition, it is proposed that a unique innodb_undo_directory should no
longer be an allowed location for new IBD files.  However, we should still
allow undo files to be created in any of the 4 known directories.

FR1: Any location listed in DATA DIRECTORY must be a known directory.
     i.e; CREATE TABLE ... DATA DIRECTORY '/my/data/location' will fail if one
     of these: '/my' or 'my/data' or 'my/data/location' is not specified in
     --innodb-directories, --datadir or --innodb-data-home-dir. 
     The error ER_WRONG_FILE_NAME will be returned with this message:
       "The DATA DIRECTORY must be in a known directory."
     The server log should show this message;
       "Cannot create a tablespace for table %s in directory '%s' because
        it is not a valid location. The DATA DIRECTORY location must be a
        known directory. The known directories are: %s".

FR2: The above error and messages will not occur with ALTER TABLE when
     a partition is exchanged or whenever an existing implicit tablespace is
     renamed. The reason is that the datafile being renamed will have already
     had a warning at startup or at upgrade about being in an unknown location.  

FR3: Startup will work if datafile locations listed in the DD are not known
     locations, but the following warning message will be put into the error
     log for each file opened this way saying:
       "The datafile '%s' for tablespace %s is in an unprotected location.
        This file cannot be recovered after a crash until this location is
        added to innodb_directories."

FR4: Upgrade from 5.7 where remote datafiles are not known directories in
     8.0 will work also, but with the same message as FR3 will be put into
     the error log. 

FR5: If --innodb-undo-directory is an otherwise unknown location, (not covered
     by --innodb-directories, --datadir or --innodb-data-home-dir, then
     datafiles created with ADD DATAFILE or DATA DIRECTORY will not be
     allowed there.
     The error ER_WRONG_FILE_NAME will be returned with this message:
       "The DATA DIRECTORY location cannot be the undo directory."
     The server log should show this message;
       "Cannot create a tablespace for table %s in directory '%s' because
        it is not a valid location. The DATA DIRECTORY location cannot be
        the undo directory. The known directories are: %s".
A new restriction will be added to the evaluation of the DATA DIRECTORY value.
The restriction will work just like the evaluation of the ADD DATAFILE value. 
If the specified file is not in or under one of these know directories, the
statement will fail.

Currently, ADD DATAFILE accepts any 'known' file locations specified by the
combination of datadir, innodb_data_home_dir, innodb_directories &
innodb_undo_directory.  

This list of known directories is used for file discovery early in the startup
process as well as validation of ADD DATAFILE locations.  

This workload will add code to determine if the undo directory is unique from
the locations specified by the other three.  If so, it will not be among the
locations allowed for either ADD DATAFILE or DATA DIRECTORY.  This will allow a
DBA to specify a unique location for undo table spaces that user table spaces
cannot be placed.

These four location settings can only be set at startup. This satisfies the
requirement that acceptable locations for any instance of InnoDB can only be
controlled by a DBA.

The error and warning messages associated with restricting table space locations
will be enhanced to more clearly explain why a particular table space cannot be
created.

At startup, the file discovery process will work as it does now so that upgrade
will be seamless.  That progress is;
1.Known directories and subdirectories are scanned for IBD and IBU datafilee.
2. Recovery of the redo log is attempted.  If a table space needs to be
recovered but is not previously scanned, the recovery stops.
3. After redo recovery, the DD::tablespace_files table is scanned and any file
referenced there that was not previously scanned in known locations is opened. 
This step also looks for missing and moved table spaces and updates the DD if
necessary.

If a remote tablespace exists and is found in dd::tablespace_files but was not
found in the list of known locations, a new warning message will be put into the
error log that points out each unrestricted table space.  This tablespace is at
risk of causing a recovery to fail since it would not be scanned before redo
recovery.  This message will recommend adding this location to —innodb-directories.

During 5.7 upgrade mode, InnoDB will collect a list of unknown table space
locations and put a warning to the error log that these locations should be
added to —innodb-directories.

In order to restrict all  table spaces to the datadir, which may be useful on
cloud-based installations,  a DBA can just not use the settings
—innodb-data-home-dir, —innodb-directories and —innodb-undo-directory.  This
will work once all new table spaces are restricted to known directories.

Summary
=======
1.  Startup will not add any new restrictions to where files can be found, but
it will complain about any file referenced in the DD but that are not in know
directories with new log messages.
2. The 5.7 upgrade process will also have new messages that encourage the DBA to
add locations to —innodb-directories.
3.  CREATE TABLE … DATA DIRECTORY will fail if the directory specified is not a
‘known’ IBD location.
4. If —innodb-undo-directory is a unique location, it will not be considered a
‘known’ IBD location for the purpose of creating new datafiles.