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

invalid gap in auto-increment values after LOAD DATA

    XMLWordPrintable

Details

    Description

      probably 5.5 is affected too, I didn't check

      this is the test case:

      --source include/have_innodb.inc
      create or replace table t1 (pk int auto_increment primary key, f varchar(20)) engine=innodb;
      insert t1 (f) values ('a'), ('b'), ('c'), ('d');
      select null, f into outfile 'load.data' from t1 limit 2;
      load data infile 'load.data' into table t1;
      insert t1 (f) values ('<===');
      select * from t1;
      drop table t1;
      

      What happens here, insert tells the engine to reserve 4 auto-increment values. This "4" number is never reset, so the following load data also reserves four values, even if it needs only two. And the next insert creates a gap in the auto-increment sequence.

      Most engines do not reserve auto-increment values, and generate one value at a time. So with MyISAM, for example, there is no gap in this test.

      The fix would be to reset the list at the end of the insert statement.

      Attachments

        Issue Links

          Activity

            People

              serg Sergei Golubchik
              serg Sergei Golubchik
              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.