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

          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17455 Slave data corruption when master is MySQL 5.5 in STATEMENT

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17455 Slave data corruption when master is MySQL 5.5 in STATEMENT

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17333 Assertion in update_auto_increment() upon exotic LOAD

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            serg Sergei Golubchik created issue -
            serg Sergei Golubchik made changes -
            Field Original Value New Value
            serg Sergei Golubchik made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            serg Sergei Golubchik made changes -
            Description _probably 5.5 and 10.0 are affected too, I didn't check_

            this is the test case:
            {code:sql}
            source include/have_innodb.inc;
            create 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;
            {code}
            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 one. 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.             		_probably 5.5 and 10.0 are affected too, I didn't check_

            this is the test case:
            {code:sql}
            --source include/have_innodb.inc
            create 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;
            {code}
            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 one. 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.
            serg Sergei Golubchik made changes -
            Description _probably 5.5 and 10.0 are affected too, I didn't check_

            this is the test case:
            {code:sql}
            --source include/have_innodb.inc
            create 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;
            {code}
            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 one. 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.             		_probably 5.5 and 10.0 are affected too, I didn't check_

            this is the test case:
            {code:sql}
            --source include/have_innodb.inc
            create 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;
            {code}
            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.
            midenok Aleksey Midenkov made changes -
            Description _probably 5.5 and 10.0 are affected too, I didn't check_

            this is the test case:
            {code:sql}
            --source include/have_innodb.inc
            create 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;
            {code}
            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.             		_probably 5.5 and 10.0 are affected too, I didn't check_

            this is the test case:
            {code:sql}
            --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;
            {code}
            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.
            midenok Aleksey Midenkov made changes -
            Status Confirmed [ 10101 ] In Progress [ 3 ]
            midenok Aleksey Midenkov made changes -
            Summary gaps in auto-increment values after LOAD DATA invalid gap in auto-increment values after LOAD DATA
            midenok Aleksey Midenkov made changes -
            Fix Version/s 5.5 [ 15800 ]
            Fix Version/s 10.0 [ 16000 ]
            Affects Version/s 5.5 [ 15800 ]
            Affects Version/s 10.0 [ 16000 ]
            midenok Aleksey Midenkov made changes -
            Assignee Aleksey Midenok [ midenok ] Sergei Golubchik [ serg ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            stephane@skysql.com VAROQUI Stephane made changes -
            serg Sergei Golubchik made changes -
            Description _probably 5.5 and 10.0 are affected too, I didn't check_

            this is the test case:
            {code:sql}
            --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;
            {code}
            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.             		_probably 5.5 is affected too, I didn't check_

            this is the test case:
            {code:sql}
            --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;
            {code}
            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.
            serg Sergei Golubchik made changes -
            Fix Version/s 10.3.11 [ 23141 ]
            Fix Version/s 10.2.19 [ 23207 ]
            Fix Version/s 10.1.38 [ 23209 ]
            Fix Version/s 5.5.63 [ 23210 ]
            Fix Version/s 10.0.38 [ 23211 ]
            Fix Version/s 10.2 [ 14601 ]
            Fix Version/s 5.5 [ 15800 ]
            Fix Version/s 10.0 [ 16000 ]
            Fix Version/s 10.1 [ 16100 ]
            Fix Version/s 10.3 [ 22126 ]
            Resolution Fixed [ 1 ]
            Status In Review [ 10002 ] Closed [ 6 ]
            sujatha.sivakumar Sujatha Sivakumar (Inactive) made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 89917 ] MariaDB v4 [ 155021 ]

            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.