MySQL Bugs: #121507: REPLACE statement fails with ERROR 1062

Bug #121507	REPLACE statement fails with ERROR 1062
Submitted:	15 Oct 3:22	Modified:	20 Oct 8:40
Reporter:	Hao Wang	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	8.0.46, 8.4.11, 9.7.2, 26.07	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
REPLACE statement fails with ERROR 1062, but it should delete the conflicting row and insert

According to the MySQL reference manual,`REPLACE`works exactly like `INSERT`, except that if an old row in the table has the same value as a new row for a `PRIMARY KEY` or a `UNIQUE` index, the old row is deleted before the new row is inserted. It either inserts, or *deletes* and inserts.

However, on a table REPLACE fails with `ERROR 1062 (23000): Duplicate entry ... for key ...` and leaves the table unchanged. This violates the documented semantics of REPLACE.

How to repeat:
CREATE DATABASE d1;
USE d1;

CREATE TABLE t0 (c1 BLOB (8), c2 NUMERIC (8, 4) SIGNED,
  UNIQUE (c2, c1(3))) AVG_ROW_LENGTH = 3233254698;

ALTER TABLE t0 ADD UNIQUE KEY (c1(2));
ALTER TABLE t0 ADD KEY i1 USING BTREE (c1(2)) INVISIBLE;
ALTER TABLE t0 RENAME COLUMN c1 TO c3;
CREATE UNIQUE INDEX i2 ON t0(c2);
ALTER TABLE t0 RENAME INDEX i2 TO i3;
ALTER TABLE t0 RENAME INDEX i1 TO i2;

INSERT INTO t0 (c2,c3) VALUES (87.1301,null);
REPLACE INTO t0 (c2,c3) VALUES (70,'0');
UPDATE t0 SET c3 = 'F' WHERE (((c2) IN ((c2 & c2))));
UPDATE IGNORE t0 SET c2 = -9.183, c3 = 'S';

-- Table content at this point:
mysql> select * from t0;
+------------+---------+
| c3         | c2      |
+------------+---------+
| 0x53       | -9.1830 |
| 0x46       | 70.0000 |
+------------+---------+
2 rows in set (0.00 sec)

REPLACE INTO t0 (c2,c3) VALUES (-9.183,'F');
-- ERROR 1062 (23000): Duplicate entry '-9.1830' for key 't0.i3'
-- But it should delete the conflicting row and insert

I am not sure that what you report is a bug.

