Skip to content

Loading…

DBAL-825: ALTER COLUMN on mssql is failing if default constraint is attached #2059

Closed
doctrinebot opened this Issue · 7 comments

2 participants

@doctrinebot

Jira issue originally created by user deepdiver:

Here is the unit test - implemented in class SchemaManagerFunctionalTestCase

    public function testChangeColumnsTypeWithDefault()
    {
        $table = new \Doctrine\DBAL\Schema\Table('column*change_type*test');
        $table->addColumn('id', 'integer', array('default' => 5));

        $this->_sm->createTable($table);

        $columns = $this->*sm->listTableColumns("column_change_type*test");
        $this->assertEquals(1, count($columns));
        $this->assertInstanceOf('Doctrine\DBAL\Types\IntegerType', $columns['id']->getType());

        $tableDiff = new \Doctrine\DBAL\Schema\TableDiff('column*change_type*test');
        $tableDiff->changedColumns['id'] = new \Doctrine\DBAL\Schema\ColumnDiff(
            'id', new \Doctrine\DBAL\Schema\Column(
                'id', \Doctrine\DBAL\Types\Type::getType('smallint'), array('default' => 5)
            ),
            array('type'),
            new \Doctrine\DBAL\Schema\Column(
                'id', \Doctrine\DBAL\Types\Type::getType('integer'), array('default' => '5')
            )
        );

        $this->_sm->alterTable($tableDiff);

        $columns = $this->*sm->listTableColumns("column_change_type*test");
        $this->assertEquals(1, count($columns));
        $this->assertInstanceOf('Doctrine\DBAL\Types\SmallIntType', $columns['id']->getType());
        $this->assertSame('', $columns['id']->getDefault());
    }

Causes following result

Exception : [Doctrine\DBAL\DBALException] An exception occurred while executing 'ALTER TABLE column*change_type*test ALTER COLUMN id SMALLINT NOT NULL':

SQLSTATE [42000, 5074]: [Microsoft][SQL Server Native Client 11.0][SQL Server]The object 'DF*A74995E2*BF396750' is dependent on column 'id'.
SQLSTATE [42000, 4922]: [Microsoft][SQL Server Native Client 11.0][SQL Server]ALTER TABLE ALTER COLUMN id failed because one or more objects access this column.

With queries:
5. SQL: 'ALTER TABLE column*change_type*test ALTER COLUMN id SMALLINT NOT NULL' Params: 
4. SQL: 'SELECT    col.name,
                          type.name AS type,
                          col.max_length AS length,
                          ~col.is_nullable AS notnull,
                          def.definition AS [default],
                          col.scale,
                          col.precision,
                          col.is_identity AS autoincrement,
                          col.collation_name AS collation,
                          CAST(prop.value AS NVARCHAR(MAX)) AS comment -- CAST avoids driver error for sql_variant type
                FROM      sys.columns AS col
                JOIN      sys.types AS type
                ON        col.user*type_id = type.user_type*id
                JOIN      sys.objects AS obj
                ON        col.object*id = obj.object*id
                JOIN      sys.schemas AS scm
                ON        obj.schema*id = scm.schema*id
                LEFT JOIN sys.default_constraints def
                ON        col.default*object_id = def.object*id
                AND       col.object*id = def.parent_object*id
                LEFT JOIN sys.extended_properties AS prop
                ON        obj.object*id = prop.major*id
                AND       col.column*id = prop.minor*id
                AND       prop.name = 'MS_Description'
                WHERE     obj.type = 'U'
                AND       (obj.name = 'column*change_type_test' AND scm.name = SCHEMA*NAME())' Params: 
3. SQL: 'ALTER TABLE column*change_type_test ADD CONSTRAINT DF_A74995E2*BF396750 DEFAULT 5 FOR id' Params: 
2. SQL: 'CREATE TABLE column*change_type*test (id INT NOT NULL)' Params: 

Trace:
C:\projects\doctrine\dbal\lib\Doctrine\DBAL\Connection.php:988
C:\projects\doctrine\dbal\lib\Doctrine\DBAL\Schema\AbstractSchemaManager.php:971
C:\projects\doctrine\dbal\lib\Doctrine\DBAL\Schema\AbstractSchemaManager.php:612
C:\projects\doctrine\dbal\lib\Doctrine\DBAL\Schema\SQLServerSchemaManager.php:232
C:\projects\doctrine\dbal\tests\Doctrine\Tests\DBAL\Functional\Schema\SchemaManagerFunctionalTestCase.php:621
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestCase.php:976
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestCase.php:831
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestResult.php:648
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestCase.php:776
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestSuite.php:775
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestSuite.php:745
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\TextUI\TestRunner.php:349
C:\Program Files (x86)\PHP\v5.3\pear\PHPUnit\TextUI\Command.php:176
C:\Users\deepdiver\AppData\Local\Temp\ide-phpunit.php:268
C:\Users\deepdiver\AppData\Local\Temp\ide-phpunit.php:506

#0 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestCase.php(946): Doctrine\Tests\DbalFunctionalTestCase->onNotSuccessfulTest(Object(Doctrine\DBAL\DBALException))
#1 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestResult.php(648): PHPUnit*Framework*TestCase->runBare()
#2 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestCase.php(776): PHPUnit*Framework*TestResult->run(Object(Doctrine\Tests\DBAL\Functional\Schema\SQLServerSchemaManagerTest))
#3 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestSuite.php(775): PHPUnit*Framework_TestCase->run(Object(PHPUnit_Framework*TestResult))
#4 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestSuite.php(745): PHPUnit*Framework_TestSuite->runTest(Object(Doctrine\Tests\DBAL\Functional\Schema\SQLServerSchemaManagerTest), Object(PHPUnit_Framework*TestResult))
#5 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\TextUI\TestRunner.php(349): PHPUnit*Framework_TestSuite->run(Object(PHPUnit_Framework*TestResult), false, Array, Array, false)
#6 C:\Program Files (x86)\PHP\v5.3\pear\PHPUnit\TextUI\Command.php(176): PHPUnit*TextUI_TestRunner->doRun(Object(PHPUnit_Framework*TestSuite), Array)
#7 C:\Users\deepdiver\AppData\Local\Temp\ide-phpunit.php(268): PHPUnit*TextUI*Command->run(Array, true)
#8 C:\Users\deepdiver\AppData\Local\Temp\ide-phpunit.php(506): IDE*Base_PHPUnit_TextUI*Command::main()
#9 {main}

@doctrinebot

Comment created by deepdiver:

Here is the unit test: DeepDiver1975@5323830

@doctrinebot

Comment created by @deeky666:

[~deepdiver] Thanks for reporting. Which version of SQL Server is affected by this?

@doctrinebot

Comment created by deepdiver:

SQL Server 2012 Express Edition

@doctrinebot

Comment created by @deeky666:

Fixed in commit: 171a876

@doctrinebot

Issue was closed with resolution "Fixed"

@doctrinebot doctrinebot added the Bug label
@deeky666 deeky666 was assigned by doctrinebot
@doctrinebot doctrinebot added this to the 2.5 milestone
@doctrinebot doctrinebot closed this
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.