Foreign key constraint problem.

From: Michael Shea (mik..itido.com)
Date: Wed Jun 17 2009 - 17:51:31 EDT

  • Next message: Aristedes Maniatis: "Re: Foreign key constraint problem."

    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 "somebod..omewhere.com".
    TaskAssignee assignee = getSomebodyFromTask( task );
    task.removeFromAssignees( assignee );
    context.deleteObject( assignee );

    // Get the email object for this address.
    Email email = getEmail( "somebod..omewhere.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.



    This archive was generated by hypermail 2.0.0 : Wed Jun 17 2009 - 17:52:17 EDT