On 13 January 2017 at 22:59, David Raymond <david.raym...@tomtom.com> wrote:
> My view is that the general thinking of the program here is simply: "just > don't make things worse." It can't help what pragmas (ie > ignore_check_constraints, writable_schema etc) others may have turned on > for their connections, or what sort of junk was there when it arrived. ignore_check_constraints doesn't seem to affect NOT NULL constraints: sqlite> create table a(b INTEGER NOT NULL); sqlite> insert into a values(NULL); Error: NOT NULL constraint failed: a.b sqlite> pragma ignore_check_constraints = 1; sqlite> insert into a values(NULL); Error: NOT NULL constraint failed: a.b I can't see any pragmas that do? writable_schema is fair, but the advertised procedure[1] _does_ come with the warning that a mistake using it will corrupt your database. It's not entirely unreasonable to classify "using writable_schema to add a NOT NULL constraint to a column without ensuring all rows are NOT NULL" as a mistake. I also agree that calling the result corrupted is not an ideal result, but it provides benefits in the form of sanity checks elsewhere. [1] https://www.sqlite.org/lang_altertable.html#otheralter I haven't figured out any other way to get a NULL value into a NOT NULL column. The regular ALTER TABLE command refuses: sqlite> alter table a add column c INTEGER NOT NULL; Error: Cannot add a NOT NULL column with default value NULL Nor does sqlite allow them to propagate from tables affected by truncation as in my original post: sqlite> create table table2 (C INTEGER NOT NULL); sqlite> insert into table2 select a from test_table; Error: NOT NULL constraint failed: table2.C As to the truncated page it would be kind of bad for it to say "hey, > someone else corrupted 28 bytes, so I won't give you info on the other 99% > that I can read just fine." All that being said though it might be nice to > know there was a noticed issue. > I don't buy this one at all. Especially as sqlite fills pages backwards (starting from the end), so a truncated page almost certainly represents lost user data. I can't see anything in the hardware assumptions[2] to suggest a truncated page is ever a valid configuration. [2] https://www.sqlite.org/atomiccommit.html#hardware It does clarify that sqlite goes to no effort to detect bit errors and the like, but again I'm not asking for checksums or error correction codes. It also says "SQLite assumes that the data it reads is exactly the same data that it previously wrote". I'm pretty sure it never does any write that is not a multiple of the page size, so a short read due to EOF is a pretty good indication that this assumption has been violated! > Also, quietly dealing with a different number of fields found in the file > compared to what it parsed from the schema is how it handles "alter table > add column" without needing to rewrite the entire table when you do that. > That might also explain why you can see nulls for fields that got > truncated. Nope, see above. If you can provide me a legitimate way to violate a NOT NULL constraint then I'll happily concede that the presence of a NULL cell cannot be trusted as an indicator of corruption, but so far all my attempts have failed. And at this stage no I don't consider shooting yourself in the foot with writable_schema a legitimate way :P I've been testing with 3.14.2 - if it was possible/easier in previous versions to violate NOT NULL then backwards compatibility may also rule this out. -Rowan sqlite3 test.db "CREATE TABLE test_table (A INT4 NOT NULL, B INT4 NOT > NULL);" > for N in `seq 1 10` > do > sqlite3 test.db "INSERT INTO test_table (A, B) VALUES ($N, $N);" > done > > dd if=test.db of=cropped.db bs=2020 count=1 > sqlite3 cropped.db "SELECT * FROM test_table;" > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
