On duplicate key update throws an en duplicate entry exception



  • I have a table with the following structure

    create table stats
    (
        id             int auto_increment primary key,
        serial_no      varchar(20) not null,
        epoch          int         not null,
        version        varchar(20) null,
    
    constraint idx_serial_no unique (serial_no)
    

    );

    And I run the following update query

    INSERT INTO stats 
           (serial_no, version, epoch)
    VALUES (?, ?, ?)
        ON DUPLICATE KEY
    UPDATE id        = LAST_INSERT_ID(id),
           serial_no = VALUES(serial_no),
           version   = VALUES(version),
           epoch     = VALUES(epoch)
    

    Oddly enough, at one point MySQL 5.7 stopped updating the data and instead started sending me Duplicate entry 'abc123' for key 'idx_serial_no', when I execute the query above.



  • You have two unique keys -- PRIMARY KEY(id) and UNIQUE(serial_no). Furthermore, the IODKU is specifying both id and serial_no. Apparently, both values are in the table, but in different rows!

    "Normally" one would not include the AUTO_INCREMENT column (id) when doing IODKU.

    Why even have id? Why not use PRIMARY KEY(serial_no)?




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2