Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Questions : What version of MySQL are you using ? Can you add to the question SHOW CREATE TABLE seudonimos\G please ??? –  RolandoMySQLDBA Aug 3 '11 at 14:20
    
OBSERVATION Your error message says Duplicate entry for key 'seudonimo'. By any chance, is that a KEY or a UNIQUE KEY on the seudonimo field ??? –  RolandoMySQLDBA Aug 3 '11 at 14:41
    
Updated my answer with new observations –  RolandoMySQLDBA Aug 3 '11 at 15:00
    
mysql Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (i486) –  Mario Aug 4 '11 at 18:45
1  
That's it. InnoDB and MyISAM operations combined can never be rolled back because once InnoDB and MyISAM join forces in queries, all tables take on the most pessimistic locking feature. InnoDB does row locking. MyISAM does table locking. Together, all tables behave as MyISAM, thus nullifying InnoDB transaction features. If the other table has FULLTEXT indexes, you must live with it and the lack of transactional behavior. –  RolandoMySQLDBA Aug 4 '11 at 19:18
show 6 more comments

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
You're right. But I have two tables. One of them is InnoDB, the other one is MyISAM. Of course, I was trying to use transactions with InnoDB table, but the problem is that both table are connected by triggers. –  Mario Aug 4 '11 at 19:56
    
See Rolando's comment to your question. It sounds like the duplicate entry may also come from the second table you're using. –  Bruno Aug 4 '11 at 20:13
    
@Mario, in your error message (#1062 - Duplicate entry 'Agatha Christie' for key 'seudonimo'), does it say which table it comes from? From the description of your triggers, it could come from seudonimos_consulta.seudonimo. For debugging this, it might be interesting to rename seudonimos_consulta.seudonimo to something different (e.g. seudonimos_consulta.seudonimo2) if possible, to check which table it's referring to. I suspect that, if the seudonimos_consulta table is using MyISAM and there's a unique constraint on seudonimo there, it won't be rolled back and cause the duplicate. –  Bruno Aug 5 '11 at 9:09
    
Yes, the problem is that seudonimos_consulta is using MyISAM and there is a unique constraint on seudonimo. You are right, problem is solved. And I think I will drop MyISAM tables, and of course, its triggers, because I've found another method with temporary tables. Thank you. I'm new here. What should I do? Should I answer my own question or should I let you or Rolando answer it? –  Mario Aug 5 '11 at 15:16
    
Usually, you can mark an answer as useful by clicking on the arrow/triangle pointing upwards above the vote counter left of the answer. In addition, because you're the one who asked the question, if you think one of the answers is the right one, you can mark it as the accepted answer (with the green tick under the answer's vote counter). You can up-vote (or down-vote if it's bad) multiple answers, but you can only accept one answer. It's up to you to choose. Don't feel pressurised. There's a bit more on the point system if you expand this: dba.stackexchange.com/faq#reputation –  Bruno Aug 5 '11 at 15:55
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.