Downgrading from MySQL 5.6 to MySQL 5.5

Last week I had to downgrade from MySQL 5.6 to 5.5. The reason for this was that the application used a very old Connector/J and that's incompatible with MySQL 5.6 because the removal of SET OPTION syntax.

We're now planning to upgrade Connector/J to be able to upgrade to 5.6 again.

There are two methods of downgrading:

• Dump/Restore with mysqldump. This is easy and reliable, but can take more time.

• In place (replace binaries, don't change data). This fast, but won't work if file formats have changed.

As expected this is documented in the MySQL Reference Manual.

I went for the in place method. I expected this to work without many issues as this database was not using the fancy new features like fulltext indexes for InnoDB. All tables used the Antelope format. As both MySQL versions support Antelope and Barracuda this shoud be fine. I don't know why Oracle didn't introduce a new Cheeta format with the introduction of the fulltext indexes.

The issues I encountered were:

• A different innodb_log_file_size default (had to set it in my.cnf)
• Missing mysql.host table.  Bug #73634 (Fixed by copying it from another instance)
• Various changes in the privilege tables. (Fixed by dropping them and running mysql_upgrade)
• Changes in mysql.user (set plugin to '' when plugin='mysql_native_plugin')
• Warnings about unknown flag 50. Bug #72371 (Not a Bug)

This server didn't user replication, so that wasn't an issue.