Hi,

I use TMySQL55Connection, TSQLTransaction and TSQLQuery. I always get Transaction is NOT active.

here is the code:

procedure TForm1.Button1Click(Sender: TObject);
var
  db : TMySQL55Connection;
  q  : TSQLQuery;
  tr : TSQLTransaction;
begin
  db := TMySQL55Connection.Create(nil);
  q  := TSQLQuery.Create(nil);
  tr := TSQLTransaction.Create(nil);
  try try
    db.DatabaseName := 'test';
    db.UserName     := 'root';
    db.Password     := 'passwd';
    db.Open;
    q.DataBase    := db;
    tr.DataBase   := db;
    q.Transaction := tr;
    tr.StartTransaction;
    try
      if tr.Active then
        Writeln('Transaction is active')
      else
        Writeln('Transaction is NOT active')
    finally
      tr.Rollback
    end
  except
    on E: Exception do
      Writeln(E.Message)
  end
  finally
    q.Close;
    FreeAndNil(q);
    FreeAndNil(tr);
    FreeAndNil(db)
  end
end;

or http://pastebin.com/qmCMfKMh

Ubuntu 13.10, mysql 5.5, Lazarus 1.0.10, FreePascal 2.6.2-5. The test table is in InnoDB:

CREATE TABLE test_table (
id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
myfield varchar(100) DEFAULT ''
) ENGINE='InnoDB' COLLATE 'utf8_bin';

Could you help me, please? Thanks a lot.

Petr

2014-03-14 8:20 GMT-03:00 Petr Hlozek :

Are you declared the mysql55conn unit on uses clause?

Yes, I did. It's in uses like mysql55conn.

Petr

2014-03-14 15:34 GMT+01:00 silvioprog :

Here you ask for table "test".

CREATE TABLE test_table (

And here you create a table named "test_table".

Change the table name on creation or ask for the correct name could be it.

id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY, myfield varchar(100) DEFAULT '' ) ENGINE='InnoDB' COLLATE 'utf8_bin';

It's the name of database. My testing database is called "test". I posted the table create command to infom about db engine I use for it.

Petr

2014-03-14 17:25 GMT+01:00 Marc Santhoff :

Petr Hlozek wrote

It's the name of database. My testing database is called "test". I posted the table create command to infom about db engine I use for it.

Oops. ;)

Maybe the TSQLQuery instance hinders the transaction from goping active, because it's SQL is empty or similar.

Marc

2014-03-14 11:55 GMT-03:00 Petr Hlozek :

Yes, I did. It's in uses like mysql55conn. Petr

Hm... Try to set a SQL in your query, after, use the q.Open (the starttransaction will automaticaly triggered) method, checking if transaction keeps inactive.

justa a tip: take a little time to see this nice and slim project: https://github.com/silvioprog/dopf.. ;)

It won't start even when I execute a query. After q.open the transaction is still not active. I think, it should start after StartTransaction is called, not after first query.

Petr

2014-03-14 19:39 GMT+01:00 Marc Santhoff :

It's inactive even after I execute a query. If AUTO COMMIT is set to 0 ,the changes are not saved to database. Very bad.

Yes, I could use other DB components, but my app already has over 60k lines of code, several threads connected to DB etc. It won't be easy to move to something else :(.

Petr

2014-03-14 19:46 GMT+01:00 silvioprog :

2014-03-15 3:06 GMT-03:00 Petr Hlozek :

It's inactive even after I execute a query. If AUTO COMMIT is set to 0 ,the changes are not saved to database. Very bad.

Very very bat.

Yes, I could use other DB components, but my app already has over 60k lines of code, several threads connected to DB etc. It won't be easy to move to something else :(.

Can you test it in latest FPC 2.6.4 (using a virtual machine, sure)?

I wanted to find out where is the problem and started with breakpoint on db := TMySQL55Connection.Create. It went fine until it reached db.Open line. After I hit F8 on this line, it hangs. Next time it hangs on tr.StartTransaction with this error:

The GDB command:
"ptype tr.StartTransaction"
did not return any result.

Very sad :(.

It seems I have to try it in virtual machine with latest FPC and Lazarus. It will be interesting but I can't use it. It won't be part of Ubuntu 12.04, it's not in Debian, yet :(.

Petr

2014-03-15 7:13 GMT+01:00 silvioprog :

So this looks like your tarnsaction cannot connect to the database burt you're getting no error message but a hang.

You could
- check that anything inbetween your program and the database is actually working. Using another client program may help to narrow down the target
- turn on logging for mysql temporarily (the log fills up fast) and check if mysql sees the connection attempt
- instead of stepping over the first erroneous line step into and look what happens in detail

HTH,
Marc

Unfortunately this problem is only when I try to run the app in Lazarus and put breakpoit somewhere. When I run it directly in terminal, it always will connect to database, I can run queries, get results but transaction is still not active like it was in the example.

2014-03-15 13:14 GMT+01:00 Marc Santhoff :

Your problem will be solved in 2.6.4 or in trunk.

I tested your code with 3 versions of fpc:

home: > ppcx64-2.6.2 tdb
home: >./tdb
Transaction is NOT active
home: >ppcx64-2.6.4 tdb
home: >./tdb
Transaction is active
home: >fpc tdb
home: >./tdb
Transaction is active

So whatever bug you encountered, it has been fixed meanwhile in FPC.

So as soon as Lazarus releases a version based on 2.6.4, your program will work. I suspect it is a MySQL specific bug.
(IIRC there were some issues with transactions in MySQL)

Michael.

Thank you Michael!

It there any way how to find out where was the problem? I'd like to backport the fix to the version I use.

Petr

2014-03-16 12:36 GMT+01:00 Michael Van Canneyt :

2.6.2 didn't have transaction support for MySQL:

1122	function TConnectionName.StartdbTransaction(trans: TSQLHandle;
AParams : string): boolean;
1123	begin
1124	  // Do nothing
1125	end;

See
http://svn.freepascal.org/cgi-bin/viewvc.cgi/tags/release_2_6_2/packages/fcl-db/src/sqldb/mysql/mysqlconn.inc?revision=23532&view=markup

and

http://svn.freepascal.org/cgi-bin/viewvc.cgi/tags/release_2_6_4/packages/fcl-db/src/sqldb/mysql/mysqlconn.inc?revision=26871&view=markup

Ludo

Or he can download fpc-2.6.4 and replace 2.6.2 with 2.6.4.

zeljko