MySQL ON DUPLICATE KEY insert into an audit or log table

Question

Is there a way to accomplish this?

INSERT IGNORE INTO some_table (one,two,three) VALUES(1,2,3)
ON DUPLICATE KEY (INSERT INTO audit_table VALUES(NOW(),'Duplicate key ignored')

I really don't want to use PHP for this :(

Thanks!

Brina

I guess "no", unless you use a stored procedure to perform this logic. – Cartilaginous

Answer 1

If you want to consider using a stored procedure, you can use a DECLARE CONTINUE HANDLER. Here's an example:

CREATE TABLE users (
    username    VARCHAR(30), 
    first_name  VARCHAR(30), 
    last_name   VARCHAR(30),
    PRIMARY KEY (username)
);

CREATE TABLE audit_table (timestamp datetime, description varchar(255));

DELIMITER $$
CREATE PROCEDURE add_user 
       (in_username    VARCHAR(30),
        in_first_name  VARCHAR(30),
        in_last_name   VARCHAR(30))
    MODIFIES SQL DATA
BEGIN
    DECLARE duplicate_key INT DEFAULT 0;
    BEGIN
        DECLARE EXIT HANDLER FOR 1062 SET duplicate_key = 1;

        INSERT INTO users (username, first_name, last_name)
               VALUES (in_username, in_first_name, in_last_name);
    END;

    IF duplicate_key = 1 THEN
        INSERT INTO audit_table VALUES(NOW(), 'Duplicate key ignored');
    END IF;
END$$
DELIMITER ;

Let's add some data, trying to insert a duplicate key:

CALL add_user('userA', 'Bob', 'Smith');
CALL add_user('userB', 'Paul', 'Green');
CALL add_user('userA', 'Jack', 'Brown');

Result:

SELECT * FROM users;
+----------+------------+-----------+
| username | first_name | last_name |
+----------+------------+-----------+
| userA    | Bob        | Smith     |
| userB    | Paul       | Green     |
+----------+------------+-----------+
2 rows in set (0.00 sec)

SELECT * FROM audit_table;
+---------------------+-----------------------+
| timestamp           | description           |
+---------------------+-----------------------+
| 2010-10-07 20:17:35 | Duplicate key ignored |
+---------------------+-----------------------+
1 row in set (0.00 sec)

If auditing is important on a database level, you may want to grant EXECUTE permissions only so that your database users can only call stored procedures.

Fact

A stored procedure or trigger (as MatTheCat proposed) will solve this problem but most hosting providers won't accept SP or triggers on their servers, don't know why, I guess some kind of security issue. – Brina

@ricardocasares: I would treat that as an opportunity to switch hosting providers :) – Fact

Thank you very much Daniel!! I'll consider to switch hosting ;) and thanks a lot for that great example! – Brina

Answer 2

ON DUPLICATE KEY is used to update the row, not to insert in another table, you have to use two queries according the first's result.

EDIT : you can use a trigger too

Minni

"you have to use two queries according the first's result." Can you explain a little bit further or give me an example of this?? Thank you very much! – Brina

Forgot this you would have to use PHP ^^' Use a trigger or try Daniel's solution. – Minni

Thanks for your replies Mat ;) – Brina

Answer 3

Not sure if this would work but look into IF statement for MySQL

Pseudo-code

IF(SELECT id_key_index FROM tbl WHERE id_key_index = $index) THEN (UPDATE SECOND TBL);
    ELSE
      INSERT ...
    END IF;

Erskine