List:Replication« Previous MessageNext Message »
From:Travis Whitton Date:August 6 2008 3:11pm
Subject:Re: Master and Slave Losing Sync
View as plain text  
Yes, the script does ORDER BY. When I was just double-checking, I found an
error in the way I've been presenting the data. When the script was
reporting errors, it had the master and slave reversed. So, it appears that
the master did increment the primary key by a value of one; however, the
slave incremented by 13, which knocked them out of sync. Any idea why the
slave would bump up the primary key by such a high increment? Other than the
primary key being off, the rows match.

On Tue, Aug 5, 2008 at 4:50 PM, Database System <database100@stripped>wrote:

> When the script exma rows, did it do ORDER BY ...?
> For missing auto_increment numbers, did you do manual search to make sure
> they are not in the table?
> Which column is the auto_increment column in user table?
>
>
> --- On Tue, 8/5/08, Travis Whitton <tinymountain@stripped> wrote:
>
> > From: Travis Whitton <tinymountain@stripped>
> > Subject: Re: Master and Slave Losing Sync
> > To: database100@stripped
> > Cc: replication@stripped
> > Date: Tuesday, August 5, 2008, 1:29 PM
> > 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