CODEWITHSUNDEEP
mysqlreplicationbinary-log
Steven Van Ingelgem
Steven Van Ingelgem

Reputation: 1009

MySQL binary log timestamps non sequential?

I do have a binary log dump here from MySQL 5.5.20 (row based). Now my slave stopped because the UPDATE event is before the INSERT event:

5424240-SET TIMESTAMP=1327402568/*!*/;
5424241-BEGIN
5424242-/*!*/;
5424243-# at 25715325
5424244-# at 25715417
5424245:#120124 11:56:08 server id 1  end_log_pos 25715417      Table_map: `auto`.`data_export` mapped to number 9142508
5424246-#120124 11:56:08 server id 1  end_log_pos 25716169      Update_rows: table id     9142508 flags: STMT_END_F
5424247:### UPDATE auto.data_export
5424248-### WHERE
5424249-###   @1=350049
5424250-###   @2='0522'
5424251-###   @3='50591'
5424252-###   @4=5
--
5424457-SET TIMESTAMP=1327402566/*!*/;
5424458-BEGIN
5424459-/*!*/;
5424460-# at 25717962
5424461-# at 25718054
5424462:#120124 11:56:06 server id 1  end_log_pos 25718054      Table_map: `auto`.`data_export` mapped to number 9142508
5424463-#120124 11:56:06 server id 1  end_log_pos 25718444      Write_rows: table id 9142508 flags: STMT_END_F
5424464:### INSERT INTO auto.data_export
5424465-### SET
5424466-###   @1=350049
5424467-###   @2='0522'
5424468-###   @3='50591'
5424469-###   @4=5

As you can see the 2nd timestamp is later in the binary log (1327402566 <-> 1327402568). @1 is the primary key. Which is in both cases 350049. But first I would expect the INSERT statement, than the UPDATE...

How could this be possible? And how can I possibly fix this?

Could not execute Update_rows event on table auto.data_export; Can't find record in 'data_export', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.001459, end_log_pos 25716169

Thanks

Upvotes: 2

Views: 2034

Answers (1)

Romain
Romain

Reputation: 12819

The binary log is written one transaction at a time. If you're using poor transaction isolation levels, the following scenario:

  1. Transaction A runs the INSERT STATEMENT
  2. Transaction B runs the UPDATE STATEMENT
  3. Transaction B is COMMITTED
  4. Transaction A is COMITTED

Could result in the UPDATE being written before the INSERT statement, as MySQL writes statements within transactions as just before the COMMIT is performed, and otherwise logs statements juste after they've been completed. But this only works if the transaction isolation level is so low that the interest of transactions is pretty much voided...

Upvotes: 1

Related Questions