[MDEV-15352] AUTO_INCREMENT breaks after updating a column value to a negative number Created: 2018-02-19  Updated: 2020-08-25  Resolved: 2018-06-14

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB, Storage Engine - XtraDB
Affects Version/s: 10.2.12, 10.3.3
Fix Version/s: 10.2.16, 10.3.8

Type: Bug Priority: Critical
Reporter: Christoph Biardzki Assignee: Alexander Barkov
Resolution: Fixed Votes: 2
Labels: regression
Environment:

SLES 12

Issue Links:
Problem/Incident
is caused by MDEV-14008 Assertion failing: `!is_set() || (m_s... Closed
Relates
relates to MDEV-16534 PPC64: Unexpected error with a negati... Closed
relates to MDEV-16652 heap.heap_auto_increment, main.auto_i... Closed

 Description   

Using MariaDB 10.2.12, there is a change in behaviour compared to, e.g. 10.1.22 when changing a value of an AUTO_INCREMENT-column to a negative value: the AUTO_INCREMENT increases to the maximum possible number and subsequent inserts fail.

In 10.1.22, the same sequence succeeds.

MariaDB [test]> select version();
 
+---------------------+
 
| version()           |
 
+---------------------+
 
| 10.2.12-MariaDB-log |
 
+---------------------+
 
1 row in set (0,00 sec)


MariaDB [test]> create table animals (`id` mediumint(9) NOT NULL AUTO_INCREMENT, `name` char(30) NOT NULL, PRIMARY KEY (`id`));
 
Query OK, 0 rows affected (0,01 sec)
 
 
 
MariaDB [test]> insert into animals (name) values ("dog");
 
Query OK, 1 row affected (0,00 sec)
 
 
 
MariaDB [test]> select * from animals;
 
+----+------+
 
| id | name |
 
+----+------+
 
|  1 | dog  |
 
+----+------+
 
1 row in set (0,00 sec)


MariaDB [test]> update animals set id=-1 where id=1;
 
Query OK, 1 row affected (0,00 sec)
 
Rows matched: 1  Changed: 1  Warnings: 0
 
 
 
MariaDB [test]> select * from animals;
 
+----+------+
 
| id | name |
 
+----+------+
 
| -1 | dog  |
 
+----+------+
 
1 row in set (0,00 sec)
 
 
 
MariaDB [test]> insert into animals (name) values ("cat");

ERROR 1467 (HY000): Failed to read auto-increment value from storage engine

MariaDB [test]> show create table animals;
 
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table   | Create Table                                                                                                                                                                                    |
 
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| animals | CREATE TABLE `animals` (
 
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
 
  `name` char(30) NOT NULL,
 
  PRIMARY KEY (`id`)
 
) ENGINE=InnoDB {color:red}AUTO_INCREMENT=18446744073709551615{color} DEFAULT CHARSET=utf8 |
 
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0,00 sec)



 Comments   
Comment by Elena Stepanova [ 2018-02-19 ]

Thanks for the report.

The behavior changed in 10.2.12, somewhere between 15219eb08a28261aa730c301583f1c47a92790b8 (OK) and 9c28fd7a3337a4d6773f3b53e70af9a3b0dbb919 (fails).

Comment by Marko Mäkelä [ 2018-05-30 ]

In the revision range reported by elenst, the only relevant change to ha_innodb.cc appears to be MDEV-14008. This change is also present in 10.0.34 and 10.1.30.

Comment by Marko Mäkelä [ 2018-05-30 ]

bar, you implemented MDEV-14008. Please take a look.

Comment by Alexander Barkov [ 2018-06-14 ]

The same problem is repeatable with all integer types:

  • tinyint
  • smallint
  • mediumint
  • int
  • bigint
Comment by Alexander Barkov [ 2018-06-14 ]

Note, this script:

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;
 
SHOW CREATE TABLE t1;
 
INSERT INTO t1 VALUES (1);
 
UPDATE t1 SET a=-1;
 
INSERT INTO t1 VALUES ();
 
SELECT * FROM t1;
 
DROP TABLE t1;

returns:

+----+
 
| a  |
 
+----+
 
| -1 |
 
|  2 |
 
+----+

for:

  • server versions 5.5, 10.0, 10.1 + engines MyISAM, HEAP, InnoDB, and
  • server versions 10.2, 10.3 + engines MyISAM, HEAP.

The problem with 10.2+InnoDB was introduced by this commit:

commit 8d70097c216081e8013a548bfbde156e48985816
 
Merge: 079c359 252e690
 
Author: Marko Mäkelä <marko.makela@mariadb.com>
 
Date:   Tue Dec 19 16:48:28 2017 +0200
 
 
 
    Merge 10.1 to 10.2
 
    
    Follow-up fix to MDEV-14008: Let Field_double::val_uint() silently
 
    return 0 on error

by this chunk:

-                                       auto_inc = row_parse_int(
 
-                                               static_cast<const byte*>(
 
-                                                       ufield->new_val.data),
 
-                                               ufield->new_val.len,
 
-                                               col->mtype,
 
-                                               col->prtype & DATA_UNSIGNED);
 
++                                      auto_inc = field->val_uint();

This code expects val_uint() to return 0 for negative signed values, while in fact val_uint() reinterprets negative signed values to huge positive unsigned values for integer Field_xxx types (truncation only works in Field_real).

During a discussion, marko and bar came to a conclusion that val_uint() should be fixed to cut negative values to 0 for all data types, like it is done in Field_real.

Generated at Thu Feb 08 08:20:38 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.