View Issue Details

IDProjectCategoryView StatusLast Update
0026505FPCDatabasepublic2015-10-19 00:31
ReporterMichl Assigned ToMichael Van Canneyt  
PrioritynormalSeverityminorReproducibilityalways
Status closedResolutionfixed 
PlatformWindowsOS7 
Product Version2.7.1 
Target Version3.0.0Fixed in Version3.1.1 
Summary0026505: SQLdb: Cleaning up after server is gone away
DescriptionAfter the exception "Server is gone away" it is not possible, to deconnect and reconnect the connection, cause allways a rollback is attempted.

The same approach is possible in Zeos.

The attached patch fixes this.
Steps To ReproduceSet the TimeOut in MySQL ver low (e.g. 2 seconds) and make after this two seconds something like:

try
  MySQL56Connection1.Connected:=False;
  MySQL56Connection1.Connected:=True;
except
  on e: exception do begin
    ShowMessage('TForm1.Button2Click.Fehler: '+e.Message);
    MySQL56Connection1.Connected:=False;
// or
    MySQL56Connection1.Free;
  end;
end;

Always a Rollback is tryed, there is no way to resolve the exception.
TagsNo tags attached.
Fixed in Revision30627
FPCOldBugId
FPCTarget
Attached Files

Activities

Michl

2014-07-19 10:21

reporter  

sqldb.pp.patch (712 bytes)   
Index: packages/fcl-db/src/sqldb/sqldb.pp
===================================================================
--- packages/fcl-db/src/sqldb/sqldb.pp	(revision 28239)
+++ packages/fcl-db/src/sqldb/sqldb.pp	(working copy)
@@ -1724,12 +1724,15 @@
     CloseDataSets;
     If LogEvent(detRollback) then
       Log(detRollback,SRollingBack);
-    if SQLConnection.RollBack(FTrans) then
-      begin
-      CloseTrans;
-      FreeAndNil(FTrans);
+    if not Assigned(FTrans) then
+      CloseTrans
+    else
+      if SQLConnection.RollBack(FTrans) then
+        begin
+          CloseTrans;
+          FreeAndNil(FTrans);
+        end;
       end;
-    end;
 end;
 
 procedure TSQLTransaction.RollbackRetaining;
sqldb.pp.patch (712 bytes)   

Michl

2014-07-21 22:58

reporter   ~0076311

Last edited: 2014-07-23 21:33

View 5 revisions

There is a discussion of this bug in the German Lazarusforum: http://www.lazarusforum.de/viewtopic.php?f=17&t=7953

If the connection is lost, a reconnect must be possible. The first patch was caRollback only. The new patch queries all actions (caCommit, caCommitRetaining, caRollback, caRollbackRetaining).

<strike>It is only queried whether FTrans nil = is. However, you could also prevent that FTrans = nil is by not allowing an OpenTrans in TSQLTransaction.StartTransaction.

I however don't know whether that's the better approach - alternative patch here!</strike>

Michl

2014-07-21 23:00

reporter  

sqldb_new.pp.patch (1,689 bytes)   
Index: packages/fcl-db/src/sqldb/sqldb.pp
===================================================================
--- packages/fcl-db/src/sqldb/sqldb.pp	(revision 28239)
+++ packages/fcl-db/src/sqldb/sqldb.pp	(working copy)
@@ -1699,12 +1699,15 @@
     CloseDataSets;
     If LogEvent(detCommit) then
       Log(detCommit,SCommitting);
-    if SQLConnection.Commit(FTrans) then
-      begin
-      CloseTrans;
-      FreeAndNil(FTrans);
+    if not Assigned(FTrans) then
+      CloseTrans
+    else
+      if SQLConnection.Commit(FTrans) then
+        begin
+          CloseTrans;
+          FreeAndNil(FTrans);
+        end;
       end;
-    end;
 end;
 
 procedure TSQLTransaction.CommitRetaining;
@@ -1713,7 +1716,8 @@
     begin
     If LogEvent(detCommit) then
       Log(detCommit,SCommitRetaining);
-    SQLConnection.CommitRetaining(FTrans);
+    if Assigned(FTrans) then
+      SQLConnection.CommitRetaining(FTrans);
     end;
 end;
 
@@ -1724,12 +1728,15 @@
     CloseDataSets;
     If LogEvent(detRollback) then
       Log(detRollback,SRollingBack);
-    if SQLConnection.RollBack(FTrans) then
-      begin
-      CloseTrans;
-      FreeAndNil(FTrans);
+    if not Assigned(FTrans) then
+      CloseTrans
+    else
+      if SQLConnection.RollBack(FTrans) then
+        begin
+          CloseTrans;
+          FreeAndNil(FTrans);
+        end;
       end;
-    end;
 end;
 
 procedure TSQLTransaction.RollbackRetaining;
@@ -1738,7 +1745,8 @@
     begin
     If LogEvent(detRollback) then
       Log(detRollback,SRollBackRetaining);
-    SQLConnection.RollBackRetaining(FTrans);
+    if Assigned(FTrans) then
+      SQLConnection.RollBackRetaining(FTrans);
     end;
 end;
 
sqldb_new.pp.patch (1,689 bytes)   

Michl

2014-07-21 23:00

reporter  

sqldb_alternativ.pp.patch (566 bytes)   
Index: packages/fcl-db/src/sqldb/sqldb.pp
===================================================================
--- packages/fcl-db/src/sqldb/sqldb.pp	(revision 28239)
+++ packages/fcl-db/src/sqldb/sqldb.pp	(working copy)
@@ -1759,7 +1759,8 @@
     Db.Open;
   if not assigned(FTrans) then FTrans := Db.AllocateTransactionHandle;
 
