Doctrine 1
  1. Doctrine 1
  2. DC-650

SoftDelete sets "default" => null for deleted_at field, causing MSSQL to silently create a Default Constraint which in turns causes Migrations to fail when reverting

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.2.0-ALPHA1, 1.2.0-ALPHA2, 1.2.0-ALPHA3, 1.2.0-BETA1, 1.2.0-BETA2, 1.2.0-BETA3, 1.2.0-RC1, 1.2.0, 1.2.1, 1.2.2, 1.2.3
    • Fix Version/s: 1.2.3
    • Component/s: Behaviors
    • Labels:
      None
    • Environment:
      PHP 5.2.11, Windows 7, Sql Server 2005, php_mssql extension

      Description

      When MSSQL receives even just a default value of null it still creates a default constraint. Said constraint prevents doctrine from removing the deleted_at field in migrations (due to the dependency on the constraint).

      Removing "default" => null prevents the silent creation of the constraint.

      I have no machine to test the effects of this on MySQL. I would imagine that it would not materially affect MySQL as the default values are never used anyways (hence the default value of null).

      Attached is a patch to fix the behavior (for rev 7544 in /branches/1.2)

      If one is running into problems with migrations being unable to move backwards due to default constraints on SoftDelete columns, one can run the following T-SQL script to remove all default value constraints from a database (EVEN THOSE YOU SET MANUALLY, USE WITH CAUTION):

      {{
      – This script removes ALL default constraints

      USE YOURDATABASENAMEHERE;

      Declare @name nvarchar(155)
      Declare @table nvarchar(155)
      Declare @sql nvarchar(1000)

      – find constraint names
      DECLARE default_constraints CURSOR FOR
      SELECT
      object.name,
      parent.name
      FROM
      sys.objects AS object
      LEFT JOIN sys.objects AS parent ON object.parent_object_id = parent.object_id
      WHERE
      object.type_desc LIKE '%CONSTRAINT'
      AND object.type_desc LIKE 'DEFAULT_CONSTRAINT'

      OPEN default_constraints

      FETCH NEXT FROM default_constraints INTO @name, @table

      WHILE @@FETCH_STATUS = 0
      BEGIN
      IF NOT @name IS NULL
      BEGIN
      SELECT @sql = 'ALTER TABLE [' + @table + '] DROP CONSTRAINT [' + @name + '];'
      --PRINT @sql
      EXECUTE sp_executesql @sql
      END

      FETCH NEXT FROM default_constraints INTO @name, @table
      END
      CLOSE default_constraints
      DEALLOCATE default_constraints
      }}

        Activity

        Hide
        Daniel Cousineau added a comment -

        I should also note this affects not only rollbacks, but any sort of ability to drop a deleted_at column (say, removing the SoftDelete behavior)

        Show
        Daniel Cousineau added a comment - I should also note this affects not only rollbacks, but any sort of ability to drop a deleted_at column (say, removing the SoftDelete behavior)
        Hide
        Craig Marvelley added a comment -

        This is related to an issue I reported a month or so ago - http://www.doctrine-project.org/jira/browse/DC-584. A solution I proposed there was to allow Doctrine to name constraints so they can be referenced and dropped later. If that were in place, the SoftDelete behaviour could manage the constraint itself?

        Show
        Craig Marvelley added a comment - This is related to an issue I reported a month or so ago - http://www.doctrine-project.org/jira/browse/DC-584 . A solution I proposed there was to allow Doctrine to name constraints so they can be referenced and dropped later. If that were in place, the SoftDelete behaviour could manage the constraint itself?
        Hide
        Daniel Cousineau added a comment -

        Craig, It maybe would if Doctrine were creating the default constraints itself. If Doctrine doesn't handle the default constraints then naming has no effect as MSSQL will silently create said constraint.

        And now that I think of it, this issue is going to crop up any and every time you use default values in MSSQL. Perhaps it would be best to consider this ticket more of a cleanup (the behavior isn't using default values so there's no point in creating the constraint anyways) and side effect of the problems listed in DC-584 which should be the primary focus.

        Show
        Daniel Cousineau added a comment - Craig, It maybe would if Doctrine were creating the default constraints itself. If Doctrine doesn't handle the default constraints then naming has no effect as MSSQL will silently create said constraint. And now that I think of it, this issue is going to crop up any and every time you use default values in MSSQL. Perhaps it would be best to consider this ticket more of a cleanup (the behavior isn't using default values so there's no point in creating the constraint anyways) and side effect of the problems listed in DC-584 which should be the primary focus.

          People

          • Assignee:
            Jonathan H. Wage
            Reporter:
            Daniel Cousineau
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: