Edit report at https://bugs.php.net/bug.php?id=77240&edit=1
ID: 77240
Updated by: cmb@php.net
Reported by: dave at mausner dot us
Summary: Incorrect binding near constraint errors.
Status: Assigned
-Type: Bug
+Type: Documentation Problem
Package: SQLite related
Operating System: win 10
PHP Version: 7.2.12
Assigned To: cmb
Block user comment: N
Private report: N
New Comment:
> This bug is similar to #77051.
Actually, this ticket is a duplicate of bug #77051, which will be
fixed in PHP 7.2.14 and PHP 7.3.0 (the latter is scheduled for
release tomorrow, the former on 3 Jan 2019).
> This is rather non-intuitive, because the php doc does not hint
> that reset() is required before bindValue(), moreover the doc on
> reset() doesn't really explain its purpose.
Indeed, the docs should be improved to cater to PHP versions where
this bug is not fixed. There is already a respective note on
::bindParams()[1], but this should be placed more prominently, and
also placed on the man pages of related functions.
[1] <http://php.net/manual/en/sqlite3stmt.bindparam.php#refsect1-sqlite3stmt.bindparam-notes>
Previous Comments:
------------------------------------------------------------------------
[2018-12-05 06:11:18] dave at mausner dot us
This bug is similar to #77051. After reading that, I added:
@$bugsql->reset();
before the bindValue calls. This solves the problem.
This is rather non-intuitive, because the php doc does not hint that reset() is required before
bindValue(), moreover the doc on reset() doesn't really explain its purpose.
I observed that the first call to reset() after a constraint violation runs silently, but the NEXT
calls to reset() after a SECOND or more consecutive violations issue a diagnostic that "reset
cannot be performed".
This is a nuisance diagnostic because adjacent violations caused by consecutive inserts should be
handled the same way, silently--it's not the programmer's fault.
------------------------------------------------------------------------
[2018-12-04 21:53:59] dave at mausner dot us
Also occurs in:
PHP 7.2.12 (cli) (built: Nov 8 2018 05:47:24) (NTS MSVC15 (Visual C++ 2017) x64)
Zend Engine v3.2.0, Copyright (c) 1998-2018 Zend Technologies
------------------------------------------------------------------------
[2018-12-04 20:19:08] dave at mausner dot us
It seems to me that if only one constraint error 19 occurs amid good inserts, the error is not
harmful. However, if two or more constraint errors occur consecutively, the NEXT good insert will
contain junky data values.
The test data demonstrates this. As the occurrence of non-unique keys increases, the amount of junk
increases, too. Yet the internal echo shows that the intended data was not junk.
------------------------------------------------------------------------
[2018-12-04 19:59:33] dave at mausner dot us
Description:
------------
Insertions into a table with UNIQUE keys having a high occurrence of error 19 (constraint violation
due to duplicate key) causes junk to be inserted. The test case reproduces the erroneous
environment. The test keys are bound such that the column values MUST be "FIRSTx",
"MIDx", and "LASTx". But the bad data shows inexplicable mix-ups in the order
and sometimes junky contents.
The test script is completely self-contained. Tested with stock Win10 PHP build:
PHP 7.0.32 (cli) (built: Sep 12 2018 15:54:04) ( NTS )
Copyright (c) 1997-2017 The PHP Group
Zend Engine v3.0.0, Copyright (c) 1998-2017 Zend Technologies
SQLite 3.22.0 2018-01-22 18:45:57
zlib version 1.2.11
Test script:
---------------
<?php
define("SQLITE_CONSTRAINT", 19); /* Abort due to constraint violation */
// connect and configure session.
$data = new SQLite3("buggy");
$data->exec("pragma foreign_keys=on");
$data->exec("pragma recursive_triggers=on");
$data->exec("pragma journal_mode=off");
$data->exec("pragma synchronous=off");
$data->exec("pragma locking_mode=exclusive");
$data->exec("drop table if exists buggy");
$data->exec(<<<EOT
create table buggy (
bug integer primary key autoincrement,
bugfirst text,
bugmid text,
buglast text,
unique (bugfirst, bugmid, buglast)
)
EOT
);
$data->exec("begin");
// prepare sql.
$bugsql = $data->prepare("insert into buggy (bugfirst, bugmid, buglast) values (:bugfirst,
:bugmid, :buglast)");
for ($i = $j = $k = 0; $i < 1000; $i += 1) {
$bugfirst = "FIRST" . rand(1, 3);
$bugmid = "MID" . rand(1, 5);
$buglast = "LAST" . rand(1, 7);
$bugsql->bindValue(":bugfirst", $bugfirst);
$bugsql->bindValue(":bugmid", $bugmid);
$bugsql->bindValue(":buglast", $buglast);
@$insert = $bugsql->execute();
$error = $data->lastErrorCode();
echo "$bugfirst, $bugmid, $buglast, $error\n";
if ($insert === false) {
if ($error != SQLITE_CONSTRAINT) echo "Code $error.\n";
$k += 1;
continue;
}
$bug = $data->lastInsertRowID();
$j += 1;
}
$data->exec("commit");
$data->close();
echo "looped $i, inserted $j, clashed $k.\n";
exit;
?>
Expected result:
----------------
All inserted column values must be like this:
"FIRSTx", "MIDx", and "LASTx". And all triplet values are to be
unique.
Actual result:
--------------
But when unique key violations occur, rows are inserted with key values out-of-order, or just junk.
Example:
PK key1 key2 key3
129 FIRST3 MID2 LAST4
130 MID5 LAST LAST3
131 MID2 LAST LAST5
132 MID1 LAST LAST3
133 LAST4 LAST MID1
134 FIRST3 MID2 LAST2
135 FIRST2 MID5 LAST3
136 MID2 LAST LAST1
137 LAST4 MID5 :bugm
138 MID1 LAST LAST7
139 MID1 LAST LAST1
140 FIRST2 MID5 LAST2
141 LAST7 LAST MID1
142 LAST1 LAST MID4
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=77240&edit=1