Re: [sqlite] pragma integrity_check doesn't check constraints without indices

[Ben Newberg]

To be clear, my question about all bets being off sounded more rhetorical
in my head than it came out. I'm perfectly content with the outcome, just
wanted to share an example of something I accidently learned the hard way
when messing with schemas. And thanks to you all I know more about why it
happened.

On Oct 17, 2016 5:21 PM, "Kees Nuyt"  wrote:

> On Mon, 17 Oct 2016 15:40:44 -0500, Ben Newberg
>  wrote:
>
> > But I've seen some strange things when messing with pragma
> writable_schema.
> > It appears all bets are off?
>
> Yes. that's why there's a warning:
> "Warning: misuse of this pragma can easily result in
>  a corrupt database file."
>
> When changing anything in the sqlite_master table you are
> circumventing all mechanisms SQLite has to keep the database
> consistent.
> What you did in your example is creating a table description in
> sqlite_master that doesn't match the physical table in the
> database, so yes, all bets are off.
>
> --
> Regards,
>
> Kees Nuyt
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] pragma integrity_check doesn't check constraints without indices

[R Smith]

On 2016/10/17 10:40 PM, Ben Newberg wrote:

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


Correct. Note that what you see is not "how it works", this is just one 
possible outcome, a corruption opportunity that happens to go o.k.


Imagine a train on a circuit track (perhaps a plastic-track toy train is 
the best example here). If you remove some rails in a straight section, 
and the ground is firm, the train /might/ keep going in a straight line 
and end up back on its tracks after the removed section. If later you 
replace the track, or place a new different track in the same place, the 
train may just recover its complete original operation. If you remove a 
curved section however, and/or replace with a different section, the 
train may get completely derailed.


What you have done here is messed with the tracks of a train, but not 
enough to completely derail (corrupt) it yet... and then replaced them 
regaining original form. The fact it still works is a matter of fortune 
or happenstance of the specific changes and not a contractual obligation 
of functioning - all bets are indeed off once you manually change the 
tracks.


The only reason the writable schema pragma exists is that sometimes, in 
special circumstances, you may want to make a change that either cannot 
be done through standard API, or you want to make the schema readable to 
an earlier version (perhaps even to try recover a broken database or 
such) - it is in no way a means of achieving standard functionality, 
hence carrying the warning. Usually it's safe when there's no data in 
the DB yet, but once there is a running train (data) and you manually 
adjust the tracks (schema), all data guarantees are void.


For almost any change to the DB you can fathom, there exists (or can be 
devised) some SQL script that will achieve the same without resorting to 
schema fiddling.


HTH
Ryan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] pragma integrity_check doesn't check constraints without indices

[Kees Nuyt]

On Mon, 17 Oct 2016 15:40:44 -0500, Ben Newberg
 wrote:

> But I've seen some strange things when messing with pragma writable_schema.
> It appears all bets are off?

Yes. that's why there's a warning:
"Warning: misuse of this pragma can easily result in
 a corrupt database file."

When changing anything in the sqlite_master table you are
circumventing all mechanisms SQLite has to keep the database
consistent. 
What you did in your example is creating a table description in
sqlite_master that doesn't match the physical table in the
database, so yes, all bets are off.

-- 
Regards,

Kees Nuyt
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] pragma integrity_check doesn't check constraints without indices

[David Raymond]

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

http://www.sqlite.org/pragma.html#pragma_writable_schema
"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.

http://www.sqlite.org/fileformat2.html

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 [mailto:sqlite-users-boun...@mailinglists.sqlite.org] 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 
indices

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;
x|y|z
1|a|A
2|b|B
3|c|C
sqlite> pragma writable_schema = 1;
sqlite> update sqlite_master set sql = 'create table t (x, y)' where name =
't';
sqlite> select * from t;
x|y|z
1|a|A
2|b|B
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;
x|y
1|a
2|b
3|c
sqlite> alter table t add column w;
sqlite> insert into t values (4, 'd', 'D');
sqlite> select * from t;
x|y|w
1|a|A
2|b|B
3|c|C
4|d|D
sqlite>

On Mon, Oct 17, 2016 at 4:03 AM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 17 Oct 2016, at 8:17am, Torsten Landschoff <torsten.landsch...@scale.eu>
> wrote:
>
> > So much about my attempt to report a bug. If you don't want to believe my
> > report, then don't.
>
> Sor

Re: [sqlite] pragma integrity_check doesn't check constraints without indices

[Ben Newberg]

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;
x|y|z
1|a|A
2|b|B
3|c|C
sqlite> pragma writable_schema = 1;
sqlite> update sqlite_master set sql = 'create table t (x, y)' where name =
't';
sqlite> select * from t;
x|y|z
1|a|A
2|b|B
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;
x|y
1|a
2|b
3|c
sqlite> alter table t add column w;
sqlite> insert into t values (4, 'd', 'D');
sqlite> select * from t;
x|y|w
1|a|A
2|b|B
3|c|C
4|d|D
sqlite>

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@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] pragma integrity_check doesn't check constraints without indices

[Simon Slavin]

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@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] pragma integrity_check doesn't check constraints without indices

[Simon Slavin]

On 13 Oct 2016, at 3:46pm, Torsten Landschoff  
wrote:

> while working on a database upgrade on our application I found a bug in
> SQLite. I was adding a not null constraint by updating the sqlite_master
> table and to see the check fail used pragma integrity_check to find the
> offending rows that I know are there.
> 
> However, the integrity check just passes. This in contrast to the
> documentation:
> 
>> The integrity_check pragma looks for out-of-order records, missing 
>> pages, malformed records, missing index entries, and UNIQUE and NOT 
>> NULL constraint errors.
> 
> Source: https://www.sqlite.org/pragma.html#pragma_integrity_check

The problem is that SQLite doesn't expect you to make manual changes to 
sqlite_master.  It doesn't reread the schema to execute every command.  As a 
diagnostic tool, please try closing the file and reopening it after you've 
changed sqlite_master.  I'm betting that the integrity check works after that.

It might also be possible to force re-reading of sqlite_master using the 
following command:

ANALYZE sqlite_master

I'm not clear on why this works, though, so don't take my word for it.  One 
thing I believe definitely causes rereading of the entire schema would be to 
perform any change of schema:

CREATE dummy_for_reread(t TEXT);
DROP TABLE dummy_for_reread;

Again, please test this rather than assuming it.  I've not seen it documented, 
I've only seen it in use as an undocumented hack.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users