Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-15352

AUTO_INCREMENT breaks after updating a column value to a negative number

Details

    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)
      

      Attachments

        Issue Links

          Activity

            Thanks for the report.

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

            elenst Elena Stepanova added a comment - Thanks for the report. The behavior changed in 10.2.12, somewhere between 15219eb08a28261aa730c301583f1c47a92790b8 (OK) and 9c28fd7a3337a4d6773f3b53e70af9a3b0dbb919 (fails).

            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.

            marko Marko Mäkelä added a comment - 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.

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

            marko Marko Mäkelä added a comment - bar , you implemented MDEV-14008 . Please take a look.

            The same problem is repeatable with all integer types:

            • tinyint
            • smallint
            • mediumint
            • int
            • bigint
            bar Alexander Barkov added a comment - The same problem is repeatable with all integer types: tinyint smallint mediumint int bigint
            bar Alexander Barkov added a comment - - edited

            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.

            bar Alexander Barkov added a comment - - edited 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.

            People

              bar Alexander Barkov
              cbi Christoph Biardzki
              Votes:
              2 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.