Importing tablespace files - Length mismatch error for DATETIME field

Question

We are currently trying to import some old historic data tables. Unfortunately, the data is in exported tablespace files (.ibd and .cfg) - there are no dump files available and the server they came from no longer exists.

There are separate tables for each month of data, but they should all have exactly the same format. We have successfully imported the more recent files (with e.g. ALTER TABLE sqlt_data_1_2017_01 IMPORT TABLESPACE), but all the remaining idb files are failing with the following error:

SQL Error [1808] [HY000]: Schema mismatch (Column datevalue length mismatch.)

The datevalue column is a DATETIME.

It's possible that different versions of MySQL were used for creating the various tables and also for exporting the table spaces, but we are 99% sure that the starting version was 5.7.

Some of the tables have .frm files, so we compared a few with mysqlfrm --vvv -diagnostic and noticed that some of the datevalue columns have field_length 26 and some have field_length 19. Also, the stringvalue is VARCHAR(255) but the field_length is 762, although I suspect this is because it's in UTF8.

• All the tables we can import have MYSQL_VERSION_ID 50722 and datevalue field_length 19
• All the tables we can't import have MYSQL_VERSION_ID 50714 and datevalue field_length 26

Did something change with the DATETIME storage format at some point? What can we do to import these tables?

Thanks.

Answer 1

In all honesty, I know that DATETIME did change from 5.5 to 5.6. How do I know this ???

I ran into situation where I was setting up replication from MySQL 5.5 to 5.6 and replication would break because 5.6 wasexpecting microseconds in the relay log for DATETIME, tried to unpack the DATETIME and found out it was not the right length as 5.5 did not have microseconds (This was originally discovered by my manager then). The workaround was to switch to STATEMENT replication.

Here you are mentioning 5.7 to 5.7. I don't see why this should happen, but I have a theory.

If the source data on the running MySQL 5.7.14 instance came from an older version of MySQL as a result of doing inplace upgrades, then I can see this happening if MySQL 5.7.22 stores DATETIME different between minor 5.7 releases.

According to MySQL Documentation on Date and Time Data Type Representation:

DATETIME

• Before MySQL 5.6.4 : 8 bytes, little endian
• After MySQL 5.6.4 :5 bytes + fractional-seconds storage, big endian

So, there was change in data storage format starting in MySQL 5.6.4 in terms of looking at 5 bytes + an unknown number of bytes for microseconds (WEIRD) !!!

I am just speculating at this point, but if the internals for DATETIME changed for 5.7 and it was not published, SHAME ON YOUR, ORACLE !!!. I am just kidding. What you should do from here is research if this bug was reported already. If not, you must report this one and let Oracle have some fun with it (They have missed a patch or overlooked microseconds somewhere).

You may have to get a mysqldump (Logical Dump) of the data and load it into target DB.

I wish you well in this adventure !!!

Answer 2

According to the documentation, MySQL 5.7 allows importing without a .cfg file, but will skip metadata checks if this is missing.

The exported data had both .ibd and .cfg files, so we were copying both over. If we just copy over the .ibd file and leave the .cfg file alone (i.e. use the one created by the new blank table), the import works. The imported data appears to be correct.

I don't know why it was failing with the .cfg file. Something must have changed in MySQL between 5.7.14 and 5.7.22. Unless, as Rolando says, the data did actually come from an earlier version of MySQL which was upgraded in place.