Categories
Database Development

How to make MySQL 8 alert when integer overflow happens during INSERT … ON DUPLICATE KEY?

Let’s consider we have a simple table with auto-incrementing integer ID and some unique column:

CREATE TABLE `test` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `value` tinyint DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `value` (`value`)
);

Imagine we inserted into it so many times that ID reached the maximum:

INSERT INTO test VALUES 
(4294967294, 1),
(4294967295, 2);

Now we are trying to insert one more row and if it’s duplicate then change the value to 3:

INSERT INTO test (`value`) VALUES (1) ON DUPLICATE KEY UPDATE `value` = 3;

What is the expected behaviour?

        id     value  
----------  --------
4294967294         3
4294967295         2

What we get in fact:

        id     value  
----------  --------
4294967294         1
4294967295         3

How? Because of the integer overflow the auto-generated ID of the new row was again 4294967295.

So now the question. Is it possible to make MySQL 8 alert if the overflow happens during query? In my case such silent overflow caused huge data loss. Because all newly added rows just overwrote the last row.

PS I’m not asking how to fix or prevent such overflow. That’s obvious. I’m asking exactly how to catch such errors.

Leave a Reply

Your email address will not be published. Required fields are marked *