0

Here is the multi master setup. Table is not created on middle master.

log-slave-updates is not enabled in my.cnf file.

Master 1

mysql> create database replication;
mysql> use replication;
mysql> CREATE TABLE T1 (ID INT Not Null) ENGINE = InnoDB CHARACTER SET latin1 COLLATE latin1_bin;

Master 2

mysql> use replication;
mysql> select * from T1;
ERROR 1146 (42S02): Table 'replication.T1' doesn't exist

Master 3

mysql> use replication;
Database changed
mysql> select * from T1;
+----+
| ID |
+----+
|  2 |
+----+
1 row in set (0.00 sec)
2
  • Questions: Q1) Which direction is Replication, is it M1->M2->M3 and back to M1, or is it M3->M2->M1 and back to M3 ? Q2) When you run SHOW VARIABLES LIKE 'server_id'; on each Master, what values do you get ? May 27 '14 at 17:52
  • 1
    If log_slave_updates is not enabled in any of your my.cnf files, then the real question is not why M2 is wrong. T1 was created separately on M1 and M3 (or why is there data on M3, which you don't show inserting it on M1?). Multi-master requires log_slave_updates on every server. May 27 '14 at 21:03
0

@Michaelsqlbot's comment makes it easy to see the issue

Replication must be going

+----<------------<-----+
|                       ^
|                       |
V                       |
+--> M3 -> M2-> M1 -->--+

or

+----<------------<-----+
|                       ^
|                       |
V                       |
+--> M1 -> M3-> M2 -->--+

and M2 does not have log-slave-updates

Given that topology, here is the sequence of events that must have taken place

  • You created table T1 on M1
  • M3 has table T1
  • M2 does not have table T1 because M2 does not have log-slave-updates
  • You inserted 2 into table T1
    • If the INSERT was done on M1, M3 has data, M2 has no data and no table T1
    • If the INSERT was done on M3, M1 has no data, M2 has no data and no table T1

EPILOGUE

Configuring log-slave-updates tells a Slave to take a binlog event from its relay logs, execute it, and record the event in its local binlogs (if log-bin is enabled on the Slave). Thus, M2 not having log-slave-updates enabled in your MultiMaster setup is the root cause.

Big thanks to @Michaelsqlbot for the hint.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.