Re: [Help] JPA-2.1 EclipseLink try to create constraints multiple times and throw warning

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Re: [Help] JPA-2.1 EclipseLink try to create constraints multiple times and throw warning

Wim Jongman-2
Hi Yun Jie Zhou,

I have no answers only the same observation. It looks like the duplicate constraint error is caused when a constraint name is invented that clashes with an already existing _different_ constraint.

for example, the generation of constraint name: "WRKLDLDMDULEWRKLD    D" looks like it can be duplicated in case of similar named fields and tables.

I think you should explicitly name the constraints where possible to avoid the ugly generated names. 

If this is at all possible I don't know. Please update this thread if you have more insights.

Cheers,

Wim

  

On Wed, Sep 27, 2017 at 5:17 AM, Yun Jie Zhou <[hidden email]> wrote:
Hello experts,
 
We noticed duplicated constraint creation warning in application logs when the application was first accessed since started. Since the constraint already exists, why eclipselink tries to create again and throw warnings? thanks.
 
[9/26/17 10:12:32:926 EDT] 0000007a eclipselink                                                  W CWWJP9991W: Exception [EclipseLink-4002] (Eclipse Pers    istence Services - 2.6.3.WAS-v20160817-8475a53): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.ibm.db2.jcc.am.SqlSyntaxErrorException: The name of the object to be created is identical to the existing name "WRKLDLDMDULEWRKLD    D" of type "FOREIGN KEY".. SQLCODE=-601, SQLSTATE=42710, DRIVER=3.63.123
Error Code: -601
Call: ALTER TABLE WORKLOAD_LOADMODULE ADD CONSTRAINT WRKLDLDMDULEWrkldD FOREIGN KEY (Workload_ID) REFERENCES WORKLOAD (ID)
Query: DataModifyQuery(sql="ALTER TABLE WORKLOAD_LOADMODULE ADD CONSTRAINT WRKLDLDMDULEWrkldD FOREIGN KEY (Workload_ID) REFERENCES WORKLOAD (ID)")
 
While another Unique constraint in the following, eclipselink ignore the duplicated creation, which makes more sense. Was the above error related to join table?
 
[9/26/17 10:12:32:883 EDT] 0000007a query         3   Execute query DataModifyQuery(sql="ALTER TABLE USERPREFERENCES ADD CONSTRAINT USERPREFERENCES0 UNIQUE (username)")
[9/26/17 10:12:32:884 EDT] 0000007a connection    3   Connection acquired from connection pool [read].
[9/26/17 10:12:32:884 EDT] 0000007a connection    3   reconnecting to external connection pool
[9/26/17 10:12:32:884 EDT] 0000007a sql           3   ALTER TABLE USERPREFERENCES ADD CONSTRAINT USERPREFERENCES0 UNIQUE (username)
[9/26/17 10:12:32:900 EDT] 0000007a sql           3   The referential, primary key, or unique constraint "USERPREFERENCES0" is ignored because it is a duplicate constraint.. SQLCODE=541, SQLSTATE=01543, DRIVER=3.63.123:01543 - null
[9/26/17 10:12:32:901 EDT] 0000007a connection    3   Connection released to connection pool [read].
 
Thanks & Best Regards

Austin ( 周运杰/Yun Jie Zhou)
z/OS System Test and Performance, China System & Technology Lab
Tel: 86-10-82452455 Ext: 52455
Mobile: 13811310391
E-Mail: [hidden email]
 
 
----- Original message -----
From: Yun Jie Zhou/China/IBM
To: [hidden email]
Cc: [hidden email]
Subject: [Help] JPA-2.1 EclipseLink try to create constraints multiple times and throw warning
Date: Wed, Sep 27, 2017 10:56 AM
 
 
Hello experts,
 
We noticed duplicated constraint creation warning in application logs. Since the constraint already exists, why eclipselink tries to create again and throw warnings? thanks.
 
[9/26/17 10:12:32:926 EDT] 0000007a eclipselink                                                  W CWWJP9991W: Exception [EclipseLink-4002] (Eclipse Pers    istence Services - 2.6.3.WAS-v20160817-8475a53): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.ibm.db2.jcc.am.SqlSyntaxErrorException: The name of the object to be created is identical to the existing name "WRKLDLDMDULEWRKLD    D" of type "FOREIGN KEY".. SQLCODE=-601, SQLSTATE=42710, DRIVER=3.63.123
Error Code: -601
Call: ALTER TABLE WORKLOAD_LOADMODULE ADD CONSTRAINT WRKLDLDMDULEWrkldD FOREIGN KEY (Workload_ID) REFERENCES WORKLOAD (ID)
Query: DataModifyQuery(sql="ALTER TABLE WORKLOAD_LOADMODULE ADD CONSTRAINT WRKLDLDMDULEWrkldD FOREIGN KEY (Workload_ID) REFERENCES WORKLOAD (ID)")
 
