From: Eric D. Patterson
Date: March 15 1999 11:24pm
Subject: replace problems (ctnd)
List-Archive: http://lists.mysql.com/mysql/284
Message-Id: <36ED96BC.C5AF2738@halfast.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

I have been consistantly seeing this problem since upgrading to version
3.22.19:

after creating my table, I issue the following command to create a
primary
key (all have already been declared as not null):

ALTER TABLE SerDeal ADD PRIMARY KEY (Serial_No,Model,Dealer)

I then issue 2 replace commands, the first to create (insert) the entry
within the table, the second *should* update the associated data within
that record, as the keys do match up, as you will see later.

(Please note that this is being done through the perl DBI, and looks
kinda' ugly:)

$insertsql = "REPLACE INTO SerDeal
VALUES($data[0],$data[1],$data[2],'$dat
a[3]','$data[4]',$data[5],$data[6],$data[7],$data[8],$data[9],$data[10],$d
ata[11],$data[12],$data[13],$data[14],$data[15],'$data[16]',$data[17],$dat
a[18],$data[19],$data[20],$data[21],$data[22],$data[23],$data[24],$data[25
],$data[26],$data[27],$data[28],$data[29],$data[30],$data[31],$data[32],$d
ata[33],$data[34],0,0,0,0,0,0,0,0,0,0)";
$sth = $dbh->query($insertsql);

Finally, the following select shows that the row does in fact exist
within
the database twice, not just once, despite the key declarations.  Am I
wrong, or is the following not possible, given that I declared these
fields as the primary key?

select Serial_No,Model,Dealer from SerDeal where Serial_No='67110264'
+-----------+--------+--------+
| Serial_No | Model  | Dealer |
+-----------+--------+--------+
| 67110264  | AF5206 |   1010 |
| 67110264  | AF5206 |   1010 |
+-----------+--------+--------+


I hope that all makes sence to everybody, please let me know if you have
heard of a way to solve this one...