MySQL Bugs: #52013: Unique constraint violation warning deferred

Bug #52013	Unique constraint violation warning deferred
Submitted:	12 Mar 2010 19:46	Modified:	29 Nov 2013 11:53
Reporter:	Todd Farmer (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster/J	Severity:	S2 (Serious)
Version:	7.1+	OS:	Any
Assigned to:	Jon Stephens	CPU Architecture:	Any

Description:
When inserting a duplicate row/object using Cluster/J, the resulting Exception is not thrown until commit():

com.mysql.clusterj.ClusterJDatastoreException: Error in NdbJTie: returnCode -1, code 630, mysqlCode 121, status 2, classification 3, message Tuple already existed when attempting to insert .
    at com.mysql.clusterj.tie.Utility.throwError(Utility.java:552)
    at com.mysql.clusterj.tie.ClusterTransactionImpl.handleError(ClusterTransactionImpl.java:306)
    at com.mysql.clusterj.tie.ClusterTransactionImpl.executeCommit(ClusterTransactionImpl.java:110)
    at com.mysql.clusterj.core.SessionImpl.internalCommit(SessionImpl.java:600)
    at com.mysql.clusterj.core.SessionImpl$2.commit(SessionImpl.java:771)
    at com.mysql.clusterj.core.SessionImpl.commit(SessionImpl.java:579)
    at com.mysql.clusterj.core.TransactionImpl.commit(TransactionImpl.java:48)
    at firstsimpletest.insertTest(firstsimpletest.java:156)
    at firstsimpletest.main(firstsimpletest.java:20) 

This is different than the behavior of issuing the logically-equivalent statement against an NDB table via mysqld:

mysql> set autocommit = off;
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t_fish_food values(1, 'testing', 'again', 13);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

How to repeat:
tx.begin();
session.persist(fish);
tx.commit();

Suggested fix:
Throw Exception on persist().

It's an optimization, to avoid round-trip it will defer contacting ndb until "forced" to. (This is equivalent to using "set sql_allow_batching=1;")

For JPA i think that we should
1) create an explicit flush (unless already existing)
2) create a "flush after each operation"-setting (default off) (unless already existing)
3) document behavior

For immediate feedback use autotransaction. Instead of:

tx.begin();
session.persist(fish);
tx.commit();

do this:

session.persist(fish);

You will get an immediate exception.

I'll see where this behavior can be documented.

So, to be clear, what we are saying then is that the only time Cluster/J and MySQL SQL (JDBC) behavior is the same is when auto-commit is enabled?  If Cluster/J is used in a transactional context, it will exhibit different behavior than the corresponding SQL executed against a SQL node?

I understood Jonas to say that this may be configurable - I would encourage that to be done, and for the default to be behavior consistent with existing behavior.

Posted by developer:
 
I suggest that we update http://dev.mysql.com/doc/ndbapi/en/mccj-using-clusterj-operations.html where we discuss basic operations. Specifically, this section is not precisely correct: 
"If the row with the same id as this Employee instance already exists in the database, the persist() method fails."

This would be a good place to introduce the flush method and the fact that database operations in general are deferred until commit unless autocommit is on. 

We can start with a brief enhanced description:

If autocommit is on, and a row with the same id as this Employee instance already exists in the database, the persist() method fails. If autocommit is off and a row with the same id as this Employee instance already exists in the database, the persist() method succeeds but a subsequent commit() fails.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Fixed in mysqldoc rev 36857. Closed.