ID: 43381
User updated by: tim at moocowproductions dot org
Reported By: tim at moocowproductions dot org
-Status: Feedback
+Status: Closed
Bug Type: MySQLi related
Operating System: Gentoo Linux & Mac OS X Leopard
PHP Version: 5.2.5
New Comment:
Figured it out. The problem was I was trying to use value=xyz within an
INSERT with a VALUES:
INSERT INTO mytable (myfield1, myfield2) VALUES (myfield1=?,
myfield2=?
);
That is not the correct syntax. Although the error reporting that
indicates that this is the problem is horribly mediocre at best.
However, once correcting the mistake, the both queries work as they
should, as does the transaction handling.
Thanks for taking a look at this! Looks like it was PEBCAK on my part!
Previous Comments:
------------------------------------------------------------------------
[2008-01-04 14:58:37] [EMAIL PROTECTED]
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!
------------------------------------------------------------------------
[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 [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 this bug report at http://bugs.php.net/?id=43381&edit=1
