Corrupt table

[Former Member]

Hi,

We have a log table that is notoriously getting corrupt.

The usage is to log all web requests, and in an batch job aggregate those requests and delete the aggregated rows.

Approx rows pr day is 400000 (during holliday...).

The table is defined as:

CREATE TABLE wisweb.DBA.Log (

  Id int NOT NULL,

  Time timestamp NULL,

  Servlet char(100) NULL,

  Params text NULL,

  Type int NOT NULL,

  TableName char(50) NULL,

  IdFieldName char(50) NULL,

  IdFieldValue int NULL,

  Referer text NULL,

  SearchString char(100) NULL,

  RecordCount int NOT NULL,

  Url text NULL,

  TableId int NOT NULL,

  ServletId int NOT NULL,

  IP varchar(50) NULL,

  UserAgent varchar(200) NULL

);

-- Creating indexes

CREATE INDEX "Time" ON Log ( "Time" ASC );

CREATE INDEX "Type" ON Log ( "Type" ASC );

CREATE INDEX "IdFieldValue" ON Log ( "IdFieldValue" ASC );

CREATE INDEX "RecordCount" ON Log ( "RecordCount" ASC );

CREATE INDEX "TableId" ON Log ( "TableId" ASC );

CREATE INDEX "ServletId" ON Log ( "ServletId" ASC );

CREATE INDEX "IdFieldName" ON Log ( "IdFieldName" ASC );

And when I validate the table I get:

Validate table log;

ERROR: Row count mismatch between table "Log" and index "Time"

SQL Anywhere Error -300: Run time SQL error -- Validation of table "Log" has failed

And the corresponding:

Validate index "Time" on Log;

Fails with the same error:(

I can rebuild the Time index without problem, but it still will not validate.

I can even drop the index and recreate it and it still fails validation!

And if I validate the table without the Time index, it fails on the IdFieldValue index.

Last time I had to rename table, create a new one and copy data over to the new table. But I only stayed uncorrupt for about two weeks:(

We are running the version:

dbsrv12 GA 12 0 1 3967 linux 2013/09/04 15:54:03 posix 64 production

Best regards

Ove Halseth

[JasonHinsperger · Accepted solution]

Are you running the validation while the database server is in use (ie. while data is being modified in the database)? If so, this can cause the types of errors you are seeing.  Index/table validation should be done when nobody is accessing the database.

If not, you should probably apply the latest service pack/ebf for version 12.  There are a few that can cause database corruption.  The service pack/ebf contains a readme detailing all issues that have been fixed so you can review it for more details. 

--Jason

[Former Member · Accepted solution]

Ahh, I did not notice the Caution notice in the documentation:(

Then it makes sense that the table validates if we drop all indexes.

We found a bug in our aggregation routine that stopped the deletion of aggregated rows.

So when the number of rows grew, we tried to validate the table. And when that failed, we expected that to be the problem...

Thanks for the guidance:)

Ove

PS: I don't know why I can't mark your answer as correct...

[Former Member]

If I drop all indexes the table validates ok, but if I create one index, it fails again:(

Ove   

[Former Member]

Unload the table, remove the duplicate entries and load the data back into the database, then you should be able to recreate the indexes.