glassfish
  1. glassfish
  2. GLASSFISH-18362

EclipseLink generates illegal SQL code with Field Access and OneToMany relationships when creating tables

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Works as designed
    • Affects Version/s: 3.1.1
    • Fix Version/s: None
    • Component/s: entity-persistence
    • Labels:
      None
    • Environment:

      Windows 7 Pro 64-bit, Glassfish 3.1.1, Postgresql 9.1

      Description

      JPA Entities with Field access and OneToMany relationships generate illegal SQL code when creating tables. The generated SQL includes two columns named "id" and it tries to set "PRIMARY KEY (id, id)". The specific SQL errors are:

      [ASADMIN] [ERROR] PER01000: Got SQLException executing statement "CREATE TABLE INVENTORY (ID SERIAL NOT NULL, CAPTION VARCHAR(1024), DEVICEBARCODE VARCHAR(30), DEVICECOORDINATES INTEGER, INSTALLEREMAIL VARCHAR(40), LASTALERT BIGINT, LASTCHANGED BIGINT, LASTFEEDER INTEGER DEFAULT -1, LASTMISSED BIGINT, LASTPHASE CHAR(1), LASTREPORT BIGINT, NICKNAME INTEGER NOT NULL, STATE VARCHAR(10), UID INTEGER NOT NULL, OBJECTTABLE_ID BIGINT NOT NULL, ID SERIAL NOT NULL, PRIMARY KEY (ID, ID))": org.postgresql.util.PSQLException: ERROR: column "id" appears twice in primary key constraint
      [ASADMIN] [ERROR] Position: 398

      [ASADMIN] [ERROR] PER01000: Got SQLException executing statement "CREATE TABLE REPORTS (ID SERIAL NOT NULL, CHANNELID INTEGER, FEEDER INTEGER DEFAULT -1, FIMSEQUENCENUMBER BIGINT, PAYLOAD VARCHAR(510), PHASE CHAR(1), RECEIVED BIGINT, INVENTORY_ID BIGINT NOT NULL, ID SERIAL NOT NULL, PRIMARY KEY (ID, ID))": org.postgresql.util.PSQLException: ERROR: column "id" appears twice in primary key constraint

      I've attached a simplified example that reproduces this error. The readme.txt file contains the psql commands to create a test database and schema and the asadmin commands to create a connection pool and JDBC resource in glassfish.

        Activity

        Hide
        dwschulze added a comment -

        If you comment out the @OneToMany and corresponding @ManyToOne annotations the problem doesn't exist. EclipseLink generates a join table, but the two Entity tables get created properly.

        Show
        dwschulze added a comment - If you comment out the @OneToMany and corresponding @ManyToOne annotations the problem doesn't exist. EclipseLink generates a join table, but the two Entity tables get created properly.
        Hide
        Mitesh Meswani added a comment -

        I believe this issue has been fixed in latest version of EclipseLink shipping with GlassFish 3.1.2. Can you please have a run of your test case against GlassFish 3.1.2. You can download the latest build from here http://glassfish.java.net/public/downloadsindex.html (scroll to bottom for 3.1.2 builds)

        Show
        Mitesh Meswani added a comment - I believe this issue has been fixed in latest version of EclipseLink shipping with GlassFish 3.1.2. Can you please have a run of your test case against GlassFish 3.1.2. You can download the latest build from here http://glassfish.java.net/public/downloadsindex.html (scroll to bottom for 3.1.2 builds)
        Hide
        dwschulze added a comment -

        Slight mistake in the reported version of Glassfish. The errors above are from the Glassfish 3.1.2 beta from 2011.12.26.

        If I run against 3.1.1 I get nearly the same error:

        [ASADMIN] [ERROR] PER01000: Got SQLException executing statement "CREATE TABLE INVENTORY (ID SERIAL NOT NULL, CAPTION VARCHAR(1024), DEVICEBARCODE VARCHAR(30), DEVICECOORDINATES INTEGER, INSTALLEREMAIL VARCHAR(40), LASTALERT BIGINT, LASTCHANGED BIGINT, LASTFEEDER INTEGER DEFAULT -1, LASTMISSED BIGINT, LASTPHASE CHAR(1), LASTREPORT BIGINT, NICKNAME INTEGER NOT NULL, STATE VARCHAR(10), UID INTEGER NOT NULL, OBJECTTABLE_ID BIGINT NOT NULL, id INTEGER, PRIMARY KEY (ID))": org.postgresql.util.PSQLException: ERROR: column "id" specified more than once
        [ASADMIN] [ERROR] PER01000: Got SQLException executing statement "CREATE TABLE REPORTS (ID SERIAL NOT NULL, CHANNELID INTEGER, FEEDER INTEGER DEFAULT -1, FIMSEQUENCENUMBER BIGINT, PAYLOAD VARCHAR(510), PHASE CHAR(1), RECEIVED BIGINT, INVENTORY_ID BIGINT NOT NULL, id INTEGER, PRIMARY KEY (ID))": org.postgresql.util.PSQLException: ERROR: column "id" specified more than once

        Show
        dwschulze added a comment - Slight mistake in the reported version of Glassfish. The errors above are from the Glassfish 3.1.2 beta from 2011.12.26. If I run against 3.1.1 I get nearly the same error: [ASADMIN] [ERROR] PER01000: Got SQLException executing statement "CREATE TABLE INVENTORY (ID SERIAL NOT NULL, CAPTION VARCHAR(1024), DEVICEBARCODE VARCHAR(30), DEVICECOORDINATES INTEGER, INSTALLEREMAIL VARCHAR(40), LASTALERT BIGINT, LASTCHANGED BIGINT, LASTFEEDER INTEGER DEFAULT -1, LASTMISSED BIGINT, LASTPHASE CHAR(1), LASTREPORT BIGINT, NICKNAME INTEGER NOT NULL, STATE VARCHAR(10), UID INTEGER NOT NULL, OBJECTTABLE_ID BIGINT NOT NULL, id INTEGER, PRIMARY KEY (ID))": org.postgresql.util.PSQLException: ERROR: column "id" specified more than once [ASADMIN] [ERROR] PER01000: Got SQLException executing statement "CREATE TABLE REPORTS (ID SERIAL NOT NULL, CHANNELID INTEGER, FEEDER INTEGER DEFAULT -1, FIMSEQUENCENUMBER BIGINT, PAYLOAD VARCHAR(510), PHASE CHAR(1), RECEIVED BIGINT, INVENTORY_ID BIGINT NOT NULL, id INTEGER, PRIMARY KEY (ID))": org.postgresql.util.PSQLException: ERROR: column "id" specified more than once
        Hide
        dwschulze added a comment -

        I used updatetool to install Hibernate and changed the persistence.xml to use Hibernate. Now the .war deploys and creates the tables, but it throws this Exception from the @Startup class:

        Caused by: java.lang.IllegalStateException: Attempting to execute an operation on a closed EntityManagerFactory.

        I inject the EntityManager so anything having to do with the EntityManagerFactory is a framework problem.

        This looks like this bug:

        http://java.net/jira/browse/GLASSFISH-17977

        Show
        dwschulze added a comment - I used updatetool to install Hibernate and changed the persistence.xml to use Hibernate. Now the .war deploys and creates the tables, but it throws this Exception from the @Startup class: Caused by: java.lang.IllegalStateException: Attempting to execute an operation on a closed EntityManagerFactory. I inject the EntityManager so anything having to do with the EntityManagerFactory is a framework problem. This looks like this bug: http://java.net/jira/browse/GLASSFISH-17977
        Hide
        dwschulze added a comment -

        Using a beta of 3.1.2 is not an option. Is there a fix for 3.1.1?

        Show
        dwschulze added a comment - Using a beta of 3.1.2 is not an option. Is there a fix for 3.1.1?
        Hide
        dwschulze added a comment -

        I installed all upgrades to 3.1.1 and I now get the same error I'm seeing in 3.1.2 beta.

        Show
        dwschulze added a comment - I installed all upgrades to 3.1.1 and I now get the same error I'm seeing in 3.1.2 beta.
        Hide
        Mitesh Meswani added a comment -

        Filled EclipseLink issue to track this. https://bugs.eclipse.org/bugs/show_bug.cgi?id=371917

        Show
        Mitesh Meswani added a comment - Filled EclipseLink issue to track this. https://bugs.eclipse.org/bugs/show_bug.cgi?id=371917
        Hide
        Mitesh Meswani added a comment -

        Following is from corresponding bug from EclipseLink. The recommended approach is to use @OrderBy, if you intend to order using an attribute of target entity. Closing this issue. The EclipseLink issue will remain open to track implementing a validation that catches this.

        /
        Further analysis indicates that this issue arises from confusion about the
        difference between @OrderColum and @OrderBy

        @OrderColumn is meant to indicate there is a column in the database table
        specifically for order - hence DDL generation will generate that column and as
        a result, it should have a name that is different from other columns in the
        same table.

        This issue is about validation. i.e. If @OrderColumn is specified, it would be
        a good idea to validate that the name is not already existing before generating
        it in the table. If we found an existing column we could either ignore the
        column for the purpose of DDL generation and act as if it were an @OrderBy or
        throw an exception.
        /

        Show
        Mitesh Meswani added a comment - Following is from corresponding bug from EclipseLink. The recommended approach is to use @OrderBy, if you intend to order using an attribute of target entity. Closing this issue. The EclipseLink issue will remain open to track implementing a validation that catches this. / Further analysis indicates that this issue arises from confusion about the difference between @OrderColum and @OrderBy @OrderColumn is meant to indicate there is a column in the database table specifically for order - hence DDL generation will generate that column and as a result, it should have a name that is different from other columns in the same table. This issue is about validation. i.e. If @OrderColumn is specified, it would be a good idea to validate that the name is not already existing before generating it in the table. If we found an existing column we could either ignore the column for the purpose of DDL generation and act as if it were an @OrderBy or throw an exception. /

          People

          • Assignee:
            tware
            Reporter:
            dwschulze
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: