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

AUTO_INCREMENT value too high after certain insert

    XMLWordPrintable

    Details

      Description

      The auto increment of a table is not counting continuously in any case. For example create a table:

      CREATE TABLE `foo` (
      	`ID` INT(11) NOT NULL AUTO_INCREMENT,
      	`Name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
      	PRIMARY KEY (`ID`)
      )
      COLLATE='utf8mb4_unicode_ci'
      ENGINE=InnoDB;
      

      Then insert some rows:

      insert into foo(Name)
      SELECT '421' UNION
      SELECT '422' UNION
      SELECT '423' UNION
      SELECT '424';
      

      After that the added rows have the correct ID, but the auto_increment of the table is not 5 but 8.
      Running the statement again:

      insert into foo(Name)
      SELECT '421' UNION
      SELECT '422' UNION
      SELECT '423' UNION
      SELECT '424';
      

      Leads to the following table:

      ID Name
      1 421
      2 422
      3 423
      4 424
      8 421
      9 422
      10 423
      11 424

      And the auto_increment of the table is 15.

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            optonaegele Markus Nägele
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: