php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

Bug #47145 Unable to close SQLite3 database due to unfinalised statements
Submitted: 2009-01-19 09:46 UTC Modified: 2009-01-20 00:27 UTC
From: pcdinh at gmail dot com Assigned: scottmac (profile)
Status: Closed Package: SQLite related
PHP Version: 5.3.0alpha3 OS: Windows XP
Private report: No CVE-ID: None
View Add Comment Developer Edit
 [2009-01-19 09:46 UTC] pcdinh at gmail dot com 
Description:
------------
I try to use prepared statement to insert a batch of rows into a SQLite3 database. Also, I have created a constraint to make sure that 
I do not insert duplicate rows. When the constraint is violated, SQLite3 class does not allow me to close the database.

The scenario is as follows:

1. I write a script to iterate through a directory and add found files into a SQLite3 empty database. The round 1 is OK because there is no violated constraint.

2. I run it again with the same directory. Now the database is not empty anymore. Lot of warnings are generated as below:

Warning: SQLite3Stmt::execute() [sqlite3stmt.execute]: Unable to execute statement: constraint failed in D:\wvbsrc\repos\pone\trunk\incubator\Pone\Pone_PluginManager.php on line 261
Error: constraint failed
Warning: SQLite3Stmt::reset() [sqlite3stmt.reset]: Unable to reset statement: column plugin_class is not unique in D:\wvbsrc\repos\pone\trunk\incubator\Pone\Pone_PluginManager.php on line 275

Warning: SQLite3Stmt::execute() [sqlite3stmt.execute]: Unable to execute statement: constraint failed in D:\wvbsrc\repos\pone\trunk\incubator\Pone\Pone_PluginManager.php on line 261
Error: constraint failed
Warning: SQLite3Stmt::reset() [sqlite3stmt.reset]: Unable to reset statement: column plugin_class is not unique in D:\wvbsrc\repos\pone\trunk\incubator\Pone\Pone_PluginManager.php on line 275

Warning: SQLite3::close() [sqlite3.close]: Unable to close database: 5, Unable to close due to unfinalised statements in D:\wvbsrc\repos\pone\trunk\incubator\Pone\Pone_PluginManager.php on line 281
D:\wvbsrc\repos\pone\trunk\tests\Plugin/plugins/plugins.db

Reproduce code:
---------------
    protected function _buildDatabase($path)
    {
        $files = new DirectoryIterator($path);
        // By default, open uses SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE.
        $db = new SQLite3($this->_databasePath);

        $sql = 'CREATE TABLE IF NOT EXISTS pone_plugins (
                  plugin_id INTEGER PRIMARY KEY AUTOINCREMENT,
                  plugin_class TEXT,
                  plugin_version INTEGER,
                  plugin_title TEXT,
                  plugin_desc TEXT,
                  plugin_author TEXT,
                  plugin_hooks TEXT,
                  plugin_url TEXT,
                  plugin_deps TEXT,
                  plugin_status INTEGER
                )';
        $db->exec($sql);

        $sql = 'CREATE UNIQUE INDEX IF NOT EXISTS pone_plugin_idx ON pone_plugins (plugin_class)';
        $db->exec($sql);

        $sql = 'INSERT INTO pone_plugins (
                  plugin_class, plugin_version, plugin_title, plugin_desc,
                  plugin_author, plugin_hooks, plugin_url, plugin_deps, plugin_status
                ) VALUES (
                  :class, :version, :title, :desc,
                  :author, :hooks, :url, :deps, :status
                )';

        $stmt = $db->prepare($sql);

        foreach ($files as $file)
        {
            if (!$file->isDot() && !$file->isDir())
            {
                /* @var $file DirectoryIterator */
                $path     = $file->getPathname();
                $dotPos   = strpos($path, '.php');

                if (false === $dotPos)
                {
                    continue;
                }

                include_once $path;
                $class  = str_replace('.php', '', $file->getFilename());
                $plugin = new $class();
                /* @var $plugin Pone_Pluggable */
                $info   = $plugin->getInfo();
                /* @var $plugin Pone_PluginInfo */

                /* @var $stmt SQLite3Stmt */
                $stmt->bindValue(':class',   $class, SQLITE3_TEXT);
                $stmt->bindValue(':version', (int) $info->version, SQLITE3_INTEGER);
                $stmt->bindValue(':title',   $info->title, SQLITE3_TEXT);
                $stmt->bindValue(':desc',    $info->desc, SQLITE3_TEXT);
                $stmt->bindValue(':author',  $info->author, SQLITE3_TEXT);
                $stmt->bindValue(':hooks',   serialize($info->hooks), SQLITE3_TEXT);
                $stmt->bindValue(':url',     $info->url, SQLITE3_TEXT);
                $stmt->bindValue(':deps',    serialize($info->depends), SQLITE3_TEXT);
                $stmt->bindValue(':status',  3, SQLITE3_INTEGER);

                $rs = $stmt->execute();

                if (false != $rs)
                {
                    /* @var $rs SQLite3Result */
                    $rs->finalize();
                }
                else
                {
                    echo 'Error: '. $db->lastErrorMsg();
                }

                // Resets the prepared statement to its state prior to execution.
                // All bindings remain intact after reset.
                $stmt->reset();
                $stmt->clear();
            }
        }

        $stmt->close();
        $db->close();
    }

