php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Doc Bug #43942 Unexpected SQLite SQLITE_SCHEMA error
Submitted: 2008-01-26 03:31 UTC Modified: 2013-02-18 19:52 UTC
Votes:2
Avg. Score:4.0 ± 1.0
Reproduced:2 of 2 (100.0%)
Same Version:0 (0.0%)
Same OS:0 (0.0%)
From: leon at messiah dot co dot nz Assigned:
Status: Not a bug Package: Documentation problem
PHP Version: 5.2.5 OS: Linux (Ubuntu 7.10)
Private report: No CVE-ID: None
 [2008-01-26 03:31 UTC] leon at messiah dot co dot nz
Description:
------------
Using PHP 5.2.5 (SQLite 3.4.2) the PHP code below gives the SQLite error SQLITE_SCHEMA (database schema has changed).

According to the SQLite documentation:
"In SQLite version 3, an SQLITE_SCHEMA error can only occur when using the sqlite3_prepare()/sqlite3_step()/sqlite3_finalize()  API to execute SQL, not when using the sqlite3_exec()."
http://www.sqlite.org/faq.html#q15

In the following code I AM getting a SQLITE_SCHEMA error, but I am not using prepared queries.  It would therefore seem that either the version of SQLite I am using breaks the above promise, or the PHP PDO interface is using prepared queries internally.

Reproduce code:
---------------
// Print variable utility
function show($var)
{
	print "<pre>\n";
	print_r( $var );
	print "</pre>\n\n";
}

// Path of database file
$path = '/tmp/pdo-bug.db';

// Create first connection object and create table
$conn_1 = new PDO("sqlite:$path");
$conn_1->exec('CREATE TABLE test1 ( key, value );');

// Fetch list of tables using first connection
$result = $conn_1->query("SELECT name FROM sqlite_master WHERE type='table';");
show($result->fetchAll());

// Create a second PDO connection object to same database file
$conn_2 = new PDO("sqlite:$path");

// Create a second table using second connection
$conn_2->exec('CREATE TABLE test2 ( key2, value2 );');

// Fetch list of tables using first connection
// Print error information if query fails
$result = $conn_1->query("SELECT name FROM sqlite_master;");
if( $result instanceof PDOStatement )
	show( $result->fetchAll() );
else
	show( $conn_1->errorInfo() );

// Remove database file
unlink($path);

Expected result:
----------------
Array
(
    [0] => Array
        (
            [name] => test1
            [0] => test1
        )

)

Array
(
    [0] => Array
        (
            [name] => test1
            [0] => test1
        )

    [1] => Array
        (
            [name] => test2
            [0] => test2
        )

)


Actual result:
--------------
Array
(
    [0] => Array
        (
            [name] => test1
            [0] => test1
        )

)

Array
(
    [0] => HY000
    [1] => 17
    [2] => database schema has changed
)


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2008-07-30 21:18 UTC] ezyang@php.net
I'm tempted to reclassify this as a documentation problem. PDO::query() does use prepared statements internally (PDO::exec(), however, uses sqlite3_exec and should never throw the schema error). As the caller of sqlite, you're expected to check for this error and re-execute the query as necessary.

However, since PDO is responsible for the entire life of the prepared query, this would seem to be something it should transparently handle for the user. Either way, you'll have to fix it yourself.
 [2008-11-07 13:55 UTC] vrana@php.net
PDO::query() internally uses prepared statements but the documentation doesn't document PHP internals.
 [2013-02-18 14:17 UTC] chris+php dot net at aptivate dot org
Wait, what? How is this NOT a bug in PDO? Weird random database-specific 
behaviour is exactly what PDO is supposed to abstract over and hide from the 
user!
 [2013-02-18 19:52 UTC] leon at messiah dot co dot nz
Welcome to reason #56 why I left PHP for Python
 
PHP Copyright © 2001-2019 The PHP Group
All rights reserved.
Last updated: Wed Jul 17 10:01:26 2019 UTC