Dup Key Error Messages

--_000_E4F8B4BE4DC7CB4CB082A7541A624E8D2E5B31F696lstk02macne ti_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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 ex=
ample:
Table `device` :

CREATE TABLE `device` (
`id` INT NOT NULL AUTO_INCREMENT ,
`ip_addr` VARCHAR( 15 ) NOT NULL ,
`type` VARCHAR( 32 ) NOT NULL ,
`print_name` VARCHAR( 32 ) NOT NULL ,
PRIMARY KEY ( `id` ) ,
UNIQUE (
`ip_addr` ,
`print_name`
)
) ENGINE =3D InnoDB;


Let's do for example this two queries.

INSERT INTO `device` (ip, type, print_name) VALUES ('192.168.0.1', 'pc', 'd=
esktop1');
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 w=
ho 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'.


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();
}

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


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

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

Greets Thorsten








****************************************
MACNETIX GmbH

Huttenstr. 31
10553 Berlin

Tel. +49 (0) 30 34 34 678 - 0
Fax. +49 (0) 30 34 34 678 - 99

heymann@macnetix.de

Sitz & Gerichtsstand /Registered Office & Place of Jurisdiction : Berlin
Registergericht/ Registered at: HRB 86505 B Berlin-Charlottenburg
Geschäftsführung/ Member of the Management Board: Dirk Wahrheit, Peggy =
Bielke
Bitte beachten Sie unsere Webseite www.macnetix.de=
!

Diese Information ist ausschliesslich fuer den Adressaten bestimmt und kann=
vertraulich oder gesetzlich geschuetzte Informationen enthalten. Wenn Sie =
nicht der bestimmungsgemaesse Adressat sind, unterrichten Sie bitte den Abs=
ender und vernichten Sie diese Mail. Anderen als dem bestimmungsgemaessen A=
dressaten ist es untersagt, diese E-Mail zu lesen, zu speichern, weiterzule=
iten oder ihren Inhalt auf welche Weise auch immer zu verwenden. Wir verwen=
den aktuelle Virenschutzprogramme. Fuer Schaeden, die dem Empfaenger gleich=
wohl durch von uns zugesandte mit Viren befallene E-Mails entstehen, schlie=
ssen wir jede Haftung aus.

The information contained in this email is intended only for its addressee =
and may contain confidential and/or privileged information. If the reader o=
f this email is not the intended recipient, you are hereby notified that re=
ading, saving, distribution or use of the content of this email in any way =
is prohibited. If you have received this email in error, please notify the =
sender and delete the email. We use updated antivirus protection software. =
We do not accept any responsibility for damages caused anyhow by viruses tr=
ansmitted via email.


--_000_E4F8B4BE4DC7CB4CB082A7541A624E8D2E5B31F696lstk02macne ti_--

Re: Dup Key Error Messages

--00163630f397ac395c048f96f14f
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hi

You have a PRIMARY KEY of just the id field like PRIMARY KEY ( `id` ). You
need both ip and type to be the primary key

Cheers
Neil

