Bug #3379 Replace deletes wrong record
Submitted: 3 Apr 2004 6:39 Modified: 3 Apr 2004 8:27
Reporter: SINISA MILIVOJEVIC Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.0.19 OS:Any (any)
Assigned to: Sergei Golubchik CPU Architecture:Any

[3 Apr 2004 6:39] SINISA MILIVOJEVIC
Description:
A table has one primary and one unique index.

When column in primary index is not specified in REPLACE command and both columns in UNIQUE index are specified, MySQL deletes a record containing only one of the columns in UNIQUE index, although a second column is in the mismatch.

So, instead of adding a new column, the old one is removed, although column values do not match.

How to repeat:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
  poolID int(11) NOT NULL default '0',
  thunk int(11) NOT NULL default '0',
  rushID bigint(20) NOT NULL default '0',
  start int(11) NOT NULL default '0',
  finish int(11) NOT NULL default '0',
  databytes int(11) NOT NULL default '0',
  id int(11) NOT NULL auto_increment,
  composite int(11) default NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY thunk (thunk),
  UNIQUE KEY composite (composite),
  KEY rushID (rushID,start)
) TYPE=MyISAM;
INSERT INTO t1 (poolID,thunk,rushID,start, finish,databytes, composite) VALUES (2,1611071, 193514046953637,1886, 1991,772096,35165505);
SELECT * from t1;
REPLACE INTO t1 (poolID, thunk, rushID, start, finish, dataBytes, composite) VALUES (1, 1611071, 193514046953957, -1, -1, -1, 18388287);
SELECT * from t1;
DROP TABLE IF EXISTS t1;
[3 Apr 2004 8:27] Sergei Golubchik
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

REPLACE deletes all rows that has a collision on at least one UNIQUE key.
As the second row you insert contain the same value in the thunk column (which is UNIQUE) as the first row, the first row has to be removed.