•  

Announcement

Announcement Module
Collapse
No announcement yet.

Mysql/Percona 5.1.54 Duplicate Entry on autoinc PK field

Page Title Module
Move Remove Collapse
Conversation Detail Module
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts

  • Mysql/Percona 5.1.54 Duplicate Entry on autoinc PK field

    #1
    We've been getting the following error recently fairly often (once or twice a day).

    Error:
    Duplicate entry '5367702' for key 'PRIMARY'

    Query:
    INSERT INTO myTableName (field1, field2, field3) VALUES (5972, 321, 3127532);

    Mysql Version:
    5.1.54-rel12.5-log Percona Server on CentOS5.5

    Table:
    CREATE TABLE `myTableName` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `field1` int(11) DEFAULT NULL,
    `field2` int(11) DEFAULT NULL,
    `field3` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB;


    I was hoping someone could shed some light into this for us. The PK field is not defined in the insert query, so it should use the next available auto increment value.

    Thanks!
    Stephen
    Tags: None
  • #2
    Looks strange,

    what innodb_auto_increment_mode does it happens in ?

    Are you able to create some test case which will trigger the problem
    (even if it takes it hours to run to trigger one)

    Comment

    • #3
      We have not attempted to create any test cases... but that is a good idea. I will try to setup a test instance and duplicate it.

      I can give a little more background information. Our innodb_auto_increment_mode is the default, 1/traditional.

      This seems limited to occurring on tables that we do batch inserts onto.

      The table this is most often occurring on we have ~25 parallel processes running each doing batch inserts of 1000 rows per statement, which generally insert between 10,000 to 100,000 rows in total. The table is used more or less as a queue, so there will be brief periods of inserts like these, then have little activity.

      We have a few other tables that we regularly do batch inserts on and we see the same problem there as well.

      Thanks!
      Stephen

      Comment

      • #4
        Stephen,

        Thanks for info. It is very hard to provide any meaningful explanation without bug report test case. It must be something specific in your case as the general description is very simple and it works fine in many instances

        Comment

        • #5
          Are there triggers on the table you're trying to insert into? If the trigger is inserting into another table, that could actually be where the error is happening.

          Comment

          • #6
            No triggers on any of these tables.

            We've also tried Mysql 5.1.55 (not a Percona build) and the issue persists.

            I'll try to put together a bug report/test case.

            Comment

            • #7
              This sounds very similar to the issue I'm having here:

              http://forum.percona.com/index.php/mv/msg/1762/

              If you set the innodb_auto_increment_mode variable to 0, do you continue to see the issue?

              I have only been able to duplicate the issue when there is a trigger on the table and multiple threads are running bulk inserts. However, the trigger does a simple sleep and doesn't touch any other tables.

              Comment

              • #8
                mbenshoof wrote on Tue, 22 March 2011 09:13
                This sounds very similar to the issue I'm having here:

                http://forum.percona.com/index.php/mv/msg/1762/

                If you set the innodb_auto_increment_mode variable to 0, do you continue to see the issue?

                I have only been able to duplicate the issue when there is a trigger on the table and multiple threads are running bulk inserts. However, the trigger does a simple sleep and doesn't touch any other tables.

                Unsure.. we were experimenting with running mysql 5.1.x on a new shard and we ended up just downgrading that shard back to mysql 5.0.x until I have some free time to better investigate it.

                It def appears related to bulk inserts.

                Comment

                • #9
                  Attempted to run 5.5.15-21 percona on a new shard, and we ran into the exact same problem as we had with 5.1. We have 17 other 5.0.x percona master-master shards setup that have the same application code running on it, and we have zero issues with them

                  Comment

                  • #10
                    Perhaps this is relevant? http://optimmysql.blogspot.com/2008/04/variable-day-out-7-in nodbautoinclockmod.html We're currently have a value of 1 set for innodb_autoinc_lock_mode

                    Comment

                    • #11
                      Just got this error over replication... its not even an insert query and the PK reference doesn't match up with the duplicate entry key error... When we get these errors over replication, if we run stop slave; start slave; All works as expected until the next bogus error occurs.


                      Error 'Duplicate entry '979802' for key 'PRIMARY'' on query. Default database: 'XXXX'. Query: 'UPDATE table_name SET VALUE = 0,PROBABILITY = 0,IS_WON = 0,IS_ARCHIVED = 0,UPDATED_AT = '2011-10-06 16:03:20' WHERE table_name.ID=2'

                      Comment

                      Working...
                      X