php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #23480 Sequences for DB:MySQL
Submitted: 2003-05-04 18:12 UTC Modified: 2003-06-16 13:18 UTC
From: blair at squiz dot net Assigned: ssb (profile)
Status: Closed Package: PEAR related
PHP Version: 4.3.1 OS: Redhat Linux
Private report: No CVE-ID: None
View Add Comment Developer Edit
Anyone can comment on a bug. Have a simpler test case? Does it work for you on a different platform? Let us know!
Just going to say 'Me too!'? Don't clutter the database with that please !
Your email address:
MUST BE VALID
Solve the problem:
21 + 46 = ?
Subscribe to this entry?

 
 [2003-05-04 18:12 UTC] blair at squiz dot net
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;");
     }
 
     // }}}


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2003-05-04 18:27 UTC] nicos@php.net
I'm not sure if we should apply this if we dont want to break BC. 

Stig ?
 [2003-05-04 18:27 UTC] nicos@php.net
I'm not sure if we should apply this if we dont want to break BC. 

Stig ?
 [2003-05-04 18:50 UTC] blair at squiz dot net
Just to confirm that there is no BC issue for people using nextId() with it's ondemand feature.

The potential BC issue will be for those that are running createSequence() themselves (eg in an install script).
 [2003-05-05 04:21 UTC] lsmith@php.net
the patch is good imho as I have stated in the linked thread. It does not add overhead to common use of nextId. Also it makes the behavior finally consistent with other backends. BC issues should be minimal since the purpose of nextId is to return unique indentifiers and not a specific number. Therefore only people aware of this issues with their own BC hacks will be affected.
 [2003-06-14 23:46 UTC] cox@php.net
This bug has been fixed in CVS.

In case this was a PHP problem, snapshots of the sources are packaged
every three hours; this change will be in the next snapshot. You can
grab the snapshot at http://snaps.php.net/.
 
In case this was a documentation problem, the fix will show up soon at
http://www.php.net/manual/.

In case this was a PHP.net website problem, the change will show
up on the PHP.net site and on the mirror sites in short time.
 
Thank you for the report, and for helping us make PHP better.


 [2003-06-16 00:08 UTC] blair at squiz dot net
Thanks for committing the patch but you left out the changes to nextId(). 
Without this change the first AND second nextIds() calls that utilise the ondemand feature will return 1

Below is the diff on the lastest CVS version just to confirm.

cheers,
BCR


Index: mysql.php
===================================================================
RCS file: /repository/pear/DB/DB/mysql.php,v
retrieving revision 1.17
diff -u -r1.17 mysql.php
--- mysql.php	15 Jun 2003 04:45:46 -0000	1.17
+++ mysql.php	16 Jun 2003 05:03:29 -0000
@@ -523,13 +523,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 **/
 [2003-06-16 13:18 UTC] cox@php.net
Yes, it's true, I forgot to commit that part. Done now.

Thanks again,

Tomas V.V.Cox
 
PHP Copyright © 2001-2021 The PHP Group
All rights reserved.
Last updated: Fri Oct 22 19:07:12 2021 UTC