org.hibernate.HibernateException: More than one row with the given identifier was found: 5002526, for class: org.mbari.oni.jpa.entities.ConceptMetadataEntity

[hohonuuli]

This exception is being throw even though there is only one matching identifier in the database. In think this is related to the @OneToOne join between concept and concept metadata. I'm referring to this link, https://stackoverflow.com/questions/24210478/hibernate-more-than-one-row-with-the-given-identifier-was-found-error for a solution.

[hohonuuli]

I changed the query for Concept.findByName:

--- a/oni/src/main/java/org/mbari/oni/jpa/entities/ConceptEntity.java
+++ b/oni/src/main/java/org/mbari/oni/jpa/entities/ConceptEntity.java
@@ -31,11 +31,12 @@ import org.mbari.oni.jpa.IPersistentObject;
 @NamedQueries( {
     @NamedQuery(name = "Concept.eagerFindById", query = "SELECT c FROM Concept c JOIN FETCH c.conceptMetadata m WHERE c.id = :id"),
     @NamedQuery(name = "Concept.findAll", query = "SELECT c FROM Concept c"),
-    @NamedQuery(name = "Concept.findAllByNameGlob", query = "SELECT DISTINCT c FROM Concept c, IN (c.conceptNames) AS n WHERE LOWER(n.name) LIKE :name"),
-    @NamedQuery(name = "Concept.findAllByNameGlobNew", query = "SELECT c FROM Concept c JOIN FETCH c.conceptNames n WHERE LOWER(n.name) LIKE :name"),
+    @NamedQuery(name = "Concept.findAllByNameGlob", query = "SELECT DISTINCT c FROM Concept c LEFT JOIN c.conceptNames n WHERE LOWER(n.name) LIKE :name"),
+    @NamedQuery(name = "Concept.findAllByNameGlobNew", query = "SELECT c FROM Concept c LEFT JOIN c.conceptNames n WHERE LOWER(n.name) LIKE :name"),
     @NamedQuery(name = "Concept.findByAphiaId", query = "SELECT c FROM Concept c WHERE c.aphiaId = :aphiaId") ,
     @NamedQuery(name = "Concept.findById", query = "SELECT v FROM Concept v WHERE v.id = :id") ,
-    @NamedQuery(name = "Concept.findByName", query = "SELECT c FROM Concept c, IN (c.conceptNames) AS n WHERE n.name = :name"),
+    @NamedQuery(name = "Concept.findByName", query = "SELECT c FROM Concept c LEFT JOIN c.conceptNames n WHERE n.name = :name"),
+//    @NamedQuery(name = "Concept.findByName", query = "SELECT c FROM Concept c, IN (c.conceptNames) AS n WHERE n.name = :name"),
     @NamedQuery(name = "Concept.findByRankLevel", query = "SELECT c FROM Concept c WHERE c.rankLevel = :rankLevel") ,
     @NamedQuery(name = "Concept.findByRankName", query = "SELECT c FROM Concept c WHERE c.rankName = :rankName"),
     @NamedQuery(name = "Concept.findRoot", query = "SELECT c FROM Concept c WHERE c.parentConcept IS NULL")

[hohonuuli]

OK, found out that the DARC database does have duplicate records:

SELECT
    cd.conceptid_fk,
    count(*) as n
FROM
    conceptdelegate cd
GROUP BY cd.conceptid_fk
HAVING count(*) > 1
ORDER BY conceptid_fk ASC

returns 191 rows with more than one concept delegate per concept

[hohonuuli]

SELECT
    *
FROM
    ConceptDelegate
WHERE
    conceptdelegate.conceptid_fk IN (SELECT cd.conceptid_fk
           FROM conceptdelegate cd
           GROUP BY cd.conceptid_fk
           HAVING count(*) > 1)
ORDER BY conceptid_fk ASC

[hohonuuli]

Looks like the old KB app has a bug where it can create an extra conceptdelegate.

[hohonuuli]

It looks like the duplicates are created during adding history:

SELECT
    *
FROM history
WHERE conceptdelegateid_fk IN (SELECT id
                               FROM ConceptDelegate
                               WHERE conceptdelegate.conceptid_fk IN (SELECT cd.conceptid_fk
                                                                      FROM conceptdelegate cd
                                                                      GROUP BY cd.conceptid_fk
                                                                      HAVING count(*) > 1)
                               ORDER BY conceptid_fk ASC)
ORDER BY conceptdelegateid_fk DESC

[hohonuuli]

I added a script, oni_issue6.sh, that removes the duplicate problems. I can't fully fix the issue until the new kb app is done. So here's the steps to resolve:

• [ ] Retire old KB app and use the new one. The old app is the source of the bug, but I can't roll out the permanent fix yet or it will break that app.
• [ ] Add a unique constraint to conceptdelegate.conceptid_fk column to prevent this from happening again.