Is pragma integrity_check safe during app working?
(1) By gguinea on 2021-07-19 15:50:03 [link] [source]
I would like to setup 'pragma integrity_check' with cron as daily database check. But I wonder if it's safe for incoming transactions since application will be running all the time. Do you have any interesting thoughts about that?
(2) By Richard Hipp (drh) on 2021-07-19 16:10:26 in reply to 1 [link] [source]
The PRAGMA integrity_check takes a read transaction. This might interfere with simultaneous write operations if you are not in WAL mode. Other than that, there shouldn't be any issues.
(3) By gguinea on 2021-07-19 17:19:21 in reply to 2 [source]
Do you know what kind of consequences can be from making write operation and pragma integrity_check? Database corruption? Wrong integrity_check output or wrong write transaction?
(4) By Richard Hipp (drh) on 2021-07-19 17:39:47 in reply to 3 [link] [source]
In rollback mode, if you try to write while PRAGMA integrity_check is running, you will get an SQLITE_BUSY error. To avoid this error, you can:
Run in WAL mode
Set "PRAGMA busy_timeout=N" to cause the writes to wait for N milliseconds before giving up and returning SQLITE_BUSY.
(5) By gguinea on 2021-07-19 18:06:27 in reply to 4 [link] [source]
I really appreciate your response! Thank you Sir.
Unfortunately, I cannot switch to WAL mode and i think, that changing busy_timeout is not the best option for app that I working on.
I see that I will be forced to run pragma integrity_check during hours when the app is not used by users or just simply turn off the app and then check db.