PRAGMA writable_schema and schema_version: changes are ignored on the same connection

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

PRAGMA writable_schema and schema_version: changes are ignored on the same connection

Barry Smith
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Fwd: PRAGMA writable_schema and schema_version: changes are ignored on the same connection

Barry Smith
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users