I received a database "backup" of binary files (.frm and .ibd) that were copied from a MySQL 5.5 install. Unfortunately, I was unable to get these files to import into a MySQL 5.5 vm that I created. It seems that MySQL 5.6 is much happier about accepting .ibd files without a corresponding .cfg file, as it will just assume the layout of the binary files matches the existing schema. Which would be fine except datetimes are stored differently between the two versions:
I went ahead and altered a table with a datetime column in the middle of it to use datetime(6). This will force the 8-byte datetime from the binary to fit nicely within the datetime(6) column, without overflowing into the columns to the right.
So, one problem solved: the datetime columns no longer corrupt all columns after them. Hooray!
Next problem: now, the datetimes are incorrect. I know this date should be within the last 10 years, but I am unsure how to correct it:
I assume there is a math solution for this, where I can take the data from the column and convert it in place. Something like
execution_datetime = execution_datetime * math and come out with the correct datetime.
Has anyone tried anything like this before? Is there a standard code snippet for this?
Any help would be much appreciated!