Package home | Report new bug | New search | Development Roadmap Status: Open | Feedback | All | Closed Since Version 1.12.1

Bug #551 mysql sequences cant honour transactions
Submitted: 2004-01-10 04:28 UTC
From: alan_k Assigned: danielc
Status: Wont fix Package: DB
PHP Version: Irrelevant OS: na
Roadmaps: (Not assigned)    
Subscription  


 [2004-01-10 04:28 UTC] alan_k
Description: ------------ If you define sequences to use innodb (Eg. with transactions) and use begin insert ... rollback insert .. commit you skip a sequence number.. The patch below fixes this.. Although I may be tempted to flag this as Wont Fix (due to speed/BC) RCS file: /repository/pear/DB/DB/mysql.php,v retrieving revision 1.31 diff -u -r1.31 mysql.php --- mysql.php 6 Jan 2004 03:19:37 -0000 1.31 +++ mysql.php 10 Jan 2004 04:29:06 -0000 @@ -504,12 +504,13 @@ do { $repeat = 0; $this->pushErrorHandling(PEAR_ERROR_RETURN); - $result = $this->query("UPDATE ${seqname} ". - 'SET id=LAST_INSERT_ID(id+1)'); + $result = $this->query("UPDATE {$seqname} ". + 'SET id= id+1'); + $this->popErrorHandling(); if ($result == DB_OK) { /** COMMON CASE **/ - $id = mysql_insert_id($this->connection); + $id = $this->getOne("SELECT id FROM {$seqname}"); if ($id != 0) { return $id; } @@ -573,7 +574,7 @@ { $seqname = $this->getSequenceName($seq_name); $res = $this->query("CREATE TABLE ${seqname} ". - '(id INTEGER UNSIGNED AUTO_INCREMENT NOT NULL,'. + '(id INTEGER UNSIGNED NOT NULL,'. ' PRIMARY KEY(id))'); if (DB::isError($res)) { return $res;

Comments

 [2004-01-11 11:57 UTC] lsmith
I am note sure how moving away from auto_increment will result in unique id's. this opens up the possibility of two requests fetching the same id. Also I dont think the job of a sequence is to necessarily ensure that you get them all in a nice order and instead the job is more to generate unique id's. I am definately for "wont fix" for this one.
 [2004-01-15 17:50 UTC] danielc
I agree. The goal is to get a valid unique ID number, not necessarily the last number plus one.