On Mon, Sep 6, 2010 at 2:02 PM, 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` :
>
> CREATE TABLE `device` (
> `id` INT NOT NULL AUTO_INCREMENT ,
> `ip_addr` VARCHAR( 15 ) NOT NULL ,
> `type` VARCHAR( 32 ) NOT NULL ,
> `print_name` VARCHAR( 32 ) NOT NULL ,
> PRIMARY KEY ( `id` ) ,
> UNIQUE (
> `ip_addr` ,
> `print_name`
> )
> ) ENGINE =3D InnoDB;
>
>
> 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 erro=
r
> 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'.
>
>
> 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();
> }
>
> ... it will not run on mysql 5.1.51.
>
>
> 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. :(
>
> Is there a possible better, reliable way to detect what key is duplictate=
d
> as searching in the error message?
>
> Greets Thorsten
>
>
>
>
>
>
>
>
> ****************************************
> MACNETIX GmbH
>
> Huttenstr. 31
> 10553 Berlin
>
> Tel. +49 (0) 30 34 34 678 - 0
> Fax. +49 (0) 30 34 34 678 - 99
>
> heymann@macnetix.de
>
> Sitz & Gerichtsstand /Registered Office & Place of Jurisdiction : Berlin
> Registergericht/ Registered at: HRB 86505 B Berlin-Charlottenburg
> Geschäftsführung/ Member of the Management Board: Dirk Wahrheit, Pegg=
y
> Bielke
> Bitte beachten Sie unsere Webseite www.macnetix.de /
> >!
>
> Diese Information ist ausschliesslich fuer den Adressaten bestimmt und ka=
nn
> vertraulich oder gesetzlich geschuetzte Informationen enthalten. Wenn Sie
> nicht der bestimmungsgemaesse Adressat sind, unterrichten Sie bitte den
> Absender und vernichten Sie diese Mail. Anderen als dem bestimmungsgemaes=
sen
> Adressaten ist es untersagt, diese E-Mail zu lesen, zu speichern,
> weiterzuleiten oder ihren Inhalt auf welche Weise auch immer zu verwenden=
..
> Wir verwenden aktuelle Virenschutzprogramme. Fuer Schaeden, die dem
> Empfaenger gleichwohl durch von uns zugesandte mit Viren befallene E-Mail=
s
> entstehen, schliessen wir jede Haftung aus.
>
> The information contained in this email is intended only for its addresse=
e
> and may contain confidential and/or privileged information. If the reader=
of
> this email is not the intended recipient, you are hereby notified that
> reading, saving, distribution or use of the content of this email in any =
way
> is prohibited. If you have received this email in error, please notify th=
e
> sender and delete the email. We use updated antivirus protection software=
..
> We do not accept any responsibility for damages caused anyhow by viruses
> transmitted via email.
>
>

--00163630f397ac395c048f96f14f--

AW: Dup Key Error Messages

--_000_E4F8B4BE4DC7CB4CB082A7541A624E8D2E5B31F697lstk02macne ti_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

No, I think you misunderstood me. I have these keys (in this example)


- PRIMARY (id)

- UNIQUE (ip_addr)

- UNIQUE (print_name)

And when of of the UNIQUEs is duplicated, I'll wshow user a message WHAT ke=
y is a doublette.

Von: Tompkins Neil [mailto:neil.tompkins@googlemail.com]
Gesendet: Montag, 6. September 2010 15:06
An: Thorsten Heymann
Cc: mysql@lists.mysql.com
Betreff: Re: Dup Key Error Messages

Hi

You have a PRIMARY KEY of just the id field like PRIMARY KEY ( `id` ). You=
need both ip and type to be the primary key

Cheers
Neil
On Mon, Sep 6, 2010 at 2:02 PM, Thorsten Heymann o:heymann@macnetix.de>> 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 ex=
ample:
Table `device` :

CREATE TABLE `device` (
`id` INT NOT NULL AUTO_INCREMENT ,
`ip_addr` VARCHAR( 15 ) NOT NULL ,
`type` VARCHAR( 32 ) NOT NULL ,
`print_name` VARCHAR( 32 ) NOT NULL ,
PRIMARY KEY ( `id` ) ,
UNIQUE (
`ip_addr` ,
`print_name`
)
) ENGINE =3D InnoDB;


Let's do for example this two queries.

INSERT INTO `device` (ip, type, print_name) VALUES ('192.168.0.1', 'pc', 'd=
esktop1');
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 w=
ho 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'.


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();
}

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


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

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

Greets Thorsten








****************************************
MACNETIX GmbH

Huttenstr. 31
10553 Berlin

Tel. +49 (0) 30 34 34 678 - 0
Fax. +49 (0) 30 34 34 678 - 99

heymann@macnetix.de mailto:heymann@macnetix.de>>

Sitz & Gerichtsstand /Registered Office & Place of Jurisdiction : Berlin
Registergericht/ Registered at: HRB 86505 B Berlin-Charlottenburg
Geschäftsführung/ Member of the Management Board: Dirk Wahrheit, Peggy =
Bielke
Bitte beachten Sie unsere Webseite www.macnetix.de<=
http://www.macnetix.de/>!

Diese Information ist ausschliesslich fuer den Adressaten bestimmt und kann=
vertraulich oder gesetzlich geschuetzte Informationen enthalten. Wenn Sie =
nicht der bestimmungsgemaesse Adressat sind, unterrichten Sie bitte den Abs=
ender und vernichten Sie diese Mail. Anderen als dem bestimmungsgemaessen A=
dressaten ist es untersagt, diese E-Mail zu lesen, zu speichern, weiterzule=
iten oder ihren Inhalt auf welche Weise auch immer zu verwenden. Wir verwen=
den aktuelle Virenschutzprogramme. Fuer Schaeden, die dem Empfaenger gleich=
wohl durch von uns zugesandte mit Viren befallene E-Mails entstehen, schlie=
ssen wir jede Haftung aus.

The information contained in this email is intended only for its addressee =
and may contain confidential and/or privileged information. If the reader o=
f this email is not the intended recipient, you are hereby notified that re=
ading, saving, distribution or use of the content of this email in any way =
is prohibited. If you have received this email in error, please notify the =
sender and delete the email. We use updated antivirus protection software. =
We do not accept any responsibility for damages caused anyhow by viruses tr=
ansmitted via email.


--_000_E4F8B4BE4DC7CB4CB082A7541A624E8D2E5B31F697lstk02macne ti_--

Re: Dup Key Error Messages

On Mon, 6 Sep 2010 15:02:24 +0200, Thorsten Heymann
wrote:
> 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. :(
>
> Is there a possible better, reliable way to detect what key is
duplictated
> as searching in the error message?

Not really. And to make matters worse, this isn't just a version issue.
You'd have the same type of problems if your app connects to a server
installed with a non-Enlish language pack.

The only reliable thing I can think of is to detect that you got an error
1062, then do a db search to find out which field or fields have duplicate
information. Cumbersome, but OTOH you'd also get a chance to realize if
more than one key field is duped.

/ Carsten



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

AW: Dup Key Error Messages

Tm90IHRoZSBhbnN3ZXIgSSBob3BlZCB0byBnZXQgYnV0LCB0aGFua3MgQ2Fy c3RlbiENCg0KLS0t
LS1VcnNwcsO8bmdsaWNoZSBOYWNocmljaHQtLS0tLQ0KVm9uOiBDYXJzdGVu IFBlZGVyc2VuIFtt
YWlsdG86Y2Fyc3RlbkBiaXRieWJpdC5ka10gDQpHZXNlbmRldDogTW9udGFn LCA2LiBTZXB0ZW1i
ZXIgMjAxMCAxNToxNw0KQW46IFRob3JzdGVuIEhleW1hbm4NCkNjOiBteXNx bEBsaXN0cy5teXNx
bC5jb20NCkJldHJlZmY6IFJlOiBEdXAgS2V5IEVycm9yIE1lc3NhZ2VzDQoN Ck9uIE1vbiwgNiBT
ZXAgMjAxMCAxNTowMjoyNCArMDIwMCwgVGhvcnN0ZW4gSGV5bWFubiA8aGV5 bWFubkBtYWNuZXRp
eC5kZT4NCndyb3RlOg0KPiBEaWdnaW5nIHRocm91Z2ggbXlzcWxkIHNvdXJj ZSwgSSBmb3VuZCB0
aGlzIGJlaGF2aW91ciBoYW5kbGVkIGluDQo+IHNxbC9oYW5kbGVyLmNjIGFu ZCBjaGFuZ2VkIGZy
b20gcHJpbnRpbmcga2V5X25yIHRvIGtleS5uYW1lIGJldHdlZW4gdGhpcw0K PiB2ZXJzaW9ucy4g
OigNCj4gDQo+IElzIHRoZXJlIGEgcG9zc2libGUgYmV0dGVyLCByZWxpYWJs ZSB3YXkgdG8gZGV0
ZWN0IHdoYXQga2V5IGlzDQpkdXBsaWN0YXRlZA0KPiBhcyBzZWFyY2hpbmcg aW4gdGhlIGVycm9y
IG1lc3NhZ2U/DQoNCk5vdCByZWFsbHkuIEFuZCB0byBtYWtlIG1hdHRlcnMg d29yc2UsIHRoaXMg
aXNuJ3QganVzdCBhIHZlcnNpb24gaXNzdWUuDQpZb3UnZCBoYXZlIHRoZSBz YW1lIHR5cGUgb2Yg
cHJvYmxlbXMgaWYgeW91ciBhcHAgY29ubmVjdHMgdG8gYSBzZXJ2ZXINCmlu c3RhbGxlZCB3aXRo
IGEgbm9uLUVubGlzaCBsYW5ndWFnZSBwYWNrLiANCg0KVGhlIG9ubHkgcmVs aWFibGUgdGhpbmcg
SSBjYW4gdGhpbmsgb2YgaXMgdG8gZGV0ZWN0IHRoYXQgeW91IGdvdCBhbiBl cnJvcg0KMTA2Miwg
dGhlbiBkbyBhIGRiIHNlYXJjaCB0byBmaW5kIG91dCB3aGljaCBmaWVsZCBv ciBmaWVsZHMgaGF2
ZSBkdXBsaWNhdGUNCmluZm9ybWF0aW9uLiBDdW1iZXJzb21lLCBidXQgT1RP SCB5b3UnZCBhbHNv
IGdldCBhIGNoYW5jZSB0byByZWFsaXplIGlmDQptb3JlIHRoYW4gb25lIGtl eSBmaWVsZCBpcyBk
dXBlZC4gDQoNCi8gQ2Fyc3Rlbg0KDQoNCg0KDQo=

Re: AW: Dup Key Error Messages

On 9/6/2010 9:10 AM, Thorsten Heymann wrote:
> No, I think you misunderstood me. I have these keys (in this example)
>
>
> - PRIMARY (id)
>
> - UNIQUE (ip_addr)
>
> - UNIQUE (print_name)
>
> And when of of the UNIQUEs is duplicated, I'll wshow user a message WHAT key is a doublette.
>

Actually, that was not what you posted above. In your original example
you have one primary key and one unique key. That unique key is based on
the combination of values {`ip_addr`,`print_name`}. This means that all
of these values pairs are unique

'192.168.1.1','queue1'
'192.168.1.1','queue2'
'192.168.2.1','queue1'
'192.168.2.1','queue2'

If I assume that each row was assigned a sequential ID value, then these
are rows 1..4 . If I attempted to modify row 1 like this

UPDATE `device` SET `print_name` = 'queue_2' where ID = 1;

Then I would hit a conflict. The combination of

{ip_addr='192.168.1.1', `print_name='queue2'}

