I am using the fantastic last_insert_id trick described here, but now run into the problem of non-deterministic behavior.
The PHP code works approximately every other time I run it. This is on a completely idle system (no production running simultaneously).
Is there anything I am forgetting here? What could the problem be?
My PHP code:
$query = "UPDATE `session`
SET `date_access` = NOW(), someField = LAST_INSERT_ID(someField)
WHERE ID = :cookie LIMIT 1 ;";
$stmt = $dbh->prepare($query);
$stmt->bindParam(':cookie', $_COOKIE['x']);
if ($stmt->execute()) {
$count = $stmt->rowCount();
if ($count == 1) {
$result = $dbh->lastInsertId();
}
}
The code is run with an existing "cookie" value every time, but the $count is 0 (instead of 1) every other time the code runs.
And this is the table:
SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
SET NAMES utf8mb4;
DROP TABLE IF EXISTS `session`;
CREATE TABLE `session` (
`ID` char(50) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
`date_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`date_access` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`someField` bigint(20) unsigned NOT NULL,
UNIQUE KEY `ID` (`ID`),
KEY `someField` (`someField`),
CONSTRAINT `session_ibfk_2` FOREIGN KEY (`someTable`) REFERENCES `someTable` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
