liquibase.exception.DatabaseException: Primary key already exists on the table. [Failed SQL: ALTER TABLE jxclean351:ngp.military_service ADD CONSTRAINT PRIMARY KEY (id) CONSTRAINT pk_military_service]

JIRA | Lyle H. Gray | 3 months ago
  1. 0

    Attempting to use <foreignKeyConstraintExists> precondition against an Informix database causes Liquibase 3.5.1 to crash. Executing the following series of changesets against an Informix 12.10 database {code} <changeSet id="ngp-constituent-jpa.country_type.NewTableCreation" author="lgray"> <createTable tableName="country_type"> <column name="id" type="bigint"> <constraints nullable="false"/> </column> <column name="value" type="varchar(100"> <constraints nullable="false"/> </column> </createTable> <addPrimaryKey tableName="country_type" columnNames="id" constraintName="pk_country_type"/> </changeSet> <changeSet id="ngp-constituent-jpa.military_service.NewTableCreation" author="lgray"> <createTable tableName="military_service"> <column name="id" type="bigint"> <constraints nullable="false"/> </column> <column name="country_type_id" type="bigint"> <constraints nullable="false" references="country_type(id)" foreignKeyName="fk_military_service_country_type"/> </column> </createTable> <addPrimaryKey tableName="military_service" columnNames="id" constraintName="pk_military_service"/> <createIndex tableName="military_service" indexName="idx_mil_country_type_id"> <column name="country_type_id"/> </createIndex> </changeSet> <changeSet id="ngp-constituent-jpa.military_server.DropForeignKey" author="lgray"> <preConditions onFail="MARK_RAN"> <foreignKeyConstraintExists foreignKeyName="fk_military_service_country_type" foreignKeyTableName="candidacy_student_export"/> </preConditions> <dropForeignKeyConstraint constraintName="fk_military_service_country_type" baseTableName="military_service"/> </changeSet> {code} will cause Liquibase 3.5.1 to crash with the following error message and stack: {code} SEVERE 9/21/16 2:33 PM: liquibase: com.jenzabar.ngp:ngp-test-jpa::ngp-constituent-jpa.country_type.NewTableCreation::lgray: A syntax error has occurred. [Failed SQL: CREATE TABLE jxclean351:ngp.country_type (id INT8 NOT NULL, value VARCHAR(100(100) NOT NULL)] liquibase.exception.MigrationFailedException: Migration failed for change set com.jenzabar.ngp:ngp-test-jpa::ngp-constituent-jpa.country_type.NewTableCreation::lgray: Reason: liquibase.exception.DatabaseException: A syntax error has occurred. [Failed SQL: CREATE TABLE jxclean351:ngp.country_type (id INT8 NOT NULL, value VARCHAR(100(100) NOT NULL)] at liquibase.changelog.ChangeSet.execute(ChangeSet.java:605) at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:51) at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:79) at liquibase.Liquibase.update(Liquibase.java:214) at liquibase.Liquibase.update(Liquibase.java:192) at liquibase.integration.commandline.Main.doMigration(Main.java:1126) at liquibase.integration.commandline.Main.run(Main.java:184) at liquibase.integration.commandline.Main.main(Main.java:103) Caused by: liquibase.exception.DatabaseException: A syntax error has occurred. [Failed SQL: CREATE TABLE jxclean351:ngp.country_type (id INT8 NOT NULL, value VARCHAR(100(100) NOT NULL)] at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:301) at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:55) at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:107) at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1273) at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1255) at liquibase.changelog.ChangeSet.execute(ChangeSet.java:568) ... 7 more Caused by: java.sql.SQLException: A syntax error has occurred. at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:413) at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3650) at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3977) at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2698) at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2614) at com.informix.jdbc.IfxSqli.executeExecute(IfxSqli.java:2491) at com.informix.jdbc.IfxSqli.executeExecute(IfxSqli.java:2466) at com.informix.jdbc.IfxResultSet.b(IfxResultSet.java:394) at com.informix.jdbc.IfxStatement.a(IfxStatement.java:1333) at com.informix.jdbc.IfxStatement.executeImpl(IfxStatement.java:1303) at com.informix.jdbc.IfxStatement.c(IfxStatement.java:1007) at com.informix.jdbc.IfxStatement.execute(IfxStatement.java:893) at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:299) ... 12 more Caused by: java.sql.SQLException at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:413) at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3982) ... 22 more {code}

    JIRA | 3 months ago | Lyle H. Gray
    liquibase.exception.DatabaseException: A syntax error has occurred. [Failed SQL: CREATE TABLE jxclean351:ngp.country_type (id INT8 NOT NULL, value VARCHAR(100(100) NOT NULL)]
  2. 0

    When using the <primaryKeyExists> precondition in Informix to check to see if an primary key index exists, the check does not appear to succeed if a primary key actually exists. Applying the following pair of changesets to "jxclean" {code} <changeSet id="ngp-constituent-jpa.military_service.NewTableCreation" author="lgray"> <createTable tableName="military_service"> <column name="id" type="bigint"> <constraints nullable="false"/> </column> <column name="country_type_id" type="bigint"> <constraints nullable="false"/> </column> </createTable> <addPrimaryKey tableName="military_service" columnNames="id" constraintName="pk_military_service"/> </changeSet> <changeSet id="ngp-constituent-jpa.military_service.createIndex" author="lgray"> <preConditions onFail="MARK_RAN"> <tableExists tableName="military_service"/> <not> <primaryKeyExists primaryKeyName="pk_military_service" tableName="military_service"/> </not> </preConditions> <addPrimaryKey tableName="military_service" columnNames="id" constraintName="pk_military_service"/> </changeSet> {code} generates the following error when Liquibase attempts to execute the second changeset: {code} SEVERE 9/21/16 4:14 PM: liquibase: com.jenzabar.ngp:ngp-test-jpa: com.jenzabar.ngp:ngp-test-jpa::ngp-constituent-jpa.military_service.createIndex::lgray: Change Set com.jenzabar.ngp:ngp-test-jpa::ngp-constituent-jpa.military_service.createIndex::lgray failed. Error: Primary key already exists on the table. [Failed SQL: ALTER TABLE jxclean351:ngp.military_service ADD CONSTRAINT PRIMARY KEY (id) CONSTRAINT pk_military_service] INFO 9/21/16 4:14 PM: liquibase: com.jenzabar.ngp:ngp-test-jpa::ngp-constituent-jpa.military_service.createIndex::lgray: Successfully released change log lock SEVERE 9/21/16 4:14 PM: liquibase: com.jenzabar.ngp:ngp-test-jpa::ngp-constituent-jpa.military_service.createIndex::lgray: Primary key already exists on the table. [Failed SQL: ALTER TABLE jxclean351:ngp.military_service ADD CONSTRAINT PRIMARY KEY (id) CONSTRAINT pk_military_service] liquibase.exception.MigrationFailedException: Migration failed for change set com.jenzabar.ngp:ngp-test-jpa::ngp-constituent-jpa.military_service.createIndex::lgray: Reason: liquibase.exception.DatabaseException: Primary key already exists on the table. [Failed SQL: ALTER TABLE jxclean351:ngp.military_service ADD CONSTRAINT PRIMARY KEY (id) CONSTRAINT pk_military_service] at liquibase.changelog.ChangeSet.execute(ChangeSet.java:605) at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:51) at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:79) at liquibase.Liquibase.update(Liquibase.java:214) at liquibase.Liquibase.update(Liquibase.java:192) at liquibase.integration.commandline.Main.doMigration(Main.java:1126) at liquibase.integration.commandline.Main.run(Main.java:184) at liquibase.integration.commandline.Main.main(Main.java:103) Caused by: liquibase.exception.DatabaseException: Primary key already exists on the table. [Failed SQL: ALTER TABLE jxclean351:ngp.military_service ADD CONSTRAINT PRIMARY KEY (id) CONSTRAINT pk_military_service] at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:301) at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:55) at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:107) at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1273) at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1255) at liquibase.changelog.ChangeSet.execute(ChangeSet.java:568) ... 7 more Caused by: java.sql.SQLException: Primary key already exists on the table. at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:413) at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3650) at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3977) at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2698) at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2614) at com.informix.jdbc.IfxSqli.executeExecute(IfxSqli.java:2491) at com.informix.jdbc.IfxSqli.executeExecute(IfxSqli.java:2466) at com.informix.jdbc.IfxResultSet.b(IfxResultSet.java:394) at com.informix.jdbc.IfxStatement.a(IfxStatement.java:1333) at com.informix.jdbc.IfxStatement.executeImpl(IfxStatement.java:1303) at com.informix.jdbc.IfxStatement.c(IfxStatement.java:1007) at com.informix.jdbc.IfxStatement.execute(IfxStatement.java:893) at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:299) ... 12 more Caused by: java.sql.SQLException at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:413) at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3982) ... 22 more {code} even though the precondition should have prevented the second changeset from attempting to create the primary key again.

    JIRA | 3 months ago | Lyle H. Gray
    liquibase.exception.DatabaseException: Primary key already exists on the table. [Failed SQL: ALTER TABLE jxclean351:ngp.military_service ADD CONSTRAINT PRIMARY KEY (id) CONSTRAINT pk_military_service]
  3. 0

    How to configure activemq to use informix database?

    Stack Overflow | 4 years ago | user358448
    java.sql.SQLException
  4. Speed up your debug routine!

    Automated exception search integrated into your IDE

  5. 0

    Liquibase dropAll fails with the message "A syntax error has occurred" Probably the stacktrace gives a hint: DEBUG 03.12.13 08:39:liquibase: Executing UPDATE database command: UPDATE master.DATABASECHANGELOGLOCK SET LOCKED = 'f', LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1 INFO 03.12.13 08:39:liquibase: Successfully released change log lock Liquibase dropAll Failed: liquibase.exception.UnexpectedLiquibaseException: liquibase.exception.DatabaseException: java.sql.SQLException: A syntax error has occurred. SEVERE 03.12.13 08:39:liquibase: liquibase.exception.UnexpectedLiquibaseException: liquibase.exception.DatabaseException: java.sql.SQLException: A syntax error has occurred. liquibase.exception.DatabaseException: liquibase.exception.UnexpectedLiquibaseException: liquibase.exception.DatabaseException: java.sql.SQLException: A syntax error has occurred. at liquibase.Liquibase.dropAll(Liquibase.java:658) at liquibase.Liquibase.dropAll(Liquibase.java:638) at liquibase.integration.commandline.Main.doMigration(Main.java:831) at liquibase.integration.commandline.Main.main(Main.java:133) Caused by: liquibase.exception.UnexpectedLiquibaseException: liquibase.exception.DatabaseException: java.sql.SQLException: A syntax error has occurred. at liquibase.database.AbstractJdbcDatabase.dropDatabaseObjects(AbstractJdbcDatabase.java:1006) at liquibase.Liquibase.dropAll(Liquibase.java:651) ... 3 more Caused by: liquibase.exception.DatabaseException: java.sql.SQLException: A syntax error has occurred. at liquibase.snapshot.jvm.ColumnSnapshotGenerator.snapshotObject(ColumnSnapshotGenerator.java:53) at liquibase.snapshot.jvm.JdbcSnapshotGenerator.snapshot(JdbcSnapshotGenerator.java:59) at liquibase.snapshot.SnapshotGeneratorChain.snapshot(SnapshotGeneratorChain.java:42) at liquibase.snapshot.DatabaseSnapshot.include(DatabaseSnapshot.java:89) at liquibase.snapshot.DatabaseSnapshot.replaceObject(DatabaseSnapshot.java:157) at liquibase.snapshot.DatabaseSnapshot.includeNestedObjects(DatabaseSnapshot.java:116) at liquibase.snapshot.DatabaseSnapshot.include(DatabaseSnapshot.java:103) at liquibase.snapshot.DatabaseSnapshot.replaceObject(DatabaseSnapshot.java:157) at liquibase.snapshot.DatabaseSnapshot.replaceObject(DatabaseSnapshot.java:170) at liquibase.snapshot.DatabaseSnapshot.includeNestedObjects(DatabaseSnapshot.java:116) at liquibase.snapshot.DatabaseSnapshot.include(DatabaseSnapshot.java:103) at liquibase.snapshot.DatabaseSnapshot.<init>(DatabaseSnapshot.java:34) at liquibase.snapshot.JdbcDatabaseSnapshot.<init>(JdbcDatabaseSnapshot.java:22) at liquibase.snapshot.SnapshotGeneratorFactory.createSnapshot(SnapshotGeneratorFactory.java:125) at liquibase.snapshot.SnapshotGeneratorFactory.createSnapshot(SnapshotGeneratorFactory.java:121) at liquibase.snapshot.SnapshotGeneratorFactory.createSnapshot(SnapshotGeneratorFactory.java:109) at liquibase.database.AbstractJdbcDatabase.dropDatabaseObjects(AbstractJdbcDatabase.java:1003) ... 4 more Caused by: java.sql.SQLException: A syntax error has occurred. at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:413) at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3635) at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3963) at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2693) at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2609) at com.informix.jdbc.IfxSqli.executeStatementQuery(IfxSqli.java:1724) at com.informix.jdbc.IfxSqli.executeStatementQuery(IfxSqli.java:1697) at com.informix.jdbc.IfxResultSet.a(IfxResultSet.java:209) at com.informix.jdbc.IfxStatement.executeQueryImpl(IfxStatement.java:1230) at com.informix.jdbc.IfxStatement.executeQuery(IfxStatement.java:220) at liquibase.snapshot.jvm.ColumnSnapshotGenerator.readColumn(ColumnSnapshotGenerator.java:144) at liquibase.snapshot.jvm.ColumnSnapshotGenerator.snapshotObject(ColumnSnapshotGenerator.java:48) ... 20 more Caused by: java.sql.SQLException at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:413) at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3968) ... 29 more

    JIRA | 3 years ago | Björn Engel
    liquibase.exception.DatabaseException: liquibase.exception.UnexpectedLiquibaseException: liquibase.exception.DatabaseException: java.sql.SQLException: A syntax error has occurred.
  6. 0

    Hashset Object Setting in Stored procedure

    Oracle Community | 4 years ago | 1006812
    java.sql.SQLException: Routine (testing_set) can not be resolved.

    9 unregistered visitors
    Not finding the right solution?
    Take a tour to get the most out of Samebug.

    Tired of useless tips?

    Automated exception search integrated into your IDE

    Root Cause Analysis

    1. java.sql.SQLException

      No message provided

      at com.informix.util.IfxErrMsg.getSQLException()
    2. com.informix.util
      IfxErrMsg.getSQLException
      1. com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:413)
      1 frame
    3. com.informix.jdbc
      IfxStatement.execute
      1. com.informix.jdbc.IfxSqli.E(IfxSqli.java:3982)
      2. com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2698)
      3. com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2614)
      4. com.informix.jdbc.IfxSqli.executeExecute(IfxSqli.java:2491)
      5. com.informix.jdbc.IfxSqli.executeExecute(IfxSqli.java:2466)
      6. com.informix.jdbc.IfxResultSet.b(IfxResultSet.java:394)
      7. com.informix.jdbc.IfxStatement.a(IfxStatement.java:1333)
      8. com.informix.jdbc.IfxStatement.executeImpl(IfxStatement.java:1303)
      9. com.informix.jdbc.IfxStatement.c(IfxStatement.java:1007)
      10. com.informix.jdbc.IfxStatement.execute(IfxStatement.java:893)
      10 frames
    4. Liquibase Core
      Main.main
      1. liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:299)
      2. liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:55)
      3. liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:107)
      4. liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1273)
      5. liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1255)
      6. liquibase.changelog.ChangeSet.execute(ChangeSet.java:568)
      7. liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:51)
      8. liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:79)
      9. liquibase.Liquibase.update(Liquibase.java:214)
      10. liquibase.Liquibase.update(Liquibase.java:192)
      11. liquibase.integration.commandline.Main.doMigration(Main.java:1126)
      12. liquibase.integration.commandline.Main.run(Main.java:184)
      13. liquibase.integration.commandline.Main.main(Main.java:103)
      13 frames