On 1/25/2011 10:45, Robinson, Eric wrote:
>>> There is a very good reason: it is the phenomenon of row drift. The
>>> master and slave can appear to be in good sync, but often it is not
>>> actually the case.
>>
>> ... sounds interesting; have you got any document explaining
>> this phenomenon? AFAIK, the things that (silently) break
>> replication are:
>> - non-deterministic functions in statement-based replication
>> - hand-made updates on the slave db
>> is this enough to justify a *daily* resync?!
>
>
> I'm definitely no expert on this. All I know is that we used to
> frequently experience situations where queries to the slaves would
> return different recordsets than the same queries to the masters. Yet by
> all other indications the servers were in sync. All the replication
> threads were running and the row counts were identical, but the data in
> the rows was sometimes different. I asked about this in the list and the
> answers I got back were that the phenomenon was called row drift and was
> fairly well known and not always easy (or sometimes even possible) to
> eliminate because of bad programming practices in some off-the-shelf
> applications. At that time, the consensus in the list was that it was
> not safe to trust replication slaves for backup purposes. That's when I
> came up with the idea of doing an rsync every night, which creates a
> slave that is 100% reliable for using as a backup source and also
> eliminates problems with row-drift. Since we started using that
> technique, we don't get calls from users complaining that their reports
> are showing bogus totals and such.
>
I suspect that your queries were not as deterministic as you thought
they were. Do you have a sample of a query that produced different
results between the master and the slave? We shouldn't need the results,
just the query.
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
