0

I have this configuration:

  1. Master running MySQL 5.5 with log file format: STATEMENT
  2. Slave running MySQL 8.0 - connected to (1) master. Log file format: ROW
  3. Another Slave running MySQL 8.0 - connected to (2) the first slave. Log format: ROW

Main problem: Slave number 3 (who is connected to slave number 2 that is connected to the master) sometime goes out of sync for query. I get errors like "Can not execute query. Duplicate ID '12345' already exists". Then I need to dump the database again from slave(2) and start the slave(3) again on the correct location. It keeps syncing for a day or two and then I get an error again.

Secondary problem(s): Trying to fix Main Problem, I tried to change slave(2) (which is also master to slave(3)) log format to STATEMENT but it stopped syncing at all (it was working great before). No error. Just the second_behind_master goes up and up and not advancing the relay log. When I changed the log format back to ROW it resume syncing with no problems.

I also tried to change slave(3) log format to STATEMENT but then I got the error of Master sending ROW format (although I changed it to STATEMENT - see above) and slave is in STATEMENT format.

Any ideas ? Thanks.

aviv
  • 167
  • 1
  • 1
  • 9
  • first 5.5 to 8.0 replication is not supported, second you may be missing log_slave_updates in server 2. Post my.cnf from all nodes? – jerichorivera Jun 03 '20 at 22:10

1 Answers1

0

Log format is set on the master, not the slaves. So set it to ROW on 1. You will also need log_slave_updates on 2.

Make sure 2 and 3 are configured with

read_only=1

It sounds like something is injecting an operation downstream of the master, and setting everything except the master to read_only should prevent that.

Make sure that nothing is using an account with SUPER privileges, generally only your root@localhost should have SUPER privileges and your app shouldn't be connecting as foot. SUPER privileges ignore read_only.

Gordan Bobić
  • 936
  • 4
  • 10
  • Thanks for trying. Unfortunately that's doesn't explains why when I change (2) to STATEMENT it stops replicating from (1) .... – aviv Jun 08 '20 at 17:45