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 busy_timeout?

migrated from serverfault.com Nov 11 '13 at 3:52

This question came from our site for system and network administrators.

  • What file system? Any VM? – CL. Nov 11 '13 at 11:11
  • Thanks for replying @CL. Yes. Ext4 on OpenVZ. Does the VM affect anything? If so, what can I do to minimize the risk of this happening (besides moving to a dedicated server)? – Alix Axel Nov 11 '13 at 11:37
  • Try disabling WAL mode. (You should then also set busy_timeout.) – CL. Nov 11 '13 at 12:24
  • @CL.: WAL is really necessary in this case. However, I'm interested in knowing your rationale. Also, please consider the following: temporarily removing the pragmas I mentioned above makes the error go away, even though the WAL mode is persistent between connections - so I figure the journal_mode is not the culprit here? – Alix Axel Nov 11 '13 at 13:41
  • @CL.: As for the busy_timeout, I've increased it to 1000 - not sure if this is an appropriate value or how it can affect other connections, but the error hasn't showed up since. – Alix Axel Nov 11 '13 at 13:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.