Home » Database » DB » Bug #2666

[2004-11-01 17:32 UTC] vile at nativecomm dot net

Description:
------------
There is a problem with transactions with mysql (maybe also with other DB engines).
SELECT ... FOR UPDATE has to be treaten as manipulation query. Because it is not treaten like that , a simple transaction like:
BEGIN
SELECT ... FOR UPDATE
UPDATE
COMMIT
cannot been realized with PEAR::DB

Reproduce code:
---------------
//table voucher is an InnoDB table

$gdb->autoCommit(FALSE);
$sql = "SELECT id,credits FROM voucher WHERE status='ENABLED' and secret='$code' for update;"; if($row = $gdb->getRow($sql)) {
$gdb->query("UPDATE voucher SET status='USED', used=NOW() where id=".$row[0].";");
$gdb->commit();
return $row[1];
} else {
$gdb->commit();
return -1;
}

Expected result:
----------------
a safe transaction ;)

Actual result:
--------------
there is no transaction

[2004-11-03 09:21 UTC] vile at nativecomm dot net

Here is the table, but i think thats irrelevant. The table surely works safely with transactions.
I solved the problem for myself - i'v edited the PEAR:DB's mysql.php in the DB directory and change the function simpleQuery:

if (!$this->autocommit && $ismanip) {

with

if (!$this->autocommit) {

and it's work , but that is not a nice solution.
It will be better solution to change the DB::isManip to recognise the SELECT .. FOR UPDATE query as a manipulation query.

CREATE TABLE `voucher` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`secret` varchar(32) NOT NULL default '',
`status` enum('ENABLED','USED','DISABLED') NOT NULL default 'ENABLED',
`serie` varchar(16) NOT NULL default '',
`credits` int(11) NOT NULL default '0',
`created` datetime NOT NULL default '0000-00-00 00:00:00',
`used` datetime NOT NULL default '0000-00-00 00:00:00',
`fk_user` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `secret` (`secret`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;