Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-818

Fetching identity value from an insert fails with merge replication enabled

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 2.4.2
    • Fix Version/s: None
    • Component/s: Drivers
    • Security Level: All
    • Labels:
      None

      Description

      The way that the PDOSqlsrv driver fetches the identity value for a freshly inserted record will fail if there are triggers on a table that do a secondary insert on a table that also has an identity column.

      This is the case when you set up merge replication in SQL Server. The replication creates a series of triggers on the tables to catch any of the changes made for the purposes of replication and inserts those changes elsewhere.

      We have switched to using the native SQLSrv drivers instead to work around this since that uses "SELECT SCOPE_IDENTITY() AS LastInsertId" to fetch the value during the insert command.

        Activity

        Hide
        Steve Müller added a comment -

        I'm not quite sure whether we can fix this is in a reasonable way. See PDO's lastInsertId method in itself is documented to be very inconsistent and behaves differently throughout different drivers and even database versions.

        This method may not return a meaningful or consistent result across different PDO drivers, because the underlying database may not even support the notion of auto-increment fields or sequences.

        Because of this we do not have tests in our testsuite yet that cover the last insert id topic that work as expected on all drivers.
        IIRC the fact that DBAL's implementation for the native sqlsrv driver uses "SELECT SCOPE_IDENTITY() AS LastInsertId" is a workaround to support this feature at all and come around the driver limitation IIRC. This as such is a dirty workaround and should IMO not be relied on for scenarios such as you describe.
        I'm not quite sure what the expected behaviour of PDO drivers in general would be concerning triggers on PK columns that do other inserts regarding last insert IDs. IMO this is not a Doctrine bug but rather an unsupported use case or even a driver bug/limitation? Not sure on this.
        The only thing we could actually do is implement the same workaround we have in the native sqlsrv driver for the PDO driver. But I would rather not do this for such an edge case scenarion.
        But that's just my opinion

        Show
        Steve Müller added a comment - I'm not quite sure whether we can fix this is in a reasonable way. See PDO's lastInsertId method in itself is documented to be very inconsistent and behaves differently throughout different drivers and even database versions. This method may not return a meaningful or consistent result across different PDO drivers, because the underlying database may not even support the notion of auto-increment fields or sequences. Because of this we do not have tests in our testsuite yet that cover the last insert id topic that work as expected on all drivers. IIRC the fact that DBAL's implementation for the native sqlsrv driver uses "SELECT SCOPE_IDENTITY() AS LastInsertId" is a workaround to support this feature at all and come around the driver limitation IIRC. This as such is a dirty workaround and should IMO not be relied on for scenarios such as you describe. I'm not quite sure what the expected behaviour of PDO drivers in general would be concerning triggers on PK columns that do other inserts regarding last insert IDs. IMO this is not a Doctrine bug but rather an unsupported use case or even a driver bug/limitation? Not sure on this. The only thing we could actually do is implement the same workaround we have in the native sqlsrv driver for the PDO driver. But I would rather not do this for such an edge case scenarion. But that's just my opinion
        Hide
        Michael Anthon added a comment -

        Yes, I agree that any workaround will be a bit of a dirty hack.

        The main problem is the PDO driver using @@identity to get the last inserted id, which is pretty much the wrong way to do it in all but the simplest of cases... SCOPE_IDENTITY is there for a reason but won't work when called subsequently since it's run inside an sp_prepexec and will be out of scope anyway (it has to be tacked onto the end of the insert statement as it is in the sqlsrv driver)

        There's another piece of code in the SQLSrvConnection that uses this method...

         $sql = "SELECT IDENT_CURRENT(".$this->quote($name).") AS LastInsertId";
        

        That could potentially be used as well and would probably give a more accurate answer but is also subject to race conditions on busy systems.

        Show
        Michael Anthon added a comment - Yes, I agree that any workaround will be a bit of a dirty hack. The main problem is the PDO driver using @@identity to get the last inserted id, which is pretty much the wrong way to do it in all but the simplest of cases... SCOPE_IDENTITY is there for a reason but won't work when called subsequently since it's run inside an sp_prepexec and will be out of scope anyway (it has to be tacked onto the end of the insert statement as it is in the sqlsrv driver) There's another piece of code in the SQLSrvConnection that uses this method... $sql = "SELECT IDENT_CURRENT(" .$ this ->quote($name). ") AS LastInsertId" ; That could potentially be used as well and would probably give a more accurate answer but is also subject to race conditions on busy systems.

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Michael Anthon
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated: