That sounds like the bug. In my example, I specified all
five primary keys in both the update and the WHERE part, so that
matches the situation reported in the bug. Thanks.
I changed my program to use a REPLACE instead of an UPDATE,
which seems to have worked around the problem. Does that bug ever
affect REPLACE?
John Nagle
Animats
Andrew Schmidt wrote:
>
> 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
