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 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?

share|improve this question

1 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);
share|improve this answer
    
Ok my server parameters are sync_binlog = 0 and innodb_flush_log_at_trx_commit = 2. Now if I need to follow this logic, then would it be possible for Master to remove lines from what it had committed in binary logs after restart? –  Mannoj May 2 '13 at 10:13
    
Yes, it would be possible. The lines are not removed as such - MySQL would have told the OS to write to the binary log, but the server would have crashed before it could write the files. Setting sync_binlog = 1 tells MySQL to ensure that the binary log has been written, instead of leaving that job to the OS. –  Nathan Jolly May 3 '13 at 16:39
    
No assumptions I would like to keep here for Crash recovery. @NathanJolly - Slave takes information from Master's binlog, and that is how Slave DB server gets updated. ( I can get convinced when before flushing to disk the DB server got crashed and that is why I get previous internval data on Master) . But what about the lines that found missing for me in Master binlog?? –  Mannoj May 6 '13 at 9:21
    
Can you rephrase your last comment? I'm not sure that I understand your question. In case it helps answer your question, it is possible for the Slave to retrieve data from the Master's binary log before the Master has physically written it to disk. –  Nathan Jolly May 9 '13 at 0:28
    
@Mike - I didn't see any such error type in error log. It has info about recovery as Recovering after a crash using mysql-bin Starting crash recovery... Crash recovery finished. –  Mannoj May 9 '13 at 8:35

Your Answer

 
discard

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

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