[SOLVED] TMySQL55Connection (AutoCommit & Refresh) now is different in Laz1.2.2

[JZS]

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]

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]

i think is related to http://forum.lazarus.freepascal.org/index.php/topic,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]

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]

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]

Using 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

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).

Yes in Lazarus 1.2.2 you must after ApplyUpdates also Commit changes

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?

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]

So now even select has to be called with Commit.
Can that be disabled?

Thanks Lacak, eara & Taazz

[taazz]

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]

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]

So 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]

I dont get it... 

User 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]

If 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.

If 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.

What 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]

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?

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]

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]

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...