Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a master-master replication which is used rather like a master-slave because of replication breaks from time to time. It is always broken on the same table. To find the reason on this problem I analyzed slave relay-logs and master bin-logs. Please look (I selected events for this table):

### UPDATE test.BATCH_JOB_EXECUTION_SEQ
### WHERE
###   @1=395257
### SET
###   @1=395258
# at 306434626
# at 306434704
#140701 13:35:00 server id 173  end_log_pos 306434704   Table_map: `test`.`BATCH_JOB_EXECUTION` mapped to number 5087
#140701 13:35:00 server id 173  end_log_pos 306434796   Write_rows: table id 5087 flags: STMT_END_F
### INSERT INTO test.BATCH_JOB_EXECUTION
### SET
--
### UPDATE test.BATCH_JOB_EXECUTION_SEQ
### WHERE
###   @1=395259
### SET
###   @1=395260
# at 306795145
# at 306795223
#140701 13:36:00 server id 173  end_log_pos 306795223   Table_map: `test`.`BATCH_JOB_EXECUTION` mapped to number 5087
#140701 13:36:00 server id 173  end_log_pos 306795315   Write_rows: table id 5087 flags: STMT_END_F
### INSERT INTO test.BATCH_JOB_EXECUTION
### SET
--
### UPDATE test.BATCH_JOB_EXECUTION_SEQ
### WHERE
###   @1=395258
### SET
###   @1=395259
# at 306796078
# at 306796156
#140701 13:36:00 server id 173  end_log_pos 306796156   Table_map: `test`.`BATCH_JOB_EXECUTION` mapped to number 5087
#140701 13:36:00 server id 173  end_log_pos 306796248   Write_rows: table id 5087 flags: STMT_END_F
### INSERT INTO test.BATCH_JOB_EXECUTION
### SET
--
### UPDATE test.BATCH_JOB_EXECUTION_SEQ
### WHERE
###   @1=395260
### SET
###   @1=395261
# at 308940346
# at 308940424

As you can see the order of this events are wrong (The 2's and 3's queries should on opposite positions). I would like to ask is there a way to avoid this problem?

MySQL version: 5.1.52-community-log

share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.