MySQL Recovery – Fixing Length Mismatch Error for DATETIME Field When Importing Tablespace Files

MySQLrecovery

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.

Best Answer

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 !!!