CODEWITHSUNDEEP
mysql
DarkSnake
DarkSnake

Reputation: 59

MySQL INSERT not working as expected

This query is running an upsert which is being called from PHP, I wasn't getting the updates except for one table, which was really odd. I tried the query and noticed this was seeming to be the case. If I run it for the following value, the row does not update.

Row

operation_attendee_id  operation_id   member_id   status
         1                 5              1         1

Query:

INSERT INTO
    `operation_attendee` (
    `operation_id`,
    `member_id`,
    `status`)
VALUES
    (5,
    1,
    3)
ON DUPLICATE KEY UPDATE
    `status` = 3

This will not update the table, but there is a unique index on operation_id, member_id

If I run for this row

operation_attendee_id  operation_id   member_id   status
         0                7              1         1

Same exact query, different numbers

INSERT INTO
    `operation_attendee` (
    `operation_id`,
    `member_id`,
    `status`)
VALUES
    (7,
    1,
    3)
ON DUPLICATE KEY UPDATE
    `status` = 3

This one updates fine. I'm really perplexed as to why this seems to be the only value set that is updating.

EDIT: Adding table DDL and all indexes:

CREATE TABLE `operation_attendee` (
 `operation_attendee_id` int(11) NOT NULL,
 `operation_id` int(11) NOT NULL,
 `member_id` int(11) NOT NULL,
 `status` int(11) NOT NULL,
 PRIMARY KEY (`operation_attendee_id`),
 UNIQUE KEY `uk_operation_member` (`operation_id`,`member_id`),
 KEY `fk_operation_attendee_operation1_idx` (`operation_id`),
 KEY `fk_operation_attendee_member1_idx` (`member_id`),
 KEY `fk_operation_attendee_operation_attendee_status1_idx` (`status`),
 CONSTRAINT `fk_operation_attendee_member1` FOREIGN KEY (`member_id`) REFERENCES `member` (`member_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 CONSTRAINT `fk_operation_attendee_operation1` FOREIGN KEY (`operation_id`) REFERENCES `operation` (`operation_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 CONSTRAINT `fk_operation_attendee_operation_attendee_status1` FOREIGN KEY (`status`) REFERENCES `operation_attendee_status` (`operation_attendee_status_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8

I also tried to run the query with foreign key check off and it still doesn't update.

and

Upvotes: 1

Views: 49

Answers (1)

DarkSnake
DarkSnake

Reputation: 59

It seems it was an issue with the surrogate key.

I removed it (it was not needed for that table, someone else built it a long while back), and then replace the primary key with the unique key I was using prior (removed the unique key as well since it was redundant) the query now seems to be working fine.

I'm not sure why it was causing issues in terms of an underlying reason, but it works now. I would think the unique key I put on the table would have matched the conditions for the ON DUPLICATE KEY UPDATE requirement.

Upvotes: 1

Related Questions