#23480 [NEW]: Sequences for DB:MySQL

From: blair at squiz dot net Operating system: Redhat Linux PHP version: 4.3.1 PHP Bug Type: PEAR related Bug description: Sequences for DB:MySQL After sounding this out on the pear-dev mailing list here, http://marc.theaimsgroup.com/?l=pear-dev&m=105183392425465&w=2, I am submitting this as a bug, this is a copy of the post : ... I have noticed an inconsistency between MySQL and PostgreSQL DB objects when you are creating sequences. When you call createSequence() with MySQL it set's the value to 1, so when you make your first call with nextId() a 2 is returned. When you call createSequence() with Postgre and then call nextId() a 1 is returned, which is what I would expect. I can't test what the other databases do, but by looking at nextId() fn in the other DB classes that are using tables to emulate sequences (ie mssql, fbsql and odbc) createSequence() appears to initialise the value to zero because these fns repeat in the do..while loop after creating the sequence. I have attached a patch to the DB/mysql.php that fixes this issue in createSequence() and alters nextId() to perform the same repeat that the other classes do - please tell me what you think. BTW - if you are wondering why I need to call createSequence() rather than just using the on demand feature of nextId() it has to do with Postgre, transactions and a failed NEXTVAL() call - see the points raised in this bug report, http://bugs.php.net/bug.php?id=22761 --- /home/brobertson/pear/DB/mysql.php Wed Apr 23 09:50:42 2003 +++ mysql.php Fri May 2 09:49:36 2003 @@ -553,13 +553,11 @@ $result->getCode() == DB_ERROR_NOSUCHTABLE) { $result = $this->createSequence($seq_name); - // Since createSequence initializes the ID to be 1, - // we do not need to retrieve the ID again (or we will get 2) if (DB::isError($result)) { return $this->raiseError($result); } else { - // First ID of a newly created sequence is 1 - return 1; + // now just repeat and we will get our first value + $repeat = 1; } /** BACKWARDS COMPAT **/ @@ -590,8 +588,13 @@ if (DB::isError($res)) { return $res; } - // insert yields value 1, nextId call will generate ID 2 - return $this->query("INSERT INTO ${seqname} VALUES(0)"); + // insert yields value 1 + $res = $this->query("INSERT INTO ${seqname} VALUES(0)"); + if (DB::isError($res)) { + return $res; + } + // so reset to zero + return $this->query("UPDATE ${seqname} SET id = 0;"); } // }}} -- Edit bug report at http://bugs.php.net/?id=23480&edit=1 -- Try a CVS snapshot: http://bugs.php.net/fix.php?id=23480&r=trysnapshot Fixed in CVS: http://bugs.php.net/fix.php?id=23480&r=fixedcvs Fixed in release: http://bugs.php.net/fix.php?id=23480&r=alreadyfixed Need backtrace: http://bugs.php.net/fix.php?id=23480&r=needtrace Try newer version: http://bugs.php.net/fix.php?id=23480&r=oldversion Not developer issue: http://bugs.php.net/fix.php?id=23480&r=support Expected behavior: http://bugs.php.net/fix.php?id=23480&r=notwrong Not enough info: http://bugs.php.net/fix.php?id=23480&r=notenoughinfo Submitted twice: http://bugs.php.net/fix.php?id=23480&r=submittedtwice register_globals: http://bugs.php.net/fix.php?id=23480&r=globals PHP 3 support discontinued: http://bugs.php.net/fix.php?id=23480&r=php3 Daylight Savings: http://bugs.php.net/fix.php?id=23480&r=dst IIS Stability: http://bugs.php.net/fix.php?id=23480&r=isapi Install GNU Sed: http://bugs.php.net/fix.php?id=23480&r=gnused