In the change log for 3.23.29:
Fixed a bug in UPDATE involving multi-part keys where one specified all key
parts both in the update and the WHERE part. In this case MySQL could try to
update a record that didn't match the whole WHERE part.
I think that was fixed. Try upgrading your mysql server to the latest
3.23.39 and try testing that bug.
thanks,
-- Andrew
----- Original Message -----
From: "John Nagle" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, July 12, 2001 1:07 PM
Subject: UPDATE which changes nothing gets duplicate error - bug?
> We have a table "ticker" with five primary keys. Sometimes we
> do an UPDATE but don't change any values. This produces the
> error "ERROR 1062: Duplicate entry". The manual indicates that
> a no-change UPDATE is allowed. (ref page 583) Is this a bug,
> or a documentation error?
>
> There are no auto-increment fields involved.
>
> Server 3.23.28-gamma, running on Win2K, SP1.
>
> John Nagle
> Downside.com
>
> mysql> describe ticker;
>
+------------------------+-------------------------------------------+------
+-----+------------+-------+---------------------------------+
> | Field | Type |
> Null | Key | Default | Extra | Privileges |
>
+------------------------+-------------------------------------------+------
+-----+------------+-------+---------------------------------+
> | symbol | varchar(5)
> | | PRI | | | select,insert,update,references |
> | exchange_section | char(3)
> | | PRI | | | select,insert,update,references |
> | exchange | varchar(6)
> | | PRI | | | select,insert,update,references |
> | cik | bigint(10)
> | | MUL | 0 | | select,insert,update,references |
> | company_conformed_name | varchar(120)
> | | MUL | | | select,insert,update,references |
> | security_name | varchar(120)
> | | PRI | | | select,insert,update,references |
> | valid_from | date
> | | PRI | 0000-00-00 | | select,insert,update,references |
> | valid_to | date
> | | | 0000-00-00 | | select,insert,update,references |
> | data_source | varchar(6)
> | | | | | select,insert,update,references |
> | cik_confidence | smallint(1)
> | | | 9 | | select,insert,update,references |
> | security_class | enum('normal','bank','foreign','unknown') |
> YES | | unknown | | select,insert,update,references |
> | errors | text |
> YES | | NULL | | select,insert,update,references |
>
+------------------------+-------------------------------------------+------
+-----+------------+-------+---------------------------------+
> 12 rows in set (0.00 sec)
>
> mysql> SELECT * FROM ticker WHERE symbol='ACSEF'
> -> AND exchange='NASDAQ'
> -> AND exchange_section='SCM'
> -> AND security_name='ACS-Tech80 Limited - Common Stock'
> -> AND valid_from='2001-05-24';
>
+--------+------------------+----------+-----+------------------------+-----
------------------------------+------------+------------+-------------+-----
-----------+----------------+-----------------------------------------+
> | symbol | exchange_section | exchange | cik | company_conformed_name |
> security_name | valid_from | valid_to |
> data_source | cik_confidence | security_class |
> errors |
>
+--------+------------------+----------+-----+------------------------+-----
------------------------------+------------+------------+-------------+-----
-----------+----------------+-----------------------------------------+
> | ACSEF | SCM | NASDAQ | 0 | |
> ACS-Tech80 Limited - Common Stock | 2001-05-24 | 2001-07-11 |
> EDGAR | 7 | unknown | Company name "ACS TECH80
> LTD" not found |
>
+--------+------------------+----------+-----+------------------------+-----
------------------------------+------------+------------+-------------+-----
-----------+----------------+-----------------------------------------+
> 1 row in set (0.00 sec)
>
> mysql> UPDATE ticker SET
> symbol='ACSEF',exchange_section='SCM',exchange='NASDAQ',
> -> cik='0',company_conformed_name='',security_name='ACS-Tech80
> Limited - Common Stock',
> ->
> security_class='unknown',valid_from='2001-05-24',valid_to='2001-07-11',
> -> data_source='EDGAR',errors='Company name "ACS TECH80 LTD" not
> found',cik_confidence='7'
> -> WHERE symbol='ACSEF'
> -> AND exchange='NASDAQ'
> -> AND exchange_section='SCM'
> -> AND security_name='ACS-Tech80 Limited - Common Stock'
> -> AND valid_from='2001-05-24';
> ERROR 1062: Duplicate entry 'ACSEF-NASDAQ-SCM-ACS-Tech80 Limited -
> Common Stock-2001-05-24' for key 4
>
> ---------------------------------------------------------------------
> 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
