4

I'm using MySQL 5.7 with GTID master-master replication and I'm experiencing a strange error.

Randomly one of my masters will stop replicating with: "Cannot replicate anonymous transaction when @@GLOBAL.GTID_MODE = ON"

When I check there is indeed an anonymous entry in the binlog, but what isn't clear is how it got there since the other master also has GTID_MODE=ON and that should not allow any Anonymous transactions to execute or make their way in to the binlog.

enforce_gtid_consistency is also ON so queries that would result in an Anonymous transaction should be failing.

It's also only ever a single query/transaction. The previous and next queries in the binlog always have GTID's.

Nick
  • 594
  • 2
  • 7
  • 18
  • can you include the output from SHOW SLAVE STATUS – Jess Balint Apr 19 '16 at 20:21
  • Sorry for the delay, it's just occurred again. Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Cannot replicate anonymous transaction when @@GLOBAL.GTID_MODE = ON, at file /var/log/mysql/mysql-bin.002642, position 55634042.; the first event 'mysql-bin.002636' at 90512641, the last event read from '/var/log/mysql/mysql-bin.002642' at 55634107, the last byte read from '/var/log/mysql/mysql-bin.002642' at 55634107.' – Nick May 02 '16 at 18:00
  • Have you found a solution to the issue? – Tan Hong Tat May 03 '16 at 08:58

2 Answers2

2

I ran into this same issue. The first time I saw it was when I upgraded to mysql 5.7.12.

I don't have a workaround/fix yet either. What version of mysql are you on?

Scott Nebor
  • 63
  • 1
  • 4
  • I am on MySQL 5.7.11. The workaround I've found is to set @@global.GTID_MODE=ON_PERMISSIVE; on both servers, start slave, wait for it to fully catch up then set both servers back to set @@global.GTID_MODE=ON; – Nick May 02 '16 at 18:02
  • Setting GTID_MODE to ON_PERMISSIVE really helped. – Abhijit Buchake May 06 '17 at 10:06
0

Setting GTID_MODE to ON_PERMISSIVE helped me either on mysql 8.0.12 with the same error. I left it at ON_PERMISSIVE because I don't see any disadvantage of it.

SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
Leo Bosnjak
  • 33
  • 1
  • 4
  • I have noticed some issues when resyncing when it's set to ON_PERMISSIVE however it does work to get around the problem so I normally set it to ON_PERMISSIVE, then once the slave delay is gone set it back to ON. – Nick Oct 23 '19 at 14:29