# Invalid Informix Dialect? **URL:** \ **Category:** General Discussion\ **Created:** [December 10, 2011, 1:54am UTC](https://forum.liquibase.org/t/invalid-informix-dialect/1760 "2011-12-10T01:54:00Z")\ **Posts on this page:** 16\ **Page:** 1
**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forum.liquibase.org/u/Guest)\ **Post date:** [December 10, 2011, 1:54am UTC](https://forum.liquibase.org/t/invalid-informix-dialect/1760/1 "2011-12-10T01:54:00Z")
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 ---
**Author:** ![ljnelson](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.liquibase.org/ljnelson/32/120_2.png) [@ljnelson](https://forum.liquibase.org/u/ljnelson)\ **Post date:** [December 10, 2011, 1:54am UTC](https://forum.liquibase.org/t/invalid-informix-dialect/1760/2 "2011-12-10T01:54:00Z")
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](http://about.me/lairdnelson) ---
**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forum.liquibase.org/u/Guest)\ **Post date:** [December 10, 2011, 1:54am UTC](https://forum.liquibase.org/t/invalid-informix-dialect/1760/3 "2011-12-10T01:54:00Z")
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? ---
**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forum.liquibase.org/u/Guest)\ **Post date:** [December 10, 2011, 1:54am UTC](https://forum.liquibase.org/t/invalid-informix-dialect/1760/4 "2011-12-10T01:54:00Z")
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.             \ **Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forum.liquibase.org/u/Guest)\ **Post date:** [December 10, 2011, 1:54am UTC](https://forum.liquibase.org/t/invalid-informix-dialect/1760/5 "2011-12-10T01:54:00Z") Or alternatively: 1.  if (database instanceof InformixDatabase && \ **Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forum.liquibase.org/u/Guest)\ **Post date:** [December 10, 2011, 1:54am UTC](https://forum.liquibase.org/t/invalid-informix-dialect/1760/6 "2011-12-10T01:54:00Z") 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 ---
**Author:** ![ljnelson](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.liquibase.org/ljnelson/32/120_2.png) [@ljnelson](https://forum.liquibase.org/u/ljnelson)\ **Post date:** [December 10, 2011, 1:54am UTC](https://forum.liquibase.org/t/invalid-informix-dialect/1760/7 "2011-12-10T01:54:00Z")
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](http://about.me/lairdnelson) ---
**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forum.liquibase.org/u/Guest)\ **Post date:** [December 10, 2011, 1:54am UTC](https://forum.liquibase.org/t/invalid-informix-dialect/1760/8 "2011-12-10T01:54:00Z")
(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 ---
**Author:** ![caleb.powell](https://avatars.discourse-cdn.com/v4/letter/c/7c8e57/32.png) [@caleb.powell](https://forum.liquibase.org/u/caleb.powell)\ **Post date:** [December 10, 2011, 1:54am UTC](https://forum.liquibase.org/t/invalid-informix-dialect/1760/9 "2011-12-10T01:54:00Z")
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. ---
**Author:** ![caleb.powell](https://avatars.discourse-cdn.com/v4/letter/c/7c8e57/32.png) [@caleb.powell](https://forum.liquibase.org/u/caleb.powell)\ **Post date:** [December 10, 2011, 1:54am UTC](https://forum.liquibase.org/t/invalid-informix-dialect/1760/10 "2011-12-10T01:54:00Z")
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. ---
**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forum.liquibase.org/u/Guest)\ **Post date:** [December 10, 2011, 1:54am UTC](https://forum.liquibase.org/t/invalid-informix-dialect/1760/11 "2011-12-10T01:54:00Z")
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 ---
**Author:** ![nvoxland](https://avatars.discourse-cdn.com/v4/letter/n/87869e/32.png) [@nvoxland](https://forum.liquibase.org/u/nvoxland)\ **Post date:** [December 10, 2011, 1:54am UTC](https://forum.liquibase.org/t/invalid-informix-dialect/1760/12 "2011-12-10T01:54:00Z")
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 ---
**Author:** ![ljnelson](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.liquibase.org/ljnelson/32/120_2.png) [@ljnelson](https://forum.liquibase.org/u/ljnelson)\ **Post date:** [December 10, 2011, 1:54am UTC](https://forum.liquibase.org/t/invalid-informix-dialect/1760/13 "2011-12-10T01:54:00Z")
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](http://about.me/lairdnelson) ---
**Author:** ![nvoxland](https://avatars.discourse-cdn.com/v4/letter/n/87869e/32.png) [@nvoxland](https://forum.liquibase.org/u/nvoxland)\ **Post date:** [December 10, 2011, 1:54am UTC](https://forum.liquibase.org/t/invalid-informix-dialect/1760/14 "2011-12-10T01:54:00Z")
Thanks for looking into it. I dont’ use informix enough to be able to to troubleshoot it well. Nathan ---
**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forum.liquibase.org/u/Guest)\ **Post date:** [December 10, 2011, 1:54am UTC](https://forum.liquibase.org/t/invalid-informix-dialect/1760/15 "2011-12-10T01:54:00Z")
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](mailto:ivaylo5ev@gmail.com) Ivaylo Slavov ---
**Author:** ![lbkatz](https://avatars.discourse-cdn.com/v4/letter/l/96bed5/32.png) [@lbkatz](https://forum.liquibase.org/u/lbkatz)\ **Post date:** [December 10, 2011, 1:54am UTC](https://forum.liquibase.org/t/invalid-informix-dialect/1760/16 "2011-12-10T01:54:00Z")
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?