Bug #21147 The description for each error should include the status permanent/transient
Submitted: 19 Jul 2006 12:55 Modified: 19 Jul 2006 22:10
Reporter: Shawn Green Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Documentation Severity:S4 (Feature request)
Version: OS:
Assigned to: Stefan Hinz CPU Architecture:Any
Tags: error, error code, permanent, transient

[19 Jul 2006 12:55] Shawn Green
Filing per customer's request. Here is how they phrased their initial issue:

Looking through your website I am unable to find anywhere that categorises the various errors that may result from the C API into transient or permanent errors. Can you please point me towards a definitive list?
==end quote==

To fill in a few more details based on later conversations:

Transient errors are those that may go away. The best example of a transient error is a deadlocked query. If we resubmit the original query, the query that we deadlocked with will probably have finished an our original processing can continue

A permanent error will not be executable no matter how many times we retry the statement.  An example of a permanent error would be that a column name does not exist on a table.

How to repeat:
This is a request for a documentation change

Suggested fix:
This is a request for a documentation change
[19 Jul 2006 13:24] MySQL Verification Team
Thank you for the bug report.
[19 Jul 2006 22:10] Stefan Hinz
The distinction between transient and permanent is fuzzy. Taking the example in the bug description: sure, if a column name doesn't exist that's an error
that is likely to last longer than a deadlock, but it's not permanent because ALTER TABLE exists. On the other hand, a syntax error is more permanent, it lasts until MySQL changes server behaviour.
I don't think anyone could come up with a suitable definition of what "transient" and "permanent" means, or where exactly to draw the line. Therefore, I'm closing this feature request.
[20 Jul 2006 8:13] Ian Sparrowhawk
Please do not close this feature request.  The distinction is not fuzzy.  With your NDB API you have NdbError::Status which categorises errors as Temporary or Permanent (or Unknown) as detailed in the documentation referred to us on the NDB API training course:

The ndbapi_retries.cpp example also displays the kind of functionality we need to implement with the C API: 

An error due to an invalid column name is, in database terms, a permanent error.  Yes, it can be rectified but there is no point in an application hammering away at the database trying to execute it's query again and again in the hope that a DBA might come along and correct the table in the meantime; especially in the case of a single threaded system!  This said we don't want to give up on a query if communication with the database is temporarily lost and a retry a second later might prove successful.

We need to write a high-performance, resilient system; we don't want to give up on the first error each time but equally we don't want to keep hitting the same error indefinitely.  Please could you suggest how we might achieve such functionality without the aforementioned error categorisation?
[21 Jul 2006 16:59] Stefan Hinz
In NDB, if you get an error 1297 (Got temporary error %d '%s' from %s) the error is temporary. Any other error is permanent.
We're considering stating this more explicitly in the NDB API documentation.