Bug #109510 ON DUPLICATE KEY UPDATE change wrong row
Submitted: 2 Jan 2023 15:14 Modified: 3 Jan 2023 14:21
Reporter: Jan Novak Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: General Severity:S2 (Serious)
Version:8.0.31-0ubuntu2 OS:Ubuntu (22.04)
Assigned to: CPU Architecture:Any

[2 Jan 2023 15:14] Jan Novak
Description:
requirements:
* autoincrement overflowed (has max value)
* use "on duplicate key"

All insert parameters are ignored and the last id in table is updated. Without error and warning.

How to repeat:
CREATE TABLE `slot_stats` (
  `id` tinyint unsigned NOT NULL AUTO_INCREMENT,
  `cnt` int unsigned NOT NULL DEFAULT '0',
  `slot_id` int unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slot_id` (`slot_id`)
) ENGINE=InnoDB AUTO_INCREMENT=255 DEFAULT CHARSET=utf8mb3

...

mysql> insert into slot_stats set slot_id=4, cnt=7 on duplicate key update cnt=cnt+7;

mysql> insert into slot_stats set slot_id=5, cnt=7 on duplicate key update cnt=cnt+7;
Query OK, 2 rows affected (0,01 sec)

mysql> select * from slot_stats;
+-----+-----+---------+
| id  | cnt | slot_id |
+-----+-----+---------+
|   1 | 203 |       1 |
| 250 |  21 |       2 |
| 253 |   7 |       3 |
| 255 |  14 |       4 |
+-----+-----+---------+
4 rows in set (0,00 sec)

mysql> insert into slot_stats set slot_id=53453, cnt=7 on duplicate key update cnt=cnt+7;
Query OK, 2 rows affected (0,01 sec)

mysql> select * from slot_stats;
+-----+-----+---------+
| id  | cnt | slot_id |
+-----+-----+---------+
|   1 | 203 |       1 |
| 250 |  21 |       2 |
| 253 |   7 |       3 |
| 255 |  21 |       4 |
+-----+-----+---------+
4 rows in set (0,00 sec)

Suggested fix:
raise error
[3 Jan 2023 14:21] MySQL Verification Team
Hi Mr. Novak,

Thank you for your bug report.

However, it is not a bug. It is expected behaviour. 

If you want to add / change this auto-increment value you have to run DDL statement that will increase the width of the auto-inc column.

Not a bug.