MySQL 5.0 commit SOMETIMES !? lazarus / 16-Databases ---------------------------------------------------------------------- mirce.vladimirov · 2015-10-16 00:30 Until one week a go I used Lazarus 1.0.2 and created few applications with total of 300-400 forms. MySQL worked fine without Commit/Rollback, not even one of my forms used transactions. Now, since one week a go I use Lazarus 1.4.2 with FPC 2.6.4 and I develope under Win7. Some forms from my existing application which I created with 1.0.2 work as they worked before but some forms are acting like they are inserting/update/deleting but they actually don't, so I have to do commit !? Some forms need commit and some don't ?! I have been reading similar posts on the forum about ALWAYS using commit or NEVER using commit but in my case it is SOME FORMS must use commit while SOME FORMS don't use it at all. What's the catch ? I want the previous way, i don't want to go back and do a lots of commit, my applications were working. ---------------------------------------------------------------------- HeavyUser · 2015-10-16 02:09 I don't know. what components did you use? how are they linked? do the ones that don't need commit have autocommit enabled? and a bunch of questions that might come up later on. have you try to duplicate one of those forms that require calling commit to a new simple application? one that you could post as a demo here probably that will show the problem and do one on the ones that do not require commit as well it will help you find the problem. ---------------------------------------------------------------------- mirce.vladimirov · 2015-10-16 07:47 I dont even know how to enable autocommit i never needed to use it. I have used commit/rollback in some of my  separate applications that use FirebirdSQL and SQLite but not with MySQL , and not autocommit. EDIT: I can see in the Object Inspector a property on the SQLTransaction called action, possible values are caCommit, caCommitretaining, caRollback, caRollbackretaining and caNone. Is that the autocommit thing ? If I set this property should my existing application work as before ? ---------------------------------------------------------------------- mirce.vladimirov · 2015-10-16 08:09 The components used are TMySQL50Connection, TSQLTransaction, TSQLQuery. I set this to caCommitRetaining (since I want the connection still open after I write to the DB) and now when run from within the Lazarus IDE i get an Exception from the debugger as on the attached picture: Operation cannot be performed on an active transaction. But when not running the application from the IDE but directly from the OS then I dont get the exception report. On the other hand if I set it to caCommit then nothing is writen/deleted until the application is closed. That is not what I need, i need the changes done immediately. After all appears that things are messed up on this property, things are not so unpredictable now. But in most of my existing applications this property is set to caNone and it works normally (normally as I mean normally, not need Commiting). Some apps work and some are not until I change this property. ---------------------------------------------------------------------- dinmil · 2015-10-16 12:13 I had similar problem with MySQL and my service application which works without human intervention. Application in programmed in the way that when connection is broken for some reason, it will try to reconnect itself. Commit and rollback works fine until MySQL is disconnected and connected again. After connection is dropped and connected again, sometimes commit does not work, and sometimes it works OK. I found the reason. When MySQL is disconnected by force (or else) TMySQLConnection holds transaction object which stays forever. If that happens every commit and rollback does nothing in database. So for me the solution was to find transaction object (check is it nil) and destroy it manually. Regards, Dinko ---------------------------------------------------------------------- LacaK · 2015-10-20 05:58 Quote from: mirce.vladimirov on October 16, 2015, 09:47:02 amI dont even know how to enable autocommit i never needed to use it. I have used commit/rollback in some of my  separate applications that use FirebirdSQL and SQLite but not with MySQL , and not autocommit. You must explicitly commit transactions: http://wiki.freepascal.org/User_Changes_2.6.4#MySQL_support_for_transa ctions_added Quote from: mirce.vladimirov on October 16, 2015, 09:47:02 amEDIT: I can see in the Object Inspector a property on the SQLTransaction called action, possible values are caCommit, caCommitretaining, caRollback, caRollbackretaining and caNone. Is that the autocommit thing ? If I set this property should my existing application work as before ? No it is not related to "autocommit" These settings only affect behavior, when transaction object is closed (for example in case of disconnection) ---------------------------------------------------------------------- mirce.vladimirov · 2015-10-20 08:04 QuoteYou must explicitly commit transactions: http://wiki.freepascal.org/User_Changes_2.6.4#MySQL_support_for_transa ctions_addedBut, I do have 3 of my existing applications with total of +300 forms and only some of them need transaction.commit, most of them don't. And I only use one TMySQLConnection with one TSQLTransatction for one DataBase in one project. QuoteNo it is not related to "autocommit" These settings only affect behavior, when transaction object is closed (for example in case of disconnection)In Object Inspector, on SQLTransaction, property called "Action". Set it to "caCommitretaining" , That does what I want : Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i MyQuery.close;Myquery.SQL.clear;MyQuery.sql.text:='insert into ttable (f1, f2, f3) values (v1, v2, v3)';MyQuery.ExecSQL;// SQLTransaction.commit;     // no need for this line if SQLTransaction.action:=caCommit// SQLTransaction.commitretaining; // no need for this line if SQLTransaction.action:=caCommitretaining ---------------------------------------------------------------------- LacaK · 2015-10-20 09:57 To be sure: Does you use on MySQL Server side transactional engine (like InnoDB) ? I supose you use. caCommitRetaining can work for you, but only if you disconnect and reconnect or close transaction. Because this is default action which is taken when transaction is going to close and is still active. (SQLTransaction1.Active:=False) ---------------------------------------------------------------------- mirce.vladimirov · 2015-10-20 11:06 I understand. You say that Transaction.Action is to be used if a connection is closed. OK. And the link to FP ChangeLog says that MySQL now must use transactions. OK. My application was created during 2011/2012 with Laz 1.0.2 which did not need transactions in MySQL. After I upgraded Lazarus to 1.4.2 few weeks a go, and re-compiled my application (the existing and working one) all seemed to work as it worked before. That application has many forms that insert, update and delete and it all worked well. But one day one a form which is not used often appeared to not insert, update, delete. In this one particular form I had to do a SQLTransaction.commitretaining from within the program. Other forms in the same application still work as before. I click the "write" button in my application to do the query.execsql and then I check my database and it's all there, writen well.  Here is the picture attached of the SQLTransaction.Action, it's set to caNone as it is by default. Second application, smaller one, also developed with Laz 1.0.2 after recompile with 1.4.2 acts the same, some forms don't need commit some do. In this one I did SQLTransaction.action=caCommitretaining. Why ? Because when I tested  SQLTransaction.action=caCommit : click the write button in the app and then check my DB.table nothing was writen/deleted until i exit the program (which is what you was telling). Then, i changed to  SQLTransaction.action=caCommitretaining : click the write button and then check my DB.table and all was there, writen inside the table at the proper field and looked wonderful. Trust me, I understand what you are telling me, but this is my personal experience. Both applications use one connection and one transaction. If someone wants to cry over this - here I am, I have a lots of compassion. Not that I could understand all this but simply I will have compassion, no one comforted me when I cried.