6Answers

SQLite/PHP read-only?

17.3k Views
  • 6
name

A PHP Error was encountered

Severity: Notice

Message: Undefined index: userid

Filename: views/question.php

Line Number: 191

Backtrace:

File: /home/prodcxja/public_html/questions/application/views/question.php
Line: 191
Function: _error_handler

File: /home/prodcxja/public_html/questions/application/controllers/Questions.php
Line: 433
Function: view

File: /home/prodcxja/public_html/questions/index.php
Line: 315
Function: require_once

name Punditsdkoslkdosdkoskdo

SQLite/PHP read-only?

I've been trying to use SQLite with the PDO wrapper in PHP with mixed success. I can read from the database fine, but none of my updates are being committed to the database when I view the page in the browser. Curiously, running the script from my shell does update the database. I suspected file permissions as the culprit, but even with the database providing full access (chmod 777) the problem persists. Should I try changing the file owner? If so, what to?

By the way, my machine is the standard Mac OS X Leopard install with PHP activated.

@Tom Martin

Thank you for your reply. I just ran your code and it looks like PHP runs as user _www. I then tried chowning the database to be owned by _www, but that didn't work either.

I should also note that PDO's errorInfo function doesn't indicate an error took place. Could this be a setting with PDO somehow opening the database for read-only? I've heard that SQLite performs write locks on the entire file. Is it possible that the database is locked by something else preventing the write?

I've decided to include the code in question. This is going to be more or less a port of Grant's script to PHP. So far it's just the Questions section:

<?php

$db = new PDO('sqlite:test.db');

$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, "https://stackoverflow.com/users/658/kyle");
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_COOKIE, "shhsecret=1293706652");
$page = curl_exec($ch);

preg_match('/summarycount">.*?([,\d]+)<\/div>.*?Reputation/s', $page, $rep);
$rep = preg_replace("/,/", "", $rep[1]);

preg_match('/iv class="summarycount".{10,60} (\d+)<\/d.{10,140}Badges/s', $page, $badge);
$badge = $badge[1];

$qreg = '/question-summary narrow.*?vote-count-post"><strong.*?>(-?\d*).*?\/questions\/(\d*).*?>(.*?)<\/a>/s';
preg_match_all($qreg, $page, $questions, PREG_SET_ORDER);

$areg = '/(answer-summary"><a href="\/questions\/(\d*).*?votes.*?>(-?\d+).*?href.*?>(.*?)<.a)/s';
preg_match_all($areg, $page, $answers, PREG_SET_ORDER);

echo "<h3>Questions:</h3>\n";
echo "<table cellpadding=\"3\">\n";

foreach ($questions as $q)
{
    $query = 'SELECT count(id), votes FROM Questions WHERE id = '.$q[2].' AND type=0;';
    $dbitem = $db->query($query)->fetch(PDO::FETCH_ASSOC);
    if ($dbitem['count(id)'] > 0)
    {
        $lastQ = $q[1] - $dbitem['votes'];
        if ($lastQ == 0)
        {
            $lastQ = "";
        }
        $query = "UPDATE Questions SET votes = '$q[1]' WHERE id = '$q[2]'";
        $db->exec($query);
    }
    else
    {
        $query = "INSERT INTO Questions VALUES('$q[3]', '$q[1]', 0, '$q[2]')";
        echo "$query\n";
        $db->exec($query);
        $lastQ = "(NEW)";
    }
    echo "<tr><td>$lastQ</td><td align=\"right\">$q[1]</td><td>$q[3]</td></tr>\n";
}

echo "</table>";

?>
      • 2
    • Sorry man, I can't help you any further. Still sounds like a permissions issue though, if you can get it working from the shell.
      • 1
    • Thanks anyways. It seems like a permissions thing to me too, but I don't see how that's possible here.
      • 2
    • Tried changing $db = new PDO('sqlite:test.db'); to a full path? Like.. $db = new PDO('sqlite:/tmp/test.db'); ?

Kyle, in order for PDO/Sqlite to work you need write permission to directory where your database resides.

Also, I see you perform multiple selects in loop. This may be ok if you are building something small and not heavy loaded. Otherwise I'd suggest building single query that returns multiple rows and process them in separate loop.

  • 11
Reply Report
      • 2
    • You fixed it! Thank you! The script has been more or less a line-by-line copy of Grant's script, which performs its SQL statements in this manner. I am, however, looking into doing an O(1) SELECT and queueing the UPDATES and executing them at the end.
    • I would just like to express my thanks, I would have never guessed this to be the answer. I'm using SQLite3 (without PDO) and this helped me a lot.

For those who have encountered read-only issues with SQLite on OS X:

1) Determine the Apache httpd user and group the user belongs to:

grep "^User" /private/etc/apache2/httpd.conf
groups _www

2) Create a subdirectory in /Library/WebServer/Documents for your database(s) and change the group to the httpd's group:

sudo chgrp _www /Library/WebServer/Documents/db

A less secure option is to open permissions on /Library/WebServer/Documents:

sudo chmod a+w /Library/WebServer/Documents

  • 1
Reply Report

@Tom Depends on how the hosting is setup, If the server runs PHP as an Apache Module then its likely that it is 'nobody' (usually whatever user apache is setup as). But if PHP is setup as cgi (such as fast-cgi) and the server runs SuExec then php runs as the same user who owns the files.

Eitherway the folder that will contain the database must be writable by the script, either by being the same user, or by having write permission set to the php user.

@Michal That aside, one could use beginTransaction(); perform all the actions needed then comit(); to actually comit them.

  • 0
Reply Report
    • PS: As I recall the 'standard' apache/php installation that comes with OSX even up to 10.5 (desktop, not server) is only PHP4, you actually have to recompile PHP if you want the benefit of PHP5 (and on that note recompile Apache to get more of the features that commonly come with a Apache+PHP+MySQL setup).

Well, I had the same problem now and figured it out by a mistake: just put every inserting piece of SQL instruction inside a try...catch block that it goes. It makes you do it right way otherwise it doesn't work. Well, it works now. Good luck for anyone else with this problem(as I used this thread myself to try to solve my problem).

  • 0
Reply Report

Warm tip !!!

This article is reproduced from Stack Exchange / Stack Overflow, please click

Trending Tags

Related Questions