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 <heym...@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
> 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 = 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 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'.
>
>
> 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 duplictated
> as searching in the error message?
>
> Greets Thorsten
>
>
>
>
>
>
>
>
> ****************************************
> MACNETIX<http://www.macnetix.com/> GmbH
>
> Huttenstr. 31
> 10553 Berlin
>
> Tel. +49 (0) 30 34 34 678 - 0
> Fax. +49 (0) 30 34 34 678 - 99
>
> heym...@macnetix.de<mailto:heym...@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
> Absender und vernichten Sie diese Mail. Anderen als dem bestimmungsgemaessen
> 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-Mails
> entstehen, schliessen 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 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 the
> 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.
>
>
