Reads about right. I'm writing the below step-by-step thing as much to test my 
own knowledge of what's going on as to help, so knowledgeable folks please 
correct me where I mess up.

The short version is: don't mess with writable schema unless you have no other 
choice. (Which you probably do) As Firefox has said "this may void your 
"Warning: misuse of this pragma can easily result in a corrupt database file."

So, following your specific test there...

When you update sqlite_master you're literally just changing a value in that 
table, you're not re-parsing it. It treats it just like any other table update. 
So the schema cookie in the file header isn't updated, nor are any data pages 
associated with table t. So when you do the next select * statement, your 
session sees that the schema cookie is the same as what it saw before and keeps 
the old schema in memory, no need to parse it all every single statement. The 
data pages in the file also haven't changed for table t, so everything matches 
nicely and comes out as if you hadn't done the sqlite_master change at all.

When you try to vacuum, it re-parses things and sees that there is an 
unrecoverable issue, so fails. But gracefully fails and doesn't alter the 
existing file.

The file format is such that as long as you start at the correct root page of a 
table, you don't need the schema to read the actual values. Since SQLite lets 
you put different data types into any declared field type, the data types are 
stored in the data section for every record. So you can get all the data just 
fine, the number of fields just might not jive with what you've been told to 
expect from the contents of sqlite_master. So your next select expects 2 
fields, and (I guess) stops there, even though the data pages say that there 
are 3 fields.

The alter table command also only updates sqlite_master. None of the data pages 
are updated until you start doing update or insert statements. So in your 
specific case, the add field statement just makes the number of fields in the 
schema jive with the number of fields in the data again.

When you do your insert there, the number of fields in the schema, the 
statement, and the data pages all match up, so it doesn't re-write anything 
that was already there, and thus adds in the new record just fine.

Nothing poped up as corrupt in your test except for the vacuum attempt, but 
that's just because of the ordering and what you did with the schema. Without 
trying very hard though you can mess things up real easy, especially if you 
change the rootpage field.

It's a powerful tool. But just as though you were handed an industrial circular 
saw to use by hand, read the manual before use, and practice on something you 
don't mind destroying. And most importantly: if you can do the job with the 
nice, sturdy, guarded, documented, comparatively safe table saw there that's 
still under warranty, use that instead of the monster hand held one.

Now I'm gonna go back to lurking in the corner...

-----Original Message-----
From: sqlite-users [] On 
Behalf Of Ben Newberg
Sent: Monday, October 17, 2016 4:41 PM
To: SQLite mailing list
Subject: Re: [sqlite] pragma integrity_check doesn't check constraints without 

First off, my apologies for hijacking this thread.
But I've seen some strange things when messing with pragma writable_schema.
It appears all bets are off?

example: create a table of columns (x, y, z), and fill it with values.
then, modify sqlite_master to take out column z.
let's say later on down the line, you either add a column using the
pragma writable_schema, or the ALTER TABLE.
What happens is the new column is filled with the old column's values:

SQLite version 3.14.2 2016-09-12 18:50:49
Enter ".help" for usage hints.
sqlite> .headers on
sqlite> create table t (x, y, z);
sqlite> insert into t values (1, 'a', 'A'), (2, 'b', 'B'), (3, 'c', 'C');
sqlite> select * from t;
sqlite> pragma writable_schema = 1;
sqlite> update sqlite_master set sql = 'create table t (x, y)' where name =
sqlite> select * from t;
3|c|C  -- we are still seeing column z, until we vaccum:
sqlite> vacuum;
Error: table vacuum_db.t has 2 columns but 3 values were supplied
sqlite> select * from t;
sqlite> alter table t add column w;
sqlite> insert into t values (4, 'd', 'D');
sqlite> select * from t;

On Mon, Oct 17, 2016 at 4:03 AM, Simon Slavin <> wrote:

> On 17 Oct 2016, at 8:17am, Torsten Landschoff <>
> wrote:
> > So much about my attempt to report a bug. If you don't want to believe my
> > report, then don't.
> Sorry, just to make it clear, I'm just a fellow-user of SQLite.  I'm not
> on the development team.  And I totally believe what you wrote.  One of
> more of the development team might be figuring out the problem right now.
> Simon.
> _______________________________________________
> sqlite-users mailing list
sqlite-users mailing list
sqlite-users mailing list

Reply via email to