On 6/16/15, Christian Nassau <christian.nassau at dionglobal.de> wrote:
> Dear group,
>
> We encountered a somewhat obscure scenario that leaves an sqlite database in
> a corrupted state (tested with sqlite 3.8.8).
Thanks for the bug report and for the reproducible test case. This
problem goes back to before version 3.5.1 (circa 2007) which is as far
back as I tried to reproduce it. So it has been in the code for a
long while. A fix is now on trunk.
If you have a database that was corrupted by this bug that you need to
recover, I believe you can do so by running the following SQL:
PRAGMA writable_schema=ON;
DELETE FROM sqlite_master WHERE name IS NULL;
>
> Our case deals with
>
> - an in-memory database "db" with an attached file database "other.db".
> - "db" starts a transaction,
> - "db" tries to create a table in the attached "other.db" using "create
> table ... as select ...", but the "select" raises an error.
> - "db" commits the transaction.
>
> As a result, the attached database is corrupted. The corruption seems to
> take place during the final commit.
>
> The following Tcl script reproduces the problem:
>
> ------------------------
> package require sqlite3
> sqlite3 db :memory:
>
> catch {file delete other.db}
>
> sqlite3 odb other.db
> odb eval {create table dummy(whatever text);}
> puts "check that other.db is ok:[odb eval {select count(*) from dummy}]"
> rename odb ""
>
> db function myerror myerror
> proc myerror args {error "deliberate error"}
>
> db eval {attach [other.db] as other}
>
> catch {
> db transaction {
> catch {
> db eval {create table other.xxx as select myerror()}
> }
> # error "--- without this error the other.db gets corrupted ---"
> }
> }
>
> sqlite3 odb2 other.db
> puts "check that other.db is ok:[odb2 eval {select count(*) from dummy}]"
> ------------------------
>
> This is the output:
> ------------------------
> check that other.db is ok:0
> malformed database schema (?)
> while executing
> "odb2 eval {select count(*) from dummy}"
> invoked from within
> "puts "check that other.db is ok:[odb2 eval {select count(*) from dummy}]""
> (file "bugreport.tcl" line 26)
> ------------------------
>
> If the commented line is activated the other.db is not corrupted.
>
> Best Regards,
> Christian
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
drh at sqlite.org
