Let's say that we have two servers. One of the servers has a table with a temporal type that was created with MySQL 5.5 or earlier, and the other is a MariaDB 10.1 server that has mysql56_temporal_format=ON (the default). If we want to import a .ibd file for a table with temporal types from the first server into the second server, it seems difficult to do in a safe manner without setting mysql56_temporal_format=OFF and restarting mysqld.
We can simulate this problem by doing the following:
- Set mysql56_temporal_format=OFF and restart mysqld.
- Create a table with a temporal type:
- Set mysql56_temporal_format=ON (the default) and restart mysqld.
- Create a second copy of the table:
- Discard the tablespace of the new copy:
- Flush the tablespace of the old copy for export:
- Copy the files to the new database:
- Attempt to import the tablespace:
This will fail:
Update: Can we please support the following simplified workflow:
to have InnoDB automatically create its table definition based on the .frm and optionally .cfg files, with no need to execute CREATE TABLE and ALTER TABLE…DISCARD TABLESPACE? That would create all temporal-type columns exactly as is.