Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
5.5.50, 10.1.20
-
CentOS7 x86_64
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.