Occasional disk I/O errors in SQLite
- by Alix Axel
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?
Environment:
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 busy_timeout?
