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
to5.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 theDATETIME
and found out it was not the right length as5.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
: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 !!!