Timeline for Duplicate UNIQUE KEY error after ROLLBACK in MySQL
Current License: CC BY-SA 3.0
17 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Aug 5, 2011 at 16:47 | comment | added | RolandoMySQLDBA | @Mario, this question deservers a +1 because it uncovers a MySQL gotcha in Duplicate entry errors that can eminate from triggers. Sorry, I just noticed I gave you +1 already. | |
| Aug 5, 2011 at 16:14 | vote | accept | Mario | ||
| Aug 4, 2011 at 20:10 | comment | added | Mario |
seudonimosAU UPDATE seudonimos BEGIN UPDATE seudonimos_consulta SET seudonimos_consulta.seudonimo=NEW.seudonimo WHERE seudonimos_consulta.seudonimo=OLD.seudonimo; END AFTER NULL NO_AUTO_VALUE_ON_ZERO root@localhost utf8 utf8_general_ci utf8_general_ci seudonimosAD DELETE seudonimos BEGIN DELETE FROM seudonimos_consulta WHERE seudonimos_consulta.id_seudonimo=OLD.id_seudonimo; END
|
|
| Aug 4, 2011 at 20:09 | comment | added | Mario |
seudonimosAI INSERT seudonimos BEGIN INSERT INTO seudonimos_consulta SET seudonimos_consulta.id_seudonimo=NEW.id_seudonimo, seudonimos_consulta.seudonimo=NEW.seudonimo; END AFTER NULL NO_AUTO_VALUE_ON_ZERO root@localhost utf8 utf8_general_ci utf8_general_ci
|
|
| Aug 4, 2011 at 19:28 | comment | added | Bruno | @RolandoMYSQLDBA, sorry, I hadn't noticed your comment before posting my answer. Here is the quote from the doc if Mario is interested: "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." | |
| Aug 4, 2011 at 19:22 | answer | added | Bruno | timeline score: 2 | |
| Aug 4, 2011 at 19:20 | comment | added | RolandoMySQLDBA | Can you post the triggers in your question ??? | |
| Aug 4, 2011 at 19:18 | comment | added | RolandoMySQLDBA | 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. | |
| Aug 4, 2011 at 18:50 | comment | added | Mario |
yes, it is a unique key, but I think the problem is that there is another table seudonimos_consulta for fullindex query and of course it is MyISAM. The data of this table is inserted, updated and deleted by a trigger. Perhaps this warning | Warning | 1196 | Some non-transactional changed tables couldn't be rolled back | is suggesting that the problem is the MyISAM table. What do you think? If I am right, should I drop those triggers?
|
|
| Aug 4, 2011 at 18:45 | comment | added | Mario | mysql Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (i486) | |
| Aug 3, 2011 at 15:07 | history | edited | RolandoMySQLDBA | CC BY-SA 3.0 |
edited title
|
| Aug 3, 2011 at 15:00 | comment | added | RolandoMySQLDBA | Updated my answer with new observations | |
| Aug 3, 2011 at 14:41 | comment | added | RolandoMySQLDBA | 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 ??? | |
| Aug 3, 2011 at 14:20 | comment | added | RolandoMySQLDBA | Questions : What version of MySQL are you using ? Can you add to the question SHOW CREATE TABLE seudonimos\G please ??? | |
| Aug 2, 2011 at 23:57 | history | edited | RolandoMySQLDBA |
mysql should be included with mysql-5
|
|
| Aug 2, 2011 at 23:39 | history | tweeted | twitter.com/#!/StackDBAs/status/98538539490353152 | ||
| Aug 2, 2011 at 19:26 | history | asked | Mario | CC BY-SA 3.0 |