Master updates Slave but not Master itself

Question

I have a setup master/slave in which applications are pointed only to master. Yesterday Master had got crashed due to "multi bit error on dimm detected" in Front indication panel in orange color.

After reboot I see the master went for recovery then it came up and application started to use the Master normally but still the err msg exist in Front indication panel. But now the slave had stuck out due to primary key constraint for a table.

The problem I face is master has a table data until what exist in binlog of master. But slave has the table data what is not in master binlog. Below is the table details when compared to Master table and Slave table.

Last info in binlog timestamp is 2013-04-29 02:13:11 
System shut down timestamp 2013-04-29 02:54         

            mysql> select * from audit where id=11298907;
            ------------------------------------------------------------
            | id     | ipaddress      | dated        | msisdn  |
            +----------+----------------+-------------------------------
            |11298907 | 82.25.226.183  | 2013-04-29 02:13:11 | 998282821|
            -----------------------------------------------------------
            mysql> select * from audit where id > 11298907 limit 1;

            -----------------------------------------------------------
            | id     | ipaddress      | dated        |msisdn  |
            +----------+----------------+------------------------------
            |11298908 | 82.25.226.183 | 2013-04-29 04:31:13 | 992828111|
            ------------------------------------------------------------

            On Slave
            ==========

            mysql> select * from audit where id=11298907;
            ------------------------------------------------------------
            | id     | ipaddress      | dated        | msisdn  |
            +----------+----------------+-------------------------------
            |11298907 | 82.25.226.183  | 2013-04-29 02:13:11 | 998282821|
            -----------------------------------------------------------



            mysql> select * from audit where id > 11298907 limit 1;

            -----------------------------------------------------------
            | id     | ipaddress      | dated        |msisdn  |
            +--------+----------------+---------------------------------
            |11298908 | 82.25.226.183 | 2013-04-29 02:13:12 | 762616173|
            ------------------------------------------------------------

Both the servers are ntp synched with GST. And they remain same after Master reboot too. It took almost 2 hrs to bring up Master Server. How come only slave can have the data that Master is not aware of? Please have someone come accross such a thing?

Answer 1

The reason for this is pretty straightforward:

if you are using InnoDB tables and the MySQL server processes a COMMIT statement, it writes the whole transaction to the binary log and then commits this transaction into InnoDB

http://dev.mysql.com/doc/refman/5.5/en/binary-log.html

In periods of moderate to low traffic, it's easy for the "last thing that happened" to make it into the binary log and get handed over to the slave, where it's executed, but not persist on the master. If you're not using sync_binlog = 1 to sync the binary log to disk after every write and innodb_flush_log_at_trx_commit = 1 to flush every InnoDB commit immediately to disk, then you don't have an ACID-compliant installation ... and if the machine itself crashes, then MySQL has no means of realizing that InnoDB and the binary log aren't consistent on crash recovery.

---

Update: the original question suggests that you reviewed the master's binlogs and found the data that made it to the slave to be missing from there, too. To clarify, there are two possible explanations, if this is the case:

• the event was "written" to the master binary log and sent to the slave, but "writing" to a file and the operating system flushing that file to the hard drive are not the same thing; a file that has been written to does not have to be flushed in order for the unflushed data to be read, since the "written" data exists in the OS cache even if it hasn't been persisted to disk.

• there is some language at the link above that suggests that even if the binlog did get persisted to disk, crash recovery could have trimmed those transactions when InnoDB discovered they hadn't been committed:

after a crash, after doing a rollback of transactions, the MySQL server cuts rolled back InnoDB transactions from the binary log

The justification for this action is stated as:

This ensures that the binary log reflects the exact data of InnoDB tables, and so, that the slave remains in synchrony with the master (not receiving a statement which has been rolled back).

This statement is not fully true, since there's a small window of time that the event could have gotten sent to the slave. On the other hand, if this trimming does happen, the binlog would agree with the data that got persisted on the master, which is arguably better than it not agreeing with the data that got persisted to the master.

I'm not wholly convinced that the example, below, from the source code, represents the process described in the documentation, but if you have a message in your error log that looks like this, then that explanation seems to address your question.

sql/binlog.cc:
  sql_print_information("Crashed binlog file %s size is %llu, "
                        "but recovered up to %llu. Binlog trimmed to %llu bytes.",
                        log_name, binlog_size, valid_pos, valid_pos);