While another Unique constraint in the following, eclipselink ignore the duplicated creation, which makes more sense. Was the above error related to join table?
[9/26/17 10:12:32:883 EDT] 0000007a query         3   Execute query DataModifyQuery(sql="ALTER TABLE USERPREFERENCES ADD CONSTRAINT USERPREFERENCES0 UNIQUE (username)")
[9/26/17 10:12:32:884 EDT] 0000007a connection    3   Connection acquired from connection pool [read].
[9/26/17 10:12:32:884 EDT] 0000007a connection    3   reconnecting to external connection pool
[9/26/17 10:12:32:884 EDT] 0000007a sql           3   ALTER TABLE USERPREFERENCES ADD CONSTRAINT USERPREFERENCES0 UNIQUE (username)
[9/26/17 10:12:32:900 EDT] 0000007a sql           3   The referential, primary key, or unique constraint "USERPREFERENCES0" is ignored because it is a duplicate constraint.. SQLCODE=541, SQLSTATE=01543, DRIVER=3.63.123:01543 - null
[9/26/17 10:12:32:901 EDT] 0000007a connection    3   Connection released to connection pool [read].
 
Thanks & Best Regards

Austin ( 周运杰/Yun Jie Zhou)
z/OS System Test and Performance, China System & Technology Lab
Tel: 86-10-82452455 Ext: 52455
Mobile: 13811310391
E-Mail: [hidden email]
 


_______________________________________________
eclipselink-users mailing list
[hidden email]
To change your delivery options, retrieve your password, or unsubscribe from this list, visit
https://dev.eclipse.org/mailman/listinfo/eclipselink-users


_______________________________________________
eclipselink-users mailing list
[hidden email]
To change your delivery options, retrieve your password, or unsubscribe from this list, visit
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
Reply | Threaded
Open this post in threaded view
|

Re: [Help] JPA-2.1 EclipseLink try to create constraints multiple times and throw warning

christopher delahunt
As to why:  checking constraints is very database dependent, and it is more efficient and less error prone to create the constraint and catch the error if it already exists then other options. Not to say it cannot be done, just that there isn’t a great demand for it when the errors that result can be easily ignored, and DDL generation isn’t meant for production.


JPA does allow you to define and name constraints using @UniqueConstraint and @ForeignKey annotations under the table and mappings.

Best Regards,
Chris






On Oct 10, 2017, at 6:02 AM, Wim Jongman <[hidden email]> wrote:

Hi Yun Jie Zhou,

I have no answers only the same observation. It looks like the duplicate constraint error is caused when a constraint name is invented that clashes with an already existing _different_ constraint.

for example, the generation of constraint name: "WRKLDLDMDULEWRKLD    D" looks like it can be duplicated in case of similar named fields and tables.

I think you should explicitly name the constraints where possible to avoid the ugly generated names. 

If this is at all possible I don't know. Please update this thread if you have more insights.

Cheers,

Wim

  

On Wed, Sep 27, 2017 at 5:17 AM, Yun Jie Zhou <[hidden email]> wrote:
Hello experts,
 
We noticed duplicated constraint creation warning in application logs when the application was first accessed since started. Since the constraint already exists, why eclipselink tries to create again and throw warnings? thanks.
 
[9/26/17 10:12:32:926 EDT] 0000007a eclipselink                                                  W CWWJP9991W: Exception [EclipseLink-4002] (Eclipse Pers    istence Services - 2.6.3.WAS-v20160817-8475a53): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.ibm.db2.jcc.am.SqlSyntaxErrorException: The name of the object to be created is identical to the existing name "WRKLDLDMDULEWRKLD    D" of type "FOREIGN KEY".. SQLCODE=-601, SQLSTATE=42710, DRIVER=3.63.123
Error Code: -601
Call: ALTER TABLE WORKLOAD_LOADMODULE ADD CONSTRAINT WRKLDLDMDULEWrkldD FOREIGN KEY (Workload_ID) REFERENCES WORKLOAD (ID)
Query: DataModifyQuery(sql="ALTER TABLE WORKLOAD_LOADMODULE ADD CONSTRAINT WRKLDLDMDULEWrkldD FOREIGN KEY (Workload_ID) REFERENCES WORKLOAD (ID)")
 
