Error: stepping, database disk image is malformed (11)
(1) By Reinhard Nißl (rnissl) on 2022-02-14 15:59:50 [link] [source]
I've used the following code sequence for several years up to and including version 3.31.1 to purge everything from a database:
PRAGMA writable_schema=1;
delete from sqlite_master;
PRAGMA writable_schema=0;
vacuum;
After moving to version 3.37.2, the above command
delete from sqlite_master;
returns an error.
Digging through the code I see that clearDatabasePage() has changed to return SQLITE_CORRUPT_BKPT when sqlite3PagerPageRefcount(pPage->pDbPage)!=1 (it is actually 2). Formerly the test was pPage->bBusy and did not hit.
A workaround is to add a condition to the delete statement, for example:
delete from sqlite_master where 1;
Instead of a Clear instruction, this generates a loop with a Delete for each row.
I'd like to get the former behaviour back as there is a huge codebase which uses the command that way, and it's just a matter of time, when the query optimizer will strip away the simple "always true" condition.
(2) By Larry Brasfield (larrybr) on 2022-02-14 16:25:38 in reply to 1 [source]
I've used the following code sequence for several years ...
PRAGMA writable_schema=1; delete from sqlite_master ......
After moving to version 3.37.2, the above command ... returns an error.
That has never been a supported method for altering the DB schema.
That it worked was not an intended or documented aspect of the SQLite library design. It was, therefor, never something which should have been relied upon.
... Digging through the code I see ...
Having to dig through code instead of relying upon the API documentation is a sign that you are on thin ice.
A workaround is ...
Oh, look, the ice looks a little thicker here!
I'd like to get the former behaviour back ...
The way to do that is to use the older version of the library which accidentally, with inputs for which its behavior was not defined, worked in a way that did not instantly show you the error of your way(s).
it's just a matter of time, when the query optimizer will strip away the simple "always true"
Your concern is another sign of being on thin ice.
Your time would be better spent using the introspection pragmas to iterate though the tables and DROP them. You would have skated back into the demarcated area where the ice is known to bear the weight of humans able to skate, with a comfortable margin.
Another option is to delete the DB file(s) and recreate. I cannot fathom why that is not the original "solution".
(3) By Richard Hipp (drh) on 2022-02-14 16:54:31 in reply to 1 [link] [source]
What is the "huge codebase" that you speak of? How huge is it really?
(4) By Reinhard Nißl (rnissl) on 2022-02-15 11:02:14 in reply to 3 [link] [source]
5000000e-3
(5.1) By Reinhard Nißl (rnissl) on 2022-02-15 15:48:27 edited from 5.0 in reply to 2 [link] [source]
Deleting the DB file doesn't work, because the file may be in use by another client.
Despite library design, posts like this might motivate people to do it that way.
And it's just a small step to remove the where clause to purge everything.
That this change yields different instructions which won't work then is inconsistent.
(6) By Reinhard Nißl (rnissl) on 2022-02-15 14:55:12 in reply to 1 [link] [source]
I've investigated a bit further and found that refcount of page 1 is 2 because a transcation was started which caused an increment.
I've changed the test like that
sqlite3PagerPageRefcount(pPage->pDbPage)!=(pgno == 1 ? 2 : 1)
to make the command working again.
Is there a chance that this change gets accepted?
(7) By Richard Hipp (drh) on 2022-02-15 15:02:01 in reply to 6 [link] [source]
(8) By Larry Brasfield (larrybr) on 2022-02-15 15:13:21 in reply to 5.0 [link] [source]
You are correct with your contention that direct schema table alteration has been encouraged to some extent. We might argue about the extent, but that's an overly fine distinction for me, at least with respect to the validity of your point.
The warning which has been present in the PRAGMA writable_schema documentation, "Warning: misuse of this pragma can easily result in a corrupt database file." (with yellow highlight), has been there to discourage such alteration. Yet the existence of the pragma seems to suggest there are supported uses for it.
You (or others) may notice that a term in that warning, "misuse", is not defined. That should serve to discourage those not intrepid enough to go exploring the boundaries between what seems to work, what clearly does not work, and what can be counted upon to keep working.
The contour of the boundary for direct schema table changes, between what works in all schemas and what fails in some way, should not be expected to remain stable. We consider "bugs" to be deviations from documented behavior, not from "weakly implied to maybe work as loosely expected" behavior.
(9) By Reinhard Nißl (rnissl) on 2022-02-15 15:52:56 in reply to 7 [link] [source]
Thanks for the commit.
(10) By Reinhard Nißl (rnissl) on 2022-02-15 16:06:21 in reply to 8 [link] [source]
Thanks for your explanation.
In my case, it seems that there has been a supported use.
Actually, it was just a matter that two commands which should yield the same result behaved differently, i. e. the one with a where clause succeeded and the other one (the original one which had worked for years) failed.
With the simple fix provided, I'm glad that the codebase doesn't have to be touched.