[SOLVED] TMySQL55Connection (AutoCommit & Refresh) now is different in Laz1.2.2 lazarus / 16-Databases ---------------------------------------------------------------------- JZS · 2014-05-01 12:48 Hi 2 All, Using Laz1.2.2 fpc2.6.4 and Laz1.2 fpc2.6.2 has differences that are not told. Here is what has happened, I have a project, that was successfully managing data over MariaDB Tables using Laz1.2, until I upgraded to Laz1.2.2 then I noticed that, changing the data does not take effect on the server, instead it effects only the local view and upon restarting, the data reverts back (i.e. changes undone). Just to give an example of how I am doing it: Code: [Select]     with TSQLQuery.Create(nil) do         begin           try             DataBase:=MySQL55Connection1;             SQL.Clear;             SQL.Add('insert into MyTable (aID, aName) values(:aID, :aName);'); //update is performed using the same scenario             ParamByName('aID').AsInteger:=1;             ParamByName('aName').AsString:='Name';             ExecSQL;           finally             Close;             Free;           end;         end;Sample application attached. The bizarre behavior is, when refreshing data, or simply select over a table. That does not bring up the actual data (i.e. it does not show any changes that has been done by another application). And, yes, I am sure the changes by the other application has been posted, in case someone asks. I have double checked changing the data by the Application from Laz1.2 that posts the changes to the DB, while same application compiled with Laz1.2.2 fails to show the changes even when refreshing or performing (Select * from table), it keeps showing the local view only. The question is, is there any new settings that am missing? I read that the only new feature that was introduced is AutoCommit=False, but if I read correctly that was for ODBCConnection only, however that shouldn't be the case even if AutoCommit is off. Am I missing something here? ---------------------------------------------------------------------- taazz · 2014-05-01 14:46 I think I red somewhere that the default behavior of MySQL has changed from auto commit to auto cancel now you have to add code something like Code: [Select] ...           finally             Close;             Database.Transaction.Commit;             Free; or something along those lines check the various positions ee if calling close before commit you loose the changes (which I doubt at this point but just to be safe check it for your self), then you need to call commit before closing the query and make sure that committing does not close query as well in process. I think it was in this forums that I show the behavior change for mysql can't recall for sure though. ---------------------------------------------------------------------- eara · 2014-05-01 15:44 i think is related to http://forum.lazarus.freepascal.org/index.php/to pic,24437.msg147077.html#msg147077 If no transaction support at all (for MySQL) in previous versions, then didn't needed to commit, the not existed transaction was autocommited by default  But now, with transaction support there is an underlying transaction which waits for commit... Autocommit could be also disabled, or enabled from server settings so don't count on that (Somewhere i read this) ---------------------------------------------------------------------- JZS · 2014-05-02 08:25 Assuming it's about AutoCommit then I can understand posting changes do not take effect, but I do not know why changes by others do not show up when refreshing or do a "Select * from table" in my application? Something not clear. Is it only me? Has anyone tried successfully? ---------------------------------------------------------------------- eara · 2014-05-02 10:43 I am not sure, but i speculate: it is about transaction. If in 1.2 was no transaction under your connection, then changes in each statement where autocommited (by MySQL) and everyone could see it. Now in 1.2.2 suddently we have transaction support for mysql, and the connection starts a transaction. So all statements are inside a transaction and if you not commit them then they are rolled back when you close connection. Nobody can see them? i think, is because of transaction isolation level which is not dirty read i suspect. Anyway, now you should call TSQLTransaction.Commit (if you use this component). I just realized this (with 1.2) from your posts. In my project i call in afterPost, AfterDelete always the Commit after the ApplyUpdates, so i didn't notice it that i had no transaction, but it hit me when i was trying to generate serial codes and needed SELECT FOR UPDATE to lock the data and lock was not possible (due to no lack of transaction support), but now with 1.2.2 works as expected. ---------------------------------------------------------------------- LacaK · 2014-05-02 11:39 Quote from: JZS on May 01, 2014, 02:48:05 pmUsing Laz1.2.2 fpc2.6.4 and Laz1.2 fpc2.6.2 has differences that are not told. Yes it should be mentioned in "User changes" section Quote from: JZS on May 01, 2014, 02:48:05 pmHere is what has happened, I have a project, that was successfully managing data over MariaDB Tables using Laz1.2, until I upgraded to Laz1.2.2 then I noticed that, changing the data does not take effect on the server, instead it effects only the local view and upon restarting, the data reverts back (i.e. changes undone). Yes in Lazarus 1.2.2 you must after ApplyUpdates also Commit changes Quote from: JZS on May 01, 2014, 02:48:05 pmThe question is, is there any new settings that am missing? I read that the only new feature that was introduced is AutoCommit=False, but if I read correctly that was for ODBCConnection only, however that shouldn't be the case even if AutoCommit is off. Am I missing something here? No there is no new settings related to MySQL, only support for transactions was added. So if you use transactional engine on MySQL server you must take care of transaction commiting (this was not a case in previous version, because there was no transaction support so all statements was send in autocommit mode) ---------------------------------------------------------------------- JZS · 2014-05-02 11:53 So now even select has to be called with Commit. Can that be disabled? Thanks Lacak, eara & Taazz ---------------------------------------------------------------------- taazz · 2014-05-02 13:11 err can you elaborate? select does not change database data so commit/rollback or even keep it open for the next 120 years shouldn't make any difference. ---------------------------------------------------------------------- JZS · 2014-05-02 13:38 If there are two users using same Application "user: A" and "user: B" Using laz1.2.2 when user A posts data to the DB with commit. User B can not see the new data if the "Select" was not called after commit is called (in user B application). if user B selects this way:   SQLQuery1.Close;   SQLQuery1.SQL.Text:='Select * from Test';   SQLQuery1.Open; Changes done by user A will not be shown for user B, unless "Commit" is called somewhere in the application then the data is updated and when Select is called the new data is shown. So in order to show updated data I do this:   SQLQuery1.Close;   SQLQuery1.SQL.Text:='Select * from Test';   SQLTransaction1.CommitRetaining;   SQLQuery1.Open; ---------------------------------------------------------------------- taazz · 2014-05-02 14:30 Quote from: JZS on May 02, 2014, 03:38:02 pmSo in order to show updated data I do this:   SQLQuery1.Close;   SQLQuery1.SQL.Text:='Select * from Test';   SQLTransaction1.CommitRetaining;   SQLQuery1.Open; Well you are right in that you need to close and open the query to show the changed data. SQLtransaction1.commitretaining has nothing to do with the data retrieval process so it is not needed or at least it shouldn't be required. what is the contents of sqltransaction.sqlparams? ---------------------------------------------------------------------- eara · 2014-05-02 16:51 I dont get it...  QuoteUser B can not see the new data if the "Select" was not called after commit is called (in user B application)If user A don't commit his changes then what new data are you expect to read, the "dirty" (uncommited)  ? If user A commits, then where is the problem for b? You can do a refresh, or close and reopen the query. What are you expecting exactly, global notification from A that data are changed and autorefresh from B on that? ---------------------------------------------------------------------- JZS · 2014-05-02 20:13 Quote from: eara on May 02, 2014, 06:51:29 pmIf user A don't commit his changes then what new data are you expect to read, the "dirty" (uncommited)  ? No. No data to be seen if user A did not commit. We all agreed that in Laz1.2.2 no changes posting without commit. Quote from: eara on May 02, 2014, 06:51:29 pmIf user A commits, then where is the problem for b? You can do a refresh, or close and reopen the query. That's what am saying. Now Laz1.2.2 "Refresh" does not update the data for user B after user A has updated the data with commit. Attached sample to ease testing. Quote from: eara on May 02, 2014, 06:51:29 pmWhat are you expecting exactly, global notification from A that data are changed and autorefresh from B on that? Off course not, I was not talking about, or even near anything related to, global notification!!! I refer to that "refresh" does not get what physically exist in the DB unless commit is called before opening the query (at user B side). So in other words, commit now is required not only for apply changes (Insert, Update, Delete) but also for (Select) otherwise you keep getting the data of the very first time the query has retrieved. Close and reopen or refresh do not detect other users changes. Hope it's clear now. ---------------------------------------------------------------------- JZS · 2014-05-02 20:20 Quote from: taazz on May 02, 2014, 04:30:29 pmWell you are right in that you need to close and open the query to show the changed data. SQLtransaction1.commitretaining has nothing to do with the data retrieval process so it is not needed or at least it shouldn't be required. what is the contents of sqltransaction.sqlparams? Close & open or refresh does not show the changes in Laz1.2.2. But with "SQLtransaction1.commitretaining" then it shows the changes. No params used with the sqltran. ---------------------------------------------------------------------- eara · 2014-05-02 20:58 holy sht! you are RIGHT!  i reported it as http://bugs.freepascal.org/view.php?id=26104 and lets see what they will tell us... ---------------------------------------------------------------------- eara · 2014-05-02 22:47 i think i found a solution, based on http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html in your connection (assuming you name it "con") on AfterConnect event handler put ALSO this 2 lines Code: [Select]con.ExecuteDirect('SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED'); // or UNCOMMITED, or whatever you like con.ExecuteDirect('COMMIT'); // << this is necessary First line changes the MySQL's default ISOLATION LEVEL from REPEATABLE READ to READ COMMITED and the COMMIT in 2nd statement commits the current transaction (if there is one  ) from current session and a new transaction will start with the new ISOLATION LEVEL so refresh will work again !! After that you can forget the Commit after select (except if you like to play with ISOLATION LEVEL REPEATABLE READ) I tested this twice, and worked, but needs .... testing, testing and again ... testing... (i think) If this works then put a [SOLVED] in your first messag title and make it more accurate in order to help also others with the same problem... ---------------------------------------------------------------------- LacaK · 2014-05-03 16:50 Quote from: JZS on May 02, 2014, 10:20:02 pmClose & open or refresh does not show the changes in Laz1.2.2. But with "SQLtransaction1.commitretaining" then it shows the changes. As mentioned. It depends on level of "transaction isolation" In some isolation mode for transaction A are not visible changes made by other transactions (it does not depend if they was commited or not by other transactions) unless transaction A is finished. ---------------------------------------------------------------------- taazz · 2014-05-03 20:29 let me ask those that know how do you set the transaction isolation for mysql sqlparams should be the holder but what it holds? Any documentation or source code that we can look and find out all the supported parameters and their values? ---------------------------------------------------------------------- eara · 2014-05-03 21:34 if you find one them mail me... till then use your own commands which are safer (at least you know what you are doing)... ---------------------------------------------------------------------- JZS · 2014-05-04 05:45 Quote from: eara on May 03, 2014, 12:47:57 ami think i found a solution, based on http://dev.mysql.com/doc/refman/5.5/en/set- transaction.html in your connection (assuming you name it "con") on AfterConnect event handler put ALSO this 2 lines Code: [Select]con.ExecuteDirect('SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED'); // or UNCOMMITED, or whatever you like con.ExecuteDirect('COMMIT'); // << this is necessary It works. So the short answer is: In Laz1.2.2 it depends on level of "transaction isolation". Thank you LacaK I think adding a warning would help those who developed in versions prior 1.2.2, who will hit troubles if they were not aware of that, when migrating projects to 1.2.2 Thanks to you all, Taazz, eara, and LacaK. I noticed another thing in Laz1.2.2, previously I was not able to use DBGrid Column property "DisplayFormat" to format float fields using "#,##0.00" now it works flawlessly. ---------------------------------------------------------------------- JZS · 2014-05-06 06:37 Since support for transactions was added in Laz1.2.2, is it possible to set the ISOLATION LEVEL without execute? For instance, it's more convenient also to, enable specifying params for such options from the TMySQLxxConnection itself? ---------------------------------------------------------------------- LacaK · 2014-05-06 13:26 Quote from: JZS on May 06, 2014, 08:37:21 amSince support for transactions was added in Laz1.2.2, is it possible to set the ISOLATION LEVEL without execute? AFAIK it is not possible ATM ---------------------------------------------------------------------- eara · 2014-05-06 17:23 Quote from: JZS on May 06, 2014, 08:37:21 amSince support for transactions was http://forum.lazarus.freepascal.org/Smileys/Excellent Smileys1/angry2.gif added in Laz1.2.2, is it possible to set the ISOLATION LEVEL without execute? For instance, it's more convenient also to, enable specifying params for such options from the TMySQLxxConnection itself? take a look on this again http://dev.mysql.com/doc/refman/5.5/en/set- transaction.html. This is just a default behaviour of MySQL, other db's beahave differently. Mayby they will do it in future, mayby not. There are also other things that we need them also from db and can't get them (or i don't know how, e.g. how can you create STORED PROC from lazarus side and call the  SET TERM...) but, without execute?    what is wrong with execute? i love the "execute" (    ) You send to server a command, then is parsed and executed, and best of all YOU know what you send! What did you prefer, an implemantation (some time with its own bugs) version in settings (that has to change with every db connector version) of a server settings that does the same (under some conditions, but not under others), with insuffiecient documentation (due to time pressure) that is not logged etc etc, OR such a clean approach? In either cases you must send something to server, OR yes there is also another option: QuoteGo and "hijack" SQL server room and persuate the db admins to run the SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED, or what ever you like. (Just kidding man) Anyway there are cases that you may need more than one isolation level for you app and you will execute some commands like this. I don't like to hide everything behind OO approaches that get the freedom of choice from you! And think it again, if it wasn't this documentation (http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html) then we both we would sit and cry here expecting from someone else to solve our problem... ---------------------------------------------------------------------- JZS · 2014-05-07 06:24 Partially agree with you eara, nothing wrong with Execute. In fact, I love the "Execute" too  But I like execute at other places rather than this one If you have more than one connection, you have to set the session beforehand of each connection, otherwise you will end up setting the Global. But with params you got nothing to worry about, you set and connect instead of set-connect-execute. Here: http://wiki.lazarus.freepascal.org/User_Changes_2.6.4#TODBCConnection_ .28odbcconn.29_No_longer_autocommit Look at it from different angle, TODBCConnection can specify AutoCommit as Param, why not having more options included as params for TMySQLxxConnection, if in general, is possible. I am not complaining here, I am more than happy with things Lazarus offers and is capable of handling. Was just checking the possibility. Thank you Lacak and eara. You are great help. ---------------------------------------------------------------------- eara · 2014-05-07 10:55 QuoteIf you have more than one connection, you have to set the session beforehand of each connection, otherwise you will end up setting the Global. But with params you got nothing to worry about, you set and connect instead of set-connect-execute. I don't get it what do you mean... i am stucked with my case, and propably i am blind to other perspectives. Anyway there is no magic, in both cases there should be a direct execute for setting this or that.  And in both cases you can set it before open, but the actually set will hapen after connect (otherwise MySQL should have mystirious prediction abilities, that leads to the question "is MySQL an entity with telepathy?" etc etc). here Quotehttp://wiki.lazarus.freepascal.org/User_Changes_2.6.4#TODBCC onnection_.28odbcconn.29_No_longer_autocommit i see what i didn't knew before your post (thanks again for the post) that they didn't support transactions for MySQL and now they support (but they didn't cleared this out before. I just found a TSQLtransaction component and thought that i had transactions with it, but.... what the heck, now we have them and we use them). If you wish to avoid the execute then you can encapsulate it in a new TMyConection class and adapt it to your project (but who's got time for this now) ---------------------------------------------------------------------- JZS · 2014-05-07 11:47 Quote from: eara on May 07, 2014, 12:55:41 pmshould be a direct execute for setting this or that.  And in both cases you can set it before open, but the actually set will hapen after connect I know, it must happen after connect  , I only wanted to have TMySQLxxConnection ready to be setup before connect and once connected you got it ready to do what you set it up to do (no mysterious prediction no telepathy). Now you make me sound picky. It was all about ON-OFF options rather than using events. I confirm I got no issue with what can be done so far, with or without Execute. And I still confirm I like execute  Quote from: eara on May 07, 2014, 12:55:41 pm (thanks again for the post) Thanks for you (and your beloved Execute) for the solution  Quote from: eara on May 07, 2014, 12:55:41 pmbut who's got time for this now Lazarus team has  They have nothing to do, except making Lazarus better and better  I am happy with what Lazarus Team has provided for us, and what Lazarus can do so far, so no issues no complaints. ---------------------------------------------------------------------- eara · 2014-05-08 11:57 Sorry, no intention to do this... ---------------------------------------------------------------------- JZS · 2014-05-08 13:05 FINALLY something that can be an alternative: Code: [Select]uses mysql50; ... Conn.Connected := True; mysql_autocommit(Conn.Handle, 1); // 1= True, 0= False mysql_commit(Conn.Handle); Conn is TMySQL55Connection ---------------------------------------------------------------------- eara · 2014-05-08 15:15 mmm... calling underlying lib directly... This is also an approach, but it the negative i see with this approach is that it binds your app to the specific lib version (something that can be hidden in case you are using TSQLConnector). Anyway is also a solution if it does what you want to do. I still insist on my approach... (that means not that is the best approach, i don't want to blame me afterwards, but since we are talking about it...) If you are using TSQLConnector (instead of TMySQLXXConnection) you can wrap your calls (as i did) in AfterConnect Event handler. In case you don't use a single main connection component, but you create connections on  the fly you can still do this with something like the following mySQLConnector.AfterConnect:=@myEventHandlerMethod where myEventHandlerMethod is just a member in one of your classes (e.g. main_form's that usually lives as long as your app) which has a suitable signature to be used as event handler for the AfterConnect which means that you have declare like this: TfrmMain.myEventHandlerMethod(Sender: TObject); begin .... end; And what this appi does? Looks like it just set autocommit (for session i supose) to true and then commits the current transaction (in order to apply the setting for the new transactions). But then i think that you can't do rollback! Is this what you need?