I seem to have a database that has been corrupted. I'm trying to figure out
what I did wrong and how to keep it from happening again. This is with SQLite
3.3.x.
There is a simple table as follows:
CREATE TABLE Statistic
(
StatID INTEGER PRIMARY KEY,
OwnerType INTEGER NON NULL,
ItemName TEXT NON NULL,
StatName TEXT NON NULL
);
CREATE INDEX Ind_Statistic_StatName on Statistic (StatName);
When I do a SELECT * FROM Statistic, I get:
1|4|\\MACHINE1\Memory\Pages/sec|CounterValue
2|4|\\MACHINE1\Paging File(_Total)\% Usage|CounterValue
3|4|\\MACHINE1\PhysicalDisk(_Total)\Avg. Disk Read Queue Length|CounterValue
4|4|\\MACHINE1\PhysicalDisk(_Total)\Avg. Disk sec/Read|CounterValue
5|4|\\MACHINE1\PhysicalDisk(_Total)\Avg. Disk sec/Write|CounterValue
6|4|\\MACHINE1\PhysicalDisk(_Total)\Avg. Disk Write Queue Length|CounterValue
7|4|\\MACHINE1\Processor(_Total)\% Processor Time|CounterValue
8|4|\\MACHINE1\Processor(_Total)\Interrupts/sec|CounterValue
9|4|\\MACHINE2\Memory\Pages/sec|CounterValue
10|4|\\MACHINE2\Paging File(_Total)\% Usage|CounterValue
11|4|\\MACHINE2\PhysicalDisk(_Total)\Avg. Disk Read Queue Length|CounterValue
12|4|\\MACHINE2\PhysicalDisk(_Total)\Avg. Disk sec/Read|CounterValue
<snip... you get the picture...>
12|||
12|||
12|||
12|||
12|||
<snip ... many more of these bogus 12 rows>
149|||
129|||
11|||
11|||
11|||
11|||
<snip... many more of these 11 rows>
411|4|\\MACHINE3\Paging File(_Total)\% Usage|CounterValue
412|4|\\MACHINE3\PhysicalDisk(_Total)\Avg. Disk Read Queue Length|CounterValue
413|4|\\MACHINE3\PhysicalDisk(_Total)\Avg. Disk Write Queue Length|CounterValue
<snip... many more good rows>
How did I get so many duplicate primary keys??? (a proper 129 and 149 StatID
row also exist). How was the NON NULL ignored?
Further, if I do
SELECT * FROM Statistic WHERE StatID=11;
I only get one row back (the proper row), and I can't seem to select those
bogus rows.
I've tried running VACUUM, which fails with "contraint failed".
sqlite3_analyzer has these odd statistics:
Pages in the whole file (measured).... 26225
Pages in the whole file (calculated).. 26228
Pages that store data................. 26228 100.011%
Pages on the freelist (calculated).... -3 -0.0%
Size of the file in bytes............. 26854400
Bytes of user payload stored.......... 63512 0.24%
I'm _guessing_ this is a file corruption issue (trying to find out if there was
a power outage, etc). I _think_ (still verifying) that "PRAGMA synchronous =
OFF;" was set, so if it is corrupted, I'll have to accept that it was my fault.
Is there anything I can do to fix this or prevent it (other than changing
PRAGMA synchronous) in the future?
Thanks for any ideas.
Doug
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