While another Unique constraint in the following, eclipselink ignore the duplicated creation, which makes more sense. Was the above error related to join table?
 
[9/26/17 10:12:32:883 EDT] 0000007a query         3   Execute query DataModifyQuery(sql="ALTER TABLE USERPREFERENCES ADD CONSTRAINT USERPREFERENCES0 UNIQUE (username)")
[9/26/17 10:12:32:884 EDT] 0000007a connection    3   Connection acquired from connection pool [read].
[9/26/17 10:12:32:884 EDT] 0000007a connection    3   reconnecting to external connection pool
[9/26/17 10:12:32:884 EDT] 0000007a sql           3   ALTER TABLE USERPREFERENCES ADD CONSTRAINT USERPREFERENCES0 UNIQUE (username)
[9/26/17 10:12:32:900 EDT] 0000007a sql           3   The referential, primary key, or unique constraint "USERPREFERENCES0" is ignored because it is a duplicate constraint.. SQLCODE=541, SQLSTATE=01543, DRIVER=3.63.123:01543 - null
[9/26/17 10:12:32:901 EDT] 0000007a connection    3   Connection released to connection pool [read].
 
Thanks & Best Regards

Austin ( 周运杰/Yun Jie Zhou)
z/OS System Test and Performance, China System & Technology Lab
Tel: 86-10-82452455 Ext: 52455
Mobile: 13811310391
E-Mail: [hidden email]
 
 
----- Original message -----
From: Yun Jie Zhou/China/IBM
To: [hidden email]
Cc: [hidden email]
Subject: [Help] JPA-2.1 EclipseLink try to create constraints multiple times and throw warning
Date: Wed, Sep 27, 2017 10:56 AM
 
 
Hello experts,
 
We noticed duplicated constraint creation warning in application logs. Since the constraint already exists, why eclipselink tries to create again and throw warnings? thanks.
 
[9/26/17 10:12:32:926 EDT] 0000007a eclipselink                                                  W CWWJP9991W: Exception [EclipseLink-4002] (Eclipse Pers    istence Services - 2.6.3.WAS-v20160817-8475a53): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.ibm.db2.jcc.am.SqlSyntaxErrorException: The name of the object to be created is identical to the existing name "WRKLDLDMDULEWRKLD    D" of type "FOREIGN KEY".. SQLCODE=-601, SQLSTATE=42710, DRIVER=3.63.123
Error Code: -601
Call: ALTER TABLE WORKLOAD_LOADMODULE ADD CONSTRAINT WRKLDLDMDULEWrkldD FOREIGN KEY (Workload_ID) REFERENCES WORKLOAD (ID)
Query: DataModifyQuery(sql="ALTER TABLE WORKLOAD_LOADMODULE ADD CONSTRAINT WRKLDLDMDULEWrkldD FOREIGN KEY (Workload_ID) REFERENCES WORKLOAD (ID)")
 
While another Unique constraint in the following, eclipselink ignore the duplicated creation, which makes more sense. Was the above error related to join table?
[9/26/17 10:12:32:883 EDT] 0000007a query         3   Execute query DataModifyQuery(sql="ALTER TABLE USERPREFERENCES ADD CONSTRAINT USERPREFERENCES0 UNIQUE (username)")
[9/26/17 10:12:32:884 EDT] 0000007a connection    3   Connection acquired from connection pool [read].
[9/26/17 10:12:32:884 EDT] 0000007a connection    3   reconnecting to external connection pool
[9/26/17 10:12:32:884 EDT] 0000007a sql           3   ALTER TABLE USERPREFERENCES ADD CONSTRAINT USERPREFERENCES0 UNIQUE (username)
[9/26/17 10:12:32:900 EDT] 0000007a sql           3   The referential, primary key, or unique constraint "USERPREFERENCES0" is ignored because it is a duplicate constraint.. SQLCODE=541, SQLSTATE=01543, DRIVER=3.63.123:01543 - null
[9/26/17 10:12:32:901 EDT] 0000007a connection    3   Connection released to connection pool [read].
 
Thanks & Best Regards

