Mysql – Converting MySQL 5.5 datetime binary to MySQL 5.6 (math problem)

date mathdatetimemysql-5.5

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:

https://dev.mysql.com/doc/internals/en/date-and-time-data-type-representation.html

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: 0000-00-09 04:59:58.8100193

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!

Best Answer

For posterity:

SELECT
     execution_datetime
    ,string_formatted_date
    ,STR_TO_DATE(string_formatted_date,'%Y%m%d%H%i%s')
FROM
    (
        SELECT
              day(execution_datetime) * POWER(2,41)
            + hour(execution_datetime) * POWER(2,36)
            + minute(execution_datetime) * POWER(2,30)
            + second(execution_datetime) * POWER(2,24)
            + (CASE WHEN DATE_FORMAT(execution_datetime,'%f') >= 8388608 THEN 1 ELSE 0 END) * POWER(2,24) /*extra second*/
            + DATE_FORMAT(execution_datetime,'%f') as string_formatted_date
        FROM execution_history
    ) a

It appears that there is some math error in the original import that misses a second when the 23 bit is 1.

Answer was arrived at by using the MySQL doc provided above to reverse engineer the bits and put them back in the old-style datetime format. From there, STR_TO_DATE nicely parses the decimal representation into a datetime. Extra second error was found through trial and error, while obsessively staring at the data and cursing. I would not recommend this approach.

Related Question