php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Doc Bug #77240 Incorrect binding near constraint errors.
Submitted: 2018-12-04 19:59 UTC Modified: 2018-12-05 10:42 UTC
Votes:1
Avg. Score:5.0 ± 0.0
Reproduced:1 of 1 (100.0%)
Same Version:1 (100.0%)
Same OS:1 (100.0%)
From: dave at mausner dot us Assigned: cmb (profile)
Status: Assigned Package: SQLite related
PHP Version: 7.2.12 OS: win 10
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: dave at mausner dot us
New email:
PHP Version: OS:

 

 [2018-12-04 19:59 UTC] 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

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2018-12-04 20:19 UTC] 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 21:53 UTC] dave at mausner dot us
-PHP Version: 7.0.32 +PHP Version: 7.2.12
 [2018-12-04 21:53 UTC] 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 22:53 UTC] cmb@php.net
-Assigned To: +Assigned To: cmb
 [2018-12-05 06:11 UTC] 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-05 10:42 UTC] cmb@php.net
-Type: Bug +Type: Documentation Problem
 [2018-12-05 10:42 UTC] cmb@php.net
> 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>
 
PHP Copyright © 2001-2018 The PHP Group
All rights reserved.
Last updated: Tue Dec 11 23:01:25 2018 UTC