Avoiding MySQL ERROR 1784 When Replicating From 5.7 to 5.6
Recently I upgraded some MySQL databases from 5.6 to 5.7, but – for boring reasons unique to my environment – I had to leave one replica on version 5.6. I knew there was a chance that the 5.7 -> 5.6 replication wouldn’t work, but I decided to try it out to see if (and why) it would fail. Once I upgraded the master, replication failed, so I checked the error log on the replica and found these messages:
The error surprised me a little bit since I’m not using GTIDs in that replication topology. I asked around a bit, and Kenny Gryp hypothesized that I might be experiencing MySQL bug #74683, which was fixed in version 5.6.23. Since my replica was on 5.6.22, I decided to do an incremental 5.6 upgrade to see if that resolved the issue. I upgraded to 5.6.31 and replication started working.
YMMV and there are certainly bound to be 5.7 -> 5.6.31 replication scenarios that don’t work, but this was a simple fix for me. In hindsight it makes sense that replicating from a new major version to an older major version is more likely to work with a more recent minor version, so if I have to do this in the future I’ll make sure the replica is running the latest minor version before upgrading the master.
P.S. Thanks Kenny!