Problem with liquibase when use tag command on informix

General Discussion
1

hi all,

i am getting the error as shown below when i try to tag the informix database using liquibase command. have anyone come into this issue before? please advise how to resolve this.

Thanks in advance.

and btw, i am using informix version 10

Liquibase Update Failed: Error executing SQL UPDATE DATABASECHANGELOG SET TAG =
‘2’ WHERE DATEEXECUTED = (SELECT MAX(DATEEXECUTED) FROM DATABASECHANGELOG)
Sep 8, 2010 8:59:27 AM liquibase.logging.jvm.JavaUtilLogger info
INFO: Error executing SQL UPDATE DATABASECHANGELOG SET TAG = ‘2’ WHERE DATEEXECU
TED = (SELECT MAX(DATEEXECUTED) FROM DATABASECHANGELOG)
liquibase.exception.DatabaseException: liquibase.exception.DatabaseException: Er
ror executing SQL UPDATE DATABASECHANGELOG SET TAG = ‘2’ WHERE DATEEXECUTED = (S
ELECT MAX(DATEEXECUTED) FROM DATABASECHANGELOG)
        at liquibase.database.AbstractDatabase.tag(AbstractDatabase.java:632)
        at liquibase.Liquibase.tag(Liquibase.java:507)
        at liquibase.integration.commandline.Main.doMigration(Main.java:631)
        at liquibase.integration.commandline.Main.main(Main.java:105)
Caused by: liquibase.exception.DatabaseException: Error executing SQL UPDATE DAT
ABASECHANGELOG SET TAG = ‘2’ WHERE DATEEXECUTED = (SELECT MAX(DATEEXECUTED) FROM
DATABASECHANGELOG)
        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:62)
        at liquibase.executor.jvm.JdbcExecutor.update(JdbcExecutor.java:222)
        at liquibase.executor.jvm.JdbcExecutor.update(JdbcExecutor.java:199)
        at liquibase.database.AbstractDatabase.tag(AbstractDatabase.java:624)
        … 3 more
Caused by: java.sql.SQLException: Cannot modify table or view used in subquery.
        at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:373)
        at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3175)
        at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3484)
        at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2328)
        at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2244)
        at com.informix.jdbc.IfxSqli.executeCommand(IfxSqli.java:766)
        at com.informix.jdbc.IfxResultSet.b(IfxResultSet.java:291)
        at com.informix.jdbc.IfxStatement.c(IfxStatement.java:1253)
        at com.informix.jdbc.IfxStatement.b(IfxStatement.java:407)
        at com.informix.jdbc.IfxStatement.executeUpdate(IfxStatement.java:261)
        at liquibase.executor.jvm.JdbcExecutor$1UpdateStatementCallback.doInStat
ement(JdbcExecutor.java:214)
        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:55)
        … 6 more
Caused by: java.sql.SQLException
        at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:373)
        at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3489)
        … 15 more

2

Do you know if there is a good workaround in informix to let you do a call like in the exception? (UPDATE DAT
ABASECHANGELOG SET TAG = ‘2’ WHERE DATEEXECUTED = (SELECT MAX(DATEEXECUTED) FROM
DATABASECHANGELOG))

Nathan

3

Hi Nathan,

as for my understanding so far,Dbms which support subquery are SQL Server, Oracle and PostgreSQL and not all the dbms support the subquery and will get the “Cannot modify table or view used in subquery error”.

for the current moment, what i can think of is create a temp table and  update the original table based on the temp table.

do u have any other workaround which do not required modifying any of the source code? please advise.

4
Originally posted by: Nathan
Do you know if there is a good workaround in informix to let you do a call like in the exception? (UPDATE DAT ABASECHANGELOG SET TAG = '2' WHERE DATEEXECUTED = (SELECT MAX(DATEEXECUTED) FROM DATABASECHANGELOG))

Nathan

Hi, Nathan; I’ll ask around at my shop (we’re heavy-duty Informix, but I, alas, am not).  Hope I can help you guys out.

Best,
Laird

5

Informix version 10 doesn’t allow for the modification of the same table used in a subquery. Version 11 however does let you. The best work around is to use a temp table. However, note that IBM is ending support for version 10 at the end of this month (Sept. 30, 2010). So you may want to work with Version 11.

Anyway, here is the work around:

    SELECT MAX(dateexecuted) max_date FROM databasechangelog INTO TEMP max_date_temp WITH NO LOG; UPDATE databasechangelog SET TAG = '2' WHERE DATEEXECUTED = (SELECT max_date FROM max_date_temp);

The ‘WITH NO LOG’ is optional, I just always use that to prevent logging in our transaction logs (though some people may have logging turned off).

Let me know if you need any other informix info. I work with Laird, and am a heavy informix user.

  • Dan
6

Thanks for the SQL, starting with 2.0 RC6, all informix databases should use it for tagging the database.  I didn’t separate out version 10 in case version 11 ends up with similar issues.

Nathan