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.
Copyright (c) 2000, 2021, Oracle Corporation and/or its affiliates. All rights reserved.