List:Replication« Previous MessageNext Message »
From:Database System Date:August 5 2008 8:50pm
Subject:Re: Master and Slave Losing Sync
View as plain text  
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