1

I was going through the documentation for sync_binlog parameter and found a discrepancy in sync_binlog parameter documentation.

The documentation here http://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#sysvar_sync_binlog says:

A value of 1 is the safest choice because in the event of a crash you lose at most one commit group from the binary log.

Which essentially means that the data will be updated but might not be there in the binlogs.

However, in the binary log documentation here http://dev.mysql.com/doc/refman/5.6/en/binary-log.html says:

For example, if you are using InnoDB tables and the MySQL server processes a COMMIT statement, it writes many prepared transactions to the binary log in sequence, synchronizes the binary log, and then commits this transaction into InnoDB. If the server crashes between those two operations, the transaction is rolled back by InnoDB at restart but still exists in the binary log.

Which essentially means that the transaction is first written in binlog and then committed to the InnoDB, so there is a chance that in case of crash the row is there in the binlog but doesn't exist in the Database.

I have asked this question in MySQL forum and waiting for the response, but would really appreciate if someone who has used this parameter can give the details on which of the following two behaviour is correct?

Dharman
  • 30,962
  • 25
  • 85
  • 135
Ashu
  • 163
  • 4
  • 13

1 Answers1

0

Syncing in the server itself is different than syncing with Slaves in a Replication setup.

The binlog (hence, not sync_binlog and "group commit") is not used in the integrity of InnoDB tables on the Master. However, as you point out, it is involved.

The crash you mention will happen after the logs are written to the local machine, hence the local machine (if it comes back up) can recover. But the "group commit" may not make it to the slaves. Even if it gets to the binlog, that does not assure that any Slave (much less, all Slaves) has gotten the value yet.

See also "semi-sync" replication, wherein at least one Slave must acknowledge before the COMMIT is ack'd.

You quoted something that implies that a transaction could get to a Slave, yet be rolled back on the Master. I think there is an open bug on that case.

GTIDs also confuse the issue. It could be that some of the quotes you made predate GTIDs and need updating.

Consider reporting this at http://bugs.mysql.com .

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • I understand that running mysql in semi-sync mode will ensure that the commit has been written to the bin logs of one of the slaves, however here i am more concerned about _sync_binlog=1_ setting in mysql, i wanted to know what is the kind of guarantee it can give in case of a crash, will it be that it writes to bin log but not commit in the Master DB or can it be that it commits to masterDB but fails to write into bin logs. The documentation in itself have contradictory statements, i have logged a bug against this: https://bugs.mysql.com/bug.php?id=82900 . Thanks for the information. – Ashu Sep 08 '16 at 07:30