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

invalid gap in auto-increment values after LOAD DATA

    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

              • Assignee:
                serg Sergei Golubchik
                Reporter:
                serg Sergei Golubchik
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: