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
    
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
show 1 more comment

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

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

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.