CODEWITHSUNDEEP
oracle-databasesymfonydoctrine-orminsertsequence
MicWit
MicWit

Reputation: 685

Using Symfony and Doctrine with Oracle not returning correct ID on add and increasing sequence by 2

I am using Symfony with Doctrine to connect to an Oracle database. Most things are working fine, however, I have just gone to add a record to the database and I am finding I am getting the wrong number and the sequence is going up by 2.

So in the entity, for the primary key I have:

/**
 * @var integer
 *
 * @ORM\Column(name="PK", type="integer", nullable=false)
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="SEQUENCE")
 * @ORM\SequenceGenerator(sequenceName="SEQ_GET_LOCATIONS_PK", allocationSize=1, initialValue=1)
 */
private $pk;

When I add the data and return the PK I have:

$newMap = new Locations();
$newMap->setName($mapName);
$em = $this->getDoctrine()->getManager();
$em->persist($newMap);
$em->flush();
$mapId = $newMap->getPk();

In the database I have a trigger:

create or replace TRIGGER insert_locations
Before Insert On "LOCATIONS" Referencing NEW AS NEW 
For Each Row  
  Begin
  SELECT SEQ_GET_LOCATIONS_PK.nextval into :new.pk from dual;
END;

and a sequence:

CREATE SEQUENCE  "SCHEMA"."SEQ_GET_LOCATIONS_PK"  MINVALUE 14 MAXVALUE 99999999999999999999 INCREMENT BY 1 START WITH 109 CACHE 20 NOORDER  NOCYCLE ;

When I add a new item to the database, if the past PK was 95 say, I would expect the new one to be 96, and would expect that to be the value of $mapId. However, I find that on the database, the PK is 97 although the returned value is 96!

I'm wondering if its something to do with the trigger being left there (it was there from a past non-symfony version of the software) or something, but I removed the trigger and still have the issue.

Is this a Doctrine bug?

Upvotes: 0

Views: 633

Answers (1)

MicWit
MicWit

Reputation: 685

I found the issue for anyone who has this in the future. It ended up being the trigger, I had pointed it to a different table to stop it running but had forgotten to compile it so it was still working. To keep the trigger there (for testing purposes) and still have everything work, change the trigger to:

create or replace trigger insert_locations
Before Insert On "LOCATIONS" Referencing NEW AS NEW 
FOR EACH ROW  
  BEGIN
  IF :NEW.pk is NULL THEN
    SELECT SEQ_GET_LOCATIONS_PK.nextval INTO :NEW.pk FROM dual;
  END IF;
END;

The cause: When I insert a new row from Doctrine/Symfony, Doctrine looks to the sequence and gets the next number. When it sends the query to Oracle to be inserted, Oracle fires the trigger which then looks to the sequence. Of course because Doctrine already got the sequence, this will cause the sequence to increase again, giving it a new key that Doctrine doesn't know about.

The solution: Either remove the trigger (I want to keep it so I can manually add an item to the database for testing) or add a check in the trigger to get it to replace the ID only when one isn't supplied.

Upvotes: 1

Related Questions