-  if Db.StartdbTransaction(FTrans,FParams.CommaText) then OpenTrans;
+  if (FTrans <> nil) and Db.StartdbTransaction(FTrans,FParams.CommaText) then
+    OpenTrans;
 end;
 
 constructor TSQLTransaction.Create(AOwner : TComponent);
sqldb_alternativ.pp.patch (566 bytes)   

Michl

2014-07-23 21:27

reporter   ~0076321

Last edited: 2014-07-23 21:34

View 3 revisions

It's me again ;)

After now several tests, I'm sure that my first 3 patchs can not lead to the desired success, because the action of the transaction in MySQL is always blocked (FTrans is always nil). See German Forum (a big thanks to mse).

Unfortunately I don't have the option, to delete the previous patchs, please ignore them (or the Administaror can remove them).

A solution seems to be inserting try except blocks. At run time (not in debug mode), it is possible to restore the connection after a breakup. The actions (rollback or commit) of the transaction are not longer prevented.

Please check out patch4, hopefully the last...

Michl

2014-07-23 21:28

reporter  

SQLdb_Patch4.patch (1,774 bytes)   
Index: packages/fcl-db/src/base/database.inc
===================================================================
--- packages/fcl-db/src/base/database.inc	(revision 28239)
+++ packages/fcl-db/src/base/database.inc	(working copy)
@@ -93,10 +93,11 @@
 
 begin
   If Assigned(FTransactions) then
-    begin
     For I:=FTransactions.Count-1 downto 0 do
-      TDBTransaction(FTransactions[i]).EndTransaction;
-    end;
+      try
+        TDBTransaction(FTransactions[i]).EndTransaction;
+      except
+      end;
 end;
 
 procedure TDatabase.RemoveDataSets;
Index: packages/fcl-db/src/sqldb/sqldb.pp
===================================================================
--- packages/fcl-db/src/sqldb/sqldb.pp	(revision 28239)
+++ packages/fcl-db/src/sqldb/sqldb.pp	(working copy)
@@ -1696,13 +1696,14 @@
 begin
   if Active then
     begin
-    CloseDataSets;
-    If LogEvent(detCommit) then
-      Log(detCommit,SCommitting);
-    if SQLConnection.Commit(FTrans) then
-      begin
-      CloseTrans;
-      FreeAndNil(FTrans);
+      CloseDataSets;
+      If LogEvent(detCommit) then
+        Log(detCommit, SCommitting);
+      try
+        SQLConnection.Commit(FTrans)
+      finally
+        CloseTrans;
+        FreeAndNil(FTrans);
       end;
     end;
 end;
@@ -1721,13 +1722,14 @@
 begin
   if Active then
     begin
-    CloseDataSets;
-    If LogEvent(detRollback) then
-      Log(detRollback,SRollingBack);
-    if SQLConnection.RollBack(FTrans) then
-      begin
-      CloseTrans;
-      FreeAndNil(FTrans);
+      CloseDataSets;
+      If LogEvent(detRollback) then
+        Log(detRollback, SRollingBack);
+      try
+        SQLConnection.RollBack(FTrans);
+      finally
+        CloseTrans;
+        FreeAndNil(FTrans);
       end;
     end;
 end;
SQLdb_Patch4.patch (1,774 bytes)   

Michael Van Canneyt

2015-04-17 21:30

administrator   ~0083022

Last edited: 2015-04-17 21:31

View 2 revisions

This must be solved differently. You cannot just ignore errors like that globally.

This was solved differently in the past for MySQL, but was not implemented globally. I have now attempted to do this.

When an unexpected error such as 'server went away' is detected, you must call

Connection.Close(true);

This tells SQLDB that it must force all connections to close, ignoring any low-level driver errors. It will then try to clean up everything 'nicely'.

The difference with a normal disconnect is that a flag is set, which helps SQLDB to decide what must be done about errors.

Michl

2015-10-19 00:31

reporter   ~0086688

Thank you!

Issue History

Date Modified Username Field Change
2014-07-19 10:21 Michl New Issue
2014-07-19 10:21 Michl File Added: sqldb.pp.patch
2014-07-21 22:58 Michl Note Added: 0076311
2014-07-21 23:00 Michl File Added: sqldb_new.pp.patch
2014-07-21 23:00 Michl File Added: sqldb_alternativ.pp.patch
2014-07-22 21:22 Michl Note Edited: 0076311 View Revisions
2014-07-22 21:23 Michl Note Edited: 0076311 View Revisions
2014-07-22 21:28 Michl Note Edited: 0076311 View Revisions
2014-07-23 21:27 Michl Note Added: 0076321
2014-07-23 21:28 Michl File Added: SQLdb_Patch4.patch
2014-07-23 21:32 Michl Note Edited: 0076321 View Revisions
2014-07-23 21:33 Michl Note Edited: 0076311 View Revisions
2014-07-23 21:34 Michl Note Edited: 0076321 View Revisions
2015-04-05 11:10 Michael Van Canneyt Assigned To => Michael Van Canneyt
2015-04-05 11:10 Michael Van Canneyt Status new => assigned
2015-04-17 21:30 Michael Van Canneyt Fixed in Revision => 30627
2015-04-17 21:30 Michael Van Canneyt Note Added: 0083022
2015-04-17 21:30 Michael Van Canneyt Status assigned => resolved
2015-04-17 21:30 Michael Van Canneyt Fixed in Version => 3.1.1
2015-04-17 21:30 Michael Van Canneyt Resolution open => fixed
2015-04-17 21:30 Michael Van Canneyt Target Version => 3.0.0
2015-04-17 21:31 Michael Van Canneyt Note Edited: 0083022 View Revisions
2015-10-19 00:31 Michl Note Added: 0086688
2015-10-19 00:31 Michl Status resolved => closed