List:Replication« Previous MessageNext Message »
From:Travis Whitton Date:August 1 2008 4:11pm
Subject:Master and Slave Losing Sync
View as plain text  
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