MySQL Bugs: #121494: UPDATE statement fails with a duplicate-key error but partially applies changes to the table

Bug #121494	UPDATE statement fails with a duplicate-key error but partially applies changes to the table
Submitted:	13 Oct 10:42
Reporter:	Hao Wang	Email Updates:
Status:	Open	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	8.0.36	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When updating a `LONGTEXT` column that is part of a prefix primary key (`PRIMARY KEY (c1(1), c2)`), the `UPDATE` fails with `ERROR 1062 Duplicate entry 'f-0' for key 't0.PRIMARY'`. Despite the error, the row is modified: the `c3` value is truncated to a single character `'F'` (the prefix used in the primary key), and the statement is reported as failed. The table is left in an inconsistent state.

How to repeat:
create database d1;
use d1;

CREATE TABLE IF NOT EXISTS t0 (
  c1 LONGTEXT CHARACTER SET UTF8,
  c2 FLOAT4,
  PRIMARY KEY (c1(1), c2)
);
ALTER TABLE t0 STATS_AUTO_RECALC 1;
ALTER TABLE t0 RENAME COLUMN c1 TO c3;
ALTER TABLE t0 ADD c4 FLOAT FIRST;
ALTER TABLE t0 ADD UNIQUE KEY (c2, c4);

INSERT HIGH_PRIORITY IGNORE INTO t0 (c2, c3, c4)
  VALUES (1.009, 'FlGOhG3K62', 33.1920);

REPLACE LOW_PRIORITY INTO t0 (c2, c3, c4)
  VALUES (-109, 'JvS7ZKcL5f', 88.0651);

UPDATE LOW_PRIORITY IGNORE t0 SET c2 = null;

select * from t0;
-- c4=33.192,  c3=FlGOhG3K62, c2=0
-- c4=88.0651, c3=JvS7ZKcL5f, c2=0

UPDATE t0 SET c3 = 'fSl4mSJilH', c4 = 1.8053450785227723E38;
-- ERROR 1062 (23000): Duplicate entry 'f-0' for key 't0.PRIMARY'

select * from t0;
-- c4=33.192,  c3=F,          c2=0   <-- c3 was truncated/corrupted
-- c4=88.0651, c3=JvS7ZKcL5f, c2=0

## Expected Behavior

The `UPDATE` should either:

1. Succeed and set `c3 = 'fSl4mSJilH'`, `c4 = 1.8053450785227723E38` on the matching row(s), **or**
2. Fail atomically with `ERROR 1062`, leaving **all rows completely unchanged**.

## Actual Behavior

The statement fails with `ERROR 1062`, but the first row's `c3` value is partially written/truncated to `'F'` (a single character). The table is left in a corrupted state that cannot be reproduced by any valid SQL statement.

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

I am able to reproduce this bug in 8.0.36, but not in 8.0.46 nor in 8.4.11, see below.  You can expect the same result as in Bug#121514: your bug being closed as Unsupported.  Maybe the best is to do it yourself so Oracle can focus on unfixed bugs.

