Errors of constraints in SQL databases

[Archos]

Every driver implementing "database/sql/driver" returns different messages when there are errors related to the integrity of constraints so they can not be handled easily.

Which would be the best way to be solved? With an interface in such standard package to return a same type of error by all drivers?
So we can get the type of error and being able to differenciate it of other errors got from the database.

* * *

These are the messages got by 2 drivers ( under of lines starting with '+' ):

=== Driver "github.com/lib/pq"

--- Constraints messages from the database
 + Column("Int_", Int).PrimaryKey()
    pq: duplicate key value violates unique constraint "type_pkey"
 + Column("Int8_", Int8).UniqueIndex()
    pq: duplicate key value violates unique constraint "type_int8__idx"
 + Column("String_", String).Unique()
    pq: duplicate key value violates unique constraint "type_string__key"

 + types.Unique("Float32_", "Float64_")
    pq: duplicate key value violates unique constraint "type_float32__float64__key"
 + types.UniqueIndex("Int32_", "Int64_")
    pq: duplicate key value violates unique constraint "type_int32__int64__idx"

=== Driver "github.com/go-sql-driver/mysql"

--- Constraints messages from the database
 + Column("Int_", Int).PrimaryKey()
    Error 1062: Duplicate entry '0' for key 'PRIMARY'
 + Column("Int8_", Int8).UniqueIndex()
    Error 1062: Duplicate entry '8' for key 'Type_Int8__idx'
 + Column("String_", String).Unique()
    Error 1062: Duplicate entry 'f...@iana.org' for key 'String_'

 + types.Unique("Float32_", "Float64_")
    Error 1062: Duplicate entry '3.2-6.4' for key 'Float32_'
 + types.UniqueIndex("Int32_", "Int64_")
    Error 1062: Duplicate entry '32-64' for key 'Type_Int32__Int64__idx'

[Damian Gryski]

These look exactly like the error strings returned directly by the database. As nice as it might be to have the driver map these to consistent entries it seems like that making would be tedious to maintain easily become out of date -- SQL doesn't define the equivalent of errno.

What different errors would group together in this new standard you're proposing?

[Archos]

It's easy to handle because the database returns a set of error codes which are mapped by the driver; at least, for the driver "github.com/lib/pq": https://github.com/lib/pq/blob/master/error.go#L152

(I don't know how it is handled in the driver for mysql: https://github.com/go-sql-driver/mysql)

So, if the database returns one of those error codes, the driver could return an error named like ErrConstraint which shouls be defined in the standard package for that all drivers use the same type of error.

[Archos]

I filled up an issue in those drivers to know what the developers of such drivers think about it:

https://github.com/lib/pq/issues/212
https://github.com/go-sql-driver/mysql/issues/202

[Arne Hormann]

github.com/go-sql-driver/mysql returns what MySQL sends, there's no mapping involved.

How should this be changed?

The error code alone is insufficient to infer all information (which index, which field, which table, ...).

Parsing the error string is brittle because it may change a little between versions and there's a whole bunch of them and they are not documented AFAIK.

How should the error types providing all the useful information look?

As is, we provide http://godoc.org/github.com/go-sql-driver/mysql#MySQLError 

Everything else can easily be handled by a support library that does this stuff, but I personally wouldn't want the maintenance burden in the driver.

[gwenn...@gmail.com]

Maybe you are looking for something similar to the Java Spring SQLExceptionTranslator:

http://docs.spring.io/spring/docs/4.0.1.BUILD-SNAPSHOT/javadoc-api/org/springframework/jdbc/support/SQLExceptionTranslator.html

[Maciek Sakrejda]

(a maintainer of pq here)

In general, I think that structured, first-class error information would be a good thing in `database/sql`: ORMs and other tools where automated interaction with the database (and even database schemas) is de rigeur have a lot to gain from this, and plenty of common errors are (conceptually, at least) standard across all SQL databases.

I think specific error types are overkill here, but the Postgres docs [1] suggest that error codes are at least partly standardized by SQL. I'm not sure how useful that would be on its own (or to what extent these standard error codes are supported across other SQL databases), but that may be worth a look. Alternately, per-driver error codes and a translation facility as suggested above may offer the same thing with more work but less bikeshedding.

-Maciek

[1]: http://www.postgresql.org/docs/9.3/static/errcodes-appendix.html

[Kamil Kisiel]

One possibility might be to do something similar to Python's DB-API:

http://www.python.org/dev/peps/pep-0249/#integrityerror

The spec defines some very broad classes of errors and leaves it up to the driver implementation to decide which one to throw.

That being said, I'm in the camp that there's not much value in doing something like this. In lib/pq we recently added a fairly rich error type (https://github.com/lib/pq/blob/master/error.go#L25) so if anyone is interested in distinguishing between specific kinds of errors it's not too hard for them to write a small utility that suits their application.

The implementations of various SQL databases are so vastly different that any kind of general solution would not solve a lot of problems. I think the intersection of people writing fully database-agnostic code and those interested in distinguishing specific error types is small enough that each person or group would have needs specific enough to not be met by any kind of fully general error-handling scheme.

[ma...@joh.to]

My 2M satoshi, from a PostgreSQL person's perspective:

As I think everyone here agrees, parsing error messages is not the way to go: they might get arbitrarily changed around between releases and/or they might be localized.  Parsing them is hard as well, even if the two aforementioned problems weren't present.

As pointed out by Maciek, SQLSTATEs are somewhat standardized, but I'm not exactly sure as to how well that works in practice; I would expect the "error classes" to at least match quite accurately in the most common cases, but it would be an awful caveat if some of them didn't.  PostgreSQL 9.3 also added a lot more information to constraint violation errors (such as the name of the constraint), but my understanding is that a lot of other database implementations don't really provide that information, so adding that to the general interface seems shady at best.

I think being able to translate an error from the driver to a "general interface" as suggested upthread would be the most reasonable thing to do here as far as a general facility goes, but the lowest common denominator would be pretty much only the error class derived from the SQLSTATE.  And that's a *lot* of work for the driver maintainers to do.  I'm concerned that the work might not be justified by the yield.

[Andy Balholm]

On Thursday, December 26, 2013 11:50:06 AM UTC-8, ma...@joh.to wrote:

My 2M satoshi, from a PostgreSQL person's perspective:

As I think everyone here agrees, parsing error messages is not the way to go: they might get arbitrarily changed around between releases and/or they might be localized.  Parsing them is hard as well, even if the two aforementioned problems weren't present.

As pointed out by Maciek, SQLSTATEs are somewhat standardized, but I'm not exactly sure as to how well that works in practice; I would expect the "error classes" to at least match quite accurately in the most common cases, but it would be an awful caveat if some of them didn't.  PostgreSQL 9.3 also added a lot more information to constraint violation errors (such as the name of the constraint), but my understanding is that a lot of other database implementations don't really provide that information, so adding that to the general interface seems shady at best.

Probably the best solution would be to define an interface that a driver's custom error type can implement:

type SQLError interface {

    SQLState() string

}

[Archos]

https://code.google.com/p/go/issues/detail?id=7021