[CDBI] processing mysql errors

Dave Howorth dhoworth at mrc-lmb.cam.ac.uk
Tue Sep 6 10:37:37 BST 2005

Brad Bowman wrote:
>> Thanks for this. Yes DBD::mysql makes some attributes available, 
>> including mysql_errno and mysql_error. I believe these are propagated 
>> up through DBI and Class::DBI. But the detailed information, which 
>> users want and which my attempt at automatic recovery needs, is buried 
>> in the MySQL mysql_error string, AFAIK. So it seems like I need to 
>> parse that and I can't believe I'm the first to want to do it :)
> You're not the first.  Although it shames me to admit it, I'm rewriting
> the "Duplicate entry" key violation errors.  I couldn't find a module to
> do this, nor much guidance in the MySQL docs.  The code therefore
> depends on the formatting and other, possibly unstable, properties of
> the error message.

It was the duplicate key message that was my first target too :)

One ray of hope I had about the error message dependency is that AFAIK 
MySQL do make the printf formats available so it might be possible to 
parse the parameters out with a scanf in a slightly less risky way.

> It has worked ok for my modest needs but it's inherently flaky.
> For instance, to rewrite a message like:
>   "Duplicate entry '123' for key 2"
> You need to know what key 2 is and the only documentation I could find
> is in the CREATE TABLE docs:
>   In the created table, a PRIMARY KEY is placed first, followed by all
>   UNIQUE indexes, and then the non-unique indexes. This helps the MySQL
>   optimizer to prioritize which index to use and also more quickly to
>   detect duplicated UNIQUE keys.
> So I'm dependent on something which happens to suit the optimizer.
> Furthmore, the '123' data string has dashes inserted between the items
> for multi-column data.  I vaguely recollect dashes in the data being
> unescaped, so you may hit some ambiguity.
> See also:
>   DESCRIBE $table
>   SHOW INDEX FROM $table
> Have I scared you off this approach yet?

I didn't get much encouragement either here or on the perl at mysql list so 
I just implemented a special case function that seems to work in the 
particular case I need. I punted a more general solution to some other 
day (year?, century?).

It seems to me that the fundamental problem is that MySQL doesn't make 
enough structured details available when there's an error. So the 
problem will always be a nightmare until they change that.

Cheers, Dave

More information about the ClassDBI mailing list