Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-512

Update schema not working on MsSql due to no support for alter identity

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 2.2.2
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None
    • Environment:
      Symfony 2.1, SQL Server 2008, driver: pdo_sqlsrv

      Description

      When running: php app/console doctrine:schema:update --force

      [Doctrine\DBAL\DBALException]
      An exception occurred while executing 'ALTER TABLE tableName ALTER COLUMN id INT IDENTITY NOT NULL':

      SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'IDENTITY'.

      According to this stackoverflow http://stackoverflow.com/a/1049305/1833322 MSSQL does not support this query.

        Activity

        Hide
        Steve Müller added a comment - - edited

        Obviously SQL Server doesn't support this. So what solution would you expect here? IMO such a scenario should be avoided as there is no reasonable way to deal with changing identity columns. I guess most people are not even aware of this. The solutions available are very risky (creating new schema and then migrating data for example) and can take very long to be finished and is extremly error prone. I don't know if we should support one of those workarounds.

        Show
        Steve Müller added a comment - - edited Obviously SQL Server doesn't support this. So what solution would you expect here? IMO such a scenario should be avoided as there is no reasonable way to deal with changing identity columns. I guess most people are not even aware of this. The solutions available are very risky (creating new schema and then migrating data for example) and can take very long to be finished and is extremly error prone. I don't know if we should support one of those workarounds.
        Hide
        Flip added a comment -

        The bug is about the SQL statement that does not work.
        1. One solution could be to prevent generating this statement and issue a warning instead. (not preferred)

        Two other solutions are mentioned in that Stackoverflow post I mentioned, namely:
        2. Create a new table with identity & drop the existing table
        3. Create a new column with identity & drop the existing column

        That is just the way things have to be done in SQL Server (unfortunately), so it wouldn't be a workaround. I think this is a very reasonable way and in fact similar solutions to similar problems have already been implemented into Doctrine. See http://www.doctrine-project.org/blog/doctrine-2-4-released.html "ALTER TABLE support for SQLite (by hason) by creating new tables, moving all the data and then renaming."

        Show
        Flip added a comment - The bug is about the SQL statement that does not work. 1. One solution could be to prevent generating this statement and issue a warning instead. (not preferred) Two other solutions are mentioned in that Stackoverflow post I mentioned, namely: 2. Create a new table with identity & drop the existing table 3. Create a new column with identity & drop the existing column That is just the way things have to be done in SQL Server (unfortunately), so it wouldn't be a workaround. I think this is a very reasonable way and in fact similar solutions to similar problems have already been implemented into Doctrine. See http://www.doctrine-project.org/blog/doctrine-2-4-released.html "ALTER TABLE support for SQLite (by hason) by creating new tables, moving all the data and then renaming."
        Hide
        Benjamin Eberlei added a comment -

        Flip not many people actually use Sqlite in production, its more of a "play database" with Doctrine, wheras SQL Server carries much more weigh. I don't want to implement this kind of workaround, Steve Müller what do you think is safe here?

        Show
        Benjamin Eberlei added a comment - Flip not many people actually use Sqlite in production, its more of a "play database" with Doctrine, wheras SQL Server carries much more weigh. I don't want to implement this kind of workaround, Steve Müller what do you think is safe here?
        Hide
        Flip added a comment -

        Not to argue .. but it's not a workaround

        Really .. if it is ever implemented it would have to be in a few steps because SQL Server does not accept this one-line command like that. So basically when you don't want this solution it means that will never be implemented.

        Talking about safe .. mind that the safety should also be ensured in other area's like 1. don't run --force on a production database 2. make backups. So when regarding the safety it's a very narrow area to take a look at. The operation could be setup in such a way that if the new table/column could not be created then the old one doesn't get deleted. That way you have always the safety like a database transaction.

        I guess no point in making a Proof of Concept, because the general outline of the problem is actually not that complicated ..

        Thx guys for the input on this issue.

        Show
        Flip added a comment - Not to argue .. but it's not a workaround Really .. if it is ever implemented it would have to be in a few steps because SQL Server does not accept this one-line command like that. So basically when you don't want this solution it means that will never be implemented. Talking about safe .. mind that the safety should also be ensured in other area's like 1. don't run --force on a production database 2. make backups. So when regarding the safety it's a very narrow area to take a look at. The operation could be setup in such a way that if the new table/column could not be created then the old one doesn't get deleted. That way you have always the safety like a database transaction. I guess no point in making a Proof of Concept, because the general outline of the problem is actually not that complicated .. Thx guys for the input on this issue.
        Hide
        Steve Müller added a comment -

        Benjamin Eberlei we could of course use one of the approaches Flip mentioned, but the question is what to do in case something goes wrong during one of the steps required to alter an IDENTITY column. If it is acceptable that there exist "dead" tables/columns in case something goes wrong, we could try implementing that.

        Show
        Steve Müller added a comment - Benjamin Eberlei we could of course use one of the approaches Flip mentioned, but the question is what to do in case something goes wrong during one of the steps required to alter an IDENTITY column. If it is acceptable that there exist "dead" tables/columns in case something goes wrong, we could try implementing that.

          People

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

            Dates

            • Created:
              Updated: