#43381 [NEW]: prepared statements not sending correct data to MySQL when using transactions

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 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 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