If the original server with the source table is running a version that supports the new temporal type format, then a workaround for this is to "modify" the datetime column, so that it has the same data type, but uses the new format. e.g.:

ALTER TABLE dt_test MODIFY dt datetime;

Then you should be able to export/import the table.

In reality, if you import a tablespace from "pre-5.6" instance, you won't be able to run FLUSH FOR EXPORT, so no cfg file, thus you'll have to import it without metadata verification, and it will "work" (in the sense that it won't fail); but the data will be broken. It also appears true for MySQL 5.5 => 5.6 import. So, maybe it's good that it actually produces an error.

I'm assigning it to bar to clarify if it was designed this way.

It does seem to be an intentional decision by the MySQL team. The MySQL 5.6 upgrade notes describe a pretty convoluted workaround:

Due to the temporal type changes described in the previous incompatible change item above, importing pre-MySQL 5.6.4 tables (using ALTER TABLE ... IMPORT TABLESPACE) that contain DATETIME and TIMESTAMP types into MySQL 5.6.4 (or later) fails. Importing a MySQL 5.5 table with these temporal types into MySQL 5.6.4 (or later) is the mostly likely scenario for this problem to occur.

The following procedures describe workarounds that use the original pre-MySQL 5.6.4 .frm file to recreate a table with a row structure that is compatible with 5.6.4 (or later). The procedures involve changing the original pre-MySQL 5.6.4 .frm file to use the Memory storage engine instead of InnoDB, copying the .frm file to the data directory of the destination instance, and using ALTER TABLE to change the table's storage engine type back to InnoDB. Use the first procedure if your tables do not have foreign keys. Use the second procedure, which has additional steps, if your table includes foreign keys.

If the table does not have foreign keys:

Copy the table's original .frm file to the data directory on the server where you want to import the tablespace.

Modify the table's .frm file to use the Memory storage engine instead of the InnoDB storage engine. This modification requires changing 7 bytes in the .frm file that define the table's storage engine type. Using a hexidecimal editing tool:

Change the byte at offset position 0003, which is the legacy_db_type, from 0c (for InnoDB) to 06 (for Memory), as shown below:

00000000 fe 01 09 06 03 00 00 10 01 00 00 30 00 00 10 00
The remaining 6 bytes do not have a fixed offset. Search the .frm file for “InnoDB” to locate the line with the other 6 bytes. The line appears as shown below:

00001010 ff 00 00 00 00 00 00 06 00 49 6e 6e 6f 44 42 00 |.........InnoDB.|
Modify the bytes so that the line appears as follows:

00001010 ff 00 00 00 00 00 00 06 00 4d 45 4d 4f 52 59 00
Run ALTER TABLE ... ENGINE=INNODB to add the table definition to the InnoDB data dictionary. This creates the InnoDB table with the temporal data types in the new format. For the ALTER TABLE operation to complete successfully, the .frm file must correspond to the tablespace.

Import the table using ALTER TABLE ... IMPORT TABLESPACE.

If table has foreign keys:

Recreate the tables with foreign keys using table definitions from SHOW CREATE TABLE output. The incorrect temporal column formats do not matter at this point.

Dump all foreign key definitions to a text file by selecting the foreign key information from INFORMATION_SCHEMA.TABLE_CONSTRAINTS and INFORMATION_SCHEMA.KEY_COLUMN_USAGE.

Drop all tables and complete the table import process described in steps 1 to 4 in the procedure described above for tables without foreign keys.

After the import operation is complete, add the foreign keys from foreign key definitions that you saved to a text file.

https://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html

To simplify upgrade, we could add a special syntax, like this:

CREATE TABLE t1 (a /*!M100305 OLD */TIME);
CREATE TABLE t1 (a /*!M100305 OLD */TIMESTAMP);
CREATE TABLE t1 (a /*!M100305 OLD */DATETIME);

So one could specify the old temporal types when importing tables from old table spaces.

Adding this syntax into versions before 10.3 is not desirable. With the new type handler conception (added into 10.3) it will be easier.

Also, adding this new syntax will mean that old formats will remain in MariaDB for a long time. This is not desirable. Old data types do not support well replication with binary logging. So we want to get rid of them as soon as possible.

An entirely different user just ran into this same problem today.

The table involved was originally created on MariaDB 10.0, and the server had later been upgraded to 10.1, and then later to 10.2. The user tried to import the tablespace into another MariaDB 10.2 server. The import succeeded (probably because they didn't copy the .cfg file), but the data in the table was not being interpreted properly. When the user used the ALTER TABLE ... MODIFY command from MDEV-15228 to "upgrade" the temporal data type storage format on the original server, they were able to properly export/import the tablespace.

It does sound like it makes sense to get rid of this old storage format ASAP. Maybe we should change mysql_upgrade to "upgrade" tables that use the old format? I believe that this would require the table to be rebuilt, which could take a lot of time and resources for large tables, so maybe it could be an option that has to be manually enabled?

With MDEV-16542 done, one should be able to import mismatching format tables as follows:

1. Shutdown the destination server
2. Configure mysql56_temporal_format to match the source server
3. Start the destination server
4. Run "ALTER TABLE t1 FORCE" for the affected table.
5. Run import as usual.

Does this sound too complex?

Even without MDEV-16542, the table can already be imported like the following:

1. Shutdown the destination server
2. Configure mysql56_temporal_format=OFF
3. Start the destination server
4. Import the table
5. Shutdown the destination server
6. Configure mysql56_temporal_format=ON
7. Start the destination server
8. Use the "ALTER TABLE t1 MODIFY" workaround for the affected table.

The problem is that users can't always shutdown production databases when they need to import tables.

Would it make sense to make mysql56_temporal_format a dynamic session variable, so that something like this could be done?:

SET mysql56_temporal_format=OFF;

-- create empty table

CREATE TABLE t1 ( ... );

-- get rid of existing tablespace

ALTER TABLE t1 DISCARD TABLESPACE;

-- import tablespace from other server

ALTER TABLE t1 IMPORT TABLESPACE;

SET mysql56_temporal_format=ON;

-- convert datetime fields to new format

ALTER TABLE t1 FORCE;

We had changed formats (true VARCHAR, new DATE, new DECIMAL) even before these TIME/DATETIME/TIMESTAMP changes, and it's very likely will change formats for the built-in data types in the future.

With the idea of pluggable data types, the probability of format changes becomes even higher in the future. Developers of data type plugins will also change their formats through time.

So changing low level formats is not something extraordinary. For me it seems that further development around specifically mysql56_temporal_format is a dead way.

The problem is that IMPORT TABLESPACE does not get enough information about the exact data types.
I'd suggest we fix FLUSH TABLES to write the exact table definition to the .cfg file, so IMPORT TABLE can reproduce CREATE TABLE exactly. Why not just put the entire FRM file inside .cfg?
So these steps are not even needed on the destination server:

CREATE TABLE ...
ALTER TABLE ... DISCARD TABLESPACE
ALTER TABLE ... IMPORT TABLESPACE

Instead of the above cumbersome combination, we could introduce something like this:

IMPORT TABLE t1;

It would create the FRM file and import the IBD file in one shot.

That's true, users can't always shutdown production server.

As a workaround, one can start one more temporary mariadb server for the format conversion purposes. Then do the following on this conversion server:

• Import the table
• Change the table format (using tricks with shutdown and SET)
• Re-export the table again.

Then copy the newly exported table to the destination server and import it.

For now (before we add the precise IMPORT functionality) I can see the following solutions:

1. Do nothing in the code. Use a temporary conversion server on production servers that cannot be shut down. This sounds best for me at this point.

2. Add a format hint inside the column data type syntax:

CREATE TABLE t1 (a /*!M100305 OLD */TIME);

But here we need to think of the hint naming convention. "OLD" does not look good enough. It should be some sort of data-type-format-version-number here.

3. Add a session variable mysql56_temporal_format
I don't like this. We want to get rid of the old format asap. Having this as a session variable encourages the user just to stay with the old format, to simplify the IMPORT procedure.

My preference is to introduce names for the old data types. The format hint comment seems to be the cleanest option (as it could be emitted by SHOW CREATE TABLE too).

I would prefer not to change the format of InnoDB .cfg files in any way. Those files would be removed as part of MDEV-11655 (removing the InnoDB internal data dictionary) or MDEV-11658 (simpler, faster IMPORT).

I have always found the InnoDB design of IMPORT TABLESPACE questionable. You first have to create an empty table, then violate the referential integrity of the data dictionary by issuing DISCARD TABLESPACE and by copying some data file to the server data directory, and finally issue IMPORT TABLESPACE to hopefully bring back the referential integrity.

This would be fixed in MDEV-11658. You would just copy the .ibd and .frm files and let the table to be found by issuing an SQL statement, such as SELECT. The table would be created just like the .frm file describes it.

Unfortunately, the .frm file does not contain the root page numbers of secondary indexes. These could still be scanned from the data file, or we could change the .ibd file format and store the secondary index page numbers in the metadata record that was introduced in MDEV-11369 and will be extended by MDEV-15562 and related tasks. But, such .ibd files would no longer be compatible with older MariaDB versions.

bar ? Any possible feedback, please?

julien.fritsch, sorry this is not something I can fix or give a feedback for. marko has some solution for a future version. For a simpler fix (e.g. format hints) please ask serg to have a look and approve this architecture proposal. I'm not very happy with this proposal.

Here is one more idea what we could do. In addition to the current workflow of

CREATE TABLE t …;

ALTER TABLE t DISCARD TABLESPACE;

--copy the files t.ibd t.cfg

ALTER TABLE t IMPORT TABLESPACE;

we could support a simpler workflow:

--copy the files t.frm t.ibd t.cfg

ALTER TABLE t IMPORT TABLESPACE;

This would import the table definition exactly as it was originally created. We would have to change some code in class ha_innobase and possibly its callers, so that if the table did not exist in InnoDB, InnoDB will create its own table metadata based on the handler::table_share, which is derived from the .frm file contents.

Hi marko,

That sounds like a cool idea. That would make MDEV-18762 a lot easier to implement too.

The documentation for the mysql56_temporal_format system variable incorrectly said that the variable was not dynamic. It actually is dynamic:

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'mysql56%';

+-------------------------+-------+

| Variable_name           | Value |

+-------------------------+-------+

| mysql56_temporal_format | OFF   |

+-------------------------+-------+

1 row in set (0.00 sec)

MariaDB [(none)]> SET GLOBAL mysql56_temporal_format=ON;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'mysql56%';

+-------------------------+-------+

| Variable_name           | Value |

+-------------------------+-------+

| mysql56_temporal_format | ON    |

+-------------------------+-------+

1 row in set (0.00 sec)

I've fixed the documentation.

This makes this bug a little easier to work around, since the server does not have to be restarted in order to change the value of mysql56_temporal_format.

If the table was not rebuilt after executing CREATE INDEX or ADD INDEX, the secondary index root pages can be basically anywhere in the .ibd file. Normally they would immediately follow the clustered index root page (page 3).

I wrote in MDEV-11658 that some existing code tries to guess the root page numbers when the .cfg file is not available. I am not at all convinced that the code actually works when ADD INDEX or DROP INDEX had been executed without rebuilding the table. If there are multiple secondary indexes on similar columns (say, multiple INT columns that are indexed individually), I think that in the worst case, finding the correct mapping could require a full scan of the secondary indexes and a full scan of the clustered index. And even then, we would have to try to avoid hitting crashes or debug assertion failures when trying to interpret the secondary index records. After all, in ROW_FORMAT≠REDUNDANT, reading the records normally requires the correct index metadata.

It seems to be more worthwhile to support an option where all secondary indexes are outright marked as corrupted? Then the user would have to rebuild all secondary indexes:

ALTER TABLE imported_table DROP INDEX x, DROP INDEX y, DROP INDEX z;

ALTER TABLE imported_table ADD INDEX (x), ADD INDEX(y), ADD INDEX(z);

Only with a file format change, we could guarantee the correct import of secondary indexes without having a .cfg file. In MDEV-4259 I wrote down an idea how to store the index metadata inside the .ibd files, to eventually allow the InnoDB data dictionary tables to be removed. The directory inside the .ibd files would store the root page numbers for each secondary index, and the indexes would be written in the same order as they appear in the TABLE_SHARE or the .frm files.

To clarify, the full workaround for this bug is:

1. On the original server, execute the following query to find the mysql56_temporal_format value for the table. Replace $SCHEMA and $TABLE with the names of the specific database and table:

WITH innodb_temporals AS (

SELECT t.TABLE_SCHEMA, t.TABLE_NAME, c.COLUMN_NAME

FROM information_schema.COLUMNS c

JOIN information_schema.TABLES t

ON (c.TABLE_SCHEMA = t.TABLE_SCHEMA)

AND (c.TABLE_NAME = t.TABLE_NAME)

WHERE t.TABLE_SCHEMA NOT IN('mysql', 'information_schema', 'performance_schema')

AND t.ENGINE = 'InnoDB'

AND c.COLUMN_TYPE IN ('datetime' ,'timestamp')

)

SELECT it.TABLE_SCHEMA, it.TABLE_NAME, it.COLUMN_NAME, isc.MTYPE,

CASE isc.MTYPE

WHEN 6 THEN 'mysql56_temporal_format=OFF'

WHEN 3 THEN 'mysql56_temporal_format=ON'

ELSE 'undefined'

END AS mysql56_temporal_format_value

FROM innodb_temporals it

JOIN information_schema.INNODB_SYS_TABLES ist

ON ist.NAME = CONCAT(it.TABLE_SCHEMA, '/', it.TABLE_NAME)

JOIN information_schema.INNODB_SYS_COLUMNS isc

ON ist.TABLE_ID = isc.TABLE_ID

AND it.COLUMN_NAME = isc.NAME

WHERE it.TABLE_SCHEMA = $SCHEMA

AND it.TABLE_NAME = $TABLE

2. On the new server, set the value of mysql56_temporal_format to the value returned by the above query for the table. i.e.:

SET GLOBAL mysql56_temporal_format=OFF;

3. On the new server, create the table:

CREATE TABLE db1.dt_test (

   id int AUTO_INCREMENT PRIMARY KEY,

   dt datetime

);

4. On the new server, discard the table's tablespace:

ALTER TABLE db1.dt_test DISCARD TABLESPACE;

5. On the new server, copy the tablespace file and the .cfg file for the table:

sudo cp /path/to/backup/db1/dt_test.ibd /var/lib/mysql/db1/

sudo cp /path/to/backup/db1/dt_test.cfg /var/lib/mysql/db1/

sudo chown mysql:mysql /var/lib/mysql/db1/dt_test.ibd

sudo chown mysql:mysql /var/lib/mysql/db1/dt_test.cfg

6. On the new server, import the table's tablespace:

ALTER TABLE db1.dt_test IMPORT TABLESPACE;

And then if you want to upgrade the table's temporal types, then you can use the following additional steps:

1. Set mysql56_temporal_format to ON:

SET GLOBAL mysql56_temporal_format=ON;

2. Use the ALTER TABLE t1 MODIFY workaround for the affected table:

ALTER TABLE db1.dt_test MODIFY dt datetime;

Note that MariaDB 10.1 does not support CTEs, so the MariaDB 10.1-compatible version of the large query from my last comment is:

SELECT it.TABLE_SCHEMA, it.TABLE_NAME, it.COLUMN_NAME, isc.MTYPE,

CASE isc.MTYPE

WHEN 6 THEN 'mysql56_temporal_format=OFF'

WHEN 3 THEN 'mysql56_temporal_format=ON'

ELSE 'undefined'

END AS mysql56_temporal_format_value

FROM (

SELECT t.TABLE_SCHEMA, t.TABLE_NAME, c.COLUMN_NAME

FROM information_schema.COLUMNS c

JOIN information_schema.TABLES t

ON (c.TABLE_SCHEMA = t.TABLE_SCHEMA)

AND (c.TABLE_NAME = t.TABLE_NAME)

WHERE t.TABLE_SCHEMA NOT IN('mysql', 'information_schema', 'performance_schema')

AND t.ENGINE = 'InnoDB'

AND c.COLUMN_TYPE IN ('datetime' ,'timestamp')

) AS it

JOIN information_schema.INNODB_SYS_TABLES ist

ON ist.NAME = CONCAT(it.TABLE_SCHEMA, '/', it.TABLE_NAME)

JOIN information_schema.INNODB_SYS_COLUMNS isc

ON ist.TABLE_ID = isc.TABLE_ID

AND it.COLUMN_NAME = isc.NAME

WHERE it.TABLE_SCHEMA = $SCHEMA

AND it.TABLE_NAME = $TABLE

marko, GeoffMontee, I think this "simplified workflow" is a great idea, let's do that. It's usefulness go way beyond fixing this temporal format mismatch.

serg Please, approve syntax. Is it OK for the patch to go into 10.2?

kevg, I think that the proposed fix includes some unnecessary code changes. As far as I can tell, the parameter except_discarded is always being passed as false. If that flag were truly needed, I would suggest to add it to dict_err_ignore_t. Other than that, the change looks fine to me: we are basically making the first 2 steps optional:

# CREATE TABLE t (…) ENGINE=INNODB;

# ALTER TABLE t DISCARD TABLESPACE;

--copy_file t.ibd $datadir

ALTER TABLE t IMPORT TABLESPACE;

I don't see why you need the third step. This can be simply

• copy idb/cfg/frm files into the datadir
• use the table normally

And in ha_innobase::open() it could be, like

• if the table doesn't exist, before returning HA_ERR_NO_SUCH_TABLE
• check if idb and cfg files exist. If the do — run the import.

Doesn't need any changes in the server at all, doesn't need the user to do anything beyond copying.

It was decided to not fix the issue.
A workaround is to set global mysql56_temporal_format=ON; without restarting a server to perform an `ALTER TABLE IMPORT.

ALTER TABLE IMPORT will be enhanced in 10.7 task MDEV-26137

The root problem though was that the error message gave no hints towards mysql56 temporal format being the cause of the mismatch?

How would you guess that being the root cause when just seeing

ERROR 1808 (HY000): Schema mismatch (Column dt precise type mismatch.)

as error message?

If that message gave clear hints towards the workaround needed it would be much less of an issue.

It doesn't really matter. If you have a schema mismatch, you need to have ibd/cgf/frm files, and import using all the three. You cannot import only ibd/cfg if you have a schema mismatch. No matter what exactly mismatch it is.

MDEV-26137 is about making three-file import simpler.

serg, what you suggested 5 days ago is MDEV-11658. It is not as simple as you imply, because until we have removed the InnoDB data dictionary, the discovered table would have to be registered there. I would not want to allow arbitrary DML statements to modify the InnoDB data dictionary tables.

Okay. Then there's still a way to do it. I didn't mention it at first, hoping it won't be needed.

There's fallback-and-retry loop in the table open process. MyISAM uses it to auto-repair tables on open, discovery uses it to auto-magically create tables on open, system versioning uses it to add new partitions (MDEV-17554). It is the way to go if you need to modify metadata on open. I just hoped you won't need it.

MDEV-26137 fixed this by allowing the original .frm file to be used. The files would be imported simply by executing

ALTER TABLE dt_test IMPORT TABLESPACE;