Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

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

This question came from our site for professional 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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.