Invalid Informix Dialect?

Hi,


I am evaluating liquibase on Informix. My initial attempt to run liquibase fails to create the DATABASECHANGELOGLOCK table. It appears that Liquibase uses invalid Informix SQL statements. I have tried this using both the liquibase-maven-plugin and from the command line with the same result. The exact versions I am using are:

  • liquibase-core 2.0.3
  • liquibase-maven-plugin 2.0.3
  • Informix 10.00.TC1
  • java 1.6.0_26 (Windows 7)

On the command-line, I use the following arguments (I have tried with and without the --databaseClass arg):


  1. --driver=com.informix.jdbc.IfxDriver
  2. --databaseClass=liquibase.database.core.InformixDatabase
  3. --changeLogFile=src/main/resources/db.changelog.1.xml
  4. --url="jdbc:informix-sqli://HWNG006:1526/hw:informixserver=ol_hwng006;database=hw"
  5. --username=informix
  6. --password=password
  7. update


I always get the same error:



[ERROR] Error executing SQL CREATE TABLE DATABASECHANGELOGLOCK (ID INT NOT NULL PRIMARY KEY, LOCKED BOOLEAN NOT NULL PRIMARY KEY, LOCKGRANTED DATETIME YE

 TO FRACTION(5) PRIMARY KEY, LOCKEDBY VARCHAR(255) PRIMARY KEY); on jdbc:informix-sqli://HWNG006:1526/hw:informixserver=ol_hwng006;database=hwINSERT INTO

ATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, ‘f’): Primary key already exists on the table.

liquibase.exception.DatabaseException: Error executing SQL CREATE TABLE DATABASECHANGELOGLOCK (ID INT NOT NULL PRIMARY KEY, LOCKED BOOLEAN NOT NULL PRIMA

 KEY, LOCKGRANTED DATETIME YEAR TO FRACTION(5) PRIMARY KEY, LOCKEDBY VARCHAR(255) PRIMARY KEY); on jdbc:informix-sqli://HWNG006:1526/hw:informixserver=ol

wng006;database=hwINSERT INTO DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, ‘f’): Primary key already exists on the table.

        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:62)

        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:104)

        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:70)

        at liquibase.database.AbstractDatabase.checkDatabaseChangeLogLockTable(AbstractDatabase.java:564)

        at liquibase.Liquibase.checkDatabaseChangeLogTable(Liquibase.java:537)

        at liquibase.Liquibase.forceReleaseLocks(Liquibase.java:573)

        at org.liquibase.maven.plugins.AbstractLiquibaseMojo.cleanup(AbstractLiquibaseMojo.java:429)

        at org.liquibase.maven.plugins.AbstractLiquibaseMojo.execute(AbstractLiquibaseMojo.java:305)

        at org.apache.maven.plugin.DefaultPluginManager.executeMojo(DefaultPluginManager.java:490)

        at org.apache.maven.lifecycle.DefaultLifecycleExecutor.executeGoals(DefaultLifecycleExecutor.java:694)

        at org.apache.maven.lifecycle.DefaultLifecycleExecutor.executeStandaloneGoal(DefaultLifecycleExecutor.java:569)

        at org.apache.maven.lifecycle.DefaultLifecycleExecutor.executeGoal(DefaultLifecycleExecutor.java:539)

        at org.apache.maven.lifecycle.DefaultLifecycleExecutor.executeGoalAndHandleFailures(DefaultLifecycleExecutor.java:387)

        at org.apache.maven.lifecycle.DefaultLifecycleExecutor.executeTaskSegments(DefaultLifecycleExecutor.java:348)

        at org.apache.maven.lifecycle.DefaultLifecycleExecutor.execute(DefaultLifecycleExecutor.java:180)

        at org.apache.maven.DefaultMaven.doExecute(DefaultMaven.java:328)

        at org.apache.maven.DefaultMaven.execute(DefaultMaven.java:138)

        at org.apache.maven.cli.MavenCli.main(MavenCli.java:362)

        at org.apache.maven.cli.compat.CompatibleMain.main(CompatibleMain.java:60)


Has anyone seen this before? Is my configuration wrong, or is this a problem with the sql dialect being generated by liquibase.


Thanks,


Caleb Powell

That’s funny; we use Informix (11 in our case) as well and have not seen this.  Liquibase 2.0.1, but the changes between 2.0.1 and 2.0.3 are miniscule.

Best,
Laird


http://about.me/lairdnelson

Hi Laird,


