Verifying Data Integrity

[Michael]

I'd like to write a program to verify the consistency between various
database replicas in our environment. The rules are as follows:

- I'm given two servers, each with a database
- I don't know the schema of the database
- The databases are large (some tables with 10,000,000+ rows)

What would be the best way to perform a pretty-good-guess analysis of
whether or not these two databases were in sync?

Currently I do the following:

- Give me a list of tables. If the table lists don't match, then call the
databases inconsistent.
- Give me a row count of each table. If the row counts don't match, call
the table inconsistent

What I'd like to do is actually do something with data, but these are large
databases and I don't think it's reasonable to compare each row. What do
you guys suggest?
Michael

[steve]

I suggest you use php or other languages, and look at the database
files. In mysql, each table consists of multiple physical files
(data, index, etc.). You could try to compare the size of files, and
this should give you a good idea.

I would guess that can catch most of the inconsistency problems. If
you want to go deeper, then we get to row by row comparsion IMHO.

steve

--
http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/mySQL-Verify...ict120306.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=403043

[Michael]

"steve" <Us************ @dbForumz.com> wrote in message

I suggest you use php or other languages, and look at the database
files. In mysql, each table consists of multiple physical files
(data, index, etc.). You could try to compare the size of files, and
this should give you a good idea.

I would guess that can catch most of the inconsistency problems. If
you want to go deeper, then we get to row by row comparsion IMHO.

Hmm...thanks, Steve. I actually had a further question and wonder if anyone
here has the answer. I've tried doing the following:

- Grab the last row of table 1 (DBI, fetchrow())
- Grab the last row of table 2
- Do an MD5 hash on each (Digest::MD5, md5_base64)

I've found that for some reason, I get the same hash even though the data is
completely different. I'm hashing the output of fetchrow() rather than
parsing it first because I don't know the actual schema of the table that I
have to examine. Can someone tell me why my hashes are coming out the same?
Michael

[steve]

Michael wrote:

"steve" <Us************ @dbForumz.com> wrote in message

I suggest you use php or other languages, and look at the database files. In mysql, each table consists of multiple physical files
(data, index, etc.). You could try to compare the size of files, and this should give you a good idea.

I would guess that can catch most of the inconsistency problems. If you want to go deeper, then we get to row by row comparsion IMHO.
Hmm...thanks, Steve. I actually had a further question and wonder

if anyone here has the answer. I’ve tried doing the following:

- Grab the last row of table 1 (DBI, fetchrow())
- Grab the last row of table 2
- Do an MD5 hash on each (Digest::MD5, md5_base64)

I’ve found that for some reason, I get the same hash even though the data is completely different. I’m hashing the output of fetchrow() rather than parsing it first because I don’t know the actual schema of the table that I have to examine. Can someone tell me why my hashes are coming out the same?

Michael

Michale,
I don’t know what your problem is, but I am sure someone else can
help.

Still, I think the easiest is to do an "ls" on the db directory, and
the resulting data set has to be exactly equivalent. I would use
"ls" without the parameters that would show dates, since dates would
be different.

If the other approach does not work, this would be fairly easy to do
with Perl or PHP.

--
http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/mySQL-Verify...ict120306.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=406100

[steve]

Sorry, I meant "Michael"!

Actually my approach may not work, if mysql does some kind of file
optimization automatically where the file sizes may then be different.
Please disregard.

Your approach is a good one. Are you using Perl? Then I would go to
a Perl discussion group to get a better response.

--
http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/mySQL-Verify...ict120306.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=406101