already exists in the row where the ID value is 2.

Even if I parsed the error entire message, it would not tell me which
element of this UNIQUE key I violated, only that the combination already
exists and which constraint is protecting that combination.
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Dup Key Error Messages

Hi Thorsten, all!


Thorsten Heymann wrote:
> Hi,
>=20
> First, I'm new to this mailing list, hopefully I'll do my post the *rig=
ht* way.
>=20
> I've a problem with duplicate key error messages. In my application I u=
se a table with multiple unique keys (ip_addr and print_name). Lets use t=
his example:
> Table `device` :
>=20
> [[...]]
>=20
> Let's do for example this two queries.
>=20
> 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', 'rou=
ter', 'gw1');
>=20
> (Note the duplicate ip 192.168.0.1)
>=20
>=20
> My problem is the error message sent MySql Server if a duplicate key er=
ror occurres during insert / update. I have one machine running mysqld 5.=
0.41 who produces this message:
>=20
> Duplicate entry '192.168.0.1' for key 2.
>=20
> On the other machine who runs mysqld 5.1.51 I got the following:
>=20
> 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.

>=20
>=20
> Since my application does something like this:
>=20
> 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.

>=20
> ... 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.

>=20
>=20
> Digging through mysqld source, I found this behaviour handled in sql/ha=
ndler.cc and changed from printing key_nr to key.name between this versio=
ns. :(

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.

>=20
> Is there a possible better, reliable way to detect what key is duplicta=
ted 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

--=20
Joerg Bruehe, MySQL Build Team, joerg.bruehe@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=3Dgcdmg-mysql-2@m.gmane.o rg

AW: Dup Key Error Messages

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 wo=
uld 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_inse=
rt_id,...). Sure the mysqld and mysqlc [,...?] code has to be changed for t=
his.

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