Expected result:
----------------
I believe that the error happens to a single query (prepared statement) will be cleared out with
$stmt->reset();
$stmt->clear();

Therefore, there will be no problem with 

$stmt->close();
$db->close();

No warning should be emited and database should be closed as normal.

Actual result:
--------------
Lot of warnings are generated as below:

Warning: SQLite3Stmt::execute() [sqlite3stmt.execute]: Unable to execute statement: constraint failed in D:\wvbsrc\repos\pone\trunk\incubator\Pone\Pone_PluginManager.php on line 261
Error: constraint failed
Warning: SQLite3Stmt::reset() [sqlite3stmt.reset]: Unable to reset statement: column plugin_class is not unique in D:\wvbsrc\repos\pone\trunk\incubator\Pone\Pone_PluginManager.php on line 275

Warning: SQLite3Stmt::execute() [sqlite3stmt.execute]: Unable to execute statement: constraint failed in D:\wvbsrc\repos\pone\trunk\incubator\Pone\Pone_PluginManager.php on line 261
Error: constraint failed
Warning: SQLite3Stmt::reset() [sqlite3stmt.reset]: Unable to reset statement: column plugin_class is not unique in D:\wvbsrc\repos\pone\trunk\incubator\Pone\Pone_PluginManager.php on line 275

Warning: SQLite3::close() [sqlite3.close]: Unable to close database: 5, Unable to close due to unfinalised statements in D:\wvbsrc\repos\pone\trunk\incubator\Pone\Pone_PluginManager.php on line 281
D:\wvbsrc\repos\pone\trunk\tests\Plugin/plugins/plugins.db

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2009-01-19 10:00 UTC] scottmac@php.net 
This comes from the C level API.

http://sqlite.org/c3ref/reset.html

If the most recent call to sqlite3_step(S) for the prepared statement S indicated an error, then sqlite3_reset(S) returns an appropriate error code.

You can't call $stmt->reset() on a failed statement only $stmt->clear();
 [2009-01-19 10:08 UTC] pcdinh at gmail dot com 
I have chosen to keep $stmt->clear() only already but the warnings are so confusing and I can not close database as well.

Warning: SQLite3Stmt::execute() [sqlite3stmt.execute]: Unable to execute statement: constraint failed in D:\wvbsrc\repos\pone\trunk\incubator\Pone\Pone_PluginManager.php on line 261
Error: constraint failed
Warning: SQLite3Stmt::execute() [sqlite3stmt.execute]: Unable to execute statement: library routine called out of sequence in D:\wvbsrc\repos\pone\trunk\incubator\Pone\Pone_PluginManager.php on line 261
Error: library routine called out of sequence
Warning: SQLite3::close() [sqlite3.close]: Unable to close database: 5, Unable to close due to unfinalised statements in D:\wvbsrc\repos\pone\trunk\incubator\Pone\Pone_PluginManager.php on line 282
D:\wvbsrc\repos\pone\trunk\tests\Plugin/plugins/plugins.db

