Opened 6 months ago

Closed 6 months ago

Last modified 6 months ago

#6776 closed defect (fixed)

Ogrinfo leaves -wal and -shm files behind when reading SQLite in WAL mode

Reported by: Jukka Rahkonen Owned by: warmerdam
Priority: normal Milestone: 2.1.3
Component: OGR_SF Version: unspecified
Severity: normal Keywords: sqlite gpkg wal
Cc:

Description

If SQLite db is in journal_mode=WAL state, reading the db file with ogrinfo leads to creation of two temporary files which are named as

basename-wal

basename-shm

Creation of the temporary files is normal, but by the SQLite documentation https://sqlite.org/tempfiles.html SQLite should remove first the WAL file and then also SHM file automatically when the last connection to the database closes cleanly. Files are not harmful and they can be deleted safely because -wal is empty. However, users can't know that and deleting a non-empty wal file leads always to data loss so we cannot recommend to delete them manually.

Also, if db is in WAL mode it cannot be opened even as read-only from a read-only directory because SQLite needs to write the -wal and -shm files. In this case ogrinfo prints an error message that does not give right information for the user

ogrinfo -ro wal_mode.gpkg
ERROR 1: Unable to execute PRAGMA foreign_key_check
ERROR 1: pragma foreign_key_check on 'wal_mode.gpkg' failed. You can disable this check by setting the OGR_GPKG_FOREIGN_KEY_CHECK configuration option to NO

Change History (5)

comment:1 Changed 6 months ago by Even Rouault

In 37121:

SQLite/GPKG: add explicit error message when trying to open a read-only WAL-enabled database (refs #6776)

comment:2 Changed 6 months ago by Even Rouault

Resolution: fixed
Status: newclosed

In 37122:

SQLite/GPKG: make sure when closing a WAL-enabled database opened in read-only mode to reopen it in read-write mode so that the -wal and -shm files are removed (fixes #6776)

comment:3 Changed 6 months ago by Even Rouault

In 37123:

Commit new test that should have gone with previous commit (refs #6776)

comment:4 Changed 6 months ago by Even Rouault

In 37124:

SQLite/GPKG: add explicit error message when trying to open a read-only WAL-enabled database, and make sure when closing a WAL-enabled database opened in read-only mode to reopen it in read-write mode so that the -wal and -shm files are removed (fixes #6776)

comment:5 Changed 6 months ago by Even Rouault

Component: defaultOGR_SF
Keywords: sqlite gpkg wal added
Milestone: 2.1.3
Note: See TracTickets for help on using tickets.