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.bru...@oracle.com] Gesendet: Montag, 6. September 2010 20:03 An: Thorsten Heymann Cc: mysql@lists.mysql.com 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.bru...@oracle.com 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
