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

Bug #48616	map cluster errors to appropriate MySQL errors / SQLSTATEs
Submitted:	8 Nov 7:10	Modified:	8 Nov 15:09
Reporter:	John David Duncan
Status:	Verified
Category:	Server: Cluster	Severity:	S3 (Non-critical)
Version:	mysql-5.1-telco-6.3	OS:	Any
Assigned to:		Target Version:
Triage:	Triaged: D3 (Medium) / R2 (Low) / E2 (Low)

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".