REPLACE overwrites other rows when auto_inc capacity exceeded

[Alkaagr81]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

drop table IF EXISTS t1;

SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
create table t1 (a tinyint not null auto_increment primary key, b char(20) default "default_value");
insert into t1 values (126,"first"),(63, "middle"),(0,"last");
insert into t1 values (0,"error");
replace into t1 values (0,"tidb_is_right");
replace into t1 values (126,"first updated");
replace into t1 values (63,default);
select * from t1;

2. What did you expect to see? (Required)

mysql> SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (a tinyint not null auto_increment primary key, b char(20) default "default_value");
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values (126,"first"),(63, "middle"),(0,"last");
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into t1 values (0,"error");
ERROR 1062 (23000): Duplicate entry '127' for key 't1.PRIMARY'
mysql> replace into t1 values (0,"tidb_is_right");
ERROR 1062 (23000): Duplicate entry '127' for key 't1.PRIMARY'
mysql> replace into t1 values (126,"first updated");
Query OK, 2 rows affected (0.00 sec)

mysql> replace into t1 values (63,default);
Query OK, 2 rows affected (0.00 sec)

mysql> select * from t1;
+-----+---------------+
| a   | b             |
+-----+---------------+
|  63 | default_value |
| 126 | first updated |
| 127 | last          |
+-----+---------------+
3 rows in set (0.00 sec)

3. What did you see instead (Required)

mysql> SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (a tinyint not null auto_increment primary key, b char(20) default "default_value");
Query OK, 0 rows affected (0.13 sec)

mysql> insert into t1 values (126,"first"),(63, "middle"),(0,"last");
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into t1 values (0,"error");
ERROR 1062 (23000): Duplicate entry '127' for key 'PRIMARY'
mysql> replace into t1 values (0,"tidb_is_right");
Query OK, 2 rows affected, 1 warning (0.00 sec)

mysql> replace into t1 values (126,"first updated");
Query OK, 2 rows affected (0.00 sec)

mysql> replace into t1 values (63,default);
Query OK, 2 rows affected (0.00 sec)

mysql> select * from t1;
+-----+---------------+
| a   | b             |
+-----+---------------+
|  63 | default_value |
| 126 | first updated |
| 127 | tidb_is_right |
+-----+---------------+
3 rows in set (0.00 sec)

4. What is your TiDB version? (Required)

| Release Version: v5.2.2
Edition: Community
Git Commit Hash: da1c21fd45a4ea5900ac16d2f4a248143f378d18
Git Branch: heads/refs/tags/v5.2.2
UTC Build Time: 2021-10-20 06:03:45
GoVersion: go1.16.5
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |

[morgo]

This is actually a serious bug. REPLACE is expected to have the semantics of DELETE + INSERT. The key 0 is a special value, where the next auto_inc value will be taken for insertion.

So the MySQL behavior is quite clear:
If the 0 value is used, it will use the next auto_inc value until the auto_inc is full. It will then return an error because there is no space left in the auto_inc column.

For TiDB:
If the 0 value is used, and the auto_inc is full, it will replace the maximum value of the auto_inc (which is completely unrelated to the DELETE operation and should not be touched). This leads to data corruption as unrelated rows are modified and removed.

[morgo]

Here is a simplified case against master:

SET sql_mode="NO_ENGINE_SUBSTITUTION";
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a tinyint not null auto_increment primary key, b char(20));
INSERT INTO t1 VALUES (127,"maxvalue");
REPLACE INTO t1 VALUES (0,"newmaxvalue");
SHOW WARNINGS;
SELECT * FROM t1;

..

tidb> REPLACE INTO t1 VALUES (0,"newmaxvalue");
Query OK, 2 rows affected, 1 warning (0.01 sec)

tidb> SHOW WARNINGS;
+---------+------+--------------------------------+
| Level   | Code | Message                        |
+---------+------+--------------------------------+
| Warning | 1690 | constant 128 overflows tinyint |
+---------+------+--------------------------------+
1 row in set (0.00 sec)

tidb> SELECT * FROM t1;
+-----+-------------+
| a   | b           |
+-----+-------------+
| 127 | newmaxvalue |
+-----+-------------+
1 row in set (0.00 sec)

This does not work on the default SQL mode, but it is still problematic because it changes unrelated rows.

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'fixes-x.y.z', and then remove 'needs-more-info' label.

[VelocityLight]

update issue