Duplicate UNIQUE KEY error after ROLLBACK in MySQL

Question

I have a table like this

mysql> describe seudonimos;
+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+
| id_seudonimo | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| seudonimo    | varchar(45)      | NO   | UNI | NULL    |                |
+--------------+------------------+------+-----+---------+----------------+
2 rows in set (0.02 sec)

Let's assume that it is empty so autoincrement is 0. For example:

SET AUTOCOMMIT=0;
BEGIN TRANSACTION;
INSERT INTO seudonimos (seudonimo) VALUES ('Agatha Christie');
ROLLBACK;
SET AUTOCOMMIT=1;

As far as I konw, the rollback does not affect the autoincrement. So if I insert a new value the autoincrement will be 2 instead of 1. But if I try to insert 'Agatha Christie' again, I have the following problem:

INSERT INTO seudonimos (seudonimo) VALUES ('Agatha Christie');

#1062 - Duplicate entry 'Agatha Christie' for key 'seudonimo'

That is not what I expected. I expected this:

+--------------+--------------------+
| id_seudonimo | seudonimo          |
+--------------+--------------------+
|            2 | Agatha Christie    |
+--------------+--------------------+

What's wrong?

Answer 1

In one of your comments to your own question, you're saying you're using MyISAM.

However, MyISAM does not support transactions (see ref. table). Therefore, it will always autocommit, whether you try to turn it off or not.

If you want to use transactions, you need to use an engine that supports it, such as InnoDB.

EDIT (following comments and additional information -- I must admit I hadn't realised you were also using a second table until @RolandoMySQLDBA pointed it out):

As the documentation says: "If you use tables that are not transaction-safe within a transaction, changes to those tables are stored at once, regardless of the status of autocommit mode."

Since a second table (seudonimos_consulta, using MyISAM) is involved in the transaction, via a trigger, what's inserted after the first INSERT in that table isn't rolled back. Hence, #1062 - Duplicate entry 'Agatha Christie' for key 'seudonimo' would refer to the second table, seudonimos_consulta.seudonimo, not seudonimos.seudonimo.