|  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
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
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()."

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';");

// 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() );
	show( $conn_1->errorInfo() );

// Remove database file

Expected result:
    [0] => Array
            [name] => test1
            [0] => test1


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

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


Actual result:
    [0] => Array
            [name] => test1
            [0] => test1


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


Add a Patch

Pull Requests

Add a Pull Request


AllCommentsChangesGit/SVN commitsRelated reports
 [2008-07-30 21:18 UTC]
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]
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 
 [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-2020 The PHP Group
All rights reserved.
Last updated: Tue Aug 11 19:01:30 2020 UTC