Bug #109510 ON DUPLICATE KEY UPDATE change wrong row

• Submitted: 2 Jan 2023 15:14
• Modified: 3 Jan 2023 14:21
• Reporter: Jan Novak
• Status: Not a Bug
• Category: MySQL Server: General
• Severity: S2 (Serious)
• Version: 8.0.31-0ubuntu2
• OS: Ubuntu (22.04)
• 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.