When you create a general tablespace in MySQL 8.0, you can choose the directory where the associated datafile is created.

CREATE TABLESPACE tablespace_name ADD DATAFILE ‘/my/table/space/dir’;

However, that directory must be known to InnoDB. Known directories are defined by the following settings: datadir,  innodb_data_home_dir,  innodb_undo_directory  &  innodb_directories.

At startup, before recovery can proceed, InnoDB searches known directories for tablespace datafiles. Tablespace directories are not stored in the redo log in MySQL 8.0. Recovery therefore depends on known directories to find and open the datafiles before recovery begins.

Restricting the ADD DATAFILE path is a good thing because it allows you to keep track of all your data. You can specify where your data is located and it will not be lost to you.

Up until MySQL 8.0.21, it has been possible to specify any directory for implicit file-per-table tablespaces created by:

CREATE TABLE table_name ({column_specs}) DATA DIRECTORY ‘/my/unknown/dir’;

When you do this, the MySQL Data Dictionary knows where the datafiles are located. So, these datafiles are found at startup, but not until after the Data Dictionary has been recovered. That means that those datafiles are not recoverable. After a crash, if the Redo Log contains changes to one of these files, MySQL is not able to start up until those datafile directories are added to –innodb-directories. Startup with recovery will fail if a redo log entry cannot be applied because its tablespace is not yet open.

So, MySQL 8.0.21 requires that every implicit file-per-table datafile is created in a known directory, just like a general tablespace. This rule is only for new implicit tablespaces.

So what about an existing database? Will an upgrade to 8.0.21 be prevented if an implicit datafile is in an unknown directory? No, it will not.

When InnoDB 8.0.21 starts up and finds datafiles in the Data Dictionary that were not found by scanning known directories, it will write warnings about that to the error log.  You can then add these directories to your –innodb-directories setting.  Then, the next time InnoDB starts up, all your data will be recoverable. If you missed a directory, another message will be written to the error log.

This new rule has a couple of minor implications:

1. An implicit file-per-table tablespace can be truncated with TRUNCATE TABLE. This implicitly creates a new empty tablespace to replace the old one. However, if the old tablespace is part of an existing database and its directory is unknown, the new tablespace is not created in the same unknown directory. It is created in the default directory for implicit tablespaces. That is, it is created under the datadir in a directory named for the schema. If this happens, the following warning message is written to the error log:

“The DATA DIRECTORY location must be in a known directory. The DATA DIRECTORY location will be ignored and the file will be put into the default datadir location.”

2. When a unique undo directory is chosen, that directory should not be used for regular tablespaces, either general or implicit. So, if you choose a unique undo directory, MySQL 8.0.21 does not allow general or implicit tablespaces to be created there. Before this change, non-undo tablespaces could be created in unique undo directories. So, if some non-undo tablespace was created in a unique undo directory when using a previous MySQL version, and you upgrade to MySQL 8.0.21, InnoDB will permit it.

The new restriction on implicit tablespace directories specified by the DATA DIRECTORY clause is intended to help to make sure that your database is fully recoverable. It affects only new tablespaces, not existing ones. So, it does not prevent upgrade from an older version of MySQL.