Re: Can't open SQLite DB... but only when using mod_php??

Thodoris <tgol@xxxxxxxxxx> · Wed, 03 Oct 2007 20:25:36 +0300

Hi again Markus,
   This is realy confusing but since the command line execution works 
for you then the problem should be php configuration for the mod_php. I 
came up with this try to edit your php.ini and in case you use 
pdo_sqlite extension then change the order of the loaded modules to this:

|      extension=pdo.so
     extension=pdo_sqlite.so
     extension=sqlite.so

You could also define the full path of the extensions like this:
||
extension_dir="/usr/local/include/php/ext/pdo/"

instead of this:

||extension_dir = "./"

In case your apache is compiled by hand and you have updated sqlite you 
should recompile your apache against the new sqlite.

Hope this do some magic for you cause I have run out of ideas.

|

--
Thodoris

O/H Markus Wolff έγραψε:
Hi Thodoris,

I've checked on one of the three boxes now and the SQLite version used 
by both the commandline client and PDO is 3.2.8. I know the other two 
boxes have different versions, but I always created the database anew 
on each box.

I also tried chown/chmod on the parent directory, no change.

CU
 Markus

Thodoris schrieb:
Hey Markus,
You should try to "chown apache:apache" and "chmod +w" to the 
directory that includes frontend.db . And the link that I posted says:

|/*
 ** file_format==1    Version 3.0.0.
 ** file_format==2    Version 3.1.3.
 ** file_format==3    Version 3.1.4.
 **
 ** Version 3.0 can only use files with file_format==1. Version 3.1.3
 ** can read and write files with file_format==1 or file_format==2.
 ** Version 3.1.4 can read and write file formats 1, 2 and 3.
 */

Meaning that not all sqlite3 versions support all file formats. That 
is why you should check the version of sqlite.
|

--
Thodoris

O/H Markus Wolff έγραψε:
Hey there,

I've double-checked on three different machines now, and I'm always 
getting the same error. All having different versions of PHP, 
Apache, PDO and SQLite. So I figure it must be something that I'm 
doing wrong. I just can't figure out what it is - and I'm puzzled 
because I had used SQLite before (although briefly) and don't think 
I'm doing anything different than before.

Anyway, here's what I'm doing, step-by-step:

# sqlite3 frontend.db

Here I insert the following SQL script:

CREATE TABLE website (
    website_id INTEGER PRIMARY KEY,
    always_expand INTEGER
);

CREATE TABLE page (
    page_id INTEGER NOT NULL PRIMARY KEY,
    parent_id INTEGER,
    website_id INTEGER NOT NULL,
    title TEXT,
    link TEXT,
    depth INTEGER,
    visible INTEGER,
    element_id INTEGER,
    nav_path TEXT,
    protected INTEGER,
    sort_order INTEGER
);

Then I exit the client and make the PHP script:

# nano test.php

The content of the script still being that of my original message.
Then I adjust the rights:

# chown apache:apache frontend.db
# chmod 777 frontend.db

Then I execute the script on the command line:

# php test.php

No error.

Then I call the script on the website, one of the examples being:
http://www.21st.de/test.php

The script still manages to open the database and do a SELECT query, 
but throws the said exception when trying to do the DELETE statement.

These are all the steps that are involved to reproduce the error on 
three machines. No more, no less. Now, have I overlooked anything? 
Am I missing something really, really stupid? Or is it some kind of 
a bug? But certainly that could not have gone unnoticed for so long? 
(Tested on PHP versions 5.1.4, 5.2.0 and 5.2.4).

CU
 Markus

Markus Wolff - NorthClick schrieb:
Hey there,

I'm trying to open an SQLite3 database from a PHP very simple PHP
script:

$db = dirname(__FILE__).'/frontend.db';
$pdo = new PDO('sqlite:'.$db);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->query("SELECT * FROM page LIMIT 1");
echo "Deleting pages\n";           $pdo->query("DELETE FROM page");
echo "Deleting websites\n";
$pdo->query("DELETE FROM website");

The database file contains no data whatsoever, just the table
definitions (in case you were wondering, this is a stripped-down 
version
of a larger script for debugging purposes, hence the seemingly idiotic
DELETE statements that won't do any good in an empty database anyway,
but I digress...).

When executed on the command line, this works perfectly. When I 
execute
the same script via Apache and mod_php, I'm getting this exception:

PDOException: SQLSTATE[HY000]: General error: 1 SQL logic error or
missing database in /home/mwolff/webs/markus/cms/test.php on line 8

Getting experimental, I've tried to change the calls for the DELETE
statements from $pdo->query() to $pdo->exec(), just to see what 
happens.
Well, what happens is that I'm getting a different error:

PDOException: SQLSTATE[HY000]: General error: 14 unable to open 
database
file in /home/mwolff/webs/markus/cms/test.php on line 6

Argh... what can possibly be wrong here? The script works from the
commandline, with the exact same PHP version (Debian package, PHP
5.2.0-8+etch7, and we also tried upgrading to the latest Debian 
package
of 5.2.4, to no avail).

It can't be file permissions, I've even tried to set the database file
to 777... no change at all.

Does this ring a bell with anyone here?

Thanks,
 Markus