5.5 -> 5.6 DATE type upgrade issues / hex edit frm files

MySQL & MariaDB Other MySQL® Questions
1

Hi.

(Intro) We current have 5.5 (from the debian package percona-xtradb-cluster) running, in two x three-node galera clusters. Which is all good.

We decided to upgrade to 5.6, both to get those improvements and galera 3.

However, looking at the change notes ([url]http://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html[/url]) it show incompatible changes for DATE and releated types. And the instructions to fix this include editing the frm file in hex mode. This seems not only dangerous to me, but a bit, well, mad.

So I was wondering if there was another workaround. If I did a mysqldump/re-import (not really practical, I have rather a lot of data) would that work? Or convert the databases to MyISAM, upgrade the servers, and convert back to InnoDB?

I’d rather not write a script to go across all my databases (and all the tables therein) and monkey with the frm files in hex mode.

Are there issues with the upgrade? Given it isn’t backwardly compatible, I’d rather make sure this will work before I do it. I’ve googled around, but nothing seems to imply it is fundementally broken. But the instructions in the ‘upgrading release notes’ worry me. Given the low-level nature of the fix, I am surprised I haven’t seen more info/posts about it. Maybe everyone is just ignoring it…

Everything else in the release notes are fine.

cheers,

m.

2

Well, just in case anyone ever searchs the fora, here is what I did and the results:

EXECUTIVE SUMMARY: All is well.

More detail…

(On 5.1) I generated a new database with many, many tables, with all sorts of date/time types, with primary keys of various date/time types, including compound primary keys. And into all these tables I threw rather a lot of data, historical and future.

I then created a suite of tests to probe the data (from the simplest ‘do I have X rows in the database?’ to fancier ones.)

The database/tables started life as MyISAM. Then I upgraded to 5.5, ran the tests and all was well.

Now, 5.6 and its incompatibility. I moved the MyISAM files from this test database out of the way, did the upgrade, ran the mysql_upgrade script. All good. Start server, away I go. Then I moved the files back into positon, and ran my tests. They passed! Woohoo! Then I converted the database/tables to InnoDB, and ran the tests again. A few failed. Boo! (But only as I was stupid, it was an ORDERing issue. As in I wasn’t ORDERing one test.)

So, while it might not be backwardly compatible, and the release notes scary, it Works On My Machine.