Re: MySQL bin-log

Hello,

On Thu, Jul 19, 2007 at 06:31:16PM -0400, Dave Juntgen wrote:
> Hello everyone,
>
> I have been pondering the question on how an update makes it into the
> MySQL bin-log when something goes wrong.
>
> For example:
>
> If the table being updated is corrupted and marked as crashed and an
> update to the table comes along and see's that the table is toast, at
> this point what happens with that update and the bin-log? I guess this
> a "cart before horse" question...I'm going to guess that the update
> doesn't make it into the bin-log until after it successfully was applied
> to the table...there are obvious consequences with replication if it
> were the other way around. So, if this is the case, I'm thinking it
> would be really nice to know which updates failed because of table
> corruption...
>
> I guess I'm looking for some clarity on how MySQL handles exceptions to
> table updates and logging those updates to the bin-log.
>

If the table is transactional, the statement rolls back and nothing gets into the binlog.
If the table is not transactional:
http://dev.mysql.com/doc/refman/5.0/en/replication-features.html: "Due to the non-transactional nature of MyISAM tables, it is possible to have a statement that only partially updates a table and returns an error code. This can happen, for example, on a multiple-row insert that has one row violating a key constraint, or if a long update statement is killed after updating some of the rows. If that happens on the master, the slave thread exits and waits for the database administrator to decide what to do about it unless the error code is legitimate and execution of the statement results in the same error code on the slave. If this error code validation behavior is not desirable, some or all errors can be masked out (ignored) with the --slave-skip-errors option."
(And if the statement touches both transactional and non-transactional tables? not well-defined, see
http://bugs.mysql.com/bug.php?id=23333).

For example if on the replication master a INSERT SELECT fails after inserting the first rows, because of a duplicate key error, it will not rollback (this is MyISAM), and the statement will be written to binlog with an additional information "this statement failed because of a duplicate key error". Then slave will execute the statement; if it also gets a duplicate key error, all fine (it assumes statement executed the same way on slave as on master); otherwise (no error, or a different error) it stops with an error message in the error log.

If the statement failed on the replication master due to KILL, or shutdown, or network connection breakage, this error has no chance to reproduce itself on slave, so slave stops.

Last, all the above applies only to statement-based binary logging (all that exists in 5.0). In row-based binary logging (present in 5.1 in addition to statement-based), inserted rows are written to the binlog, failed rows are not (quite simple).

-- 
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /    Mr. Guilhem Bichot 
 / /|_/ / // /\ \/ /_/ / /__   MySQL AB, Lead Software Engineer
/_/  /_/\_, /___/\___\_\___/   Bordeaux, France
       <___/   www.mysql.com   

-- 
MySQL Internals Mailing List
For list archives: 
http://lists.mysql.com/internals
To unsubscribe:    
http://lists.mysql.com/internals?unsub=lists@pantek.com