1

I am trying to implement a simple database using PHP & sqlite on my Linux/Apache server.

I can read from it quite readily, but I cannot perform any UPDATE, DELETE or INSERT actions. The Fatal Error I get is:

General error: 5 database is locked

As a simple example:

$pdo=new PDO('sqlite:test.sqlite');
$pdo->exec("INSERT INTO menus(id,name,description) VALUES(6,'test','this is a test')");

This waits for a long time (about a minute), and then reports the above error.

I have read a lot of suggestions, many of which suggest that the database or its containing folder should be writable. They are. (Or were. I made them world writable for testing, and restored more reasonable permissions when that failed.)

I have no trouble writing to the database using other techniques such as the sqlite3 command in Linux and the SQLite manager addon in Firefox.

I would welcome any comments on how to make this work.

John Conde
  • 217,595
  • 99
  • 455
  • 496
Manngo
  • 14,066
  • 10
  • 88
  • 110
  • Just to clarify, you are able to write to this database with another program while PDO is reporting it as locked? – Brad Sep 04 '12 at 14:20
  • That’s right. I have already checked that no other process is holding the database open. Mark – Manngo Sep 04 '12 at 22:39
  • user1413856, That's bizarre! Getting into the realm of that-doesn't-happen-every-day, what version of PHP are you using? What version (if available) of the PDO driver are you using? What happens if you make a new clean database (no data, single table) and try? – Brad Sep 04 '12 at 23:42
  • PHP 5.3.6; pdo_sqlite Driver is 3.3.6. I have already tried with a new dummy database. I was able to create a new database & table using PDO. However it started to behave weirdly when I added a record. Inserting a record was OK, but updating the record took about a minute. It did, however, get through eventually. This might suggest that the size of the database has something to do with it. The real database has 4 very small tables. Mark – Manngo Sep 05 '12 at 02:16
  • Ok, what's `top` say about this while it's running? Is it blocking on IO? Do you have a failing drive? Is it CPU bound? Is this by chance on that newfangled cloud hosting where you are only paying for a few spare CPU cycles here and there? – Brad Sep 05 '12 at 02:37
  • OK, top tells me that very little CPU time is used, and I can’t see anything special while waiting for the PDO request to die off. The fact that everything else works well (I am also running MySQL and PostGreSQL databases, as well as my usual other servers including web, mail and networking), suggests that nothing is falling down. And it’s on my own server, so no to the cloud hosting question. – Manngo Sep 05 '12 at 05:52

1 Answers1

1

Please, try to give the database file a 777 permission and try again. I suspect it has something to do with permissions because you are able to modify the database using sqlite3 program.

If it fails, then try to see the answers to this question.

Community
  • 1
  • 1
Leonel Machava
  • 1,501
  • 11
  • 19
  • 1
    Tried that, with permissions 660 (no need to give execute permissions). I also tried 777 permissions on the containing directory. I’ve already had a look at the other reference, but fuser and lsof reveal no other user is holding the database open. - Mark – Manngo Sep 04 '12 at 22:34