Re: Duplicate key delete record with same key
Hi, Could you give us SHOW CREATE TABLE your_table ? Greetings from France too ;) Jocelyn - Original Message - From: BPF Webmaster [EMAIL PROTECTED] To: MySQL [EMAIL PROTECTED] Sent: Thursday, November 21, 2002 2:25 PM Subject: Duplicate key delete record with same key Using PHP 4.2.3 with MySQL 3.23.52 Report: --- - Table has 2 keys: 'account' as PRIMARY and 'id' as unique - I make an SQL update on an existing a record with id=anid setting id=anotherid but another record exists with id =anotherid - The update is performed and the record already existing with id=anotherid is deleted!!! Expected behavior: -- - Update failed with Duplicate key attempt message I guess it's a bug, isn't it? The workaround is very easy but I really guess the behavior is weird. Greetings from France, JM - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE : Duplicate key delete record with same key
Sure: CREATE TABLE `reg_member` ( `account` bigint(20) NOT NULL auto_increment, `userid` varchar(48) NOT NULL default 'Your name', `uilogin` tinyint(4) NOT NULL default '0', `id` varchar(48) NOT NULL default '', `password` varchar(10) NOT NULL default '', `joined` bigint(20) default NULL, `expired` bigint(20) default NULL, `status` varchar(20) NOT NULL default '', PRIMARY KEY (`account`), UNIQUE KEY `id` (`id`) ) TYPE=MyISAM A+ JM -Original Message- From: Jocelyn Fournier [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 21, 2002 3:31 PM To: BPF Webmaster; MySQL Subject: Re: Duplicate key delete record with same key Hi, Could you give us SHOW CREATE TABLE your_table ? Greetings from France too ;) Jocelyn - Original Message - From: BPF Webmaster [EMAIL PROTECTED] To: MySQL [EMAIL PROTECTED] Sent: Thursday, November 21, 2002 2:25 PM Subject: Duplicate key delete record with same key Using PHP 4.2.3 with MySQL 3.23.52 Report: --- - Table has 2 keys: 'account' as PRIMARY and 'id' as unique - I make an SQL update on an existing a record with id=anid setting id=anotherid but another record exists with id =anotherid - The update is performed and the record already existing with id=anotherid is deleted!!! Expected behavior: -- - Update failed with Duplicate key attempt message I guess it's a bug, isn't it? The workaround is very easy but I really guess the behavior is weird. Greetings from France, JM - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: RE : Duplicate key delete record with same key
BPF, Thursday, November 21, 2002, 4:36:58 PM, you wrote: BW CREATE TABLE `reg_member` ( BW `account` bigint(20) NOT NULL auto_increment, BW `userid` varchar(48) NOT NULL default 'Your name', BW `uilogin` tinyint(4) NOT NULL default '0', BW `id` varchar(48) NOT NULL default '', BW `password` varchar(10) NOT NULL default '', BW `joined` bigint(20) default NULL, BW `expired` bigint(20) default NULL, BW `status` varchar(20) NOT NULL default '', BW PRIMARY KEY (`account`), BW UNIQUE KEY `id` (`id`) BW ) TYPE=MyISAM I tested it with 3.23.53 and it worked fine for me: mysql update `reg_member` set id=4 where id=2; ERROR 1062: Duplicate entry '4' for key 2 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE : RE : Duplicate key delete record with same key
Victoria, So I looked deeper in my PHP code (I wrote it in February 2002) and saw I use a REPLACE statement: class persistent { // put method to save properties into the DB function put($thePrefix) { // job not done yet $returnValue = FALSE; // build table name and start the SQL REPLACE statement $sqlQuery = REPLACE INTO .$thePrefix.get_class($this). SET ; // build the SQL REPLACE query foreach(get_object_vars($this) as $property=$value) { $sqlQuery .= $property. ='.$value.', ; } // remove the , at the end of the string $sqlQuery = substr($sqlQuery, 0, strlen($sqlQuery)-2); // connect to the DB, put the object and close the DB if( $dbLink = mysql_connect(DBHOST, DBUSER, DBPASSWD) ) { if( mysql_select_db(DBNAME, $dbLink) ) { if( mysql_query($sqlQuery, $dbLink) ) $returnValue = TRUE; // job done } } mysql_close($dbLink); return($returnValue); // boolean } Etc This is PHP object code to make my member profiles (PHP objects) persistent in the DB. I use REPLACE so the member-put() method can be used to create or store change on member profiles. Therefore I looked at MySQL Manual: http://www.mysql.com/doc/en/REPLACE.html#IDX1431 REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record on a UNIQUE index or PRIMARY KEY, the old record is deleted before the new record is inserted. So the result was predictible, sorry having sollicited the list. JM -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 21, 2002 6:01 PM To: [EMAIL PROTECTED] Subject: re: RE : Duplicate key delete record with same key BPF, Thursday, November 21, 2002, 4:36:58 PM, you wrote: BW CREATE TABLE `reg_member` ( BW `account` bigint(20) NOT NULL auto_increment, BW `userid` varchar(48) NOT NULL default 'Your name', `uilogin` BW tinyint(4) NOT NULL default '0', `id` varchar(48) NOT NULL default BW '', `password` varchar(10) NOT NULL default '', BW `joined` bigint(20) default NULL, BW `expired` bigint(20) default NULL, BW `status` varchar(20) NOT NULL default '', BW PRIMARY KEY (`account`), BW UNIQUE KEY `id` (`id`) BW ) TYPE=MyISAM I tested it with 3.23.53 and it worked fine for me: mysql update `reg_member` set id=4 where id=2; ERROR 1062: Duplicate entry '4' for key 2 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php