duplicate key error does not cancel/rollback mysql transaction

mysql, rollback, transactions

Solution

MySql (and other sql engine AFAIK) does not automatically rollback a transaction if an error occurs.

You have to declare an error handler that will Rollback the transaction:

DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND
BEGIN 
  ROLLBACK; 
  CALL ERROR_ROLLBACK_OCCURRED; 
END;

Problem

When in a mysql innodb transaction, I would expect a duplicate key error to cause a rollback. It doesn't, instead it simply throws an error and continues on to the next command. Once the COMMIT command is reached, the transaction will be committed, sans the duplicate key causing command. Is this the expected behaviour? If so, how would one go about setting it up so that the transaction is rolled back instead of committed when such an error occurs? test environment: ``` CREATE TABLE `test` ( `id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 BEGIN; INSERT INTO test VALUES (5); INSERT INTO test VALUES (5); COMMIT; ``` expected result: table `test` is empty actual result: table `test` contains one record with value 5

Original source