MySQL 5.5 + TSQLQuery + FPC 2.6.4 problems
lazarus / 16-Databases

----------------------------------------------------------------------
MainMeat · 2015-05-19 10:20

Hi,

I have a simple thread that calls a function every second to check for
new records in the database. I wrote a small application to
demonstrate my problem which is included below.

When the thread (application) starts for the first time, it reads the
data from the database correctly.

The problem is that even when I manually delete data from the
database, the SQLQuery object continues to return the same data as
read when the application started - it is almost as if the changes are
not detected in the database, yet I can confirm there are no records
in the database.

The interesting part is that when I compile this in the latests FPC
(2.6.4) using the latest Lazarus IDE (1.4.0) available (SVN 48776),
this error is simulated.
However, using an older version dated 2013-08-24, FPC 2.6.2 and
Lazarus 1.0.12 (SVN 42479) I have no such problems and application and
thread works just fine and detects changes in the database every time
the query is executed.

It therefore appears that something has changed between the FPC
releases which I am not taking into account, and I would appreciate it
if someone can please tell me what I am doing wrong in the code?

Many thanks - the sample code is below:

Code: [Select]unit conn_test_thread;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, mysql55conn, sqldb;


type

{ TMysqlConnTest }

 TMysqlConnTest = class(TThread)
     public
           constructor Create();
     protected
           procedure Execute; override;
     private
           SQLConn : TMySQL55Connection;
           SQLTrans : TSQLTransaction;
           SQLQuery : TSQLQuery;

end;


implementation

{ TMysqlConnTest }

constructor TMysqlConnTest.Create;
begin
     SQLConn := TMySQL55Connection.Create(nil);
     SQLTrans := TSQLTransaction.Create(nil);
     SQLQuery := TSQLQuery.Create(nil);
     FreeOnTerminate := true;
     inherited Create(false);
end;

procedure TMysqlConnTest.Execute;
var nCntr : integer;
begin
     try
         SQLConn.HostName := '127.0.0.1';
         SQLConn.UserName := 'root';
         SQLConn.Password := '';
         SQLConn.Port := 3306;
         SQLConn.DatabaseName := 'test_db';

         SQLConn.Transaction := SQLTrans;

         SQLQuery.DataBase := SQLConn;

         SQLConn.Connected := true;

         repeat
               Writeln('Executing Query');
               SQLQuery.SQL.Text := 'Select first_name, last_name from
test_users';
               SQLQuery.Open;
               SQLQuery.Last;
               SQLQuery.First;

                Writeln('Found  : ', SQLQuery.RecordCount, '
records');
                for nCntr := 0 to SQLQuery.RecordCount -1 do
                begin
                     Writeln('Firstname : ',
SQLQuery.FieldByName('first_name').AsString + ' Lastname: ' +
SQLQuery.FieldByName('last_name').AsString);
                     SQLQuery.Next;
                end;
                SQLQuery.Close;
                Sleep(1000);
         until false;
     except
           on e : Exception do
           begin
                Writeln('ERROR : ', e.Message);
           end;
     end;
end;

end.

----------------------------------------------------------------------
LacaK · 2015-05-19 12:58

I guess it is related to added support for MySQL transactions. See: ht
tp://wiki.freepascal.org/User_Changes_2.6.4#MySQL_support_for_transact
ions_added

Do you use transactional engine like InnoDB ?
What is your transaction islolation level ?

Try put after "SQLQuery.Close;":
  SQLConn.Transaction.Commit;

----------------------------------------------------------------------
MainMeat · 2015-05-20 10:57

Hi LacaK,

Thanks for the response.

Interestingly, SQLConn.Transaction.Commit; was the magic line!