Interesting finding though !

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

  ~/sandboxes/msb_mysql_${mv//./_}/use test <<< "
CREATE TABLE IF NOT EXISTS t0 (
  c1 LONGTEXT CHARACTER SET UTF8,
  c2 FLOAT4,
  PRIMARY KEY (c1(1), c2)
);
ALTER TABLE t0 STATS_AUTO_RECALC 1;
ALTER TABLE t0 RENAME COLUMN c1 TO c3;
ALTER TABLE t0 ADD c4 FLOAT FIRST;
ALTER TABLE t0 ADD UNIQUE KEY (c2, c4);

INSERT HIGH_PRIORITY IGNORE INTO t0 (c2, c3, c4) VALUES (1.009, 'FlGOhG3K62', 33.1920);
REPLACE LOW_PRIORITY INTO t0 (c2, c3, c4) VALUES (-109, 'JvS7ZKcL5f', 88.0651);
UPDATE LOW_PRIORITY IGNORE t0 SET c2 = null;
"

  ~/sandboxes/msb_mysql_${mv//./_}/use test --table <<< "select * from t0;"
  ~/sandboxes/msb_mysql_${mv//./_}/use test <<< "UPDATE t0 SET c3 = 'fSl4mSJilH', c4 = 1.8053450785227723E38;"
  ~/sandboxes/msb_mysql_${mv//./_}/use test --table <<< "select * from t0;"

  ~/sandboxes/msb_mysql_${mv//./_}/stop > /dev/null
  rm -rf ~/sandboxes/msb_mysql_${mv//./_}
done

8.0.36
+---------+------------+----+
| c4      | c3         | c2 |
+---------+------------+----+
|  33.192 | FlGOhG3K62 |  0 |
| 88.0651 | JvS7ZKcL5f |  0 |
+---------+------------+----+
ERROR 1062 (23000) at line 1: Duplicate entry 'f-0' for key 't0.PRIMARY'
+---------+------------+----+
| c4      | c3         | c2 |
+---------+------------+----+
|  33.192 | F          |  0 |
| 88.0651 | JvS7ZKcL5f |  0 |
+---------+------------+----+

8.0.46
+---------+------------+----+
| c4      | c3         | c2 |
+---------+------------+----+
|  33.192 | FlGOhG3K62 |  0 |
| 88.0651 | JvS7ZKcL5f |  0 |
+---------+------------+----+
ERROR 1062 (23000) at line 1: Duplicate entry 'f-0' for key 't0.PRIMARY'
+---------+------------+----+
| c4      | c3         | c2 |
+---------+------------+----+
|  33.192 | FlGOhG3K62 |  0 |
| 88.0651 | JvS7ZKcL5f |  0 |
+---------+------------+----+

8.4.11
+---------+------------+----+
| c4      | c3         | c2 |
+---------+------------+----+
|  33.192 | FlGOhG3K62 |  0 |
| 88.0651 | JvS7ZKcL5f |  0 |
+---------+------------+----+
ERROR 1062 (23000) at line 1: Duplicate entry 'f-0' for key 't0.PRIMARY'
+---------+------------+----+
| c4      | c3         | c2 |
+---------+------------+----+
|  33.192 | FlGOhG3K62 |  0 |
| 88.0651 | JvS7ZKcL5f |  0 |
+---------+------------+----+

I was curious when this bug was introduced and when it was fixed.

It does not affect 5.7.44 nor 8.0.28, was introduced in 8.0.30 (or .29 which was retired), and fixed in 8.0.41.

Note that for working with 5.7, I removed RENAME COLUMN in below.

for mv in 5.7.44 8.0.28 8.0.30 8.0.40 8.0.41; do
  echo; echo $mv
  cd; dbdeployer deploy single mysql_$mv > /dev/null

  ~/sandboxes/msb_mysql_${mv//./_}/use test <<< "
CREATE TABLE IF NOT EXISTS t0 (
  c1 LONGTEXT CHARACTER SET UTF8,
  c2 FLOAT4,
  PRIMARY KEY (c1(1), c2)
);
ALTER TABLE t0 ADD c4 FLOAT FIRST;
ALTER TABLE t0 ADD UNIQUE KEY (c2, c4);

INSERT HIGH_PRIORITY IGNORE INTO t0 (c2, c1, c4) VALUES (1.009, 'FlGOhG3K62', 33.1920);
REPLACE LOW_PRIORITY INTO t0 (c2, c1, c4) VALUES (-109, 'JvS7ZKcL5f', 88.0651);
UPDATE LOW_PRIORITY IGNORE t0 SET c2 = null;
"

  ~/sandboxes/msb_mysql_${mv//./_}/use test --table <<< "select * from t0;"
  ~/sandboxes/msb_mysql_${mv//./_}/use test <<< "UPDATE t0 SET c1 = 'fSl4mSJilH', c4 = 1.8053450785227723E38;"
  ~/sandboxes/msb_mysql_${mv//./_}/use test --table <<< "select * from t0;"

  ~/sandboxes/msb_mysql_${mv//./_}/stop > /dev/null
  rm -rf ~/sandboxes/msb_mysql_${mv//./_}
done

5.7.44
+---------+------------+----+
| c4      | c1         | c2 |
+---------+------------+----+
|  33.192 | FlGOhG3K62 |  0 |
| 88.0651 | JvS7ZKcL5f |  0 |
+---------+------------+----+
ERROR 1062 (23000) at line 1: Duplicate entry 'f-0' for key 'PRIMARY'
+---------+------------+----+
| c4      | c1         | c2 |
+---------+------------+----+
|  33.192 | FlGOhG3K62 |  0 |
| 88.0651 | JvS7ZKcL5f |  0 |
+---------+------------+----+

8.0.28
+---------+------------+----+
| c4      | c1         | c2 |
+---------+------------+----+
|  33.192 | FlGOhG3K62 |  0 |
| 88.0651 | JvS7ZKcL5f |  0 |
+---------+------------+----+
ERROR 1062 (23000) at line 1: Duplicate entry 'f-0' for key 't0.PRIMARY'
+---------+------------+----+
| c4      | c1         | c2 |
+---------+------------+----+
|  33.192 | FlGOhG3K62 |  0 |
| 88.0651 | JvS7ZKcL5f |  0 |
+---------+------------+----+

8.0.30
+---------+------------+----+
| c4      | c1         | c2 |
+---------+------------+----+
|  33.192 | FlGOhG3K62 |  0 |
| 88.0651 | JvS7ZKcL5f |  0 |
+---------+------------+----+
ERROR 1062 (23000) at line 1: Duplicate entry 'f-0' for key 't0.PRIMARY'
+---------+------------+----+
| c4      | c1         | c2 |
+---------+------------+----+
|  33.192 | F          |  0 |
| 88.0651 | JvS7ZKcL5f |  0 |
+---------+------------+----+

8.0.40
+---------+------------+----+
| c4      | c1         | c2 |
+---------+------------+----+
|  33.192 | FlGOhG3K62 |  0 |
| 88.0651 | JvS7ZKcL5f |  0 |
+---------+------------+----+
ERROR 1062 (23000) at line 1: Duplicate entry 'f-0' for key 't0.PRIMARY'
+---------+------------+----+
| c4      | c1         | c2 |
+---------+------------+----+
|  33.192 | F          |  0 |
| 88.0651 | JvS7ZKcL5f |  0 |
+---------+------------+----+

8.0.41
+---------+------------+----+
| c4      | c1         | c2 |
+---------+------------+----+
|  33.192 | FlGOhG3K62 |  0 |
| 88.0651 | JvS7ZKcL5f |  0 |
+---------+------------+----+
ERROR 1062 (23000) at line 1: Duplicate entry 'f-0' for key 't0.PRIMARY'
+---------+------------+----+
| c4      | c1         | c2 |
+---------+------------+----+
|  33.192 | FlGOhG3K62 |  0 |
| 88.0651 | JvS7ZKcL5f |  0 |
+---------+------------+----+