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

Weird duplicate entry on insert

    XMLWordPrintable

    Details

      Description

      I'm getting occasional weird errors trying bulk insert into InnoDB table with a composite primary key. By bulk insert I mean "INSERT INTO TABLE (columns) VALUES (tuple1), (tuple2), ...". The number of tuples in a statement is quite large, in the order of 10k. Most of the time it works fine but occasionally I get errors like

      sqlalchemy.exc.IntegrityError: (_mysql_exceptions.IntegrityError) (1062, "Duplicate entry '1017678102-2020-01-05 02:54:45' for key 'PRIMARY'") [SQL: 'INSERT INTO "DiaObject" ...
      

      (I run data loading from Python using sqlalchemy).

      The schema for a table looks like:

      CREATE TABLE "DiaObject" (
        "diaObjectId" bigint(20) NOT NULL AUTO_INCREMENT,
        "validityStart" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        "validityEnd" timestamp NULL DEFAULT NULL,
        "lastNonForcedSource" timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
        "ra" double NOT NULL,
        "raSigma" float NOT NULL,
        -- ~50 more columns
        "flags" bigint(20) NOT NULL,
        "htmId20" bigint(20) NOT NULL,
        PRIMARY KEY ("diaObjectId","validityStart"),
        KEY "IDX_DiaObject_validityStart" ("validityStart"),
        KEY "IDX_DiaObject_htmId20" ("htmId20")
      ) ENGINE=InnoDB
      

      The validityStart is unique for each INSERT statement. sqlalchemy dumps the whole INSERT statement with the exception and I do not see a repeating diaObjectId there, so I am 100% sure there cannot be primary conflict in this case. And if I re-run the query which fails using command-line tool the INSERT works just fine.

      I saw this first with CentOS7-standard 5.5.50 mariadb, later I upgraded from mariadb repo to current 10.1.20, but the issue still remains. I also saw the same error with a different table which is also InnoDB with a 2-column composite PK.

      Did anyone see anything similar before? I can post more details if needed, I have complete query and I have a huge query log file taken with 5.5.50.

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            NaiveDeveloper Andy Salnikov
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: