Checksum Table producing different results?

I've got 2 database servers running the same database (cannot do
replication as one is live and the other development). One of the
databases needs to be synced from time to time. Because it's rather
large, I'm looking to only transfer tables that have changed, and
hence am using the CHECKSUM TABLE command to determine if tables have
identical data. Dev server is MySQL 5.0.24a Live is MySQL 5.0.18

The problem, is that a few of our tables NEVER report identical
checksums. I have verified that they are identical (by exporting from
one server, importing to the other as a different name, and doing a
checksum on that server). Any ideas as to what I can do? Schema's are
Identical. I've ran optimize and repair table, to no effect. Thanks!

ircmaxell wrote:
> I've got 2 database servers running the same database (cannot do
> replication as one is live and the other development). One of the
> databases needs to be synced from time to time. Because it's rather
> large, I'm looking to only transfer tables that have changed, and
> hence am using the CHECKSUM TABLE command to determine if tables have
> identical data. Dev server is MySQL 5.0.24a Live is MySQL 5.0.18
>
> The problem, is that a few of our tables NEVER report identical
> checksums. I have verified that they are identical (by exporting from
> one server, importing to the other as a different name, and doing a
> checksum on that server). Any ideas as to what I can do? Schema's are
> Identical. I've ran optimize and repair table, to no effect. Thanks!
>
you can use a product called sqlyog that allows for explicit scheduled
syncs. i used it to only sync the data that has been changed in one
server to the other server without replicating enabled.

--
lark -- hamzee@sbcdeglobalspam.net
To reply to me directly, delete "despam".

On May 21, 4:05 pm, lark <ham...@sbcdeglobalspam.net> wrote:
> ircmaxell wrote:
> > I've got 2 database servers running the same database (cannot do
> > replication as one is live and the other development). One of the
> > databases needs to be synced from time to time. Because it's rather
> > large, I'm looking to only transfer tables that have changed, and
> > hence am using the CHECKSUM TABLE command to determine if tables have
> > identical data. Dev server is MySQL 5.0.24a Live is MySQL 5.0.18
>
> > The problem, is that a few of our tables NEVER report identical
> > checksums. I have verified that they are identical (by exporting from
> > one server, importing to the other as a different name, and doing a
> > checksum on that server). Any ideas as to what I can do? Schema's are
> > Identical. I've ran optimize and repair table, to no effect. Thanks!
>
> you can use a product called sqlyog that allows for explicit scheduled
> syncs. i used it to only sync the data that has been changed in one
> server to the other server without replicating enabled.
>
> --
> lark -- ham...@sbcdeglobalspam.net
> To reply to me directly, delete "despam".

I tried SQLyog, but it's just too slow (my script can run about 30
times in the time it takes SQL yog to run once)... If I can figure
out this checksum thing it would be perfect...

ircmaxell wrote:
> On May 21, 4:05 pm, lark <ham...@sbcdeglobalspam.net> wrote:
>> ircmaxell wrote:
>>> I've got 2 database servers running the same database (cannot do
>>> replication as one is live and the other development). One of the
>>> databases needs to be synced from time to time. Because it's rather
>>> large, I'm looking to only transfer tables that have changed, and
>>> hence am using the CHECKSUM TABLE command to determine if tables have
>>> identical data. Dev server is MySQL 5.0.24a Live is MySQL 5.0.18
>>> The problem, is that a few of our tables NEVER report identical
>>> checksums. I have verified that they are identical (by exporting from
>>> one server, importing to the other as a different name, and doing a
>>> checksum on that server). Any ideas as to what I can do? Schema's are
>>> Identical. I've ran optimize and repair table, to no effect. Thanks!
>> you can use a product called sqlyog that allows for explicit scheduled
>> syncs. i used it to only sync the data that has been changed in one
>> server to the other server without replicating enabled.
>>
>> --
>> lark -- ham...@sbcdeglobalspam.net
>> To reply to me directly, delete "despam".
>
> I tried SQLyog, but it's just too slow (my script can run about 30
> times in the time it takes SQL yog to run once)... If I can figure
> out this checksum thing it would be perfect...
>
i think the checksum changes every time there is a change in the table
and i mean any change not just data value changes. i'd be interested to
know if somebody else can verify this.

