The implementation of the DATA DIRECTORY attribute for InnoDB in MySQL 5.6 included at least the following questionable changes:

• Introduced SYS_TABLESPACES and SYS_DATAFILES tables that only serve as an additional source of metadata mismatch. (Removed in MDEV-22343.)
• Unnecessarily introduced .isl files. A proper implementation would simply have refused the operation if the symlink cannot be created, for whatever reason. It should not be rocket science to determine which optional APIs on Microsoft Windows are available.
• Introduced an extra level of directory hierarchy (incompatibility with other storage engines).

  CREATE DATABASE a;

  CREATE TABLE a.b(c INT) ENGINE=InnoDB DATA DIRECTORY '/d';

  DROP DATABASE a;

  This example would create and not remove the subdirectory /d/a because the data file would have been not /d/b.ibd but /d/a/b.idb.

I wish MariaDB had not copied this from MySQL. I fully agree that it is a good idea for administrators to be able to disable the use of the DATA DIRECTORY attribute for InnoDB tables.

serg, wlad, how should InnoDB implement this check? By referring to the variable my_disable_symlinks? Or have_symlink? No storage engine is checking this option directly.

I suppose that we may enforce the check on CREATE TABLE and on ALTER TABLE that explicitly mentions DATA DIRECTORY. Would we also want to refuse all table-rebuilding operations (OPTIMIZE, TRUNCATE, ALTER) if the table has been defined with a DATA DIRECTORY attribute?

marko, it's my_use_symdir and ha_myisam.cc and ha_archive.cc test it.

And if you want to switch to real symlinks, you'll need to keep supporting isl files for a while still. At least support reading of them.

I think that using proper symbolic links and removing the mkdir calls would be a separate task and not something that can be done in a GA release, because it would change the file format and therefore break downgrade to earlier versions.

I would implement this as follows:

• CREATE TABLE…DATA DIRECTORY would return an error when skip-symbolic-links is in effect.
• Any table rebuild operation except TRUNCATE TABLE as well as some non-rebuilding ALTER TABLE or CREATE INDEX would return an error if a DATA DIRECTORY attribute is present.
• RENAME TABLE will keep working even if a DATA DIRECTORY attribute is present.
• DROP TABLE will be allowed.

It turns out that ALTER TABLE t1 FORCE, DATA DIRECTORY '…' will not add a DATA DIRECTORY attribute, even when the FORCE clause is present.

c1d7b4575e6 is ok to push