WC database corruption (1.7.1)

[Neil Bird]

Having just done a couple of commits (after an update), I did some work
on a file then went to commit again (no 2nd. update) and TortoiseSVN
reported no changes.

A command line 'svn status .' is giving me:

svn: E200030: database disk image is malformed
svn: E200030: database disk image is malformed

Er. Can I recover from this? Do you want any diagnostics from it (the
SQLite d/b)?

--
[neil@fnx ~]# rm -f .signature
[neil@fnx ~]# ls -l .signature
ls: .signature: No such file or directory
[neil@fnx ~]# exit

[Philip Martin]

Neil Bird <ne...@jibbyjobby.co.uk> writes:

> Having just done a couple of commits (after an update), I did some
> work on a file then went to commit again (no 2nd. update) and
> TortoiseSVN reported no changes.
>
> A command line 'svn status .' is giving me:
>
> svn: E200030: database disk image is malformed
> svn: E200030: database disk image is malformed
>
> Er. Can I recover from this? Do you want any diagnostics from it
> (the SQLite d/b)?

Never seen that before. If you have the sqlite3 tool then you can try

sqlite3 .svn/wc.db "pragma integrity_check"

which may give more information.

--
Philip

[Neil Bird]

Around about 10/11/11 13:21, Philip Martin typed ...
> pragma integrity_check

I'd done that, but it doesn't mean anything to me!

"*** 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"

[Philip Martin]

Neil Bird <ne...@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

[Neil Bird]

Around about 10/11/11 17:01, Philip Martin typed ...

> It may be as simple as
> sqlite .svn/wc.db "reindex nodes"
> sqlite .svn/wc.db "reindex pristine"

Nope. “Error: database disk image is malformed”.

> 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.

These work.

> Make a backup copy of wc.db before going further!
> Create a duplicate table NODES_COPY:

OK

> Copy NODES into NODES_COPY

OK

> Drop and recreate NODES:

OK

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

Oops. “Error: database disk image is malformed”.

Which is a bit odd, since we copied NODES to NODES_COPY OK, and have
since re-created NODES!

I didn't go any further.

I should also point out that at this stage this is academic, as I'm using
a new checkout (actually, maybe naughtily, I renamed my dead WC, checked out
a clean WC to the same (old) name, then re-shuffled the names back, and
replaced the dead .svn with the fresh one. I think that was safe, as they
were based on the same rev.).

But I don't mind continuing to fiddle if it'll highlight anything. I
have kept a copy of the dead .svn just for that.

[Philip Martin]

Neil Bird <ne...@jibbyjobby.co.uk> writes:

>> Copy NODES_COPY into NODES:
>> sqlite3 .svn/wc.db "insert into NODES select * from NODES_COPY"
>
> Oops. “Error: database disk image is malformed”.
>
> Which is a bit odd, since we copied NODES to NODES_COPY OK, and have
> since re-created NODES!

Odd indeed. Some sort of lazy, copy-on-write copy perhaps? I think
that means that the corruption extends beyond the indices into the nodes
table, and that makes it very hard to recover.

--
Philip

[Andrew Robinson]

I know this is an old thread, but I see this error quite often with SVN 1.7. I am using Cornerstone 2.7.7 which uses SVN 1.7.5 internally. I get corrupted working copies about once a month which of course ruins the working copy and is very difficult to recover from in terms of wasted time. I never had issues like this with SVN 1.6 and earlier. Not sure if it is Cornerstone that is the issue, but it is bad that there are no safeguards like transactional support to ensure that the database is never corrupted.

sqlite3 .svn/wc.db "pragma integrity_check"

*** in database main ***

Page 24268: btreeInitPage() returns error code 11

On tree page 6985 cell 3: Child page depth differs

On tree page 6985 cell 4: Child page depth differs

Error: database disk image is malformed

[Stefan Sperling]

On Mon, Oct 08, 2012 at 07:53:15AM -0700, Andrew Robinson wrote:
> I know this is an old thread, but I see this error quite often with SVN
> 1.7. I am using Cornerstone 2.7.7 which uses SVN 1.7.5 internally. I get
> corrupted working copies about once a month which of course ruins the
> working copy and is very difficult to recover from in terms of wasted time.

That's unfortunate.

Can you reproduce this problem using a stock Subversion client?
Is your working copy on local disk?

> I never had issues like this with SVN 1.6 and earlier. Not sure if it is
> Cornerstone that is the issue, but it is bad that there are no safeguards
> like transactional support to ensure that the database is never corrupted.

That's not true. There are transactional safeguards in sqlite.
The details are documented at http://sqlite.org/atomiccommit.html

> sqlite3 .svn/wc.db "pragma integrity_check"
> *** in database main ***
> Page 24268: btreeInitPage() returns error code 11
> On tree page 6985 cell 3: Child page depth differs
> On tree page 6985 cell 4: Child page depth differs
> Error: database disk image is malformed

I don't remember ever seeing this error message.

I would assume it's a problem with Cornerstone. Maybe it's some patch
they're applying to Subversion. I would try talking to them before talking
to this community. We're entirely unable to support Cornerstone software
since the software is closed source.

Since you're on Mac, does this database contain paths with non-ASCII
characters? There are known problems with this where MacOS X and Linux
or Windows SVN clients are used together with such paths. This might
not be related to the problem but it is still an interesting data point.
See http://subversion.tigris.org/issues/show_bug.cgi?id=2464