On 13 Oct 2016, at 3:46pm, Torsten Landschoff <torsten.landsch...@scale.eu> 

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

sqlite-users mailing list

Reply via email to