Operation cannot be performed on an active transaction

[MainMeat]

Hi Guys and Gals,

Oh man I hope someone can help me as I am starting to pull out my hair here. I have a function that inserts data into a MySQL 5.0.77 database. The code in this function basically comes down to the following:

Code: [Select]

procedure StoreText(sMessage : string);
var  dbConn : TMySQL50Connection;
    sqlTrans : TSQLTransaction;
    sqlQuery : TSQLQuery;
begin
        dbConn := TMySQL50Connection.Create(nil);
        sqlQuery := TSQLQuery.Create(nil);
        sqlTrans := TSQLTransaction.Create(nil);

        dbConn.HostName := MySQL_Host;
        dbConn.UserName := MySQL_User;
        dbConn.Password := MySQL_Password;
        dbConn.Port := MySQL_Port;

        dbConn.DatabaseName := 'data_store';
        dbConn.Transaction := sqlTrans;
        sqlQuery.Database := dbConn;
        sqlQuery.Transaction := sqlTrans;
        dbConn.Connected := true;

        sqlQuery.SQL.Text := 'Insert into messages_received (date_time_stamp, message) values (''' +
                       FormatDateTime('yyyy-mm-dd hh:nn:ss', Date + Time) + ''',''' +  sMessage + ''')';
        sqlQuery.ExecSQL;

        dbConn.Connected := false;
        FreeAndNil(sqlQuery);
        FreeAndNil(sqlTrans);
        FreeAndNil(dbConn);
end;

When I call this procedure the first time from within a thread, everything works well. Then, when I execute this procedure a second time or at random times, for some reason I am hoping someone can explain, I get an exception "Operation cannot be performed on an active transaction" when I execute the last line in the function ("FreeAnNil(dbConn);")

I have searched the internet for this error, some users suggests that this is related to mysqlconn.inc (http://62.166.198.202/view.php?id=18841), but I just cannot get this function to execute properly without an error and the reason why this occurs is beyond me at the moment.

I am using Lazarus 0.9.30. with FPC 2.4.2 on Ubuntu.

Is this issue a known issue that has been resolved in later releases perhaps? Does anyone have any idea what this error means and what I have done wrong?

Many many thanks in advance.

[SteveF]

Hi Guys and Gals,

Oh man I hope someone can help me as I am starting to pull out my hair here. I have a function that inserts data into a MySQL 5.0.77 database. The code in this function basically comes down to the following:

Code: [Select]

procedure StoreText(sMessage : string);
var  dbConn : TMySQL50Connection;
    sqlTrans : TSQLTransaction;
    sqlQuery : TSQLQuery;
begin
        dbConn := TMySQL50Connection.Create(nil);
        sqlQuery := TSQLQuery.Create(nil);
        sqlTrans := TSQLTransaction.Create(nil);

        dbConn.HostName := MySQL_Host;
        dbConn.UserName := MySQL_User;
        dbConn.Password := MySQL_Password;
        dbConn.Port := MySQL_Port;

        dbConn.DatabaseName := 'data_store';
        dbConn.Transaction := sqlTrans;
        sqlQuery.Database := dbConn;
        sqlQuery.Transaction := sqlTrans;
        dbConn.Connected := true;

        sqlQuery.SQL.Text := 'Insert into messages_received (date_time_stamp, message) values (''' +
                       FormatDateTime('yyyy-mm-dd hh:nn:ss', Date + Time) + ''',''' +  sMessage + ''')';
        sqlQuery.ExecSQL;

        dbConn.Connected := false;
        FreeAndNil(sqlQuery);
        FreeAndNil(sqlTrans);
        FreeAndNil(dbConn);
end;

When I call this procedure the first time from within a thread, everything works well. Then, when I execute this procedure a second time or at random times, for some reason I am hoping someone can explain, I get an exception "Operation cannot be performed on an active transaction" when I execute the last line in the function ("FreeAnNil(dbConn);")

I have searched the internet for this error, some users suggests that this is related to mysqlconn.inc (http://62.166.198.202/view.php?id=18841), but I just cannot get this function to execute properly without an error and the reason why this occurs is beyond me at the moment.

I am using Lazarus 0.9.30. with FPC 2.4.2 on Ubuntu.

Is this issue a known issue that has been resolved in later releases perhaps? Does anyone have any idea what this error means and what I have done wrong?

Many many thanks in advance.

IIRC, you need to explicitly commit the changes using your sqlTrans component.  The ExecSQL command begins the transaction & sends what needs to happen back to the DB engine, but the transaction is still open until it's committed.

Steve

[Arbee]

Steve's right ... you need to code a:

Code: [Select]

sqltrans.commit;
after the insert and before you close and free everything.

[MainMeat]

Hi,

thanks Steve and Arbee for the replies. I have included the sqltrans.commit before I free the objects, but the same error still occurs. Any other ideas why this could be happening?

[BigChimp]

Just guessing but might it help to set the transaction .active property to true before the execsql statement?
See e.g. http://wiki.lazarus.freepascal.org/SQLdb_Tutorial1#Summary_2

Also, just checking: I suppose you hove used sqltrans.commit before the dbconn.connected:=false?

Could you show the code you now have?

[MainMeat]

HI BigChimp,

The Code now reads:

Code: [Select]

procedure StoreText(sMessage : string);
var  dbConn : TMySQL50Connection;
    sqlTrans : TSQLTransaction;
    sqlQuery : TSQLQuery;
begin
        dbConn := TMySQL50Connection.Create(nil);
        sqlQuery := TSQLQuery.Create(nil);
        sqlTrans := TSQLTransaction.Create(nil);

        dbConn.HostName := MySQL_Host;
        dbConn.UserName := MySQL_User;
        dbConn.Password := MySQL_Password;
        dbConn.Port := MySQL_Port;

        dbConn.DatabaseName := 'data_store';
        dbConn.Transaction := sqlTrans;
        sqlQuery.Database := dbConn;
        sqlQuery.Transaction := sqlTrans;

        dbConn.Connected := true;
        sqlTrans.Active := true;

        sqlQuery.SQL.Text := 'Insert into messages_received (date_time_stamp, message) values (''' +
                       FormatDateTime('yyyy-mm-dd hh:nn:ss', Date + Time) + ''',''' +  sMessage + ''')';
        sqlQuery.ExecSQL;
        sqlTrans.Commit;

        sqlTrans.Active := false;
        dbConn.Connected := false;

        FreeAndNil(sqlQuery);
        FreeAndNil(sqlTrans);
        FreeAndNil(dbConn); //Comment out this line of code and all is good with no errors
end;
Items to note:
1) Comment out the last line of code and all is good - no error but the memory allocated to the object is not released
2) When I call the function the first time in a thread (with the last line un-commented), the function executes fine, but then I call this function the second time, the exception "Operation cannot be performed on an active transaction" is present

Items going through my mind include:
- Could this be related to the MySQL Libraries and DLL's being used (I am currently running this application on Centos 5.5)
- Could this be related to the FPC libraries being used?

What puzzles me it is just a procedure call, and I cannot understand why it would execute fine the first time, but the second time present me with this error?

[BigChimp]

Ok, that looks good (to my limited knowledge)...
IIRC,

Code: [Select]

       
sqlTrans.Commit;
sqlTrans.Active := false;do the same, though...

I suppose you use FreeAndNil because you get errors otherwise? (I've read posts regarding this being necessary for MySQL...)
Maybe try to do dbconn.Free instead?
(Sorry, I'm too newb to really know the difference between Free and FreeAndNil - yes, FreeAndNil resets the pointer to the released object, but what does that mean?)

I'll adapt the code and run it on my MySQL install via Win32 FPC (current git/SVN), to see what results I get...

[BigChimp]

Ok, this:

Code: [Select]

program testmysql;
{
Adapted for MySQL 5.1.
Added database name
added some glue code to run the StoreText procedure
}
{$mode objfpc}{$H+}
{$APPTYPE CONSOLE}

uses
{$IFDEF UNIX} {$IFDEF UseCThreads}
  cthreads,
{$ENDIF} {$ENDIF}
  Classes,
  SysUtils,
  inifiles,
  sqldb, mysql51conn;

var
  MYSQL_Host: string;
  MYSQL_User: string;
  MYSQL_Password: string;
  MYSQL_Port: integer;
  MYSQL_DatabaseName: string;

procedure StoreText(sMessage : string);
var  dbConn : TMySQL51Connection;
    sqlTrans : TSQLTransaction;
    sqlQuery : TSQLQuery;
begin
        dbConn := TMySQL51Connection.Create(nil);
        sqlQuery := TSQLQuery.Create(nil);
        sqlTrans := TSQLTransaction.Create(nil);
     
        dbConn.HostName := MySQL_Host;
        dbConn.UserName := MySQL_User;
        dbConn.Password := MySQL_Password;       
        dbConn.Port := MySQL_Port;       
        dbConn.DatabaseName := MySQL_DatabaseName;

        dbConn.Transaction := sqlTrans;
        sqlQuery.Database := dbConn;
        sqlQuery.Transaction := sqlTrans;
        dbConn.Connected := true;

        sqlQuery.SQL.Text := 'Insert into messages_received (date_time_stamp, message) values (''' +
                       FormatDateTime('yyyy-mm-dd hh:nn:ss', Date + Time) + ''',''' +  sMessage + ''')';       
        sqlQuery.ExecSQL;

        dbConn.Connected := false;
        FreeAndNil(sqlQuery);
        FreeAndNil(sqlTrans);
        FreeAndNil(dbConn);
end;
 
var
  Message: string;
  IniFile : TIniFile; 
begin
  writeln('getting settings from ini file');
  IniFile := TIniFile.Create(getcurrentdir + PathDelim + 'database.ini');
  MYSQL_Host:=IniFile.ReadString('Database','Host','127.0.0.1');
  MYSQL_Port:=IniFile.ReadInteger('Database','Port',3306); 
  MYSQL_DatabaseName:=IniFile.ReadString('Database','DatabaseName','');
  MYSQL_User:=IniFile.ReadString('Database','User','root');
  MYSQL_Password:=IniFile.ReadString('Database','Password','');
  IniFile.Free;

  Message:='first time';
  writeln(Message);
  StoreText(Message);
  // Wait a bit so we get a new pk
  Sleep(1000);

  Message:='2nd time';
  writeln(Message);
  StoreText(Message);
  // Wait a bit so we get a new pk
  Sleep(1000);

  Message:='third time';
  writeln(Message);
  StoreText(Message); 
end.
(with database.ini created and adjusted to taste)

seems to work fine on my:
Win Vista client
MySQL 5.1.49-3 server on Linux
using libmysql.dll... who knows what version, but probably 5.1 something
and either
FPC version 2.5.1 [2011/09/04] for i386
or
FPC version 2.7.1 [2011/09/16] for i386

[IPguy]

somewhat off topic ...
re: wait timers - I'm concerned about those built in waits.

Is it possible to get an ack back from the DB once the DB has committed the changes?

JLR

[BigChimp]

re: wait timers - I'm concerned about those built in waits.

Mmm yes, but this only applies because the code only supplies second-level precision, no (tens of) millisecond-level...
It's example code 
(I'd use something like database-generated (sequence/generator) primary keys on e.g. Firebird for more serious stuff... or a trigger with timestamp insertion at the database end)

Is it possible to get an ack back from the DB once the DB has committed the changes?

That's a good point. Don't know.

Recently, there was a discussion on the forum/bugtracker on INSERT...RETURNING statements on some database (PostgreSQL, Firebird, ...) that can return a newly generated primary key to you.
That could serve as an acknowledgement mechanism.

<later edit>
Also, I'm really not interested in whether something has been written to disk in the db, I just want to know whether the transaction as a whole has been committed - ACID database theory states that once a transaction is committed, it's... how to put it... committed and visible to new transactions (or running transactions, depending on how you set up your transactions and the support your database offers).
(But maybe I misunderstood what you meant)

[BigChimp]

<bump>

Hi Mainmeat, did you manage to fix the issue?

[Buckwheat]

I had very similar problem: TSQLTransaction.Free worked most of the time fine, but sometimes it generated EDatabaseError exception with "Operation cannot be performed on an active transaction" message.

Lazarus version: 0.9.30.4
Lazarus svn revision: 35940
Lazarus build date: 2012/03/14
Lazarus was compiled for i386-win32
Lazarus was compiled with fpc 2.6.0

This is how I fixed this problem:

Add a new class:

Code: [Select]

TCustSQLTransaction = class(TSQLTransaction)
public
  procedure CloseTrans;
end;

procedure TCustSQLTransaction.CloseTrans;
begin
  inherited;
end;

Change

Code: [Select]

FTransaction: TSQLTransaction;

to

Code: [Select]

FTransaction: TCustSQLTransaction;

Change

Code: [Select]

FTransaction := TSQLTransaction.Create(nil);
to

Code: [Select]

FTransaction := TCustSQLTransaction.Create(nil);
Add

Code: [Select]

FTransaction.CloseTrans;
before

Code: [Select]

FTransaction.Free;
"Operation cannot be performed on an active transaction" problem happens because TSQLTransaction.Commit calls TSQLConnection.Commit. If TSQLConnection.Commit returns True, then TSQLTransaction.Commit calls TDBTransaction.CloseTrans which makes transaction inactive. TSQLConnection.Commit is abstract. So, TODBCConnection.Commit is called. But TODBCConnection.Commit is not implemented, it always returns false, so TSQLTransaction.Commit never calls TDBTransaction.CloseTrans !

Code: [Select]

procedure TSQLTransaction.Commit;
begin
  if active then
    begin
    closedatasets;
    If LogEvent(detCommit) then
      Log(detCommit,SCommitting);
    if TSQLConnection(Database).commit(FTrans) then
      begin
      closeTrans;
      FreeAndNil(FTrans);
      end;
    end;
end;

TSQLConnection = class (TDatabase)
  ...
  function Commit(trans : TSQLHandle) : boolean; virtual; abstract;
  ...
end;


function TODBCConnection.Commit(trans: TSQLHandle): boolean;
begin
  // Tranactions not implemented yet
end;

Procedure TDBTransaction.CloseTrans;
begin
  FActive := false;
end;