--
lark -- hamzee@sbcdeglobalspam.net
To reply to me directly, delete "despam".

On May 21, 4:05 pm, lark <ham...@sbcdeglobalspam.net> wrote:
> ircmaxell wrote:
> > I've got 2 database servers running the same database (cannot do
> > replication as one is live and the other development). One of the
> > databases needs to be synced from time to time. Because it's rather
> > large, I'm looking to only transfer tables that have changed, and
> > hence am using the CHECKSUM TABLE command to determine if tables have
> > identical data. Dev server is MySQL 5.0.24a Live is MySQL 5.0.18
>
> > The problem, is that a few of our tables NEVER report identical
> > checksums. I have verified that they are identical (by exporting from
> > one server, importing to the other as a different name, and doing a
> > checksum on that server). Any ideas as to what I can do? Schema's are
> > Identical. I've ran optimize and repair table, to no effect. Thanks!
>
> you can use a product called sqlyog that allows for explicit scheduled
> syncs. i used it to only sync the data that has been changed in one
> server to the other server without replicating enabled.
>
> --
> lark -- ham...@sbcdeglobalspam.net
> To reply to me directly, delete "despam".

I just tried their administrator (before buying the enterprise
version), and it seems quite slow. My sync tool (that I wrote) can
sync the entire 60 meg database in the time it uploads a 1 meg table.
If I could just figure out the checksum issue (not a REALLY big deal,
but would be nice)...

On May 21, 4:05 pm, lark <ham...@sbcdeglobalspam.net> wrote:
> ircmaxell wrote:
> > I've got 2 database servers running the same database (cannot do
> > replication as one is live and the other development). One of the
> > databases needs to be synced from time to time. Because it's rather
> > large, I'm looking to only transfer tables that have changed, and
> > hence am using the CHECKSUM TABLE command to determine if tables have
> > identical data. Dev server is MySQL 5.0.24a Live is MySQL 5.0.18
>
> > The problem, is that a few of our tables NEVER report identical
> > checksums. I have verified that they are identical (by exporting from
> > one server, importing to the other as a different name, and doing a
> > checksum on that server). Any ideas as to what I can do? Schema's are
> > Identical. I've ran optimize and repair table, to no effect. Thanks!
>
> you can use a product called sqlyog that allows for explicit scheduled
> syncs. i used it to only sync the data that has been changed in one
> server to the other server without replicating enabled.
>
> --
> lark -- ham...@sbcdeglobalspam.net
> To reply to me directly, delete "despam".

I just tried their administrator (before buying the enterprise
version), and it seems quite slow. My sync tool (that I wrote) can
sync the entire 60 meg database in the time it uploads a 1 meg table.
If I could just figure out the checksum issue (not a REALLY big deal,
but would be nice)...

>I've got 2 database servers running the same database (cannot do
>replication as one is live and the other development). One of the
>databases needs to be synced from time to time. Because it's rather
>large, I'm looking to only transfer tables that have changed, and
>hence am using the CHECKSUM TABLE command to determine if tables have
>identical data. Dev server is MySQL 5.0.24a Live is MySQL 5.0.18

I don't think there is any guarantee that CHECKSUM TABLE produces
the same checksum even if the data contents is the same. For
example, the records could be in different physical order. (Try
that with a text file: run md5 on the file, re-order a couple of
the lines, and try again. You'll almost certainly come up with a
different checksum.) Unallocated space might make a difference,
too.

>The problem, is that a few of our tables NEVER report identical
>checksums. I have verified that they are identical (by exporting from
>one server, importing to the other as a different name, and doing a
>checksum on that server). Any ideas as to what I can do? Schema's are
>Identical. I've ran optimize and repair table, to no effect. Thanks!

