error: database is locked

[JosefRypacek]

Issue in Czech language. Sorry.

Je to asi tyden, co jsem na webu rozjel MultipleFileUpload. Dnes mi přišla zajímavá chybovka. Stalo se to na stránce, která nemá s uploadem vůbec nic společného. Poslední upload minimálně několik hodin před chybou. Podle laděnky jsem zjistil, že jde o čištění cache.

[2014-09-06 08-37-04] PHP Warning: SQLite3::query(): Unable to prepare statement: 5, database is locked in .../nette/vendor/others/MultipleFileUpload/Model/SQLite3/Queues.php:81

[2014-09-06 08-37-04] Nette\InvalidStateException: Can't execute query: ' SELECT queueID FROM files GROUP BY queueID '. error: database is locked in .../nette/vendor/others/MultipleFileUpload/Model/SQLite3/Queues.php:83

jkuchar:
Zamky tam jsou treba kvuli atomicite. Nastava tato chyba pravidelne nebo se to stalo jen jednou a pri pistim requestu uz to opet funguje normalne?

• Stalo se to jen jednou, pak uz web běžel normálně.

[jkuchar]

Solution: driver should wait for unlocking database on connection open? Or on query exec? Just need more info. Please upload full Debugger backtrace or full Nette\Debugger file. Thanks!

[JosefRypacek]

http://josefrypacek.cz/mfu.html

[JosefRypacek]

Second error: http://josefrypacek.cz/mfu2.html

[2014-09-09 08-23-36] PHP Warning: SQLite3::query(): Unable to execute statement: database is locked in /var/www/vets.cz/subdomeny/www/vpm/nette/vendor/others/MultipleFileUpload/Model/SQLite3/Queues.php:81 @ http://www.vets.cz/vpm/mista/obec/4405-pezinok/?vp-page=/etc/passwd%00

[2014-09-09 08-23-36] Nette\InvalidStateException: Can't execute query: 'VACUUM'. error: database is locked in /var/www/vets.cz/subdomeny/www/vpm/nette/vendor/others/MultipleFileUpload/Model/SQLite3/Queues.php:83 @ http://www.vets.cz/vpm/mista/obec/4405-pezinok/?vp-page=/etc/passwd%00 @@ exception-2014-09-09-08-23-36-d2d938d62f56a6b67a28b1cf066aaf3e.html

I will log time of uploads by MFU and I enabled cleaning cache every request (no rand(1, 100) < 5)

//EDIT:
I was still receiving errors, so I turn off cleaning cache. Any idea for solution / debug?

[jkuchar]

related: http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked

[lukyrys]

I have same problem.. Randomly i get Warning: SQLite3::query(): Unable to prepare statement: 5, database is locked web/vendor/others/MultipleFileUpload/Model/SQLite3/Queues.php:81

[jkuchar]

I use SQLite only in dev-environment and I recommend to use Dibi driver for production.

I've done some searching and I've found (probably also your problem):

http://beets.radbox.org/blog/sqlite-nightmare.html

Digging through the SQLite source code, I looked for places where it could sleep in whole-second increments. I found sqliteDefaultBusyCallback, the function that gets called when SQLite tries to acquire a lock but finds that it’s held by a different thread. In ordinary circumstances, that function uses a simple backoff algorithm to wait a few milliseconds before trying again. But that reasonable behavior is wrapped in a preprocessor conditional like #if HAVE_USLEEP and, if SQLite doesn’t think the system can sleep in millisecond intervals, it sleeps for a whole second each time.

So this was why some users saw this horrible behavior but I never did: all my systems have SQLite compiled with HAVE_USLEEP=1. Disassembling SQLite on my machine and the affected user’s confirmed the difference. Even though usleep is so old that it was obsoleted by nanosleep in 2001, the user’s SQLite had somehow been compiled assuming it did not exist.

The mystery was solved. And while one solution would be to berate the world’s software packagers into compiling SQLite with HAVE_USLEEP=1, we needed a nearer-term solution.

If this is correct there the issue is in your distribution of SQLite not in MFU. Please could you tell me what is your platform, version of related libraries and sqlite? And could you try to search if your SQLite was compiled with HAVE_USLEEP=1 Thanks!

[jkuchar]

could you please confirm that is is that issue with sleep?

[JosefRypacek]

Sorry, but i don't have time to do on this problem, so i commented cache cleaning in MFU...
// self::getQueuesModel()->cleanup();

on server is php5-sqlite 5.5.15-1

[iprodev]

I was getting "database locked" all the time until I found out some features of sqlite3 must be set by using SQL special instructions (i.e. using PRAGMA keyword). For instance, what apparently solved my problem with "database locked" was to set journal_mode to 'wal' (it is defaulting to 'delete', as stated here: https://www.sqlite.org/wal.html (see Activating And Configuring WAL Mode)).

So basically what I had to do was creating a connection to the database and setting journal_mode with the SQL statement. Example:

<?php
    $db = new SQLite3('/my/sqlite/file.sqlite3');
    $db->busyTimeout(5000);
    // WAL mode has better control over concurrency.
    // Source: https://www.sqlite.org/wal.html
    $db->exec('PRAGMA journal_mode = wal;');
?>

Hope that helps.

[jkuchar]

Sounds like a right way to go. Would you mind sending pull-request?