SQLite Forum

SQLITE_IOERR on `pragma optimize`

SQLITE_IOERR on `pragma optimize`

(1) By aryairani on 2021-05-26 05:42:09 [link] [source]

I'm sometimes getting SQLITE_IOERR from sqlite 3.28.0 when I run pragma optimize (with no mask) prior to closing a connection, and I'm at a loss for what to do about it.

pragma optimize(0xffff) gives me

ANALYZE "main"."causal_parent"
ANALYZE "main"."causal"
which seems reasonable, but then pragma optimize, immediately following that, crashes.

It's a new database, created by a unit test. I don't get a crash if I don't run pragma optimize.

Any suggestions?

Thanks, Arya

P.S. Fwiw, I do use PRAGMA journal_mode = WAL; after db open.

(2) By Richard Hipp (drh) on 2021-05-26 11:15:54 in reply to 1 [link] [source]

I'm confused. Is "PRAGMA Optimize" returning an SQLITE_IOERR or is it crashing? Which is it?

(3.1) By aryairani on 2021-05-26 14:05:02 edited from 3.0 in reply to 2 [source]

Sorry for the confusion; the library/language bindings for sqlite that I'm using crashes on SQLITE_IOERR.

PRAGMA optimize is returning SQLITE_IOERR afaict.

My suspicion that the journal_mode is a factor seems have borne out; I don't see get the error with PRAGMA journal_mode = delete, but I had wanted journal_mode = wal for performance reasons; more important than PRAGMA optimize, I suppose.

But I'm surprised that PRAGMA optimize would return SQLITE_IOERR at all; is that expected? I'm on macOS and using a local file system.