From: tim at moocowproductions dot org
Operating system: Gentoo Linux & Mac OS X Leopard
PHP version: 5.2.5
PHP Bug Type: MySQLi related
Bug description: prepared statements not sending correct data to MySQL when
using transactions
Description:
------------
When I am trying to run a transaction, with inserts that depend on
each other, I get the following:
Cannot add or update a child row: a foreign key constraint fails
(`q2test/UserPermissions`, CONSTRAINT `up_userid_fk` FOREIGN KEY
(`userID`) REFERENCES `Users` (`userID`) ON DELETE CASCADE)
I thought it might be MySQL at first, but when I tried to prepare the
statements by hand in the command-line interface, I did not have this
problem. The issue seems to be that it is trying to set the userID
variable to 0, when in fact it should be LAST_INSERT_ID.
Now if I grab LAST_INSERT_ID and put it into a variable, I get the
correct number. But if I try to execute the second query, it appears
to set it back to 0 for some reason. I turned on the MySQL general log
which indicates that a quit is being issued after the prepare (so PHP
isn't getting as far as to execute the statement):
071122 19:53:39 31 Connect [EMAIL PROTECTED] on q2test
31 Query set autocommit=0
31 Prepare [1] INSERT INTO Users (username,
firstName, lastName, password, phone, email, role) VALUES (?, ?, ?, ?,
?, ?,?)
31 Execute [1] INSERT INTO Users (username,
firstName, lastName, password, phone, email, role) VALUES ('test',
'test', 'test', 'NI!GpasswordNI!G', '555', 'email','estimator')
31 Prepare [2] INSERT INTO UserPermissions
VALUES( userID=?,
canAddBuilders=?,
canAddAddresses=?,
canAddPlans=?,
canAddUsers=?,
canModifyBuilders=?,
canModifyAddresses=?,
canModifyPlans=?,
canModifyUsers=?,
canDeleteBuilders=?,
canDeleteAddresses=?,
canDeletePlans=?,
canDeleteUsers=?,
canAssignPlans=?
)
31 Quit
Now I tried to set specific values in the code to insert another
userID and I also tried to remove the foreign key restriction. When
this happens, both INSERTS complete, but the insert in the
UserPermissions table has a userID of instead of the one from the
previous insert (or one that I hard-code in for testing).
Reproduce code:
---------------
The code that generates this is:
<?php
include '../htdocs/global.inc.php';
$dbHost = '127.0.0.1';
$dbDatabase = 'test';
$dbUsername = 'test';
$dbPassword = '?????';
$mysqli = new mysqli($dbHost, $dbUsername, $dbPassword, $dbDatabase)
or die("Cannot connect to database");
function generateSalt($length = 4)
{
$chars =
"[EMAIL PROTECTED]&*-+";
$code = "";
while (strlen($code) < $length) {
$code .= $chars[mt_rand(0,strlen($chars))];
}
return $code;
}
function addUser($username, $firstName, $lastName, $role, $phone, $email,
$password)
{
global $mysqli;
$salt =generateSalt();
$password = $salt.$password.$salt;
$stmt = $mysqli->stmt_init();
$stmt->prepare("INSERT INTO Users (username, firstName, lastName,
password, phone, email, role) VALUES (?, ?, ?, ?, ?, ?,?)") or
die($mysqli->error);
$stmt->bind_param('sssssss', $username, $firstName, $lastName,
$password,
$phone, $email, $role);
if(!$stmt->execute())
{
$stmt->close();
return 0;
}
$stmt->close();
echo $mysqli->thread_id."\n";
return $mysqli->insert_id;
}
function addUserPermissions($userID, $canAddBuilders, $canAddAddresses,
$canAddPlans, $canAddUsers, $canModifyBuilders, $canModifyAddresses,
$canModifyPlans, $canModifyUsers, $canDeleteBuilders, $canDeleteAddresses,
$canDeletePlans, $canDeleteUsers, $canAssignPlans)
{
global $mysqli;
$stmt = $mysqli->stmt_init();
$stmt->prepare("INSERT INTO UserPermissions
VALUES( userID=?,
canAddBuilders=?,
canAddAddresses=?,
canAddPlans=?,
canAddUsers=?,
canModifyBuilders=?,
canModifyAddresses=?,
canModifyPlans=?,
canModifyUsers=?,
canDeleteBuilders=?,
canDeleteAddresses=?,
canDeletePlans=?,
canDeleteUsers=?,
canAssignPlans=?
)");
$stmt->bind_param('isssssssssssss', $userID, $canAddBuilders,
$canAddAddresses, $canAddPlans, $canAddUsers, $canModifyBuilders,
$canModifyAddresses, $canModifyPlans, $canModifyUsers, $canDeleteBuilders,
$canDeleteAddresses, $canDeletePlans, $canDeleteUsers, $canAssignPlans);
if(!$stmt->execute())
{
echo $mysqli->thread_id."\n";
echo $stmt->error;
$stmt->close();
return false;
}
$stmt->close();
echo $mysqli->thread_id."\n";
return true;
}
echo $mysqli->thread_id."\n";
$mysqli->autocommit(FALSE);
$userID = addUser('test', 'test', 'test', 'estimator', '555', 'email',
'password');
if($userID == 0)
$mysqli->rollback();
addUserPermissions($userID, 'n', 'n', 'n', 'n', 'n', 'n', 'n', 'n', 'n',
'n', 'n', 'n', 'n');
$mysqli->close();
And the schema is:
mysql> show create table Users\G
*************************** 1. row ***************************
Table: Users
Create Table: CREATE TABLE `Users` (
`userID` int(10) unsigned NOT NULL auto_increment,
`username` varchar(32) NOT NULL,
`password` varchar(32) NOT NULL,
`salt` varchar(4) NOT NULL,
`firstName` varchar(32) NOT NULL,
`lastName` varchar(48) NOT NULL,
`role` enum('Admin','Webmaster','Estimator') NOT NULL default
'Estimator',
`phone` varchar(40) default NULL,
`email` varchar(255) NOT NULL,
PRIMARY KEY (`userID`),
UNIQUE KEY `username_idx` USING BTREE (`username`),
UNIQUE KEY `firstlastname_idx` (`firstName`,`lastName`)
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=utf8
1 row in set (0.08 sec)
mysql> show create table UserPermissions\G
*************************** 1. row ***************************
Table: UserPermissions
Create Table: CREATE TABLE `UserPermissions` (
`userID` int(10) unsigned NOT NULL,
`canAddBuilders` enum('Y','N') NOT NULL default 'N',
`canAddAddresses` enum('Y','N') NOT NULL default 'N',
`canAddPlans` enum('Y','N') NOT NULL default 'N',
`canAddUsers` enum('Y','N') NOT NULL default 'N',
`canModifyBuilders` enum('Y','N') NOT NULL default 'N',
`canModifyAddresses` enum('Y','N') NOT NULL default 'N',
`canModifyPlans` enum('Y','N') NOT NULL default 'N',
`canModifyUsers` enum('Y','N') NOT NULL default 'N',
`canDeleteBuilders` enum('Y','N') NOT NULL default 'N',
`canDeleteAddresses` enum('Y','N') NOT NULL default 'N',
`canDeletePlans` enum('Y','N') NOT NULL default 'N',
`canDeleteUsers` enum('Y','N') NOT NULL default 'N',
`canAssignPlans` enum('Y','N') NOT NULL default 'N',
PRIMARY KEY (`userID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)
Expected result:
----------------
Both inserts to complete and the transaction is committed.
Actual result:
--------------
First INSERT completes but the second fails with this:
Cannot add or update a child row: a foreign key constraint fails
(`q2test/UserPermissions`, CONSTRAINT `up_userid_fk` FOREIGN KEY
(`userID`) REFERENCES `Users` (`userID`) ON DELETE CASCADE)
--
Edit bug report at http://bugs.php.net/?id=43381&edit=1
--
Try a CVS snapshot (PHP 4.4):
http://bugs.php.net/fix.php?id=43381&r=trysnapshot44
Try a CVS snapshot (PHP 5.2):
http://bugs.php.net/fix.php?id=43381&r=trysnapshot52
Try a CVS snapshot (PHP 5.3):
http://bugs.php.net/fix.php?id=43381&r=trysnapshot53
Try a CVS snapshot (PHP 6.0):
http://bugs.php.net/fix.php?id=43381&r=trysnapshot60
Fixed in CVS: http://bugs.php.net/fix.php?id=43381&r=fixedcvs
Fixed in release:
http://bugs.php.net/fix.php?id=43381&r=alreadyfixed
Need backtrace: http://bugs.php.net/fix.php?id=43381&r=needtrace
Need Reproduce Script: http://bugs.php.net/fix.php?id=43381&r=needscript
Try newer version: http://bugs.php.net/fix.php?id=43381&r=oldversion
Not developer issue: http://bugs.php.net/fix.php?id=43381&r=support
Expected behavior: http://bugs.php.net/fix.php?id=43381&r=notwrong
Not enough info:
http://bugs.php.net/fix.php?id=43381&r=notenoughinfo
Submitted twice:
http://bugs.php.net/fix.php?id=43381&r=submittedtwice
register_globals: http://bugs.php.net/fix.php?id=43381&r=globals
PHP 3 support discontinued: http://bugs.php.net/fix.php?id=43381&r=php3
Daylight Savings: http://bugs.php.net/fix.php?id=43381&r=dst
IIS Stability: http://bugs.php.net/fix.php?id=43381&r=isapi
Install GNU Sed: http://bugs.php.net/fix.php?id=43381&r=gnused
Floating point limitations: http://bugs.php.net/fix.php?id=43381&r=float
No Zend Extensions: http://bugs.php.net/fix.php?id=43381&r=nozend
MySQL Configuration Error: http://bugs.php.net/fix.php?id=43381&r=mysqlcfg
