List:Replication« Previous MessageNext Message »
From:Travis Whitton Date:August 5 2008 6:29pm
Subject:Re: Master and Slave Losing Sync
View as plain text  
The compare script reads a given table on both the master and the slave and
then steps through row by row performing an md5sum on all the column data
expressed as a string. When it finds a row where the md5sum doesn't match,
it halts execution and gives a notification message.

Every time a row is examined, the total count seen so far is incremented by
one. The total rows to be examined remain static since they are calculated
ahead of time. For some unexplained reason, the primary key value is well
above the total number of rows. It seems like the primary key index is
growing by more than one in some situations; although, I'm not sure how this
would happen. I should also mention that the table is inserted via the
following trigger:

  Trigger: LoginLogInsertUserLoginHistory
    Event: UPDATE
    Table: Users
Statement: IF (NEW.TSLogin != OLD.TSLogin) THEN
INSERT DELAYED INTO LoginLog (UserID, LoggedInIP, LoggedInUserAgent)
VALUES (NEW.UserID, NEW.LoggedInIP, NEW.LoggedInUserAgent);
END IF
   Timing: AFTER
  Created: NULL
 sql_mode:
  Definer: root@localhost

Thanks,
Travis

On Sun, Aug 3, 2008 at 6:54 PM, Database System <database100@stripped>wrote:

> Travis,
>
> The compare result is depends on how did you write the compare script.
> Would you mind to show me?
>
> Also I'm sorry I don't understand why "how many rows the script
> has examined so far" is not equal to "the total rows to be examined"?
> Why the total raws to be examined keeps no increase?
>
> Lisa
>
>
> --- On Fri, 8/1/08, Travis Whitton <tinymountain@stripped> wrote:
>
> > From: Travis Whitton <tinymountain@stripped>
> > Subject: Master and Slave Losing Sync
> > To: replication@stripped
> > Date: Friday, August 1, 2008, 11:11 AM
> > Hello,
> >
> > We have a typical master/slave setup at the company I work
> > for. We're
> > running 5.0.66a-enterprise-gpl-log on both the master and
> > slave, and we've
> > been using Maatkit to detect when tables are out of sync.
> > Yesterday I wrote
> > a small script to do a row by row comparison of a table and
> > provide some
> > debug data. It looks like the primary key on the master
> > isn't always
> > auto-incrementing by one. I found the spot where they fall
> > out of sync, and
> > the master jumps ahead by 13 while the slave increments by
> > one (as it
> > should). We're still on 5.0, so it's using
> > statement based replication.
> > Please see the debug output below.
> >
> > In the comparison lines below, the first number is how many
> > rows the script
> > has examined so far. The second number is the total rows to
> > be examined, and
> > the number in parens in the primary key value on the
> > master. If you look,
> > the primary keys are increasing by a factor of one as they
> > should. UNTIL...
> > the master primary key value jumps up by 13. The slave
> > actually inserts the
> > expected value, but the master does not. The other data is
> > identical. Since
> > the replication is statement based, incrementing primary
> > keys is handled
> > using default values on the slave, and they are no longer
> > in sync.
> >
> > comparing 419748/430566 (420687)
> > comparing 419749/430566 (420688)
> > comparing 419750/430566 (420689)
> > comparing 419751/430566 (420690)
> > comparing 419752/430566 (420691)
> > comparing 419753/430566 (420704)
> > INCONSISTENCY
> > -------------
> > master data:
> > 420704,23,209.251.128.xxx,Mozilla/4.0 (compatible; MSIE
> > 6.0; Windows NT 5.1;
> > SV1; .NET CLR 2.0.50727),2008-07-28 03:53:48
> > slave data:
> > 420692,23,209.251.128.xxx,Mozilla/4.0 (compatible; MSIE
> > 6.0; Windows NT 5.1;
> > SV1; .NET CLR 2.0.50727),2008-07-28 03:53:48
> >
> > Any help would be greatly appreciated.
> >
> > Regards,
> > Travis
>
>
>
>

Thread
Master and Slave Losing SyncTravis Whitton1 Aug
  • Re: Master and Slave Losing SyncDatabase System4 Aug
    • Re: Master and Slave Losing SyncTravis Whitton5 Aug
      • Re: Master and Slave Losing SyncMarcus Bointon5 Aug
        • Re: Master and Slave Losing SyncTravis Whitton5 Aug
      • Re: Master and Slave Losing SyncDatabase System5 Aug
        • Re: Master and Slave Losing SyncTravis Whitton6 Aug