Trying again since this didn't go through the first time... This started off as a bug report about the writable schema method recommended in https://www.sqlite.org/lang_altertable.html, but I realised the problem is a bit broader.
It seems SQLite is ignoring manual changes to sqlite_master when combined with increments to schema_version. The alter table page referenced above does not mention this, and indeed has advice (to run an integrity_check) that relies on different behaviour. Restarting SQLite ensures it has re-read the schema. I did not check the behaviour of simultaneous connections. C:\Users\Me>sqlite3 SQLite version 3.24.0 2018-06-04 19:24:41 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> CREATE TABLE Hi(ID INTEGER PRIMARY KEY); sqlite> PRAGMA schema_version; 1 sqlite> PRAGMA writable_schema=ON; sqlite> UPDATE sqlite_master SET SQL='CREATE TABLE Hi(ID INTEGER PRIMARY KEY, value INTEGER)' WHERE name='Hi' AND type='Table'; sqlite> PRAGMA writable_schema=OFF; sqlite> PRAGMA schema_version=2; sqlite> INSERT INTO Hi VALUES (1, 4); Error: table Hi has 1 columns but 2 values were supplied sqlite> PRAGMA schema_version; 2 sqlite> PRAGMA schema_version=3; sqlite> INSERT INTO Hi VALUES (1, 4); Error: table Hi has 1 columns but 2 values were supplied sqlite> The context of this is: I just tried to follow the second method (writable schema) advised on https://www.sqlite.org/lang_altertable.html to alter a column and drop a NOT NULL constraint. Due to a typo I updated the SQL in sqlite_master to CREAT TABLE... instead of CREATE TABLE. This database still passed the PRAGMA integrity_check that I ran before committing the transaction. However, when I closed the database then reopened and executed a DDL statement it got all upset and told me that my database schema is malformed. I understand that writable_schema is a "You break it you buy it" type situation, but why does the documentation recommend I run the PRAGMA integrity_check if it won't pick up something so simple and obviously erroneous as misspelling CREAT? Reproduction example (this is 3.22, but as illustrated above 3.24 will give the same results): C:\Users\Me>sqlite3 test.db SQLite version 3.22.0 2018-01-22 18:45:57 Enter ".help" for usage hints. sqlite> CREATE Table foo(ID INTEGER PRIMARY KEY); sqlite> PRAGMA schema_version; 1 sqlite> BEGIN; sqlite> PRAGMA writable_schema=ON; sqlite> UPDATE sqlite_master SET SQL = 'CREAT TABLE foo(bar INTEGER PRIMARY KEY)' WHERE type='table' and name='foo'; sqlite> PRAGMA writable_schema=OFF; sqlite> PRAGMA schema_version=2; sqlite> PRAGMA schema_version; 2 sqlite> PRAGMA integrity_check; ok sqlite> COMMIT; sqlite> .exit C:\Users\Me>sqlite3 test.db SQLite version 3.22.0 2018-01-22 18:45:57 Enter ".help" for usage hints. sqlite> CREAT TABLE bar (ID INTEGER PRIMARY KEY); Error: near "CREAT": syntax error sqlite> CREATE TABLE bar (ID INTEGER PRIMARY KEY); Error: malformed database schema (foo) sqlite> _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
