cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael Shea <m...@nitido.com>
Subject Foreign key constraint problem.
Date Wed, 17 Jun 2009 21:51:31 GMT
Hi guys,

I've got something that I'm not sure is a bug or not =)...

I have a data map which contains entities "Task", "EMail" and 
"TaskAssignee".
An email can be assigned to a task via an entry in the TaskASsignee table.

The DDL for the TaskAssignee table looks like this:

create table TaskAssignee
(
    emailId integer not null,
    taskId integer not null,
   
    primary key (taskId, emailId),
   
    foreign key ( emailId )
        references email( id )
        on delete cascade
        on update cascade,
   
    foreign key ( taskId )
        references task( id )
        on delete cascade
        on update cascade
)

The problem I'm encountering manifests if I attempt to delete an 
Assignee and then add an *identical* assignee to the task. eg:

------ code -------
// Get the context and a task.
ObjectContext context = ...;
Task task = getTask( context );

// remove the assignee for the email address "somebody@somewhere.com".
TaskAssignee assignee = getSomebodyFromTask( task );
task.removeFromAssignees( assignee );
context.deleteObject( assignee );

// Get the email object for this address.
Email email = getEmail( "somebody@somewhere.com" );

// Assign this email to the task.
TaskAssignee newAssignee = context.newObject( TaskAssignee.class );
newAssignee.setEmail( email );
task.addToAssignees( newAssignee );

context.commitChanges();
----- end code -----

At the call to commitChanges(), I encounter an exception like this:

------ exception -------
org.apache.cayenne.CayenneRuntimeException: [v.3.0M5 Dec 09 2008 
00:42:23] Commit Exception
    at 
org.apache.cayenne.access.DataContext.flushToParent(DataContext.java:1199)
    at 
org.apache.cayenne.access.DataContext.commitChanges(DataContext.java:1073)
    ...
Caused by: 
com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: 
Duplicate entry '5-4' for key 1
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1011)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)
    at 
com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)
    at 
com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2019)
    at 
com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1937)
    at 
com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1922)
    at 
org.apache.cayenne.access.jdbc.BatchAction.runAsIndividualQueries(BatchAction.java:195)
    at 
org.apache.cayenne.access.jdbc.BatchAction.performAction(BatchAction.java:82)
    at 
org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:57)
    at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:236)
    at 
org.apache.cayenne.access.DataDomainFlushAction.runQueries(DataDomainFlushAction.java:226)
    at 
org.apache.cayenne.access.DataDomainFlushAction.flush(DataDomainFlushAction.java:144)
    at org.apache.cayenne.access.DataDomain.onSyncFlush(DataDomain.java:820)
    at org.apache.cayenne.access.DataDomain$2.transform(DataDomain.java:787)
    at 
org.apache.cayenne.access.DataDomain.runInTransaction(DataDomain.java:846)
    at org.apache.cayenne.access.DataDomain.onSync(DataDomain.java:784)
    at 
org.apache.cayenne.access.DataContext.flushToParent(DataContext.java:1171)
----- end exception ------

It looks to me like Cayenne is attempting to insert the new row before 
deleting the old one; I notice that in DataNode.performQueries, the 
collection of queries contains 3 queries: An InsertBatchQuery, an 
UpdateBatchQuery and a DeleteBatchQuery. Looks like the insert and 
update are being run before the delete to me, although I haven't looked 
too deeply into this code to try to figure out what's going on.

Anyway, anyone run into something like this before? I'm currently 
working around the issue by holding onto a reference to assignees that 
get removed from tasks and then before commit, I check to see if there 
is an identical assignee in the task that is new.  If so, I remove the 
new assignee object, delete it from the context, and put back the 
preexisting one that was removed. This works, but I'd rather not do it 
if I don't have to =).

Does this sound like a bug? Or a mapping issue? I reverse-engineered the 
database schema in the modeler to create the mapping.

I'm using Cayenne 3.0M5. The database is MySQL 5.something.

Thanks!


Mike Shea.


Mime
View raw message