Can just reading pragma schema_version corrupt the database file?
(1) By anonymous on 2022-11-12 13:28:02 [source]
Yesterday in my db 'pragma schema_version' said 3293. Today, after dropping/creating some index objects, it says 3300. The docs make it sound like just reading the value is dangerous - or am I misinterpeting?
<quote> PRAGMA schema.schema_version; PRAGMA schema.schema_version = integer ;
The schema_version pragma will get or set the value of the schema-version integer at offset 40 in the database header.
SQLite automatically increments the schema-version whenever the schema changes. As each SQL statement runs, the schema version is checked to ensure that the schema has not changed since the SQL statement was prepared. Subverting this mechanism by using "PRAGMA schema_version" may cause SQL statement to run using an obsolete schema, which can lead to incorrect answers and/or database corruption.
Warning: Misuse of this pragma can result in database corruption. </quote>
Also, does the 'schema_version' correspond to the SQLite version somehow?
Thanks
(2) By Dan Kennedy (dan) on 2022-11-12 13:59:13 in reply to 1 [link] [source]
The "schema version" is an integer field in the header that is incremented each time the database schema is modified. SQLite uses this so as to avoid having to reparse the database schema every transaction - it can reason that if the "schema version" header field has not been modified since last transaction, it can continue using its in-memory cache of the database schema. If the schema version has changed, SQLite has to discard its internal representation of the database schema and reparse the contents of the sqlite_schema table.
Safe to read the field. Not always safe to write it though.
Dan.
(3) By anonymous on 2022-11-12 14:06:08 in reply to 2 [link] [source]
Gotcha. It would be nice if the docs explicitly said 'Safe to read.'
Thanks Dan.
(4) By Richard Hipp (drh) on 2022-11-12 17:34:23 in reply to 1 [link] [source]
The following enhancements will appear in the 3.40.0 release:
The PRAGMA schema_version documentation is enhanced to try to make it clear that reading the schema_version is harmless.
Changing the schema_version is now prohibited in defensive mode.
A new prerelease snapshot has been uploaded to the download page and fiddle has been updated with the latest changes as well. Please report any problems. Remember: We expect to begin final release testing on Monday morning.
(5) By anonymous on 2022-11-12 17:55:18 in reply to 4 [link] [source]
Thumbs up! The responsiveness of you and others on this forum is nice.