Identifier clash in AliasPublicationList table

Alias.updatePublications
exception occured: More than one row with the given identifier was found: ac338ad5-eace-456e-97b1-25848b42894c, for class: org.researchr.www.domain.AliasPublicationList
org.hibernate.HibernateException: More than one row with the given identifier was found: ac338ad5-eace-456e-97b1-25848b42894c, for class: org.researchr.www.domain.AliasPublicationList
at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:92)

Probable cause: AliasPublicationList not an InnoDB table

Problem appears in: http://researchr.org/profile/guwac

Submitted by Eelco Visser on 11 March 2010 at 10:18

This is not so much caused by an identifier clash, as can be seen when querying for the id:

mysql> select * from _PublicationList where id LIKE '%ac338ad5%';
Empty set (0.22 sec)

However, this is probably caused by a duplicate key in the alias field of a PublicationList object, as can be verified by:

mysql> select id,AliasPublicationList_alias from _PublicationList 
       where AliasPublicationList_alias LIKE '%ac338ad5%';
 ---------------------------------- ---------------------------------- 
| id                               | AliasPublicationList_alias       |
 ---------------------------------- ---------------------------------- 
| 2c04d71c7f96465297f0d86f90415d4f | ac338ad5eace456e97b125848b42894c |
| 4fc9470c0d204634a639d8a8016b84e7 | ac338ad5eace456e97b125848b42894c |
| ba0200bb759c44bea5cd5adc1dfaadf2 | ac338ad5eace456e97b125848b42894c |
| cdc821ab0df547568b837827063e0e44 | ac338ad5eace456e97b125848b42894c |
 ---------------------------------- ---------------------------------- 
4 rows in set (0.15 sec)

Apparently there are four equivalent foreign keys. However, I do not see how this is a problem. Surely there is no constraint to prevent this, yet I do not see why there should be any. Do you know of any code, requiring alias reference uniqueness?

We discussed this issue IRL. It is most likely caused by a data model change: an addition of an inverse relation. Before the inverse relation, duplicate keys were allowed and created. After adding the inverse relation, duplicate keys are no longer allowed. Constraints preventing duplicate keys are in program code and not propagated onto the database automatically.

As a solution, we empty the affected tables, since these are caches anyway. The following queries empty the tag cloud tables:

DELETE FROM PublicationList_buckets_PublicationBucket;
DELETE FROM PublicationList_publications_Publication;
DELETE FROM Publication_lists_PublicationList;
DELETE FROM PublicationCategory_publications_Publication;
DELETE FROM PublicationBucket_publications_Publication;
DELETE FROM _PublicationCategory;
DELETE FROM _PublicationListFacet;

UPDATE _Alias SET Alias_list = NULL;
UPDATE _Bibliography SET 
       Bibliography_publicationsByYear=NULL,
       Bibliography_publicationsByTag=NULL, 
       Bibliography_publicationsByAuthor=NULL, 
       Bibliography_list=NULL;
UPDATE _Person SET Person_list = NULL;
UPDATE _ConferenceSeries SET ConferenceSeries_list = NULL;
UPDATE _Tag SET Tag_list = NULL;

DELETE FROM _PublicationList;
DELETE FROM _PublicationBucket;