Re: WC database corruption (1.7.1)

Neil Bird <neil_at_jibbyjobby.co.uk> writes:

> "*** in database main ***
> On tree page 40898 cell 60: 2nd reference to page 325
> On tree page 40898 cell 60: Child page depth differs
> On tree page 40898 cell 61: Child page depth differs"
> "rowid 14277 missing from index sqlite_autoindex_PRISTINE_1"
> "rowid 14278 missing from index sqlite_autoindex_PRISTINE_1"
> "wrong # of entries in index sqlite_autoindex_PRISTINE_1"
> "rowid 45935 missing from index I_NODES_PARENT"
> "rowid 45935 missing from index sqlite_autoindex_NODES_1"
> "rowid 45936 missing from index I_NODES_PARENT"
> "rowid 45936 missing from index sqlite_autoindex_NODES_1"
> "rowid 469 missing from index I_NODES_PARENT"
> "rowid 469 missing from index sqlite_autoindex_NODES_1"
> "rowid 470 missing from index I_NODES_PARENT"
> "rowid 470 missing from index sqlite_autoindex_NODES_1"
> "rowid 471 missing from index I_NODES_PARENT"
> "rowid 471 missing from index sqlite_autoindex_NODES_1"
> "rowid 472 missing from index I_NODES_PARENT"
> "rowid 472 missing from index sqlite_autoindex_NODES_1"
> "wrong # of entries in index I_NODES_PARENT"
> "wrong # of entries in index sqlite_autoindex_NODES_1"

Interesting! I've never seen a corrupt SQLite database before. It
seems as if the corruption is restricted to the indices so it may be
recoverable.

It may be as simple as

sqlite .svn/wc.db "reindex nodes"
sqlite .svn/wc.db "reindex pristine"

but I don't know if that will work. If it doesn't then it may be
possible to copy, drop, replace the tables. This may not work either as
the index corruption may simply be the visible effect of larger
corruption.

sqlite3 .svn/wc.db "select sql from sqlite_master where name='NODES'"
sqlite3 .svn/wc.db "select sql from sqlite_master where name='I_NODES_PARENT'"

will show you the SQL for the table and index that need to be recreated.

Make a backup copy of wc.db before going further!

Create a duplicate table NODES_COPY:

sqlite3 .svn/wc.db "CREATE TABLE NODES_COPY ( wc_id INTEGER NOT NULL REFERENCES WCROOT (id), local_relpath TEXT NOT NULL, op_depth INTEGER NOT NULL, parent_relpath TEXT, repos_id INTEGER REFERENCES REPOSITORY (id), repos_path TEXT, revision INTEGER, presence TEXT NOT NULL, moved_here INTEGER, moved_to TEXT, kind TEXT NOT NULL, properties BLOB, depth TEXT, checksum TEXT REFERENCES PRISTINE (checksum), symlink_target TEXT, changed_revision INTEGER, changed_date INTEGER, changed_author TEXT, translated_size INTEGER, last_mod_time INTEGER, dav_cache BLOB, file_external TEXT, PRIMARY KEY (wc_id, local_relpath, op_depth) )"

Copy NODES into NODES_COPY

sqlite3 .svn/wc.db "insert into NODES_COPY select * from NODES"

Drop and recreate NODES:

sqlite3 .svn/wc.db "drop table NODES"

sqlite3 .svn/wc.db "CREATE TABLE NODES ( wc_id INTEGER NOT NULL REFERENCES WCROOT (id), local_relpath TEXT NOT NULL, op_depth INTEGER NOT NULL, parent_relpath TEXT, repos_id INTEGER REFERENCES REPOSITORY (id), repos_path TEXT, revision INTEGER, presence TEXT NOT NULL, moved_here INTEGER, moved_to TEXT, kind TEXT NOT NULL, properties BLOB, depth TEXT, checksum TEXT REFERENCES PRISTINE (checksum), symlink_target TEXT, changed_revision INTEGER, changed_date INTEGER, changed_author TEXT, translated_size INTEGER, last_mod_time INTEGER, dav_cache BLOB, file_external TEXT, PRIMARY KEY (wc_id, local_relpath, op_depth) )"

sqlite3 .svn/wc.db "create index I_NODES_PARENT on NODES (wc_id, parent_relpath, op_depth)"

Copy NODES_COPY into NODES:

sqlite3 .svn/wc.db "insert into NODES select * from NODES_COPY"

Drop table NODES_COPY:

sqlite3 .svn/wc.db "drop table NODES_COPY"

Then you need to do something similar for PRISTINE, although this time
there is no extra index:

sqlite3 .svn/wc.db "select sql from sqlite_master where name='PRISTINE'"

Create PRISTINE_COPY
Copy PRISTINE into PRISTINE_COPY
Drop and recreate PRISTINE
Copy PRISTINE_COPY into PRISTINE
Drop PRISTINE_COPY

-- 
Philip