Austin ( 周运杰/Yun Jie Zhou)
z/OS System Test and Performance, China System & Technology Lab
Tel: 86-10-82452455 Ext: 52455
Mobile: 13811310391
E-Mail: [hidden email]
 


_______________________________________________
eclipselink-users mailing list
[hidden email]
To change your delivery options, retrieve your password, or unsubscribe from this list, visit
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

_______________________________________________
eclipselink-users mailing list
[hidden email]
To change your delivery options, retrieve your password, or unsubscribe from this list, visit
https://urldefense.proofpoint.com/v2/url?u=https-3A__dev.eclipse.org_mailman_listinfo_eclipselink-2Dusers&d=DwICAg&c=RoP1YumCXCgaWHvlZYR8PQcxBKCX5YTpkKY057SbK10&r=xVpSyIIO_uKj1KAOMnm4_-T4KVw7uqAp5EeFOtyE4gI&m=SFTmhgtJRmE9vdooh6JD-YT0Mu9YZsd5620nMYSMbvA&s=is_hCYqtlHoo0V21Tuw0pw4klDLDzPD2xRcTG1bCzSY&e=


_______________________________________________
eclipselink-users mailing list
[hidden email]
To change your delivery options, retrieve your password, or unsubscribe from this list, visit
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
Reply | Threaded
Open this post in threaded view
|

Re: [Help] JPA-2.1 EclipseLink try to create constraints multiple times and throw warning

Yun Jie Zhou
In reply to this post by Wim Jongman-2
Hello,
 
The foreign key constraints are generated automatically by the join table due to @ManyToOne/@ManyToMany entity relationship.
@JoinTable has uniqueConstraints element, which seems doesn't fit for foreign key constraints. Am I correct?
 
Btw, I am using EclipseLink in IBM WebSphere Liberty Profile, target DB2 database, I found with ddl-generate=create-or-extend-tables, EclipseLink can ignore duplicate Unique Constraints creation, but throws error for duplicate foreign key constraints creation.
 
Thanks & Best Regards

 
 
 
----- Original message -----
From: Wim Jongman <[hidden email]>
Sent by: [hidden email]
To: EclipseLink User Discussions <[hidden email]>
Cc:
Subject: Re: [eclipselink-users] [Help] JPA-2.1 EclipseLink try to create constraints multiple times and throw warning
Date: Tue, Oct 10, 2017 6:02 PM
 
Hi Yun Jie Zhou,
 
I have no answers only the same observation. It looks like the duplicate constraint error is caused when a constraint name is invented that clashes with an already existing _different_ constraint.
 
for example, the generation of constraint name: "WRKLDLDMDULEWRKLD    D" looks like it can be duplicated in case of similar named fields and tables.
 
I think you should explicitly name the constraints where possible to avoid the ugly generated names. 
 
If this is at all possible I don't know. Please update this thread if you have more insights.
 
Cheers,
 
Wim
 
  
 
On Wed, Sep 27, 2017 at 5:17 AM, Yun Jie Zhou <[hidden email]> wrote:
Hello experts,
 
We noticed duplicated constraint creation warning in application logs when the application was first accessed since started. Since the constraint already exists, why eclipselink tries to create again and throw warnings? thanks.
 
[9/26/17 10:12:32:926 EDT] 0000007a eclipselink                                                  W CWWJP9991W: Exception [EclipseLink-4002] (Eclipse Pers    istence Services - 2.6.3.WAS-v20160817-8475a53): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.ibm.db2.jcc.am.SqlSyntaxErrorException: The name of the object to be created is identical to the existing name "WRKLDLDMDULEWRKLD    D" of type "FOREIGN KEY".. SQLCODE=-601, SQLSTATE=42710, DRIVER=3.63.123
Error Code: -601
Call: ALTER TABLE WORKLOAD_LOADMODULE ADD CONSTRAINT WRKLDLDMDULEWrkldD FOREIGN KEY (Workload_ID) REFERENCES WORKLOAD (ID)
Query: DataModifyQuery(sql="ALTER TABLE WORKLOAD_LOADMODULE ADD CONSTRAINT WRKLDLDMDULEWrkldD FOREIGN KEY (Workload_ID) REFERENCES WORKLOAD (ID)")
 
While another Unique constraint in the following, eclipselink ignore the duplicated creation, which makes more sense. Was the above error related to join table?
 
