Question Re: dbvalid giving different errors? in Technology Q&A

dbvalid giving different errors?

former_SQLA_member1694882 — Mon, 07 Jan 2013 23:42:11 GMT

SQL Anywhere 11.0.1.2837 (Windows 2008 R1 64bit)

We run dbvalid on a backup of a database. The database has two dbspaces, total size is 26GB.

Backup is performed using:

"%SQLANY11%\\bin64\\dbbackup.exe" -k recover -s -c "links=tcpip;dsn=dbbackup" E:\\backups\\db -y -r

Database validation is performed using:

"%SQLANY11%\\bin64\\dbvalid.exe" -c "dbf=E:\\backups\\db\\database.db;uid=validator;pwd=validator;dbs=-ds E:\\backups\\db"

A recent backup that was validated raised some errors like so:

Database validation failed for page 0046df32 of database file "E:\\backups\\db\\databaseLogs.db"
Database validation failed for page 0046df33 of database file "E:\\backups\\db\\databaseLogs.db"

When running the exact same dbvalid command a second time on the exact same database files, it still produced errors but for different pages. Like so:

Database validation failed for page 0046e141 of database file "E:\\backups\\db\\databaseLogs.db"
Database validation failed for page 0046e142 of database file "E:\\backups\\db\\databaseLogs.db"

But then also produced the following:

VALIDATE TABLE "Database"."ExampleTable"
Primary key for "ExampleTable" has invalid or duplicate index entries

Does that sound correct? I would've assumed that dbvalid would've returned the exact same errors in both runs?

(note - I've changed db names, uids, pwds and table names to protect the innocent)

Re: dbvalid giving different errors?

VolkerBarth — Tue, 08 Jan 2013 06:59:15 GMT

Some questions:

I do assume the database is just started by DBVALID and is used exclusively, so that influences by other connections are excluded, right?
Is there a "DBF=" missing at the start of your connection string?
What does running "call sa_validate()" via ISQL against the backup tell? Does it also reveal that behaviour?
Is that the whole output of DBVALID in both cases? - AFAIK, at least with v12, validation may use several internal connections running concurrently, which might result in a different order of checks for different runs, but I would assume that this should still result in the same diagnostic messages. (Note: I'm wild-guessing here...)

Besides that: no, I don't have a clue. Seems puzzling.

Re: dbvalid giving different errors?

former_SQLA_member1694882 — Tue, 08 Jan 2013 15:15:01 GMT

@Volker:

Yes that is correct.
Yes was missing DBF - corrected.
Don't know yet - as this is on the production box, we're busy copying a backup down to a lower region to start up (we don't want to impact on production peformance too much).
Its not the complete output, however when the outputs are compared in beyondcompare the only differences are the errors (i.e. all other messages are the same and in the same order).

Re: dbvalid giving different errors?

Breck_Carter — Tue, 08 Jan 2013 15:53:27 GMT

Check the RAM... bad RAM can masquerade as bad data, and at different times different good data can be loaded into the same bad RAM, thus giving the impression that the errors are nomadic (moving from place to place).

If you suspect bad RAM, stop using the suspect computer altogether... bogus errors can turn into real errors if the data is written back to disk.

Re: dbvalid giving different errors?

former_SQLA_member1694882 — Wed, 09 Jan 2013 20:24:20 GMT

After quite some time, we finally managed to figure out the issue.

Due to the infrastructure the customer has, dbvalid has to be run on the same Machine as the production database.

Let me expand (again, names and file locations have been changed to protect the innocent):

Firstly this is how the Production database is set up (I've removed some cache control and logging parameters):

-xTCPIP(Port=2638)
-nDatabase
😧\\Databases\\db\\database.db
-nDatabase

We now go ahead and run the dbvalid command on the same Machine using:

"%SQLANY11%\\bin64\\dbvalid.exe" -c "dbf=E:\\backups\\db\\database.db;uid=validator;pwd=validator;dbs=-ds E:\\backups\\db"

Now even though we had specified that dbvalid should be run against E:backupsdbdatabase.db (DBF=E:backupsdbdatabase.db) dbvalid was connecting to the Running Production Database and not the backup. This is due to SQL Anywhere determining that the Server Name and Database Name for the dbvalid should be "Database" and then finding that there is already a Server and Database running with that same name, thus causing it to connect to that Server and Database combination instead of starting a new Database (or throwing an error).

Thus as this was a running database with CRUD operations being performed, dbvalid would end up throwing errors for invalid pages even though in reality there wasn't.

So after all that, we've now changed the dbvalid command to be:

"%SQLANY11%\\bin64\\dbvalid.exe" -c "START=dbsrv11 -x NONE -xd -ch 1g;DBF=E:\\backups\\db\\database.db;uid=validator;pwd=validator;ServerName=validatedb;dbs=-r -ds E:\\backups\\db"

Lesson learnt - Read the documentation with a fine toothcomb and then double-check everything.

Update - I've created a suggestion for SQL Anywhere to try and solve or at least help the user discover this problem.

Re: dbvalid giving different errors?

VolkerBarth — Thu, 10 Jan 2013 04:08:22 GMT

Would you share with us how the following could happen?

even though we had specified that dbvalid should be run against E:backupsdbdatabase.db (DBF=E:backupsdbdatabase.db) dbvalid was connecting to the Running Production Database and not the backup

I'm asking since this kind of "lesson learnt" is usually very helpful for others - confine the reactions on my (possible similar) FAQ where I missed an ENG parameter in a particular call...

Re: dbvalid giving different errors?

reimer_pods — Thu, 10 Jan 2013 04:58:41 GMT

I support Volker's suggestion. My 2 cents just in case ...

Presumably the DBN parameter was not specified, so the engine chose the already running database with the same (file) name. I've had to learn that the hard way, so now I either specify a different database name or use another engine to start the backup database.

Re: dbvalid giving different errors?

former_SQLA_member1694882 — Thu, 10 Jan 2013 19:20:06 GMT

@Volker and @Reimer - expanded answer to hopefully answer your questions.

Re: dbvalid giving different errors?

VolkerBarth — Fri, 11 Jan 2013 03:25:39 GMT

Ah, yes, what a subtle pitfall:) - And I second your suggestion to handle DBF differently - though I'd suggest to ask for that in a separate question...