Regards
Thorsten


-----Ursprüngliche Nachricht-----
Von: Joerg Bruehe [mailto:joerg.bruehe@oracle.com]=20
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,
>=20
> First, I'm new to this mailing list, hopefully I'll do my post the *right=
* way.
>=20
> 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` :
>=20
> [[...]]
>=20
> Let's do for example this two queries.
>=20
> 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', 'route=
r', 'gw1');
>=20
> (Note the duplicate ip 192.168.0.1)
>=20
>=20
> My problem is the error message sent MySql Server if a duplicate key erro=
r occurres during insert / update. I have one machine running mysqld 5.0.41=
who produces this message:
>=20
> Duplicate entry '192.168.0.1' for key 2.
>=20
> On the other machine who runs mysqld 5.1.51 I got the following:
>=20
> 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.

>=20
>=20
> Since my application does something like this:
>=20
> 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.

>=20
> ... 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.

>=20
>=20
> Digging through mysqld source, I found this behaviour handled in sql/hand=
ler.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.

>=20
> Is there a possible better, reliable way to detect what key is duplictate=
d 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

--=20
Joerg Bruehe, MySQL Build Team, joerg.bruehe@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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: AW: Dup Key Error Messages

On 9/7/2010 3:33 AM, Thorsten Heymann wrote:
> 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.

I have to agree. Due to database design issues that the end user has no
knowledge of, it is very common for the standard mysql error messages to
be cryptic at best. I always intercept duplicate key errors and display
an error that is much more meaningful to the end users.

For errors that I am not expecting, I never display them to the end
users (I normally log them and email them to myself so I know there is a
problem.) Many consider displaying raw error messages to the end user
to be a security risk, especially in a web application.


That said, I do try and write my code and design my database such that
even if my full schema and source code were available to the public, I
wouldn't have to worry about security. However I'm sure my code isn't
perfect, and I don't have full control over all of the possible ways my
web sites could be compromised so I'm certainly not going to give an
attacker help by displaying raw error messages.

Chris W

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org