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

Duplicate key with auto increment

        Details

        • Type: Bug
        • Status: Confirmed
        • Priority: Major
        • Resolution: Unresolved
        • Affects Version/s: 10.1, 10.0, 5.5
        • Fix Version/s: 10.1, 10.0
        • Component/s: Storage Engine - InnoDB
        • Labels:
        • Environment:
          CentOS 6

          Description

          I am filing this bug with MariaDB, just for the sake of completeness, as it was already filed with MySQL/InnoDB here (by someone else):

          http://bugs.mysql.com/bug.php?id=76872

          "InnoDB AUTO_INCREMENT produces same value twice"

          "When more than one INSERT is executed concurrently on a non-empty InnoDB table with an AUTO_INCREMENT PK immediately after MySQL start up (before any other INSERTs on the same table) one will often fail with the message like "Duplicate entry '123' for key 'PRIMARY'". This will only happen if innodb_autoinc_lock_mode is 1 or 2 and auto_increment_increment > 1."

          There is much more in the actual bug report, but I didn't want to copy/paste the entire report.

            Gliffy Diagrams

              Attachments

                Issue Links

                links to

                Web Link Bug #76872 - InnoDB AUTO_INCREMENT produces same value twice

                  Activity

                  Hide
                  Permalink
                  elenst Elena Stepanova added a comment -

                  Non-deterministic test case by Laurynas Biveinis from the upstream report (run with --repeat=N):

                  --source include/have_innodb.inc

# Should be != 0
SELECT @@innodb_autoinc_lock_mode;

CREATE TABLE `test` (
  `test_id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `testcol` varchar(256)
) ENGINE=InnoDB;

INSERT INTO `test` (`testcol`) VALUES ('aldsldjfhasjk');

SET SESSION auto_increment_increment = 2;
send INSERT INTO `test` (`testcol`) VALUES ('aldsldjfhasjk');
--connect(con1,localhost,root,,)
--connection con1
SET SESSION auto_increment_increment = 2;
send INSERT INTO `test` (`testcol`) VALUES ('aldsldjfhasjk');
# --error 0,1062
reap;
--disconnect con1
--connection default
# --error 0,1062
reap;

SELECT * FROM test;

DROP TABLE test;
                  Show
                  elenst Elena Stepanova added a comment - Non-deterministic test case by Laurynas Biveinis from the upstream report (run with --repeat=N): --source include/have_innodb.inc # Should be != 0 SELECT @@innodb_autoinc_lock_mode; CREATE TABLE `test` ( `test_id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, `testcol` varchar(256) ) ENGINE=InnoDB; INSERT INTO `test` (`testcol`) VALUES ('aldsldjfhasjk'); SET SESSION auto_increment_increment = 2; send INSERT INTO `test` (`testcol`) VALUES ('aldsldjfhasjk'); --connect(con1,localhost,root,,) --connection con1 SET SESSION auto_increment_increment = 2; send INSERT INTO `test` (`testcol`) VALUES ('aldsldjfhasjk'); # --error 0,1062 reap; --disconnect con1 --connection default # --error 0,1062 reap; SELECT * FROM test; DROP TABLE test;

                    People

                    • Assignee:
                      Unassigned
                      Reporter:
                      ccalender Chris Calender
                    • Votes:
                      0 Vote for this issue
                      Watchers:
                      3 Start watching this issue

                      Dates

                      • Created:
                        Updated: