Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-642

Generated IDs are not guaranteed to be unique over the table's lifetime in SQLite

    Details

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

      Description

      In

      http://docs.doctrine-project.org/en/2.0.x/reference/basic-mapping.html#identifier-generation-strategies

      it says that MySQL and SQLite use AUTO_INCREMENT by default. The generated SQL for creating an ID field with GENERATOR_TYPE_AUTO looks like this (abbreviated
      for readability):

      CREATE_TABLE_TEST (id INTEGER DEFAULT 0 NOT NULL, PRIMARY KEY(id))
      

      http://www.sqlite.org/faq.html#q1 states:

      If you declare a column of a table to be INTEGER PRIMARY KEY, then whenever you insert a NULL into that column of the table, the NULL is automatically converted into an integer which is one greater than the largest value of that column over all other rows in the table, or 1 if the table is empty. [...] Note that the integer key is one greater than the largest key that was in the table just prior to the insert. The new key will be unique over all keys currently in the table, but it might overlap with keys that have been previously deleted from the table.

      So in other words, if you remove an entity and then create a new one, the new one will have the same id as the previously deleted one. If you do both operations on the same entitymanager, id references (in proxies f.x.) will suddenly get confused and point to something else (at least that's my current theory..)

      The point is: SQLite doesn't act like MySQL as the documentation implies, and IMHO SQLite's current behaviour makes it useless for more complex scenarios. I've found some reference to this problem here:

      https://github.com/doctrine/dbal/pull/66

      Unfortunately, it doesn't mention any solution. The problem is that you can't override the columnDefinition, because it would have to be "INTEGER PRIMARY KEY AUTOINCREMENT", but then, you get an exception, because the Platform appends ", PRIMARY KEY(id)", so it's defined twice

        Activity

        Hide
        Benjamin Eberlei added a comment -

        I think there is no fix for this, this is just how SQLite works, and we cannot really keep the last ids somewhere. IMHO its a documentation issue.

        Show
        Benjamin Eberlei added a comment - I think there is no fix for this, this is just how SQLite works, and we cannot really keep the last ids somewhere. IMHO its a documentation issue.
        Hide
        flack added a comment -

        Well, you cannot fix it for cases with multiple id columns (but the Doctrine documentation already suggests that they should be avoided where possible), but for single integer columns (which is the normal case, as suggested by documentation), SQLite provides all the necessary functionality, as long as you create the column with INTEGER PRIMARY KEY AUTOINCREMENT. So IMHO the best solution would be if support for this could be implemented somehow in the SQL Platform driver.

        Show
        flack added a comment - Well, you cannot fix it for cases with multiple id columns (but the Doctrine documentation already suggests that they should be avoided where possible), but for single integer columns (which is the normal case, as suggested by documentation), SQLite provides all the necessary functionality, as long as you create the column with INTEGER PRIMARY KEY AUTOINCREMENT. So IMHO the best solution would be if support for this could be implemented somehow in the SQL Platform driver.
        Hide
        flack added a comment -

        Case in point: I implemented exactly this in a Doctrine adapter I'm working on:

        https://github.com/flack/midgard-portable/blob/master/src/midgard/portable/storage/subscriber.php#L136

        Granted, this is a very ugly workaround that only works because I know all my ID columns are actually called 'id' (and will never change), but I'm fairly sure that a more general solution could be built with reasonable effort.

        Show
        flack added a comment - Case in point: I implemented exactly this in a Doctrine adapter I'm working on: https://github.com/flack/midgard-portable/blob/master/src/midgard/portable/storage/subscriber.php#L136 Granted, this is a very ugly workaround that only works because I know all my ID columns are actually called 'id' (and will never change), but I'm fairly sure that a more general solution could be built with reasonable effort.
        Hide
        Benjamin Eberlei added a comment -

        flack We removed the Sqlite AUTOINCREMENT for some weird reason. I am inclined to add this again, however I need to find out what the reasons for removing this have been.

        Show
        Benjamin Eberlei added a comment - flack We removed the Sqlite AUTOINCREMENT for some weird reason. I am inclined to add this again, however I need to find out what the reasons for removing this have been.
        Hide
        Steve Müller added a comment -

        Benjamin Eberlei I checked that lately and I came to the same conclusion. The reason why it was removed is to support composite primary keys which was not possible before somehow. We could add autoincrement if only a single column integer primary key is given I think...

        Show
        Steve Müller added a comment - Benjamin Eberlei I checked that lately and I came to the same conclusion. The reason why it was removed is to support composite primary keys which was not possible before somehow. We could add autoincrement if only a single column integer primary key is given I think...

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            flack
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated: