Delete column with default value at Derby DB and MSSQL 2005
Description
Environment
Derby DB and MSSQL 2005
Activity
Nathan Voxland December 12, 2013 at 9:08 PM
This is the same issue as CORE-1296: dropping columns with default values in sqlserver by default need an extra step.
The easiest way to handle database differences is to use the dbms attribute on the changeSet tag. That way you can easily target the dropDefaultValue changes by using dbms="mssql"
Michal Sabo December 11, 2013 at 11:40 AM
Trying to drop default value constraint on SQL Server throws the following exception:
SEVERE 12/11/13 11:14 AM:liquibase: db/migrations/shell-my.master.changelog.test.xml: db/migrations/common/5.1.2.changelog.xml::3::riha: Change Set db/migrations/common/5.1.2.changelog.xml::3::riha failed. Error: Error executing SQL ALTER TABLE [dbo].[TerminalVariable] DROP CONSTRAINT select d.name from syscolumns c,sysobjects d, sysobjects t where c.id=t.id AND d.parent_obj=t.id AND d.type='D' AND t.type='U' AND c.name='DF_TerminalVariable_sendReversalOnCBA' AND t.name='TerminalVariable': Incorrect syntax near the keyword 'select'.
liquibase.exception.DatabaseException: Error executing SQL ALTER TABLE [dbo].[TerminalVariable] DROP CONSTRAINT select d.name from syscolumns c,sysobjects d, sysobjects t where c.id=t.id AND d.parent_obj=t.id AND d.type='D' AND t.type='U' AND c.name='DF_TerminalVariable_sendReversalOnCBA' AND t.name='TerminalVariable': Incorrect syntax near the keyword 'select'.
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:57)
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:103)
at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1513)
at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1496)
at liquibase.changelog.ChangeSet.execute(ChangeSet.java:343)
at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:40)
at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:64)
at liquibase.Liquibase.update(Liquibase.java:137)
at liquibase.integration.commandline.Main.doMigration(Main.java:864)
at liquibase.integration.commandline.Main.main(Main.java:133)
Caused by: java.sql.SQLException: Incorrect syntax near the keyword 'select'.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:584)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:546)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeImpl(JtdsStatement.java:723)
at net.sourceforge.jtds.jdbc.JtdsStatement.execute(JtdsStatement.java:1160)
at liquibase.executor.jvm.JdbcExecutor$1ExecuteStatementCallback.doInStatement(JdbcExecutor.java:90)
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:50)
... 9 more
Michal Sabo December 11, 2013 at 11:38 AM
I have to add that precondition check fails on SQL Server and throws the following exception:
db/migrations/common/5.1.2.changelog.xml::3::riha failed. Error: Migration failed for change set db/migrations/common/5.1.2.changelog.xml::3::riha:
Reason:
db/migrations/caltex.master.changelog.test.xml : liquibase.precondition.core.ForeignKeyExistsPrecondition@9c527a : null
liquibase.exception.MigrationFailedException: Migration failed for change set db/migrations/common/5.1.2.changelog.xml::3::riha:
Reason:
db/migrations/caltex.master.changelog.test.xml : liquibase.precondition.core.ForeignKeyExistsPrecondition@9c527a : null
at liquibase.changelog.ChangeSet.execute(ChangeSet.java:310)
at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:40)
at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:64)
at liquibase.Liquibase.update(Liquibase.java:137)
at liquibase.integration.commandline.Main.doMigration(Main.java:864)
at liquibase.integration.commandline.Main.main(Main.java:133)
Caused by: liquibase.exception.PreconditionErrorException: Precondition Error
at liquibase.precondition.core.ForeignKeyExistsPrecondition.check(ForeignKeyExistsPrecondition.java:79)
at liquibase.precondition.core.AndPrecondition.check(AndPrecondition.java:37)
at liquibase.precondition.core.PreconditionContainer.check(PreconditionContainer.java:199)
at liquibase.changelog.ChangeSet.execute(ChangeSet.java:274)
... 5 more
Caused by: java.lang.NullPointerException
at liquibase.structure.core.Table.hashCode(Table.java:50)
at liquibase.structure.core.ForeignKey.hashCode(ForeignKey.java:179)
at java.util.HashMap.hash(HashMap.java:351)
at java.util.HashMap.put(HashMap.java:471)
at java.util.HashSet.add(HashSet.java:217)
at liquibase.snapshot.DatabaseSnapshot.include(DatabaseSnapshot.java:97)
at liquibase.snapshot.DatabaseSnapshot.<init>(DatabaseSnapshot.java:34)
at liquibase.snapshot.JdbcDatabaseSnapshot.<init>(JdbcDatabaseSnapshot.java:20)
at liquibase.snapshot.SnapshotGeneratorFactory.createSnapshot(SnapshotGeneratorFactory.java:125)
at liquibase.snapshot.SnapshotGeneratorFactory.createSnapshot(SnapshotGeneratorFactory.java:133)
at liquibase.snapshot.SnapshotGeneratorFactory.has(SnapshotGeneratorFactory.java:90)
at liquibase.precondition.core.ForeignKeyExistsPrecondition.check(ForeignKeyExistsPrecondition.java:73)
... 8 more
In first changeSet
<addColumn tableName="Terminal">
<column name="send" type="tinyint" defaultValueNumeric="0" />
</addColumn>
and next changeSet, try to drop this column
<dropColumn columnName="send" tableName="Terminal"/>
at Derby DB is no problem but at MS SQL 2005 addColumn with default value create some foreign key "DF_Terminal_send" and when a start liquibase at MSSQL it's faild and I must use
<dropForeignKeyConstraint baseTableName="Terminal" constraintName="DF_Terminal_send"/>
but this fail at Derby DB because there is no foreign key so I try use preConditions
<foreignKeyConstraintExists foreignKeyName="DF_Terminal_send" foreignKeyTableName="Terminal" />
but this fail in Derby DB and in MS SQL not found foreign key DF_Terminal_send
so I try use at MSSQL
<dropDefaultValue columnName="send" tableName="Terminal"/>
but this aslo fail at Incorrect syntax near the keyword 'select'.
Please help me and fix this.
Thank you.