Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » MySQL: update on self-referencing foreign key tables leads to Duplicate Key Exception (Incorrect creation of temporary tables for MySQL database update query)
MySQL: update on self-referencing foreign key tables leads to Duplicate Key Exception [message #638415] Thu, 11 November 2010 04:18 Go to next message
Hans Harz is currently offline Hans Harz
Messages: 20
Registered: July 2009
Junior Member
If you have a table with a self-referencing foreign key, EclipseLink will
generate a wrong SELECT statement on a update all query for a MySQL backend.
The created statement contains an additional unconstrained table join, which
leads to many results instead of just one. Because of that you will get a
BatchUpdateException: Duplicate entry '102' for key 'PRIMARY'
Error Code: 1062
when EL tries to create the temporary table.


I created a bug for this:
https://bugs.eclipse.org/bugs/show_bug.cgi?id=329975

Steps to Reproduce:
1. Create an entity with a self-referencing foreign key.
In this example a class TermEjb with the columns ID int, isConcept char,
masterTerm TermEjb.

2. Create a complex update query
final Query query = createQuery("update TermEjb t "
+ "set t.masterTerm = t, t.isConcept = :isConcept "
+ "where t.masterTerm <> t and t.masterTerm.id in
:masterTermIds", false);
query.setParameter("isConcept", TermEjb.TRUE);
query.setParameter("masterTermIds", masterTermIds);

3. Execute query on MySQL. EclipseLink will try to create a temporary table
with this SQL:
SELECT t1.ID, ?, t0.ID, (t1.VERSION + ?) FROM TERMEJB t0, TERMEJB t2, TERMEJB
t1
WHERE ((NOT ((t1.MASTERTERM_ID = t1.ID))
AND (t2.ID IN (?)))
AND (t2.ID = t1.MASTERTERM_ID)

Please note: The unconstrained JOIN of table t0 makes this query incorrect. If
you add a AND t0.ID = t1.ID the query is still quite complex, but at least
correct.

4. Because the query shown in step 3 returns more than one result you will get
a
Internal Exception: java.sql.BatchUpdateException: Duplicate entry '102' for
key 'PRIMARY'
Error Code: 1062
Query: UpdateAllQuery(referenceClass=TermEjb sql="CREATE TEMPORARY TABLE IF NOT
EXISTS TL_TERMEJB (ID INTEGER NOT NULL, VARIANTSCONFIGURATIONS VARCHAR(255),
EXPLICITVERSION INTEGER, CONCEPTIDCOLLATIONKEY VARCHAR(255), FREQUENCY BIGINT,
EXTERNAL_ID VARCHAR(255), VERSION INTEGER, CREATOR VARCHAR(255), CREATIONDATE
DATETIME, CONCEPTUUID VARCHAR(255), CONCEPTID VARCHAR(255), LASTMODIFIER
VARCHAR(255), EXTERNALIDCOLLATIONKEY VARCHAR(255), UUID VARCHAR(255),
LASTMODIFICATIONDATE DATETIME, ISCONCEPT CHAR(1), MOSSURFACE_ID INTEGER,
MASTERTERM_ID INTEGER, topLevelLanguage_id INTEGER, PRIMARY KEY (ID))")

- Can someone confirm if this is a bug in EclipseLink and not a problem in our setup?

- Does anyone know, if there is a workaround? Or can provide a patch.

Kind regards,
Hans
Re: MySQL: update on self-referencing foreign key tables leads to Duplicate Key Exception [message #638569 is a reply to message #638415] Thu, 11 November 2010 11:19 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

Seems like a bug the duplicate join is generated. I remember fixing something like this for queries, what version are you on, have you tried the latest release?

A workaround is to use a native SQL query. You could also select the objects and update them in Java.



James : Wiki : Book : Blog : Twitter
Re: MySQL: update on self-referencing foreign key tables leads to Duplicate Key Exception [message #638593 is a reply to message #638569] Thu, 11 November 2010 12:33 Go to previous messageGo to next message
Hans Harz is currently offline Hans Harz
Messages: 20
Registered: July 2009
Junior Member
Hi James,

thank you for your fast answer.

- We are using the most current version 2.1.1.v20100817-r8050

- Unfortunately a native query is not an option, because on customer-side our system is working with one of six different database backends.

- If you fixed this already in the current codebase, could you provide a patch or point me to the modified class. Otherwise we must select the objects and change the values one-by-one in java.

best regards,
Hans
Re: MySQL: update on self-referencing foreign key tables leads to Duplicate Key Exception [message #640911 is a reply to message #638569] Tue, 23 November 2010 09:25 Go to previous messageGo to next message
Hans Harz is currently offline Hans Harz
Messages: 20
Registered: July 2009
Junior Member
Hi James,

sorry to bother you. Do you still know if/where/how you fixed the issue mentioned above. Do you may have a Bugzilla case number? It would be great if we know, which class we have to patch.

best regards,
Hans
Re: MySQL: update on self-referencing foreign key tables leads to Duplicate Key Exception [message #641468 is a reply to message #638415] Thu, 25 November 2010 10:25 Go to previous message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

If it is not fixed in 2.1.1, then it is probably still a bug.

Please log the bug.


James : Wiki : Book : Blog : Twitter
Previous Topic:ManyToMany bidirectional deletion
Next Topic:query-by-example and trim?
Goto Forum:
  


Current Time: Wed Apr 16 09:40:18 EDT 2014

Powered by FUDForum. Page generated in 0.01677 seconds