MySQL Bugs: #48616: map cluster errors to appropriate MySQL errors / SQLSTATEs

Bug #48616	map cluster errors to appropriate MySQL errors / SQLSTATEs
Submitted:	8 Nov 2009 6:10	Modified:	8 Nov 2009 14:09
Reporter:	John David Duncan	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S3 (Non-critical)
Version:	mysql-5.1-telco-6.3	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Consider the following Java exception:

Sat Nov 07 14:07:24,289 PST 2009 [cds.DBAccess,66] SEVERE State HY000
Sat Nov 07 14:07:24,291 PST 2009 [cds.SubscriberAPI,66] SEVERE <0409> Error purchasing item 3117 for subscriber 6120321.
com.sun.content.server.foundation.exception.PersistenceException: java.sql.SQLException: Got temporary error 899 'Rowid already allocated' from NDB

This is a temporary error from NDB.   The transaction should be retried.  But the application sees the HY000 SQLState and misclassifies it as a fatal error. 

How to repeat:
Create any NDB temporary error condition, using JDBC. 

Suggested fix:
(A) Perhaps NDB temporary errors can be mapped to some SQLState which correctly identifies them, and tells the application to retry the transaction.  

(B) if (A) is impossible, Connector/J can inform the application about the temporary error in some non-standard way.

(C) Alternately *we* can retry the failed transaction -- either in Connector/J or inside mysqld -- as a configurable option.

AFAIK the connector just relays the SQL state that is determined by the server. So this would be a server bug (not mapping a temporary NDB error to an appropriate sql state).

Looking at http://dev.mysql.com/doc/refman/5.1/en/error-messages-server.html I see some more oddities. I.e.

Error: 1205 SQLSTATE: HY000  (ER_LOCK_WAIT_TIMEOUT)
Message: Lock wait timeout exceeded; try restarting transaction

but

Error: 1213 SQLSTATE: 40001  (ER_LOCK_DEADLOCK)
Message: Deadlock found when trying to get lock; try restarting transaction

both are temporary failures that should lead to a retry of the current transaction. Still only the second one maps to class 40.

In other words: I think this is a feature request for the server "Map errors to more sensible SQLSTATE values"

While this seems to be a general MySQL server problem of mapping MySQL errors to SQLSTATE values, there also seem to be no good mapping on NDBAPI errors to MySQL errors. A quick and dirty fix would be to map NDBAPI(899) to MySQL(1213).

Here's a note after a quick look at Ch. 47 of "SQL 99 Complete Really".

40001 is defined as "transaction rollback - serialization failure".  This is close, but maybe it's wrong, because NDB temporary errors don't rollback a transaction (they just fail to commit it). 

I don't see any "standard-defined" codes that really represent NDB temporary errors.  But codes that begin with 5-9 or I-Z are "implementation defined."  So we could choose to map all NDB temporary errors to e.g. "NTxxx".