I have a very simple website running PHP and SQLite 3.7.9 (with PDO).
After establishing the SQLite connection I immediately execute the following queries:
PRAGMA busy_timeout=0; PRAGMA cache_size=8192; PRAGMA foreign_keys=ON; PRAGMA journal_size_limit=67110000; PRAGMA legacy_file_format=OFF; PRAGMA page_size=4096; PRAGMA recursive_triggers=ON; PRAGMA secure_delete=ON; PRAGMA synchronous=NORMAL; PRAGMA temp_store=MEMORY; PRAGMA journal_mode=WAL; PRAGMA wal_autocheckpoint=4096;
This website only has one writer and a few occasional readers, so I don't expect any concurrency problems (and I'm even using WAL). Every couple of days, I've seen this error being reported by PHP:
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 10 disk I/O error' in ... Stack trace: #0 ...: PDO->exec('PRAGMA cache_si...')
There are several things that make this error very weird to me:
- it's not a transient problem - no matter how many times I refresh the page, it won't go away
- the database file is not corrupted - the sqlite3 executable can open the database without problems
If the following pragmas are commented out, PHP stops throwing the disk I/O exception:
PRAGMA cache_size=8192; PRAGMA synchronous=NORMAL; PRAGMA journal_mode=WAL;
Then, after successfully reconnecting to the database, I'm able to reintroduce these pragmas and the code with run smoothly for days - until eventually, the same error will occur without any apparent reason. I wasn't able to reproduce this error so far, so I'm clueless about the origin of it.
I'm really curious what may be causing this problem... Any ideas?
- Ubuntu Server 12.04 LTS
- PHP 5.4.15
- SQLite 3.7.9
- Database size: ≃ 10MiB
- Transaction (write) size: ≃ 1KiB
EDIT: Might these symptoms have something to do with