tag:blogger.com,1999:blog-15319370.post7849661902262251447..comments2024-03-05T11:16:00.846+01:00Comments on Roland Bouman's blog: MySQL: Transactions and Autocommitrpboumanhttp://www.blogger.com/profile/13365137747952711328noreply@blogger.comBlogger38125tag:blogger.com,1999:blog-15319370.post-71680732581702608822012-05-09T22:22:47.872+02:002012-05-09T22:22:47.872+02:00Thanks. working perfectly.Thanks. working perfectly.ie10http://www.ie10download.comnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-17058440416985136302011-04-06T13:43:38.458+02:002011-04-06T13:43:38.458+02:00Omar, it's not really relevant anymore since d...Omar, it's not really relevant anymore since development on Falcon has stopped.rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-33692987438099624762011-04-06T13:36:20.281+02:002011-04-06T13:36:20.281+02:00Thanks alot, I didn't know about falcon's ...Thanks alot, I didn't know about falcon's reaction against failed statements.Omar Abdallahhttp://www.omaroid.comnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-81204261361502607612011-02-24T16:13:47.932+01:002011-02-24T16:13:47.932+01:00anonymous transactionsanonymous transactionsanonymous transactionshttp://www.byebyebigbrother.comnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-20246236797744643392010-05-14T09:06:02.932+02:002010-05-14T09:06:02.932+02:00Hi Mani, I have applied your way. It is working . ...Hi Mani, I have applied your way. It is working . What a great tip<br /><br /><br /><br /><br />Regards,<br />Naganathan G<br />Pace Automation Ltd<br />ChennaiNaganathan Ghttp://www.paceautomation.comnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-31514226831566014762010-05-10T14:21:08.656+02:002010-05-10T14:21:08.656+02:00@mani: thanks, useful tip :)@mani: thanks, useful tip :)rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-9644103042469382422010-05-10T14:20:46.865+02:002010-05-10T14:20:46.865+02:00@Anonymous: I have no idea why it isn't workin...@Anonymous: I have no idea why it isn't working for you. It should. What steps did you take to verify the autocommit setting?rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-21117595962957613512010-05-10T11:22:29.387+02:002010-05-10T11:22:29.387+02:00Auto commit off
linux comes with an application ...Auto commit off <br /><br />linux comes with an application called 'expect'. it interacts with the shell in such a way as to mimic your key strokes. it can be set to start mysql, wait for you to enter your password. run further commands such as SET autocommit = 0; then go into interactive mode so you can run any command you want.<br /><br /><br />spawn /usr/local/mysql/bin/mysql <br />expect "mysql>" <br />send "set autocommit=0;\r" <br />expect "mysql>" interact <br /><br /><br />Try to thismanihttp://www.paceautomation.comnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-40309599694167389852010-05-10T11:13:32.827+02:002010-05-10T11:13:32.827+02:00my.cnf
init_connect='SET autocommit=0'
...my.cnf <br /> <br />init_connect='SET autocommit=0'<br /><br /><br />this setup is not working. Is there any alternate way ?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-15755424557006326032009-07-06T18:32:54.332+02:002009-07-06T18:32:54.332+02:00Dmitriy,
ok. so it's a regression then.
I su...Dmitriy,<br /><br />ok. so it's a regression then.<br /><br />I suggest to move any discussion about the bugs to bugs.mysql.com. Please note that all additional comments should be posted to bug <a href="http://bugs.mysql.com/bug.php?id=45309" rel="nofollow">http://bugs.mysql.com/bug.php?id=45309</a> as the bug report I filed is a duplicate.<br /><br />Note that a patch has been pushed already, so it'd be really great if someone could check it out. Thank you.rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-82399841972144828602009-07-06T18:27:32.560+02:002009-07-06T18:27:32.560+02:00Dmitriy,
"Is there a good way to make init_c...Dmitriy,<br /><br />"Is there a good way to make init_connnect also apply to SUPER user?"<br /><br />No - there isn't. Unfortunately you can't just set<br /><br />autocommit=0 <br /><br />in your my.cnf either.rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-61664921627417569792009-07-06T18:17:43.962+02:002009-07-06T18:17:43.962+02:00Dmitriy, really, it should make no difference at a...Dmitriy, really, it should make no difference at all. The semantics of autocomit is as if autocommit is disabled, and every statement is executed as <br /><br />START TRANSACTION;<br /><br />...statement...;<br /><br />COMMIT;<br /><br />Now, if you agree that the above sequence of statements should execute atomically even if an abort is received along the way, then it shouldn't matter how this sequence of statements came about - implicitly through autocommit or explicitly as true separate statements. In both cases there is a possibility of noticing the abort when it is too late already (read: when you can't undo the changes anymore), and it is a bug to actually chicken out of the statement in that case.rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-53735719523865052262009-07-06T17:48:06.026+02:002009-07-06T17:48:06.026+02:00FYI, As per Brian
this Bug is not found in previou...FYI, As per Brian<br />this Bug is not found in previous release<br />http://forums.mysql.com/read.php?22,269979,270026#msg-270026<br />So it is seams like a 5.1 issue.<br />DmitriyAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-24632378475082987532009-07-06T17:41:34.938+02:002009-07-06T17:41:34.938+02:00Roland,
Is there a good way to make init_connnect ...Roland,<br />Is there a good way to make init_connnect also apply to SUPER user? It seamed disabled for a reason, but often DBAs are connect as SUPER and it is annoying<br />if you have to set all variables manually.<br /><br />Thanks,<br />DmitriyAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-45736188147869650782009-07-06T17:25:01.148+02:002009-07-06T17:25:01.148+02:00Hi Roland
The way I see it, is if you disable auto...Hi Roland<br />The way I see it, is if you disable autocommit<br />even if you issue Ctrl-C , you are in pretty<br />safe state. Even if you transaction is partially processed and you see some changes are made to the data. these changes are NOT YET COMMITTED and they only visible to your session. You have an Option<br />to either Commit or Rollback. Of course you would choose to Rollback in this case as you SQL failed.<br />But even if you do nothing and decide to exit, MYSQL will do implicit Rollback once you terminate your session to preserve transaction consistency.<br />Hence your transaction will not cause any issue, and in addition your Master/Slave consistency is preserved as well. <br />You can do the same test you did previously while disabling autocommit. to see how mysql behaves.<br />Regards,<br />Dmitriy RoyzenbergAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-50649228490609259122009-07-06T16:44:37.053+02:002009-07-06T16:44:37.053+02:00Hi Dmitriy
"it seams to me that even MYSQL d...Hi Dmitriy<br /><br />"it seams to me that even MYSQL doesn't like to rely on the autcommit=1"<br /><br />mm, with all due respect to Michael who commented on bug 45923 - I am not ready to accept his opionn as the final word from all of MySQL. As for never knowing when commit took place - this doesn't really convince me. The way I see it, there is no problem at all with noticing the abort. I mean, seriously, if the process is in a stage where some changes cannot be undone anymore, the abort request should simply be discarded.<br /><br />Another way of looking at it is this. Suppose you would do as you and Michael say, and leave autocommit disabled, and now you do a manual commit, and then a Ctrl+C aborts the commit command - you would be in exactly the same situation, right? It should be like this IMO: either the server knows it can recover and rollback, and chooses to honour the abort command, or it knows it cannot guarantee it, and simply ignores the abort command, pretending it arrived late or something like that. <br /><br />Anyways, thanks for spotting the bug.rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-12671405103696784782009-07-06T16:02:03.738+02:002009-07-06T16:02:03.738+02:00Thanks you , Roland for reporting teh Bug
I've...Thanks you , Roland for reporting teh Bug<br />I've added some comments there.<br />it seams to me that even MYSQL doesn't like to rely on the autcommit=1, as you neve know when commit took place, expecially in Master/Slave env.<br />Of course MYSQL should fix it, however<br />I'd always recoomment to set it to 0.<br />as I've explained in my comments.<br /><br />Reagrds,<br />Dmitriy RoyzenbergAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-37914687420938700842009-07-02T23:33:15.552+02:002009-07-02T23:33:15.552+02:00Dmitriy, you are right.
This looks like a really...Dmitriy, you are right. <br /><br />This looks like a really nasty bug to me. I have filed a bug report here:<br /><br /><a href="http://bugs.mysql.com/bug.php?id=45923" rel="nofollow">http://bugs.mysql.com/bug.php?id=45923</a><br /><br />If anybody can reproduce it, please add your information to the bug report. I hope it will be fixed soon.<br /><br />thanks again! this was a really good spot.rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-14714516601260103572009-07-02T19:28:17.992+02:002009-07-02T19:28:17.992+02:00Hi!
sorry if you had an issue with the blogger co...Hi!<br /><br />sorry if you had an issue with the blogger comment interface. Indeed it has its flaws (though I can paste fine)<br /><br />I'll try and test later this evening. Thanks so far !rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-66280209286238053052009-07-02T18:44:30.273+02:002009-07-02T18:44:30.273+02:00Roland
Your window is not very friendly , I can...Roland<br />Your window is not very friendly , I can't copy and paste<br />but i checked and it is InnoDB<br />ENGINE=InnoDB CHARSET=utf8 COLLATE=utf8_inicode_ci<br />I will send you email, but you can do you test on any 200K table. it is simple.<br /><br />DmitriyAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-63896914725761903592009-07-02T18:41:47.509+02:002009-07-02T18:41:47.509+02:00Thanks! and your SHOW CREATE TABLE ?Thanks! and your SHOW CREATE TABLE ?rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-66239832323036490492009-07-02T18:40:28.042+02:002009-07-02T18:40:28.042+02:00I connected to server vi mac terminal through ssh
...I connected to server vi mac terminal through ssh<br />after that just a command line to connect to sql<br />the server runs on linux 32-bit serverAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-58064020287777636102009-07-02T18:34:54.312+02:002009-07-02T18:34:54.312+02:00Hi Dmitry!
you can always mail me at roland.bouma...Hi Dmitry!<br /><br />you can always mail me at roland.bouman@gmail.com<br /><br />Can you please tell me whcih tool you are doing the Ctrl+C in? Also, can you please post the SHOW CREATE TABLE statement? I trust you of course but I want to be 100% sure it's an InnoDB table.<br /><br />Thanks! <br /><br />Rolandrpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-72615894000234410842009-07-02T18:23:00.884+02:002009-07-02T18:23:00.884+02:00I have an example but can't copy and paste ast...I have an example but can't copy and paste ast it si disabled.<br />any way to send you an example?<br />just try it it is simple if you have a table non productin wth at least 200k rows<br />issue simmilar statement agaibst column that has at least varchar(160)<br />update mytable set mycolumn = RPAD('H',150,'K');<br />as its running issue Ctrl-C it will says that the query is canceled. However check data and see it is commited and you can;t rollback. howrver is use disable autocommit it works fine.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-72574331364659559122009-07-02T18:03:25.375+02:002009-07-02T18:03:25.375+02:00Hi Dmitry!
"if autocommit=1 and you cancel u...Hi Dmitry!<br /><br />"if autocommit=1 and you cancel update statement with Ctrl-C on the middle, the data is still commited partially. This is bad, as it really should rollback."<br /><br />uhm..if it does, this is a bug.<br /><br />That said, with all respect, i highly doubt it. Please tell me more about your execution environment (mysql command line? query browser?) your table definitions (do SHOW CREATE TABLE), your dataset and your update statement.<br /><br />kind regards, <br /><br />Rolandrpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.com