I have read your link http://sqlite.org/c3ref/reset.html and it offers no clue to understand why database connection can not be closed

So I guess that SQLite3 does not allow me to execute a prepared statement in a loop. Prepared statement is designed for that usecase, isn't it?
 [2009-01-19 11:28 UTC] scottmac@php.net 
Could you give me a reduced testcase that isn't working for you? Ideally one that I can just paste into a file and test.
 [2009-01-19 14:39 UTC] pcdinh at gmail dot com 
Hi Scott,

Here is the test script:

<?php

// By default, open uses SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE.
$db  = new SQLite3('./test.db');
$sql = 'CREATE TABLE IF NOT EXISTS pone_plugins (
          plugin_id INTEGER PRIMARY KEY AUTOINCREMENT,
          plugin_class TEXT,
          plugin_version INTEGER,
          plugin_title TEXT,
          plugin_desc TEXT,
          plugin_author TEXT,
          plugin_hooks TEXT,
          plugin_url TEXT,
          plugin_deps TEXT,
          plugin_status INTEGER
        )';
$db->exec($sql);

$sql = 'CREATE UNIQUE INDEX IF NOT EXISTS pone_plugin_idx ON pone_plugins (plugin_class)';
$db->exec($sql);

$sql = 'INSERT INTO pone_plugins (
          plugin_class, plugin_version, plugin_title, plugin_desc,
          plugin_author, plugin_hooks, plugin_url, plugin_deps, plugin_status
        ) VALUES (
          :class, :version, :title, :desc,
          :author, :hooks, :url, :deps, :status
        )';

$stmt = $db->prepare($sql);

$files = array(
array(
'class'   => 'class1',
'version' => 1,
'title'   => 'Sample 1',
'desc'    => 'Sample description 1',
'url'     => 'Sample url 1',
),
array(
'class'   => 'class2',
'version' => 2,
'title'   => 'Sample 2',
'desc'    => 'Sample description 2',
'url'     => 'Sample url 2',
),
);
foreach ($files as $file)
{
    /* @var $stmt SQLite3Stmt */
    $stmt->bindValue(':class',   $file['class'], SQLITE3_TEXT);
    /* @var $plugin Pone_PluginInfo */
    $stmt->bindValue(':version', (int) $file['version'], SQLITE3_INTEGER);
    $stmt->bindValue(':title',   $file['title'], SQLITE3_TEXT);
    $stmt->bindValue(':desc',    $file['desc'], SQLITE3_TEXT);
    $stmt->bindValue(':author',  $file['class'], SQLITE3_TEXT);
    $stmt->bindValue(':hooks',   $file['class'], SQLITE3_TEXT);
    $stmt->bindValue(':url',     $file['url'], SQLITE3_TEXT);
    $stmt->bindValue(':deps',    $file['class'], SQLITE3_TEXT);
    $stmt->bindValue(':status',  3, SQLITE3_INTEGER);

    $rs = $stmt->execute();

    if (false !== $rs)
    {
        /* @var $rs SQLite3Result */
        // Closes the result set
        $rs->finalize();
    }
    else
    {
        echo 'Error: '. $db->lastErrorMsg();
    }

    // Resets the prepared statement to its state prior to execution.
    // All bindings remain intact after reset.
    // $stmt->reset();
    $stmt->clear();
}

// Close statement
$stmt->close();
// Close database connection. 
// Error: Unable to close due to unfinalised statements
$db->close();
?>
 [2009-01-20 00:27 UTC] scottmac@php.net 
I created a C version of the code and tested this and got the same error, so I went and read the SQLite documentation again and it appears that as soon as you get an error on a statement its useless and can't be reused.

SQLITE_ERROR means that a run-time error (such as a constraint violation) has occurred. sqlite3_step() should not be called again on the VM. More information may be found by calling sqlite3_errmsg(). 

The Unable to close statements error has been fixed in CVS though.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved. 		Last updated: Thu Apr 25 12:01:31 2024 UTC