dbvalid gives 'Run time SQL error' on certain tables

[Former Member]

[SQL Anywhere 12.0.1.3423 64-bit]

I ran dbvalid against an offline database using this command:

"%SQLANY12%\\bin64\\dbvalid.exe" -c "uid=dba;pwd=***;dbf=mydatabase.db;eng=validate;start=dbeng12 -r -ch 500m" -o dbvalid-log.txt

When it had completed the last line of the log stated '2 errors reported' and checking through the rest of the log I could see that for 2 tables it contained messages of the form:

VALIDATE TABLE "myowner"."mytable"





SQL error (-300) -- Run time SQL error -- Validation of table "mytable" has failed

Note that there are 5 empty lines between the two messages (it doesn't quite display properly above). I don't know if it's relevant but it seems a little odd.

In my experience of earlier versions of SQL Anywhere (8,9) when dbvalid found some kind of corruption in a table the messages it produced were a little more informative and could sometimes allow the problem to be fixed by simply dropping and recreating an index that it suggested was corrupt. I don't think I've seen a 'Run time SQL' error before and it doesn't give me much to go on.

Does this simply indicate that the table is corrupt in such a way that dbvalid can't give any more details or are could it be a problem with dbvalid itself?

[johnsmirnios]

V12 uses a new and much faster algorithm for validating databases but the new method does have as much information available to it as the old algorithm did. Suppose, for example, you have two files that are supposed to be identical but you checksum them (with MD5, for example) and get two different numbers. You know they are different but you don't know where in the file they are different or which one is "right". That's similar to what the new algorithm does.

Now, it appears that SA no longer reports what index we had a problem with when you use "VALIDATE TABLE" and the checksum for a table doesn't match an index -- it just reports that the table has problems. Strictly speaking, as noted above it is impossible to know if the corruption is in the index or the table. It would be more useful to report the index name so I'll add an enhancement request to report it. Meanwhile, you could use VALIDATE INDEX on each index to find what index has a problem.

[Former Member]

Thank-you for your reply. Following your suggestion I tried executing VALIDATE INDEX against each index and also 'VALIDATE INDEX PRIMARY KEY ON myowner.mytable' but each command came back with nothing. I then tried 'VALIDATE TABLE myowner.mytable', expecting to see the same -300 error that was returned by dbvalid but again nothing was returned.

I note that the output log from the dbvalid utility always starts with the command 'VALIDATE DATABASE', even if I pass a single table on the command line. So does this suggest that the -300 error it more likely to relate to the additional checks carried out by VALIDATE DATABASE rather than VALIDATE TABLE, and if so is there any way of getting any more detail that might help with fixing the table?

Edit: probably best to ignore the above as shortly after posting I re-ran the dbvalid utility passing it a single problem table and it now returned without error even though it was failing earlier this week. As far as I know nothing has happened to the database file in that time, it has just been sitting there offline. I think I need to do some more investigation.

[VolkerBarth]

As far as I know nothing has happened to the database file in that time, it has just been sitting there offline.

Wow, I'm quite used to appreciate the self-management capabilities of SQL Anywhere - but such "self-healing via taking a break offline" is way beyond my expectations:)

[johnsmirnios]

If the VALIDATE DATABASE portion had failed, you wouldn't have seen "mytable" mentioned explicitly in the error message.

There is always the possibility that the image of the page in cache was corrupted and that the corruption was never actually on disk. Those chances are presumably much smaller with ECC RAM though. If the corruption was just in memory, restarting the database would make the problem go away.

I'm also trusting the original statement that the server was otherwise idle/offline. Ensure that there are no events defined that may have triggered & modified the table while validation was running.

[Former Member]

Thanks for the clarification of VALIDATE DATABASE.

I'm working on a copy of the database restored from backup tape, which is why it's simply sitting on disk doing nothing unless I'm using it. There are no events in the database that affect the problem tables.

On 29 January I ran the dbvalid utility with the command given in the first post and it gave -302 errors for two tables.

On 30 January I re-ran dbvalid using the same command but restricted to one of the two problem tables by adding it to the command line. This was using a restarted engine (since I am using dbvalid to start the engine, which then shuts down automatically). Again, I got a -302 error.

Today I no longer get any errors. I'm wondering if there is some intermittent problem with the disk on this server that can randomly cause the error.

[VolkerBarth]

You may consider bad RAM, as well...