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 09:18  |
 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
|
|
| | | | |
Goto Forum:
Current Time: Thu Oct 10 11:54:38 GMT 2024
Powered by FUDForum. Page generated in 0.04030 seconds |
] 
Search
Help
Register
Login
Home
