1

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.

3
  • what sql_mode you configured ? If it is not strict, try strict sql mode. dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mode-strict
    – sudalai
    Commented May 22, 2020 at 13:23
  • @User2397, my mode was NO_UNSIGNED_SUBTRACTION,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION. Just tried adding STRICT_ALL_TABLES. Nothing changed :(
    – Stalinko
    Commented May 22, 2020 at 17:07
  • MariaDB, at least back to 10.0.28, gives this error: Out of range value for column 'id' at row 1 [in prepared statement]
    – Rick James
    Commented May 31, 2020 at 1:45

2 Answers 2

1

USE a BEFORE INSERT TRIGGER to check for all fields where you can reach their limits.

With the newest mysql incarnation you can actually can use the CKECK CONSTRAINT to prevent an INSERT at all, but with the TRIGGER you can manage and define What to do

0

I guess I would argue that it did "a right thing".

Note: You have two UNIQUE keys. On the surface, IODKU should check both keys in order to find the up to two rows to modify.

However, since IODKU is "either update (first priority) or insert (if row does not exist)" then let's consider this algorithm:

  1. Check all UNIQUE keys other than AUTO_INCREMENT. After all, you did not explicitly specify id. So, one can argue that only one row is checked.
  2. If found (value=1) then UPDATE.
  3. If not found, then do the insert. Only now does the auto_inc get involved.

In your test case, Oracle, but not MariaDB seems to

  1. Cap the overflow at 4294967295
  2. Decide that row (not the other row) is a dup, so UPDATE it.

Suggest you file a bug with bugs.mysql.com I checked 5.5 through 8.0.16.

(I looked for, but without success, the MariaDB fix that caused them to diverge, possibly in 5.5 or before.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.