Stop creating .isl files for DATA DIRECTORY

Background

In the private MySQL WorkLog #5980 that targeted MySQL 5.6, support for the DATA DIRECTORY attribute was introduced for ENGINE=InnoDB. Because the author was under the wrong impression that symbolic links are not available on Microsoft NTFS, they were emulated by writing .isl files that are intended to be editable while mysqld is not running. The contents of these files would be the .ibd file name where the actual innodb_file_per_table data is stored.

Normally, InnoDB would use the schemaname/tablename.ibd scheme for accessing files relative to the current directory; mariadbd (as well as mysqld) would change the current directory to datadir on startup. Only in the Embedded Server library (MDEV-16535), the path names would be absolute, prefixed with datadir.

The DATA DIRECTORY attribute is used with absolute path names. Hence, the files would be referred to by absolute paths. For ENGINE=MyISAM and ENGINE=Aria, the file names would start with data_directory_name/tablename. However, the InnoDB implementation would follow a different pattern, data_directory_name/schemaname/tablename.ibd, and create schemaname subdirectories on its own.

The absolute path names derived from DATA DIRECTORY would be stored in multiple places in InnoDB:

• The SYS_DATAFILES table (removed in MDEV-22343).
• FILE_ records of the write-ahead log, consulted during crash recovery as well as backup; before MDEV-12353, these used to be MLOG_FILE_ records.
• .isl files (to be removed here)

Because the path names are absolute, the BACKUP SERVER TO command (MDEV-14992) would be unable to copy any InnoDB files that use the DATA DIRECTORY attribute, because the source and target file names would be the same. The streaming variant BACKUP SERVER WITH (MDEV-38362) does not suffer from this problem, but a special tar option may be needed in order to preserve the absolute path names when extracting the streams.

In mariadb-backup --backup there may be a work-around that strips the data_directory_name/ prefix from the files, so that the data_directory_name/schemaname/tablename.ibd will be copied to backup_directory/schemaname/tablename.ibd. However, when preparing (applying log to) a backup, the log would contain absolute path names starting with data_directory_name. Any special logic for stripping that prefix was lost in the fix of MDEV-41152 if not earlier.

The current approach is prone to errors and data loss

The main problem is that there are multiple sources of truth: the FILE_ records may conflict with .isl files and the actual location of the .ibd file.

• There is no tool for validating the contents of .isl files.
• There is no tool for renaming files in the write-ahead log; doing so would change the LSN unless the lengths of the names stayed equal
• Relocation is only guaranteed to be successful between a clean shutdown and a restart (when no log record since the latest checkpoint is referring to the file).
• As demonstrated in MDEV-41595, there is no way to modify the DATA_DIR flag in SYS_TABLES, and no way to add or remove the DATA DIRECTORY attribute on an existing table.

Solution: Make the file system the single source of truth

Let us reimplement this little-used feature in a similar way as it is in other storage engines: Just make schemaname/tablename.ibd a symbolic link to data_directory_name/tablename.ibd.

• No extra directory is created, and no empty directory will be left behind after DROP TABLE.
• No DATA_DIR flag is being added to SYS_TABLES records
• InnoDB will use the common schemaname/tablename.ibd scheme everywhere.
• Backup will "just work", silently discarding the DATA DIRECTORY attribute.
• Standard operating system tools can be used for validating the symbolic links.
• Data files can be moved and symlinks adjusted any time when the server is shut down. Recovery will "just work" based on the short path names in FILE_ records

Upgrade considerations

• Any code for creating .isl files will be removed.
• Any code for creating directories in InnoDB will be removed.
• The DATA_DIR flag in SYS_TABLES.MIX_LEN will be ignored as garbage, and always written as 0.
• In fil_ibd_open() (but not in log-based recovery recv_sys!), if a file is not found and symbolic_links=ON (my_use_symdir), try to open and read schemaname/tablename.isl. If it contains the name of a matching .ibd file, create the symbolic link and remove the file schemaname/tablename.isl. Else, report an error that the file cannot be found.

As a result of these changes, the output of SHOW CREATE TABLE should change for old tables that might currently display ENGINE=InnoDB DATA_DIRECTORY='/datadir': we would start to expose the full DATA DIRECTORY='/datadir/schemaname' that InnoDB previously rewrote it to.

DDL considerations (renaming and removing files)

Operations that involve renaming files are challenging because there is no portable operating system interface for atomic multi-file operations.

Let us consider the following example:

CREATE TABLE a (a INT) ENGINE=InnoDB DATA_DIRECTORY '/tmp';