[9/26/17 10:12:32:883 EDT] 0000007a query         3   Execute query DataModifyQuery(sql="ALTER TABLE USERPREFERENCES ADD CONSTRAINT USERPREFERENCES0 UNIQUE (username)")
[9/26/17 10:12:32:884 EDT] 0000007a connection    3   Connection acquired from connection pool [read].
[9/26/17 10:12:32:884 EDT] 0000007a connection    3   reconnecting to external connection pool
[9/26/17 10:12:32:884 EDT] 0000007a sql           3   ALTER TABLE USERPREFERENCES ADD CONSTRAINT USERPREFERENCES0 UNIQUE (username)
[9/26/17 10:12:32:900 EDT] 0000007a sql           3   The referential, primary key, or unique constraint "USERPREFERENCES0" is ignored because it is a duplicate constraint.. SQLCODE=541, SQLSTATE=01543, DRIVER=3.63.123:01543 - null
[9/26/17 10:12:32:901 EDT] 0000007a connection    3   Connection released to connection pool [read].
 
Thanks & Best Regards

Austin ( 周运杰/Yun Jie Zhou)
z/OS System Test and Performance, China System & Technology Lab
Tel: 86-10-82452455 Ext: 52455
Mobile: 13811310391
E-Mail: [hidden email]
 
 
----- Original message -----
From: Yun Jie Zhou/China/IBM
To: [hidden email]
Cc: [hidden email]
Subject: [Help] JPA-2.1 EclipseLink try to create constraints multiple times and throw warning
Date: Wed, Sep 27, 2017 10:56 AM
 
 
Hello experts,
 
We noticed duplicated constraint creation warning in application logs. Since the constraint already exists, why eclipselink tries to create again and throw warnings? thanks.
 
[9/26/17 10:12:32:926 EDT] 0000007a eclipselink                                                  W CWWJP9991W: Exception [EclipseLink-4002] (Eclipse Pers    istence Services - 2.6.3.WAS-v20160817-8475a53): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.ibm.db2.jcc.am.SqlSyntaxErrorException: The name of the object to be created is identical to the existing name "WRKLDLDMDULEWRKLD    D" of type "FOREIGN KEY".. SQLCODE=-601, SQLSTATE=42710, DRIVER=3.63.123
Error Code: -601
Call: ALTER TABLE WORKLOAD_LOADMODULE ADD CONSTRAINT WRKLDLDMDULEWrkldD FOREIGN KEY (Workload_ID) REFERENCES WORKLOAD (ID)
Query: DataModifyQuery(sql="ALTER TABLE WORKLOAD_LOADMODULE ADD CONSTRAINT WRKLDLDMDULEWrkldD FOREIGN KEY (Workload_ID) REFERENCES WORKLOAD (ID)")
 
While another Unique constraint in the following, eclipselink ignore the duplicated creation, which makes more sense. Was the above error related to join table?
[9/26/17 10:12:32:883 EDT] 0000007a query         3   Execute query DataModifyQuery(sql="ALTER TABLE USERPREFERENCES ADD CONSTRAINT USERPREFERENCES0 UNIQUE (username)")
[9/26/17 10:12:32:884 EDT] 0000007a connection    3   Connection acquired from connection pool [read].
[9/26/17 10:12:32:884 EDT] 0000007a connection    3   reconnecting to external connection pool
[9/26/17 10:12:32:884 EDT] 0000007a sql           3   ALTER TABLE USERPREFERENCES ADD CONSTRAINT USERPREFERENCES0 UNIQUE (username)
[9/26/17 10:12:32:900 EDT] 0000007a sql           3   The referential, primary key, or unique constraint "USERPREFERENCES0" is ignored because it is a duplicate constraint.. SQLCODE=541, SQLSTATE=01543, DRIVER=3.63.123:01543 - null
[9/26/17 10:12:32:901 EDT] 0000007a connection    3   Connection released to connection pool [read].
 
Thanks & Best Regards

Austin ( 周运杰/Yun Jie Zhou)
z/OS System Test and Performance, China System & Technology Lab
Tel: 86-10-82452455 Ext: 52455
Mobile: 13811310391
E-Mail: [hidden email]
 


_______________________________________________
eclipselink-users mailing list
[hidden email]
To change your delivery options, retrieve your password, or unsubscribe from this list, visit
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
 


_______________________________________________
eclipselink-users mailing list
[hidden email]
To change your delivery options, retrieve your password, or unsubscribe from this list, visit
https://dev.eclipse.org/mailman/listinfo/eclipselink-users