From: pcdinh at gmail dot com
Operating system: Windows XP
PHP version: 5.3.0alpha3
PHP Bug Type: SQLite related
Bug description: Unable to close SQLite3 database due to unfinalised statements
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
--
Edit bug report at http://bugs.php.net/?id=47145&edit=1
--
Try a CVS snapshot (PHP 5.2):
http://bugs.php.net/fix.php?id=47145&r=trysnapshot52
Try a CVS snapshot (PHP 5.3):
http://bugs.php.net/fix.php?id=47145&r=trysnapshot53
Try a CVS snapshot (PHP 6.0):
http://bugs.php.net/fix.php?id=47145&r=trysnapshot60
Fixed in CVS:
http://bugs.php.net/fix.php?id=47145&r=fixedcvs
Fixed in CVS and need be documented:
http://bugs.php.net/fix.php?id=47145&r=needdocs
Fixed in release:
http://bugs.php.net/fix.php?id=47145&r=alreadyfixed
Need backtrace:
http://bugs.php.net/fix.php?id=47145&r=needtrace
Need Reproduce Script:
http://bugs.php.net/fix.php?id=47145&r=needscript
Try newer version:
http://bugs.php.net/fix.php?id=47145&r=oldversion
Not developer issue:
http://bugs.php.net/fix.php?id=47145&r=support
Expected behavior:
http://bugs.php.net/fix.php?id=47145&r=notwrong
Not enough info:
http://bugs.php.net/fix.php?id=47145&r=notenoughinfo
Submitted twice:
http://bugs.php.net/fix.php?id=47145&r=submittedtwice
register_globals:
http://bugs.php.net/fix.php?id=47145&r=globals
PHP 4 support discontinued: http://bugs.php.net/fix.php?id=47145&r=php4
Daylight Savings: http://bugs.php.net/fix.php?id=47145&r=dst
IIS Stability:
http://bugs.php.net/fix.php?id=47145&r=isapi
Install GNU Sed:
http://bugs.php.net/fix.php?id=47145&r=gnused
Floating point limitations:
http://bugs.php.net/fix.php?id=47145&r=float
No Zend Extensions:
http://bugs.php.net/fix.php?id=47145&r=nozend
MySQL Configuration Error:
http://bugs.php.net/fix.php?id=47145&r=mysqlcfg
