0

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!

  • 1
    Your assumption: "there is a math solution for this" seems wrong, based on the description of the old and new formats. – mustaccio Jan 21 at 20:37
  • This should basically work: day(execution_ts) * POWER(2,41) + hour(execution_ts) * POWER(2,36) + minute(execution_ts) * POWER(2,30) + second(execution_ts) * POWER(2,24) + DATE_FORMAT(execution_ts,'%f') ... except it doesn't :( – Phillip Jan 21 at 21:31
  • So, it works for some values. 0000-00-09 04:59:58.8100193 becomes 20130419153249, which means 2013-04-19 15:31:49 and is a valid datetime within a reasonable timeframe – Phillip Jan 21 at 21:39
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.