I have the following table in a MySQL 8.0 database (multiple fields left out of the table below) with a composite primary key that I cannot update using INSERT INTO ON DUPLICATE KEY UPDATE
CREATE TABLE IF NOT EXISTS db.entries (
id VARCHAR(36) NOT NULL COMMENT 'UUID',
scenario VARCHAR(36) NOT NULL COMMENT 'UUID',
archive BOOLEAN NOT NULL DEFAULT FALSE,
PRIMARY KEY (id, scenario)
);
I execute the query
INSERT INTO db.entries VALUES (?, ?, ?) as new
ON DUPLICATE KEY UPDATE
archive = new.archive;
and receive the following error (sample) when changing the archive
flag to TRUE
and saving the entry.
Error 1062 (23000): Duplicate entry '14a80111-eaf6-4b58-8803-7ec10f6a61c9-77bc657d-e81d-4ea8-8dbc-ce1' for key 'entries.PRIMARY'
I expected the query’s ON DUPLICATE KEY UPDATE
to update the entry’s archive status to TRUE vice throwing an error. I suspect the composite PRIMARY KEY
is where my problem is lurking. I have researched the problem in depth in MySQL’s documentation and on Stack Exchange but none of the solutions I could find involved composite PRIMARY
keys and ON DUPLICATE KEY UPDATE
Update.
I removed the ?
from VALUES
substituting in the actual values and it seems to work, but not sure why the difference.
CREATE TABLE IF NOT EXISTS entriesTest (
id VARCHAR(36) NOT NULL,
scenarioId VARCHAR(36) NOT NULL,
archive BOOLEAN NOT NULL DEFAULT FALSE,
PRIMARY KEY (id,scenarioId)
);
INSERT INTO entriesTest (id, scenarioId, archive) VALUES ('1', '1', FALSE);
INSERT INTO entriesTest (id, scenarioId, archive) VALUES ('2', '1', FALSE);
INSERT INTO entriesTest (id, scenarioId, archive) VALUES ('3', '1', FALSE);
INSERT INTO entriesTest (id, scenarioId, archive) VALUES ('1', '1', TRUE) as t
ON DUPLICATE KEY UPDATE archive = t.archive;
2
Answers
So, it turns out someone on the team decided to setup an update based SQL trigger that inserted the new entry into an identically named table in the
audit
database. The duplicate key error triggered when attempting to insert the new entry into the table in theaudit
schema. Since SQL does not provide a schema or database qualifier before the table identified in the error, we changed the name of the table in the audit database to make it easier to track down future errors. Bottom line...MySQL 8.0 and Go are working just fine!I tried it in local Mysql 5.7, with a working code, cannot test with Mysql 8.
I don’t know about the row alias
VALUES(?, ?, ?) as new
, but Mysql has a good way of reusing the original data passed for each row withVALUES(col_name)
https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html , like:Here is the full SQL codes. First I entered
id1-scenario1
with archivefalse
. And at the bottom, I re-entered the same, but with archivetrue
, it updated it to true, with no problem:From there, you can also force archive to true like
UPDATE archive = true
, or