Note that I am not part of MySQL Bug Verification Team, I am a community member trying to help (like in Bug#121514).

First, note that I simplified your reproduction to below.

CREATE TABLE t0 (c1 TEXT (8), c2 NUMERIC (8, 4) SIGNED, UNIQUE u1 (c2, c1(3)), UNIQUE u2 (c1(2)));
ALTER TABLE t0 ADD UNIQUE u3 (c2);
INSERT INTO t0 (c2,c1) VALUES (-9.183,'S'), (70,'F');
REPLACE INTO t0 (c2,c1) VALUES (-9.183,'F');

This fails with ERROR 1062 on MySQL 5.7.44, 8.0.36, 8.0.46, 8.4.11, 9.7.2 and 26.7.0.

for mv in 5.7.44 8.0.36 8.0.46 8.4.11 9.7.2 26.7.0; do
  echo; echo $mv
  cd; dbdeployer deploy single mysql_$mv > /dev/null

  ~/sandboxes/msb_mysql_${mv//./_}/use test <<< "
    CREATE TABLE t0 (c1 TEXT (8), c2 NUMERIC (8, 4) SIGNED, UNIQUE u1 (c2, c1(3)), UNIQUE u2 (c1(2)));
    ALTER TABLE t0 ADD UNIQUE u3 (c2);
    INSERT INTO t0 (c2,c1) VALUES (-9.183,'S'), (70,'F');
    REPLACE INTO t0 (c2,c1) VALUES (-9.183,'F');
  "
done

5.7.44
ERROR 1062 (23000) at line 5: Duplicate entry '-9.1830' for key 'u3'

8.0.36
ERROR 1062 (23000) at line 5: Duplicate entry '-9.1830' for key 't0.u3'

8.0.46
ERROR 1062 (23000) at line 5: Duplicate entry '-9.1830' for key 't0.u3'

8.4.11
ERROR 1062 (23000) at line 5: Duplicate entry '-9.1830' for key 't0.u3'

9.7.2
ERROR 1062 (23000) at line 5: Duplicate entry '-9.1830' for key 't0.u3'

26.7.0
ERROR 1062 (23000) at line 5: Duplicate entry '-9.1830' for key 't0.u3'

As you write, the documentation states that [1]:

[1]: https://dev.mysql.com/doc/refman/8.4/en/replace.html

> REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

So let's try "just" INSERT...

for mv in 5.7.44 8.0.36 8.0.46 8.4.11 9.7.2 26.7.0; do
  echo; echo $mv
  ~/sandboxes/msb_mysql_${mv//./_}/use test <<< "
    INSERT INTO t0 (c2,c1) VALUES (-9.183,'F');
  "
done

5.7.44
ERROR 1062 (23000) at line 2: Duplicate entry 'F' for key 'u2'

8.0.36
ERROR 1062 (23000) at line 2: Duplicate entry 'F' for key 't0.u2'

8.0.46
ERROR 1062 (23000) at line 2: Duplicate entry 'F' for key 't0.u2'

8.4.11
ERROR 1062 (23000) at line 2: Duplicate entry 'F' for key 't0.u2'

9.7.2
ERROR 1062 (23000) at line 2: Duplicate entry 'F' for key 't0.u2'

26.7.0
ERROR 1062 (23000) at line 2: Duplicate entry 'F' for key 't0.u2'

In above, we see that the INSERT conflicts with the row WHERE c1 = 'F', so let's DELETE that row and retry the INSERT:

for mv in 5.7.44 8.0.36 8.0.46 8.4.11 9.7.2 26.7.0; do
  echo; echo $mv
  ~/sandboxes/msb_mysql_${mv//./_}/use test <<< "
    BEGIN;
    DELETE FROM t0 WHERE c1 = 'F';
    INSERT INTO t0 (c2,c1) VALUES (-9.183,'F');
  "
done

5.7.44
ERROR 1062 (23000) at line 4: Duplicate entry '-9.1830' for key 'u3'

8.0.36
ERROR 1062 (23000) at line 4: Duplicate entry '-9.1830' for key 't0.u3'

8.0.46
ERROR 1062 (23000) at line 4: Duplicate entry '-9.1830' for key 't0.u3'

8.4.11
ERROR 1062 (23000) at line 4: Duplicate entry '-9.1830' for key 't0.u3'

9.7.2
ERROR 1062 (23000) at line 4: Duplicate entry '-9.1830' for key 't0.u3'

26.7.0
ERROR 1062 (23000) at line 4: Duplicate entry '-9.1830' for key 't0.u3'

After the DELETE, the INSERT now conflicts with the row WHERE c2 = -9.1830.  So the problem is not that REPLACE does not behave as it should.  It is that once it DELETEs the conflicting row, the following INSERT conflicts with another row.

So from what I see, REPLACE behaves exactly as expected.  The resulting ERROR 1062 is the consequence of the table structure and its content.

Said in another way, because the row of the REPLACE conflicts with two rows, the first row is DELETED, and then the following INSERT conflicts with the other row, explaining why REPLACE fails.

Hi Hao Wang,

Thank you for the test case. Verified as described.

Hi Jean-François Gagné,

Thank you for looking into the issue. Multiple unique-key conflicts are supported by REPLACE: it must keep deleting conflicting rows and retrying insertion. See the REPLACE documentation (https://dev.mysql.com/doc/refman/8.4/en/replace.html). So we are verifying the issue.

Reduced test case:

  CREATE TABLE t (
    a VARCHAR(10),
    b INT,
    UNIQUE ua(a(2))
  ) ENGINE=InnoDB;

  ALTER TABLE t ADD UNIQUE ub(b), ALGORITHM=INPLACE;
  INSERT INTO t VALUES ('S',1), ('F',2);
  REPLACE INTO t VALUES ('F',1);

Actual result:
ERROR 1062 (23000): Duplicate entry '1' for key 't.ub'

Expected result: the statement succeeds, replacing both conflicting rows and leaving only ('F',1).

Thanks,
Chaithra

> Expected result: the statement succeeds, replacing both conflicting rows and leaving only ('F',1).

I have something feels wrong about this.
As Jean-François Gagné described, this behavior (fails with ERROR 1062) came from very old version, "REPLACE could fail but not delete more rows than trying to inset" (and well-known by MySQL geeks I imagine )

Therefore, changing this behavior is giant incompatible for many people.
I hope you change it little by little step (ex. Add warning into a LTS, Add system-variable for compatibility into next LTS, Remove old behavior from more next LTS )

Thank you, everyone, for the investigation and feedback.

This appears to be the same underlying issue as bug#115830, so I am closing this report as a duplicate. Please add further comments there so that the investigation and compatibility considerations can be kept together.

In both the original reproducer and Chaithra's reduced example, restarting the server before executing REPLACE makes the statement succeed with the result the reporter expects. The statement can therefore already replace both conflicting rows without any change to the table definition or data.

This is why I currently favour making the statement succeed consistently. Since the error already depends on whether the server has restarted, I do not currently see a reliable existing behaviour that a compatibility switch would preserve.

For anyone proposing different behaviour or a compatibility switch, could you describe the application use case and the behaviour the proposed solution should guarantee, including across a server restart? Please leave those suggestions on bug #115830 so that whoever investigates the fix can take them into account.

Thank you Knut Anders Hatlen,

I'm surprised that this behavior depends on restart..
So, my opinion is bogus, sorry for my misunderstand

```
bravo-1> select * from t0;
+------------+---------+
| c3         | c2      |
+------------+---------+
| 0x53       | -9.1830 |
| 0x46       | 70.0000 |
+------------+---------+
2 rows in set (0.000 sec)

bravo-1> REPLACE INTO t0 (c2,c3) VALUES (-9.183,'F');
ERROR 1062 (23000): Duplicate entry '-9.1830' for key 't0.i3'

bravo-1> RESTART;
Query OK, 0 rows affected (0.000 sec)

bravo-1> use d1
Database changed
bravo-1> SELECT * FROM t0;
+------------+---------+
| c3         | c2      |
+------------+---------+
| 0x53       | -9.1830 |
| 0x46       | 70.0000 |
+------------+---------+
2 rows in set (0.008 sec)

bravo-1>
bravo-1> REPLACE INTO t0 (c2,c3) VALUES (-9.183,'F');
Query OK, 3 rows affected (0.004 sec)

bravo-1> SELECT * FROM t0;
+------------+---------+
| c3         | c2      |
+------------+---------+
| 0x46       | -9.1830 |
+------------+---------+
1 row in set (0.000 sec)
```