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

auto_increment value on table increments by one after restart

    XMLWordPrintable

Details

    Description

      From https://github.com/MariaDB/mariadb-docker/issues/527, a created table with AUTO_INCREMENT=X, after a restart, appears in SHOW CREATE TABLE as AUTO_INCREMENT=X+1 after a server restart.

      confirmed present in 3e0009dc3a771e4dbf2fa4a4cf87e750453fb2eb (10.9.

      confirmed not present in 864bbd4d0982e51c8ed643ed0fd2874c2425b451 (4700f2ac70f8c79f2ac1968b6b59d18716f492bf^) (the commit before MDEV-30796)

      test case from @Nezisi on GH https://github.com/Nezisi/mariadb-autoinc-reproducer:

      test.sql

      -- MySQL dump 10.13  Distrib 5.7.22, for Win64 (x86_64)
      --
      -- Host: 127.0.0.1    Database: XY
      -- ------------------------------------------------------
      -- Server version	5.7.25
       
      SET foreign_key_checks=0;
      SET time_zone='+00:00';
      SET unique_checks=0;
      SET sql_mode='NO_AUTO_VALUE_ON_ZERO';
       
      --
      -- Table structure for table `pcfeature`
      --
      DROP TABLE IF EXISTS `pcfeature`;
      CREATE TABLE `pcfeature` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        PRIMARY KEY (`id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=62;
      --
      -- Dumping data for table `pcfeature`
      --
      LOCK TABLES `pcfeature` WRITE;
      INSERT INTO `pcfeature` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),(41),(42),(43),(44),(45),(46),(47),(48),(49),(50),(51),(52),(53),(54),(55),(56),(57),(58),(59),(60),(61);
      UNLOCK TABLES;
       
      SET foreign_key_checks=1;
      SET unique_checks=1;
      -- Dump completed on 2020-10-20 11:38:49
      

      mariadb test < test.sql
      mariadb test -e 'show create table pcfeature; shutdown;
      # shows AUTO_INCREMENT=62 per SQL
      # restart mariadbd
      mariadb test -e 'show create table pcfeature; shutdown;
      # shows AUTO_INCREMENT=63
      

      Attachments

        Issue Links

          Activity

            People

              thiru Thirunarayanan Balathandayuthapani
              danblack Daniel Black
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.