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

AUTO_INCREMENT breaks after updating a column value to a negative number

    XMLWordPrintable

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

            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.