MySQL Bugs: #75937: InnoDB: Make general tablespaces portable from Windows to Unix

Bug #75937	InnoDB: Make general tablespaces portable from Windows to Unix
Submitted:	17 Feb 2015 17:17	Modified:	8 Jul 2015 16:18
Reporter:	Kevin Lewis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.7.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
General tablespaces created on Windows using a relative filepath cannot be opened on Unix flavored machines.

The problem is that the Windows version of the relative path, which uses '\' insted of '/', is stored in SYS_DATAFILES. This is also true for existing file-per-table tablespaces, but it is not a problem when porting to Unix because the filepath is still generated from the tablename during startup (tablespace discovery). The filepath in SYS_DATAFILES is then corrected when it is different from the generated default filepath.

However, general tablespace filepaths cannot be generated from the tablespace name or any table name. This filepath must be looked up in the data dictionary. InnoDB does not attempt to convert '\' to '/' on Linux so the file cannot be opened.

But this bug may involve more than just doing a '\' to '/' conversion on Unix when reading a filepath from the data dictionary. InnoDB does not have the ability to recognize when two filepaths that look different from a character comparison are actually referring to the same file.For example, if you reboot after changing the default datadir to an absolute path to the same directory, file-per-table tablespaces will still be found, but general tablespaces will not. In order to make this happen, there must be an OS aware routine that checks of a file with two different looking filepaths are in fact the same file.

How to repeat:
Create a general tablespace on Windows. Move the entire datadir to Unix. Startup MySQL. The general tablespace will be missing and an error mesage will be found in the error log.

Suggested fix:
Convert '\' to '/' on Linux so a filepath read from SYS_DATAFILES cannot be opened.
In order to reliably check if the filepath in SYS_DATAFILES is the same as a default file-per-table tablespace, an comparison routine must be made that uses OS tools to identify if two different filepaths are the same file.

Posted by developer:
 
Fixed as of the upcoming 5.7.8, 5.8.0 releases, and here's the changelog entry:

General tablespaces created on Windows using a relative data file path
could not be opened on Unix-like systems. InnoDB failed to convert the
backslash (\) directory separator that is used in the Windows version of
the relative data file path.

Posted by developer:
 
A note about the this limitation and its removal was added to:
https://dev.mysql.com/doc/refman/5.7/en/create-tablespace.html
https://dev.mysql.com/doc/refman/5.7/en/general-tablespaces.html