On May 22, 7:19 pm, gordonb.yr...@burditt.org (Gordon Burditt) wrote:
> >I've got 2 database servers running the same database (cannot do
> >replication as one is live and the other development). One of the
> >databases needs to be synced from time to time. Because it's rather
> >large, I'm looking to only transfer tables that have changed, and
> >hence am using the CHECKSUM TABLE command to determine if tables have
> >identical data. Dev server is MySQL 5.0.24a Live is MySQL 5.0.18
>
> I don't think there is any guarantee that CHECKSUM TABLE produces
> the same checksum even if the data contents is the same. For
> example, the records could be in different physical order. (Try
> that with a text file: run md5 on the file, re-order a couple of
> the lines, and try again. You'll almost certainly come up with a
> different checksum.) Unallocated space might make a difference,
> too.
>
> >The problem, is that a few of our tables NEVER report identical
> >checksums. I have verified that they are identical (by exporting from
> >one server, importing to the other as a different name, and doing a
> >checksum on that server). Any ideas as to what I can do? Schema's are
> >Identical. I've ran optimize and repair table, to no effect. Thanks!

Is there any alternative as to how I can check a table for mirrored
data?

>> >I've got 2 database servers running the same database (cannot do
>> >replication as one is live and the other development). One of the
>> >databases needs to be synced from time to time. Because it's rather
>> >large, I'm looking to only transfer tables that have changed, and
>> >hence am using the CHECKSUM TABLE command to determine if tables have
>> >identical data. Dev server is MySQL 5.0.24a Live is MySQL 5.0.18
>>
>> I don't think there is any guarantee that CHECKSUM TABLE produces
>> the same checksum even if the data contents is the same. For
>> example, the records could be in different physical order. (Try
>> that with a text file: run md5 on the file, re-order a couple of
>> the lines, and try again. You'll almost certainly come up with a
>> different checksum.) Unallocated space might make a difference,
>> too.
>>
>> >The problem, is that a few of our tables NEVER report identical
>> >checksums. I have verified that they are identical (by exporting from
>> >one server, importing to the other as a different name, and doing a
>> >checksum on that server). Any ideas as to what I can do? Schema's are
>> >Identical. I've ran optimize and repair table, to no effect. Thanks!
>
>Is there any alternative as to how I can check a table for mirrored
>data?

Consider this: mysqldump both tables into separate files, using
options that produce a single insert statement per record. Sort
both files. (at this point you've probably messed up the file to
the point of not being able to restore it, but this doesn't matter).
Run diff(1) between the two sorted files.

On May 23, 5:46 pm, gordonb.ni...@burditt.org (Gordon Burditt) wrote:
> >> >I've got 2 database servers running the same database (cannot do
> >> >replication as one is live and the other development). One of the
> >> >databases needs to be synced from time to time. Because it's rather
> >> >large, I'm looking to only transfer tables that have changed, and
> >> >hence am using the CHECKSUM TABLE command to determine if tables have
> >> >identical data. Dev server is MySQL 5.0.24a Live is MySQL 5.0.18
>
> >> I don't think there is any guarantee that CHECKSUM TABLE produces
> >> the same checksum even if the data contents is the same. For
> >> example, the records could be in different physical order. (Try
> >> that with a text file: run md5 on the file, re-order a couple of
> >> the lines, and try again. You'll almost certainly come up with a
> >> different checksum.) Unallocated space might make a difference,
> >> too.
>
> >> >The problem, is that a few of our tables NEVER report identical
> >> >checksums. I have verified that they are identical (by exporting from
> >> >one server, importing to the other as a different name, and doing a
> >> >checksum on that server). Any ideas as to what I can do? Schema's are
> >> >Identical. I've ran optimize and repair table, to no effect. Thanks!
>
> >Is there any alternative as to how I can check a table for mirrored
> >data?
>
> Consider this: mysqldump both tables into separate files, using
> options that produce a single insert statement per record. Sort
> both files. (at this point you've probably messed up the file to
> the point of not being able to restore it, but this doesn't matter).
> Run diff(1) between the two sorted files.- Hide quoted text -
>
> - Show quoted text -

That's the problem I was hoping to avoid... We've got a T-1 between
the servers, and about 100meg of data. To download all the data would
take about 9 minutes, processing it would be quick, and updating would
be slow (about 30 minutes, as instead of syncing, if they differ, I
drop the entire table and re-install it). I wish there was an easier
(and faster) way...