On duplicate key do nothing

I am inserting into the following table using LuaSQL with PtokaX API.

CREATE TABLE `requests` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `ctg` VARCHAR(15) NOT NULL,
    `msg` VARCHAR(250) NOT NULL,
    `nick` VARCHAR(32) NOT NULL,
    `filled` ENUM('Y','N') NOT NULL DEFAULT 'N',
    `dated` DATETIME NOT NULL,
    `filldate` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `nick_msg` (`nick`, `msg`),
    UNIQUE INDEX `ctg_msg` (`ctg`, `msg`)
)
COMMENT='Requests from users in any of the categories.'
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

Now, my problem is, when a user(represented by nick) tries to insert same request again, the UNIQUE index is checked and the script returns a false. This causes my script to fail and I have to restart the script.


Is there something I can do in the INSERT ... ON DUPLICATE KEY command so that it does nothing or at-least does NOT return an error in case of DUPLICATE KEY?

Otherwise I'd have to go for updating my dated field with the new DATETIME value.

Three ways. Either IGNORE duplicate errors
(but don't use that, see note in the end):

INSERT IGNORE 
  ... ;                   -- without ON DUPLICATE KEY

or try to do a redundant update when there is a duplicate:

INSERT 
  ... 
ON DUPLICATE KEY UPDATE
  id = id ;

or check for duplicates before inserting:

INSERT INTO requests
  (id, ctg, msg, nick, filled, dated, filldate)
SELECT
  NULL, 'urgent', 'Help!', 'Hermione', 'Y', NOW(), NOW()
FROM
  dual
WHERE NOT EXISTS
      ( SELECT *  FROM requests  WHERE (nick, msg) = ('Hermione', 'Help!') )
  AND NOT EXISTS
      ( SELECT *  FROM requests  WHERE (ctg, msg) = ('urgent', 'Help!') ) ;

A difference between the 3rd way and the first two is that when there are duplicates, the id will not be incremented. With INSERT IGNORE and INSERT ... ON DUPLICATE KEY, it will be auto incremented and since the insert will not be done, you'll have gaps in the values of id.

I should also add that your scripts should always check for errors anyway and not fail when there is one. Any query or statement can fail and return error occasionally, for various reasons. The tricks above will only save you from one kind of error.

*Note: INSERT IGNORE will ignore all insert related errors, even not null constraint violations, so it is best to avoid it.

StackExchange Database Administrators Q#38817, answer score: 29