Question 347565 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL INSERT INTO ON DUPLICATE KEY UPDATE Failing with Composite Primary Key

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;
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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 the audit 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!


  2. 0

    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 with VALUES(col_name) https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html , like:

    INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6)
  ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

    Here is the full SQL codes. First I entered id1-scenario1 with archive false. And at the bottom, I re-entered the same, but with archive true, it updated it to true, with no problem:

    create database mydb;
use mydb;

CREATE TABLE IF NOT EXISTS 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)
);

# aim at this:
INSERT INTO entries (id, scenario, archive) VALUES ('id1', 'scenario1', false)
ON DUPLICATE KEY UPDATE archive = VALUES(archive);

INSERT INTO entries (id, scenario, archive) VALUES ('id1', 'scenario2', false)
ON DUPLICATE KEY UPDATE archive = VALUES(archive);

INSERT INTO entries (id, scenario, archive) VALUES ('id2', 'scenario1', false)
ON DUPLICATE KEY UPDATE archive = VALUES(archive);

# try duplicate composite with updated archive.
INSERT INTO entries (id, scenario, archive) VALUES ('id1', 'scenario1', true)
ON DUPLICATE KEY UPDATE archive = VALUES(archive);

    From there, you can also force archive to true like UPDATE archive = true, or

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search