Bug #77240 [NEW]: Incorrect binding near constraint errors.
From: dave at mausner dot us
Operating system: win 10
PHP version: 7.0.32
Package: SQLite related
Bug Type: Bug
Bug description:Incorrect binding near constraint errors.
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 bug report at https://bugs.php.net/bug.php?id=77240&edit=1
--
Try a snapshot (PHP 5.4): https://bugs.php.net/fix.php?id=77240&r=trysnapshot54
Try a snapshot (PHP 5.5): https://bugs.php.net/fix.php?id=77240&r=trysnapshot55
Try a snapshot (trunk): https://bugs.php.net/fix.php?id=77240&r=trysnapshottrunk
Fixed in SVN: https://bugs.php.net/fix.php?id=77240&r=fixed
Fixed in release: https://bugs.php.net/fix.php?id=77240&r=alreadyfixed
Need backtrace: https://bugs.php.net/fix.php?id=77240&r=needtrace
Need Reproduce Script: https://bugs.php.net/fix.php?id=77240&r=needscript
Try newer version: https://bugs.php.net/fix.php?id=77240&r=oldversion
Not developer issue: https://bugs.php.net/fix.php?id=77240&r=support
Expected behavior: https://bugs.php.net/fix.php?id=77240&r=notwrong
Not enough info: https://bugs.php.net/fix.php?id=77240&r=notenoughinfo
Submitted twice: https://bugs.php.net/fix.php?id=77240&r=submittedtwice
register_globals: https://bugs.php.net/fix.php?id=77240&r=globals
PHP 4 support discontinued: https://bugs.php.net/fix.php?id=77240&r=php4
Daylight Savings: https://bugs.php.net/fix.php?id=77240&r=dst
IIS Stability: https://bugs.php.net/fix.php?id=77240&r=isapi
Install GNU Sed: https://bugs.php.net/fix.php?id=77240&r=gnused
Floating point limitations: https://bugs.php.net/fix.php?id=77240&r=float
No Zend Extensions: https://bugs.php.net/fix.php?id=77240&r=nozend
MySQL Configuration Error: https://bugs.php.net/fix.php?id=77240&r=mysqlcfg
Thread (4 messages)
- dave at mausner dot us