RENAME TABLE a TO b;

CREATE TABLE a (a INT) ENGINE=InnoDB DATA_DIRECTORY '/tmp';

DROP TABLE b;

CREATE TABLE a

1. write TRX_UNDO_INSERT and a SYS_TABLES record using the name schemaname/a.ibd, referred to without schemaname/ below
2. write FILE_CREATE
3. create the symbolic link a.ibd pointing to (non-existent) /tmp/a.ibd
4. create /tmp/a.ibd
5. invoke trx_purge_add_undo_to_history() as part of transaction commit

Recovery starts from replaying the write-ahead log records. We will restore the contents of the undo log and SYS_TABLES as of the latest (durable) log write before the crash.

Replaying the FILE_CREATE:

• If a.ibd does not exist, follow the "create" steps above, except that we will lose the DATA_DIRECTORY attribute!
• If a.ibd exists and is a symlink pointing to a non-existent /some_directory/a.ibd (identical base name a.ibd), create that file.
• Else, throw an error.

Rolling back the CREATE TABLE, in case the transaction commit was not durable: Similar to the FILE_DELETE part of the DROP TABLE b below.

RENAME TABLE a TO b

1. write TRX_UNDO_RENAME_TABLE and other records using the names schemaname/a.ibd and schemaname/b.ibd, referred to without schemaname/ below
2. write a FILE_RENAME record
3. create a new symbolic link b.ibd, pointing to /tmp/b.ibd (non-existent)
4. rename /tmp/a.ibd to /tmp/b.ibd
5. remove a.ibd
6. invoke trx_purge_add_undo_to_history() as part of transaction commit

Replaying the FILE_RENAME:

• If neither b.ibd nor a.ibd exist, throw an error, like we already should do.
• If b.ibd does not exist and a.ibd does, follow the "create", "rename", "remove" steps above. If a.ibd is a symbolic link, the DATA DIRECTORY attribute will be preserved there.
• If a.ibd does not exist and b.ibd does, we just ensure that the correct tablespace is accessible as b.ibd, like we already do.
• If both a.ibd and b.ibd exist as symbolic links, only one should have a valid target /tmp/a.ibd or /tmp/b.ibd. If necessary, rename the target of a.ibd to that of b.ibd. Finally, remove a.ibd.
• If a symbolic link target base name does not match the base name of the link (a.ibd points to /somewhere/c.ibd), throw an error.

For rolling back the RENAME TABLE operation: follow the middle section of the RENAME TABLE description above: write a FILE_RENAME record in the opposite direction.

DROP TABLE b

1. write TRX_UNDO_DEL_MARK_REC and delete-mark records in SYS_TABLES and other dictionary tables
2. invoke trx_purge_add_undo_to_history() as part of transaction commit
3. write a FILE_DELETE record
4. remove /tmp/b.ibd, leaving b.ibd a dangling symlink
5. remove b.ibd

Replaying the FILE_DELETE:

1. If b.ibd exists and is a symbolic link pointing to /somewhere/b.ibd that matches the tablespace identifier, remove the link target.
2. If b.ibd is a symbolic link whose target base name is not b.ibd, throw an error.
3. Remove b.ibd if it exists.

Other DDL operations, such as TRUNCATE TABLE and table-rebuilding ALTER TABLE or OPTIMIZE TABLE, are composed of these create, rename, and drop operations and should therefore work.

When DATA DIRECTORY attributes will be lost

1. BACKUP SERVER (MDEV-14992) and mariadb-backup --backup will strip DATA_DIRECTORY and INDEX_DIRECTORY attributes for all tables.
2. Recovering from a crash during an operation that involves creating an .ibd file may lose the DATA_DIRECTORY attribute.

To properly preserve the DATA DIRECTORY attribute in the crash recovery case, the log mechanism proposed in MDEV-41534 could be extended with log records for operations on creating symbolic links. However, this could reintroduce complications for replaying logs and restoring backups.

I think that it is better to just document this anomaly and let users manually recover: shut down or kill the server, move the .ibd file from the data directory to the desired location, and create a symbolic link pointing to it.

To make it clear: I understand that the DATA_DIRECTORY attribute may have been useful several years before the feature was implemented, back when storage devices were small (a few hundred megabytes or a few gigabytes) and it was necessary to split large tables or partitions to multiple devices. Nowadays, a single storage device can be dozens of terabytes, and several more flexible ways exist for composing larger storage out of smaller storage devices. Removing the feature can break existing installations. What we can and should do is to minimize the impact of this misstep that MariaDB Server 10.0 inherited from MySQL 5.6.