Lazarus1.2.2 and MySQL5.5 dont work lazarus / 16-Databases ---------------------------------------------------------------------- hubblec4 · 2014-05-18 18:30 Hi I have coded a tool(with Lazarus 1.2 FPC 2.6.2) which use the MySQL database (5.5). After the update to Lazarus 1.2.2 with FPC 2.6.4 the tool dont work anymore. When i try to make a new entry in the database: 1. dont work 2. the "auto increment" will be increased but nothing else 3. there is not a new entry in the table I have made a new tool with Lazarus 1.2.2 and im very confuesd: at the start i create a new MySQL User and generate a new database. the same procedure (other params) dont work after login into my tool Code: [Select]with Main.Frm_Main.SQLQuery do   begin    // der therapeut    Close;    SQL.Text:='INSERT INTO user (admin, status, rang, benutzer, pass, '     +'anrede, nachname, vorname, adresse, plz, ort, festnetz, handy'     +', email, anmerkung, farbe)'     +'VALUES ('+#39+'0'+#39+', '     +#39+'0'+#39+', '     +#39+'1'+#39+', '     +#39+Utf8ToAnsi(benutzer)+#39+', '     +#39+Utf8ToAnsi(pass1)+#39+', '     +#39+anrede+#39+', '     +#39+Utf8ToAnsi(n_name)+#39+', '     +#39+Utf8ToAnsi(v_name)+#39+', '     +#39+adresse+#39+', '     +#39+plz+#39+', '     +#39+ort+#39+', '     +#39+telefon+#39+', '     +#39+handy+#39+', '     +#39+email+#39+', '     +#39+anmerkung+#39+', '     +#39+farbe+#39+');';    ExecSQL;   end; so i have downgrade to Lazarus 1.2 with fpc 2.6.2 and every works well with my two tools. A big thanks to the Lazarus Team!! hubble sorry for my english ---------------------------------------------------------------------- mirce.vladimirov · 2014-05-18 19:30 is there any Error reported ? ---------------------------------------------------------------------- hubblec4 · 2014-05-18 20:49 Quote from: mirce.vladimirov on May 18, 2014, 09:30:13 pmis there any Error reported ? no, nothing. the SQL code works good no error message. the only thing what you get is that "auto increment" increase (watch with phpmyadmin on the browser). but there is no new entry ---------------------------------------------------------------------- eara · 2014-05-18 21:23 well, there is something new in 1.2.2 and this is guess what....   Transactions check the story of another post here ... http://forum.lazarus.freepascal.org/index.php/topic,24443.0.html So, if you don't commit the transaction before closing the connection then you will have a Rollback by closing the connection, and all your updates are vanished ... Check if this is the issue... Prior version 1.2, had no implementation for transactions (on MySQL) and each statement was auto-commited by server (autocommit setting in MySQL server). Now in 1.2.2 when you open a connection you are under a transaction (new behaviour, so autocommit is no longer valid i think), and in order to save your data you must ALSO issue a commit to the underlying transaction. Also notice that the default (MySQL server) transaction isolation is REPEATABLE READ (if you read the post, or the MySQL manual you will find more on this). If you don't like it (in a multiuser case) then you can also change it to READ COMMITED ... ---------------------------------------------------------------------- hubblec4 · 2014-05-18 22:40 many thanks for the very fast helping hand. i have read the post and give it a try. then i have tested with settings in the Transaction.Action: set to "caCommitRetaining" and "caCommit" but no positiv effects. in the code itself seams to work. Code: [Select]SQLQuery.Close; SQLQuery.SQL.Text:= "SQL-Code"; SQLQuery.ExecSQL; SQLTransaction.CommitRetaining; is there anything more that i have to check? ---------------------------------------------------------------------- eara · 2014-05-19 01:32 sorry, i can't follow exactly what you are saying...  you will try, you tried but no positive effects, in the code works....  If you called Commit or CommitRetaining and had Positive effects then i think this was your problem.  Otherwise... need more info...  Now if you didn't use before commitRetating (or Commit) in you app then you have a LOT  of work to write them in the proper places (  brrrrr....) Alternative, (in case that the problem was the lack of commit) you may also try to to re-enable the autocommit for MySQL session (as it was in 1.2, right after connecting to db, e.g. in AfterConnect event handler) as JZS says in the last post in topic i sended you before... http://forum.lazarus.freepascal.org/index.php/topic,24443.msg147611.ht ml#msg147611 or use something like this Code: [Select]con.ExecuteDirect('SET autocommit=1'); con.ExecuteDirect('COMMIT'); where commit is necessary to close the last transaction (that is opened by default  in lazarus 1.2.2 when you connect) and continue (if the trick works) with autocommit (as in lazarus 1.2). This may save you from altering you project....or it may not... Quoteis there anything more that i have to check?many things like... have you tried to run 2 instances of you application and try to see if changes from one instance are visible to other ? (if no, then it has to do with the and isolation level, look again the previous topic...) ---------------------------------------------------------------------- bambamns · 2014-05-19 03:43 AFAIK , the problem is solved in SVN 27738 FPC 2.7.1. You can try to rebuild Lazarus 1.2.2 with FPC 2.6.2 (as I did) or rebuild Lazarus 1.2.2 with latest FPC SVN 2.7.1. ---------------------------------------------------------------------- hubblec4 · 2014-05-20 07:52 Quote from: eara on May 19, 2014, 03:32:11 amsorry, i can't follow exactly what you are saying...  you will try, you tried but no positive effects, in the code works....  i have set the "caCommit" property in the transaction object, but only when i write the "commit" in the code then it works. Quote from: eara on May 19, 2014, 03:32:11 amIf you called Commit or CommitRetaining and had Positive effects then i think this was your problem.  Otherwise... need more info...  i think the problem is solved. Quote from: eara on May 19, 2014, 03:32:11 amNow if you didn't use before commitRetating (or Commit) in you app then you have a LOT  of work to write them in the proper places (  brrrrr....) mmh that could be a little bit of work. Quote from: eara on May 19, 2014, 03:32:11 amAlternative, (in case that the problem was the lack of commit) you may also try to to re- enable the autocommit for MySQL session (as it was in 1.2, right after connecting to db, e.g. in AfterConnect event handler) as JZS says in the last post in topic i sended you before... http://forum.lazarus.freepascal.org/index.php/topic,24443.msg147611.ht ml#msg147611 or use something like this Code: [Select]con.ExecuteDirect('SET autocommit=1'); con.ExecuteDirect('COMMIT'); where commit is necessary to close the last transaction (that is opened by default  in lazarus 1.2.2 when you connect) and continue (if the trick works) with autocommit (as in lazarus 1.2). This may save you from altering you project....or it may not... ok that is new for me. i will have a look. Quote from: eara on May 19, 2014, 03:32:11 amQuoteis there anything more that i have to check?many things like... have you tried to run 2 instances of you application and try to see if changes from one instance are visible to other ? (if no, then it has to do with the and isolation level, look again the previous topic...) oh yes that is very important i will test it. ---------------------------------------------------------------------- eara · 2014-05-20 18:35 Quotei have set the "caCommit" property in the transaction object, but only when i write the "commit" in the code then it works. , i didn't use it till now the caCommit in my project so i don't know what is supposed to do... and i was very suprised dealing the 2 issues (transaction support in 1.2.2 + isolation level) so needs to be more carefull with testing... QuoteAFAIK , the problem is solved in SVN 27738 FPC 2.7.1. You can try to rebuild Lazarus 1.2.2 with FPC 2.6.2 (as I did) or rebuild Lazarus 1.2.2 with latest FPC SVN 2.7.1.WHAT IS THE PROBLEM that is solved is not mentioned... (an not identified problem is solved in an future release X, this is not helping folks) but i suspect again modifications on transaction handling... So before adapting anything new to your app, better try to make some isolated tests first, in order to check and understand the new behaviour, cause as it seems in every new release we will have 2 deal with surprises like this... ---------------------------------------------------------------------- mangakissa · 2014-05-21 07:48 This link tells property action is not working yet ---------------------------------------------------------------------- hubblec4 · 2014-05-21 08:23 Quote from: mangakissa on May 21, 2014, 09:48:17 amThis link tells property action is not working yet ok thanks for this info. with "commitRetaining" in the source-code it works. ---------------------------------------------------------------------- LacaK · 2014-05-21 09:10 Quote from: mangakissa on May 21, 2014, 09:48:17 amThis link tells property action is not working yet Documentation is out-dated. It is no more true. ---------------------------------------------------------------------- mangakissa · 2014-05-21 14:31 LacaK, If the problem doesn't exist anymore in FPC 2.7.1, how do you tell users the property is not functional in lower version. I'm not a person who download and install everything from trunk. I can't tell that to my users, who wants to have the source code of the application. Someone needs to update that page (Yes I know, volunteers are rare). ---------------------------------------------------------------------- taazz · 2014-05-21 17:52 Quote from: LacaK on May 21, 2014, 11:10:32 amQuote from: mangakissa on May 21, 2014, 09:48:17 amThis link tells property action is not working yet Documentation is out-dated. It is no more true. OK I got a quick look around on 2.6.2 and it is unused as the page says any one can confirm that on 2.6.4 too? If you mean that it  has been corrected on 2.7.1 then I'm sorry that is not a released version doesn't count and it shouldn't be mentioned at all when it comes to documentation. ---------------------------------------------------------------------- LacaK · 2014-05-22 05:13 It is fixed in FPC 2.6.4 and 2.7.1 (but not  in 2.6.2 and down)