You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Michael Shea <mi...@nitido.com> on 2009/06/17 23:51:31 UTC
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 "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.
Re: Foreign key constraint problem.
Posted by Michael Shea <mi...@nitido.com>.
I've actually already tried using a non-meaningful primary key, but I
still want to have a unique constraint on the columns in question if I
do that, because it should not be possible to have an Email assigned to
the same Task twice.... Doing it with a unique constraint results in the
same problem as using these columns as the primary key =).
I agree that one should just alter the record rather than deleting and
re-adding it... Unfortunately, I am providing a library, and given the
API that we've got, I can't really prohibit front-end developers from
doing this; I can only try to deal with the fallout if they do.
I looked into InnoDB a bit, which is the storage system I am using. It
looks like InnoDB doesn't defer checking of constraints to the end of
the transaction. From this link
(http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html):
"*Deviation from SQL standards*: Like MySQL in general, in an SQL
statement that inserts, deletes, or updates many rows, |InnoDB| checks
|UNIQUE| and |FOREIGN KEY| constraints row-by-row. According to the SQL
standard, the default behavior should be deferred checking. That is,
constraints are only checked after the /entire SQL statement/ has been
processed. Until |InnoDB| implements deferred constraint checking, some
things will be impossible, such as deleting a record that refers to
itself via a foreign key."
Anyway. It sounds like it would be *possible* to get around this by
using SET FOREIGN_KEY_CHECKS=0 and SET_FOREIGN_KEY_CHECKS=1 at the
start/end of transactions... But I believe that this would also mean
that any keys modified during the transactions wouldn't have integrity
checks enforced at all (Source:
http://code.djangoproject.com/ticket/3615), so although this could solve
my particular problem, it could definitely introduce other ones =).
This all implies to me that the real bug here is with InnoDB, and not
with Cayenne. Thanks for the help, I'll just work around it until such
time as InnoDB gets fixed ;).
Shea.
> On 18/6/09 7:51 AM, Michael Shea wrote:
>> 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.
>
> You can turn debugging on and see the SQL statements being generated
> by Cayenne to verify what is happening. But my guess is that since the
> entire operation is in one transaction, MySQL is performing constraint
> checks before committing that transaction.
>
> Perhaps you should consider using a non-meaningful generated primary
> key, or just altering the record rather than deleting and recreating it.
>
> Ari Maniatis
>
Re: Foreign key constraint problem.
Posted by Aristedes Maniatis <ar...@maniatis.org>.
On 18/6/09 7:51 AM, Michael Shea wrote:
> 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.
You can turn debugging on and see the SQL statements being generated by Cayenne to verify what is happening. But my guess is that since the entire operation is in one transaction, MySQL is performing constraint checks before committing that transaction.
Perhaps you should consider using a non-meaningful generated primary key, or just altering the record rather than deleting and recreating it.
Ari Maniatis