php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
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
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: pcdinh at gmail dot com
New email:
PHP Version: OS:

 

 [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

Pull Requests

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: Fri Dec 27 05:01:27 2024 UTC