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

[12 Mar 2010 19:46] Todd Farmer
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().
[13 Mar 2010 6:17] Jonas Oreland
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
[16 Mar 2010 21:27] Craig Russell
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.
[17 Mar 2010 3:32] Todd Farmer
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.
[7 Nov 2013 1:45] Craig Russell
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.
[29 Nov 2013 11:53] Jon Stephens
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.