Hello,
in the new version 3.0.4 there seems to be a problem when trying to run(update) changesets containing <addColumn…
I have tried different changesets created by generateChangeLog but they all failed. I hope i am doing something wrong because on another note:
this is the best version of liquibase yet!!
In earlier versions i always had trouble with not recognized constraints, but in this version generateChangeLog works perfectly!
Thx
Correction!
the aforementioned changesets were created by diffDatabaseToChangeLog, not generateChangeLog.
sorry
Good to hear the 3.x version is generally working better for you.
Can you send more information on the failing changeset and what the error is?
Nathan
I am using a db2 database.
I have tried different changesets, but the error occurs with all changesets that look like this:
And this is always the error:
INFO 20.09.13 10:32:liquibase: Successfully acquired change log lock
SEVERE 20.09.13 10:32:liquibase: Change Set […]::Administrator (generated) failed. Error: Error executing SQL ALTER TABLE [schema].[table] ADD [column] VARCHAR(200) NOT NULL: DB2 SQL error: SQLCODE: -193, SQLSTATE: 42601, SQLERRMC: [column]
liquibase.exception.DatabaseException: Error executing SQL ALTER TABLE [schema].[table] ADD [column] VARCHAR(200) NOT NULL: DB2 SQL error: SQLCODE: -193, SQLSTATE: 42601, SQLERRMC: [column]
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:56)
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:98)
at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1413)
at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1397)
at liquibase.changelog.ChangeSet.execute(ChangeSet.java:343)
at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:28)
at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:58)
at liquibase.Liquibase.update(Liquibase.java:135)
at liquibase.integration.ant.DatabaseUpdateTask.executeWithLiquibaseClassloader(DatabaseUpdateTask.java:45)
at liquibase.integration.ant.BaseLiquibaseTask.execute(BaseLiquibaseTask.java:70)
at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:291)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:48)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:600)
at org.apache.tools.ant.dispatch.DispatchUtils.execute(DispatchUtils.java:106)
at org.apache.tools.ant.Task.perform(Task.java:348)
at org.apache.tools.ant.Target.execute(Target.java:390)
at org.apache.tools.ant.Target.performTasks(Target.java:411)
at org.apache.tools.ant.Project.executeSortedTargets(Project.java:1399)
at org.apache.tools.ant.Project.executeTarget(Project.java:1368)
at org.apache.tools.ant.helper.DefaultExecutor.executeTargets(DefaultExecutor.java:41)
at org.eclipse.ant.internal.launching.remote.EclipseDefaultExecutor.executeTargets(EclipseDefaultExecutor.java:32)
at org.apache.tools.ant.Project.executeTargets(Project.java:1251)
at org.eclipse.ant.internal.launching.remote.InternalAntRunner.run(InternalAntRunner.java:424)
at org.eclipse.ant.internal.launching.remote.InternalAntRunner.main(InternalAntRunner.java:138)
Caused by: com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -193, SQLSTATE: 42601, SQLERRMC: [column]
at com.ibm.db2.jcc.b.nh.c(nh.java:1669)
at com.ibm.db2.jcc.b.nh.d(nh.java:1657)
at com.ibm.db2.jcc.b.nh.b(nh.java:1168)
at com.ibm.db2.jcc.a.db.h(db.java:219)
at com.ibm.db2.jcc.a.db.b(db.java:46)
at com.ibm.db2.jcc.a.t.b(t.java:40)
at com.ibm.db2.jcc.a.sb.e(sb.java:121)
at com.ibm.db2.jcc.b.nh.l(nh.java:1163)
at com.ibm.db2.jcc.b.nh.a(nh.java:1941)
at com.ibm.db2.jcc.b.nh.e(nh.java:736)
at com.ibm.db2.jcc.b.nh.execute(nh.java:720)
at liquibase.executor.jvm.JdbcExecutor$1ExecuteStatementCallback.doInStatement(JdbcExecutor.java:86)
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:49)
… 25 more
INFO 20.09.13 10:32:liquibase: Successfully released change log lock
BUILD FAILED
\build.xml:102: liquibase.exception.MigrationFailedException: Migration failed for change set
[…] Administrator (generated):
Reason: liquibase.exception.DatabaseException: Error executing SQL ALTER TABLE [schema].[table] ADD [column] VARCHAR(200) NOT NULL: DB2 SQL error: SQLCODE: -193, SQLSTATE: 42601, SQLERRMC: [column]
Total time: 15 seconds
It looks like the problem is because you are adding a column that is not null. Add the column without the null constraint, make sure all rows are not null, then add the not null constraint.
Nathan
You were partly right. The null constraint was the problem in general, but in this case it was caused by a condtion that is ignored by diffDatabaseToChangeLog.
The original statement was like this:
[column] VARCHAR(80) NOT NULL GENERATED ALWAYS AS (UPPER([column2]))
The new stament ignored the part which generated the input of the column, therefore it looked like this:
[column] VARCHAR(80) NOT NULL
Since the value of the column is not auto-generated anymore, liquibase could not create the column.
Is there a solution? Are you even aware of this problem?
Well at least I know why there were problems with addColumn in general.
Thank you!
Btw, [column] and [column2] are both regular non-nullable columns in the same table, not primary keys or anything. Just in case you want to track that problem. When I searched for it I found, that there had been a similar problem which was never resolved in CORE-897 (Autoincrement column != primary key). Only that this time it is not autoincremented, but it is autogenerated.
Yes, to your statement.
The example would be this:
CREATE TABLE
TEST
(
TEST_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
TEST_PORTALUID VARCHAR(80) NOT NULL,
TEST_LOGINID VARCHAR(80) NOT NULL GENERATED ALWAYS AS (UPPER(TEST_PORTALUID)),
CONSTRAINT TEST_P PRIMARY KEY (TEST_ID),
CONSTRAINT TEST_U1 UNIQUE (TEST_LOGINID)
);
What diffChangelog creates looks like this:
CREATE TABLE
TEST
(
TEST_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
TEST_PORTALUID VARCHAR(80) NOT NULL,
TEST_LOGINID VARCHAR(80) NOT NULL,
CONSTRAINT TEST_P PRIMARY KEY (TEST_ID),
CONSTRAINT TEST_U1 UNIQUE (TEST_LOGINID)
);
So you did a diff between two databases, and one had an extra column that is not null and has a default value defined of "GENERATED ALWAYS AS (UPPER([column2])) default value?
Do you have an example create table statement that exposes the problem with diffChangeLog?
Nathan
Thanks, that helps. I created https://liquibase.jira.com/browse/CORE-1466 to track the bug. For now you will have to edit the generated changelog to add in the GENERATED logic.
Nathan
You may be able to use defaultValueComputed="Nathan
Could you tell me how I do that?
I have yet to find a way to edit the changelog without deleting the table first and inserting a sql statement to re-create the table.
I was hoping for an attribute in the changeset. Maybe: valueComputed=“UPPER(TEST_PORTALUID)”.
Well thanks anyway! Since the bug is now tracked, hopefully it will be resolved in the near future.