liquibase seems to be generating the create statement with an invalid sql dialect. This is the SQL that liquibase tried to execute:

  1. CREATE TABLE DATABASECHANGELOGLOCK (
  2.     ID INT NOT NULL PRIMARY KEY, 
  3.     LOCKED BOOLEAN NOT NULL PRIMARY KEY, 
  4.     LOCKGRANTED DATETIME YEAR TO FRACTION(5) PRIMARY KEY, 
  5.     LOCKEDBY VARCHAR(255) PRIMARY KEY)


And this is what it should execute (or at least, this is what works. note that the primary key’s are defined at the end of the statement):

  1. CREATE TABLE DATABASECHANGELOGLOCK (
  2.     ID INT NOT NULL, 
  3.     LOCKED BOOLEAN NOT NULL, 
  4.     LOCKGRANTED DATETIME YEAR TO FRACTION(5), 
  5.     LOCKEDBY VARCHAR(255),  
  6.     PRIMARY KEY(ID, LOCKED, LOCKGRANTED, LOCKEDBY)) 

Do you have any special configuration parameters for Informix? I only have the liquibase-core library in my CLASSPATH… should I have any other libs?

So, I just debugged this and I think I see the problem. The if statement on line #104 of the liquibase.sqlgenerator.core.CreateTableGenerator class does not appear to be sufficient:


  1.  if (database instanceof InformixDatabase && isSinglePrimaryKeyColumn) {
  2.             <span class="Apple-tab-span");
  3.  }

Or alternatively:


  1.  if (database instanceof InformixDatabase && <b);
  2.  }


I tried running version 2.0 and I got the same result. Even when I create the tables manually, it has problems trying to update them. 


Caleb Powell

Interesting.  I wonder if Nathan changed the primary key information on the DATABASECHANGELOG table between Liquibase 2.0.1 and 2.0.3?  Our Informix stuff creates just fine with no changes needed.

L


http://about.me/lairdnelson

(The forums won’t let me authenticate; it times out on me.)

We use Informix 11.7; maybe for that database the syntax is OK?  To be fair, we haven’t run it from scratch in a while.

Best,
Laird

Hi,


I am a little confused as to how this could not have impacted anyone else? Did you run version 2.0.3 from scratch (forcing it to create new tables)? 


In any case, I cloned the repository and fixed the problem (I also added some unit tests). I will contact the lead developer to try and get the changes in.

Yeah, we tried this on Informix 11.50.TC6DE and got the same failure (Error Code -704; Primary Key already exists on the table). To be clear, the table did not exist prior to use running this change. As a result, I don’t believe the SQL generated by the CreateTableGenerator class is correct. You might want to try running this from scratch yourself to confirm. 


Anyways, as I mentioned, I downloaded the project and fixed the bug. I have sent an email to Nathan offering him the patch.

I am having the same issue running informix with liquibase 2.0.3. Could it be possible to send me url to the fixed version, or some detailed information on how to fix it myself?

Thanks in advance
Ivaylo Slavov

I don’t think I ever got a patch for this. Does anyone know what the root cause and/or have a tested fix they could send?

Nathan

We just hit this on an Informix 11.70 database.  We have successfully used Liquibase 2.0.3 on various Informix 11.5 databases.  I’ll let the list know what we find.


Best,

Laird


http://about.me/lairdnelson

Thanks for looking into it. I dont’ use informix enough to be able to to troubleshoot it well.

Nathan

Can you confirm that? I have recently patched the 2.0.3 version so that we could migrate a project at work from postgresql to informix 11.70. Having read about some informix specific stuff in order to fix the sql queries liquibase produces, I have not encountered such significant differences between 11.50 and 11.70 (but this is subjective, since my task was to make it work on 11.70).

There have been mainly issues with:
 - boolean datatype (that required ‘t’ and ‘f’) which was not supported
 - multiple primary key columns in create table statement query were not properly defined,
 - the databasechangelog’s composite primary key exceeded informix’s default PK size
 - create view statement did not work.

I believe the same issues to be present also in 11.50. If not, please let me know, because I am sending a pull request with my fixes to github, and I would most certainly not be happy to break the support for 11.50 or other earlier versions.

My email is ivaylo5ev@gmail.com
Ivaylo Slavov

This issue is appearing in version 2.0.5. I was running with 2.0.1 and was able to run with Informix 11.70. When I upgraded to 2.0.5, I got the error.I had to revert to 2.0.1 and I’m running fine again.


Can you make sure the fix is in the latest version?