List:General Discussion« Previous MessageNext Message »
From:Thorsten Heymann Date:September 7 2010 8:33am
Subject:AW: Dup Key Error Messages
View as plain text  
Hello Jörg,

thanks for your detailed answer.

> I don't think it a good approach to scan error message texts for
> automated analysis in an application, this is a race which the
> application (developer) is bound to lose.

Yes sure, but you will consider, it is more than a nice to have to let the user know what
field he filled incorrectly (e.g. in a webform,...). And it would be nice to this in an
automated way.

So, as the key names and their ids are available at this point of code I would suggest to
store them in a static buffer and make them accessible via a method like
'mysql_dup_keys()' or so. (like mysql_error(), mysql_last_insert_id,...). Sure the mysqld
and mysqlc [,...?] code has to be changed for this.

Is this an option? If yes, how can I contribute?

Regards
Thorsten


-----Ursprüngliche Nachricht-----
Von: Joerg Bruehe [mailto:joerg.bruehe@stripped] 
Gesendet: Montag, 6. September 2010 20:03
An: Thorsten Heymann
Cc: mysql@stripped
Betreff: Re: Dup Key Error Messages

Hi Thorsten, all!


Thorsten Heymann wrote:
> Hi,
> 
> First, I'm new to this mailing list, hopefully I'll do my post the *right* way.
> 
> I've a problem with duplicate key error messages. In my application I use a table
> with multiple unique keys (ip_addr and print_name). Lets use this example:
> Table `device` :
> 
> [[...]]
> 
> Let's do for example this two queries.
> 
> INSERT INTO `device` (ip, type, print_name) VALUES ('192.168.0.1', 'pc',
> 'desktop1');
> INSERT INTO `device` (ip, type, print_name) VALUES ('192.168.0.1', 'router', 'gw1');
> 
> (Note the duplicate ip 192.168.0.1)
> 
> 
> My problem is the error message sent MySql Server if a duplicate key error occurres
> during insert / update. I have one machine running mysqld 5.0.41 who produces this
> message:
> 
> Duplicate entry '192.168.0.1' for key 2.
> 
> On the other machine who runs mysqld 5.1.51 I got the following:
> 
> Duplicate entry '192.168.0.1' for key 'ip'.

MySQL 5.0.41 is really old now, and the whole 5.0 series is in extended
support only. IMO, you should update your application to use MySQL 5.1.

> 
> 
> Since my application does something like this:
> 
> If(String.find('key 2', err_msg)){
>      Print 'you have selected the ip twice'.
> } else {
>    Print_generic_error();
> }

I don't think it a good approach to scan error message texts for
automated analysis in an application, this is a race which the
application (developer) is bound to lose.

*If* you want to add your own hints on top of the MySQL error messages,
you should rather go with the error numbers.
MySQL has the policy to never change the meaning of an error number once
it has been used; even if the error becomes obsolete in newer versions,
the number will not be used for other purposes - so going by the error
number is stable and upgradable.

> 
> ... it will not run on mysql 5.1.51.

By "not run" you probably don't mean "It will not work" but just "It
will not extract the column name from the message".

What disturbs me in your code fragment is that you seem to completely
suppress the MySQL error message (unless "Print_generic_error();" writes
it, which I don't know.).
IMO, this is risky and can lead to loss of information if your error
message analysis does not handle some (new or uncommon) message. I
propose you ensure that in most (or all?) cases you (also) output the
MySQL message, to avoid such losses.

> 
> 
> Digging through mysqld source, I found this behaviour handled in sql/handler.cc and
> changed from printing key_nr to key.name between this versions. :(

Most users would consider getting the column name in the message an
improvement. Basically, in 5.1 the MySQL server now does what your
application had to do in 5.0: Translate a number to a column name.
Of course, changes in the message text cause trouble if it is processed
automatically.

> 
> Is there a possible better, reliable way to detect what key is duplictated as
> searching in the error message?

I am not aware of any.

You could go by the error number and then extract the second quoted
string from the message, in 5.1 it holds the column name.
However, I don't think there is a guarantee this will never change.


Regards,
Jörg

-- 
Joerg Bruehe,  MySQL Build Team,  joerg.bruehe@stripped
ORACLE Deutschland B.V. & Co. KG,   Komturstrasse 18a,   D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
Amtsgericht Muenchen: HRA 95603



Thread
Dup Key Error MessagesThorsten Heymann6 Sep
  • Re: Dup Key Error MessagesTompkins Neil6 Sep
    • AW: Dup Key Error MessagesThorsten Heymann6 Sep
      • Re: AW: Dup Key Error MessagesMySQL)6 Sep
  • Re: Dup Key Error MessagesCarsten Pedersen6 Sep
    • AW: Dup Key Error MessagesThorsten Heymann6 Sep
  • Re: Dup Key Error MessagesJoerg Bruehe6 Sep
    • AW: Dup Key Error MessagesThorsten Heymann7 Sep
      • Re: AW: Dup Key Error MessagesChris W7 Sep