ID: 43381
Updated by: uw@php.net
Reported By: tim at moocowproductions dot org
-Status: Open
+Status: No Feedback
Bug Type: MySQLi related
Operating System: Gentoo Linux & Mac OS X Leopard
PHP Version: 5.2.5
New Comment:
Please provide a short example which starts from a clean schema,
creates a minimum set of required tables etc., creates two rows and
fails with the error message you mention. Shrink the tables as far as
possible and try to make the PHP code as simple as possible.
Thanks!
Previous Comments:
------------------------------------------------------------------------
[2007-11-23 02:01:37] tim at moocowproductions dot org
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 q2solutions@localhost 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 =
"abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPRQSTUVWXYZ0123456789!@#$%^&*-+";
$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 this bug report at http://bugs.php.net/?id=43381&edit=1