From: Michael Widenius
Date: March 16 1999 12:56am
Subject: replace problems (ctnd)
List-Archive: http://lists.mysql.com/mysql/296
Message-Id: <14061.43958.675538.611259@monty.pp.sci.fi>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

>>>>> "Eric" == Eric D Patterson <slacker@stripped> writes:

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

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

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

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

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

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

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

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


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

Hi!

Have you run isamchk on the above table ?  Do you get any warnings ?

Please check with 'mysqldump --no-date database SerDeal'  that you
really have a primary key!

If you can ftp a full test table to:

ftp://www.mysql.com/pub/mysql/secret

together with your 2 replace statement,  I will take a close look at
this!

Regards,
Monty