FireDAC Phys FB Unable to find savepoint with the name SP-17 in transaction context error?

[Chris LeFebvre]

I'm currently using RAD Studio 11 / Delphi and have recently migrated all my databases from Firebird 3.x to 4.0 and up until now every program that I have migrated has worked without any problem at all. Today I'm in the process of working on another and so far the only things I've really had to update in any program is to copy the latest x64 fbclient.dll to the Win64\Debug program directory and to edit the server password (which is different from the password I used on 3.x) and everything worked just fine. But for some reason in this particular program when I execute a procedure that inserts some 20-25 records into a database and then I close the dataset and then close the TFDConnection to the server on the line of code where the connection is closed I'm getting this error message that I've never seen "FireDAC Phys FB Unable to find savepoint with the name SP-17 in transaction context". I searched this group and didn't find anyone else getting this error nor did Google find any references, anyone have an idea what the issue here is?

[Vlad Khorsun]

On Sunday, 31 October 2021 at 22:55:09 UTC+2 Chris LeFebvre wrote:

I'm currently using RAD Studio 11 / Delphi and have recently migrated all my databases from Firebird 3.x to 4.0 and up until now every program that I have migrated has worked without any problem at all. Today I'm in the process of working on another and so far the only things I've really had to update in any program is to copy the latest x64 fbclient.dll to the Win64\Debug program directory and to edit the server password (which is different from the password I used on 3.x) and everything worked just fine. But for some reason in this particular program when I execute a procedure that inserts some 20-25 records into a database and then I close the dataset and then close the TFDConnection to the server on the line of code where the connection is closed I'm getting this error message that I've never seen "FireDAC Phys FB Unable to find savepoint with the name SP-17 in transaction context". I searched this group and didn't find anyone else getting this error nor did Google find any references, anyone have an idea what the issue here is?

  Error isc_invalid_savepoint ('Unable to find savepoint with name @1 in transaction context') is rased when Firebird

handle RELEASE SAVEPOINT or ROLLBACK TO SAVEPOINT statement and can't find the name of a savepoint given.

Looks like application works with explicit savepoints and something goes wrong. Reproducible example could help

to investigate a reason.

Regards,

Vlad

[Chris LeFebvre]

First keep in mind that this code has been working on Firebird 3.x with FireDAC since I migrated from the previous set of components to FireDAC in Delphi XE4; what I'm not sure of is if this is a problem with the FireDAC on RAD Studio Delphi 11 or a problem with Firebird 4.0. I'm now getting this error message in several programs and in exactly the same place:

Var

  dbData: TFDConnection;

  trData: TFDTransaction; { The default transaction for dbData  }

  FDPhysFBDriverLink1: TFDPhysFBDriverLink;

  dsDataSet: TFDQuery;   

  trDataset: TFDTransaction;

  updDataSet: TFDUpdateSQL;

In the most recent instance of this problem appearing I have dsDataSet open and the dataset contains a number of records and I go to delete a record:

  If Not trDataSet.Active Then trDataset.StartTransaction;

  dsDataSet.Delete;

  dsDataSet.ApplyUpdates;

  dsDataSet.ComitUpdates;

 

These execute with no problem but when I close the dataset:

  dsDataSet.Close;

That is when the error "FireDAC Phys FB Unable to find savepoint with the name SP-17 in transaction context" is generated. Now just to settle the question of is this a RAD Studio 11 / FireDAC or Firebird 4.0 problem I went back to RAD Studio 10.4.2 and I had the same error with that version of FireDAC so this appears to be some problem between FireDAC and Firebird 4.0.

[Dimitry Sibiryakov]

Chris LeFebvre wrote 07.11.2021 18:30:
> so this appears to be some problem between FireDAC and Firebird 4.0.

Firebird 4.0 has "Trace and Audit" service that allow you to track every
query that FireDAC sends to it. If you can create a test case reproducible with
ISQL based on this trace log - open an issue on github. My attempt failed,
everything mess I tried with savepoints worked without such error.

--
WBR, SD.

[Vlad Khorsun]

On Sunday, 7 November 2021 at 19:30:46 UTC+2 Chris LeFebvre wrote:

First keep in mind that this code has been working on Firebird 3.x with FireDAC since I migrated from the previous set of components to FireDAC in Delphi XE4; what I'm not sure of is if this is a problem with the FireDAC on RAD Studio Delphi 11 or a problem with Firebird 4.0.

...

this appears to be some problem between FireDAC and Firebird 4.0.

  Sure. But to investigate and fix it, we need to something reproducible to run.

You may try to run trace as SD suggests, or create simplest application that reproduces the issue.

Regards,

Vlad

[Chris LeFebvre]

First my preference here would be to just go back to Firebird 3 and be done with this problem, but apparently the version 4 backup files can't be restored using the version 3 gbak?

So the documentation on fbtracemgr is kind of skimpy and I wasn't able to get it to work mainly because in this example " fbtracemgr -SE service_mgr -START -NAME my_trace -CONFIG my_cfg.txt" it doesn't describe what the configuration of the "-CONFIG" file is supposed to be?

As far as I can tell to reproduce it all you need is Delphi 10.4.2 or 11 and create an application that connects to a database, open a dataset, start a transaction, insert a record, update the fields, applyupdates, commitupdates, close the dataset and that's when the error happens.

I have no experience with isql, I've always managed my databases with Database Workbench and added deleted / updated records using Delphi programs.

[Dimitry Sibiryakov]

Chris LeFebvre wrote 09.11.2021 23:29:
> So the documentation on fbtracemgr is kind of skimpy and I wasn't able to get it
> to work mainly because in this example " fbtracemgr -SE service_mgr -START -NAME
> my_trace -CONFIG my_cfg.txt" it doesn't describe what the configuration of the
> "-CONFIG" file is supposed to be?

Examples of these configurations are in doc/README.trace_services.txt file.

--
WBR, SD.

[Chris LeFebvre]

I read the README.trace_services.txt file and I created a fbtrace.conf file using the C example Trace connections and transactions in all databases except security database:

<database>
    enabled            true
    log_connections        true
    log_transactions    true
</database>

<database security2.fdb>
    enabled            false
</database>

However when I tried "fbtracemgr -SE service_mgr -START -NAME L:\Firebird\Tracelog.txt -CONFIG L:\Firebird\fbtrace.conf" it generated an error "Trace session ID 1 started Error creating trace session for service manager attachment: error while parsing trace configuration line 1: wrong section header, "database" or "service" is expected" and the config is straight out of the file. So what's the problem with this?

[AlexPeshkoff]

> First my preference here would be to just go back to Firebird 3 and be done with this problem, but apparently the version 4 backup files can't be restored using the version 3 gbak?

You should start FB4 server and connect to it using FB3 gbak (do not use -SE switch!!!). The result is backup in FB3 format.

среда, 10 ноября 2021 г. в 01:29:57 UTC+3, Chris LeFebvre:

[Dimitry Sibiryakov]

Chris LeFebvre wrote 10.11.2021 0:50:
> So what's the problem with this?

Most likely UTF-8 BOM at the beginning of the file. And you need to monitor
statements, not connections or transactions.

--
WBR, SD.

[Vlad Khorsun]

On Wednesday, 10 November 2021 at 00:29:57 UTC+2 Chris LeFebvre wrote:

So the documentation on fbtracemgr is kind of skimpy and I wasn't able to get it to work mainly because in this example " fbtracemgr -SE service_mgr -START -NAME my_trace -CONFIG my_cfg.txt" it doesn't describe what the configuration of the "-CONFIG" file is supposed to be?

Read fbtrace.conf at Firebird folder. It have a bit different syntax that examples in README.trace_services.

The file is self-documented and it should be clear how to customize it. Note, it is mentioned in README.trace_services:

> Example configuration file fbtrace.conf is placed in the Firebird root directory and contains full description of its format, rules and syntax.

 

As far as I can tell to reproduce it all you need is Delphi 10.4.2 or 11 and create an application that connects to a database, open a dataset, start a transaction, insert a record, update the fields, applyupdates, commitupdates, close the dataset and that's when the error happens.

Don't expect that everyone have and use Delphi. Instead, you could create simplest app that reproduce the issue and provide it to us.

Or you may investigate the issue debugging your app using sources of FireDAC.

Regards,

Vlad

[Vlad Khorsun]

On Wednesday, 10 November 2021 at 01:50:15 UTC+2 Chris LeFebvre wrote:

I read the README.trace_services.txt file and I created a fbtrace.conf file using the C example Trace connections and transactions in all databases except security database:

<database>
    enabled            true
    log_connections        true
    log_transactions    true
</database>

<database security2.fdb>
    enabled            false
</database>

However when I tried "fbtracemgr -SE service_mgr -START -NAME L:\Firebird\Tracelog.txt -CONFIG L:\Firebird\fbtrace.conf" it generated an error "Trace session ID 1 started Error creating trace session for service manager attachment: error while parsing trace configuration line 1: wrong section header, "database" or "service" is expected" and the config is straight out of the file. So what's the problem with this?

As I said - you should look at actual for your Firebird version syntax for trace configuration.

In your case correct syntax is:

database

{

    enabled            true
    log_connections        true
    log_transactions    true
}

database security.db

{
    enabled            false
}

Note, there is no security2.fdb at Firebird 4.

Hope it helps,

Vlad

[Pavel Katalymov]

Hi Cris,

I have the same error: Unable to find savepoint with name SP_0000000007 in transaction context

Project on Delphi 11 with IBObjects working with Firebird 4. The same Project works fine with Firebird 3 and 2.5.  Transactions in SNAPSHOT isolation mode.

Most likely the reason for the error is using RELEASE SAVEPOINT without ONLY.

In my case it was

• Savepoint N
• Savepoint N+1
• Release Savepoint N+1
• Release Savepoint N - this failed

From FB doc: https://firebirdsql.org/refdocs/langrefupd21-release-savepoint.html

RELEASE SAVEPOINT name [ONLY]

Unless ONLY is added, all the savepoints created after the named savepoint are released as well.

This is FireDAC.Phys.IBMeta.pas code which may you may consider to fix

function TFDPhysIBCommandGenerator.GetCommitSavepoint(const AName: String): String;
begin
  Result := 'RELEASE SAVEPOINT ' + AName;
end;

To fix the problem in our project I added ONLY to RELEASE SAVEPOINT and fixed the issue:

• Savepoint N
  
• Savepoint N+1
• Release Savepoint N+1 ONLY
  
• Release Savepoint N ONLY

You may consider to use altered FireDAC.Phys.IBMeta.pas or submit a ticket to Embarcadero.

  I prepared Firebird 4 trace to do so:

1. in firebird.conf file add/set:

AuditTraceConfigFile = fbtrace.conf

2. In btrace.conf added these (I use regular expression for my convenience):

database = (%[\\/]testdbfilename).fdb
{
    enabled = true
    log_filename = \1.trace.log
    log_connections = true
    log_transactions = true
}

3. restart Firebird service

Regards,

Pavel

[Ertan Küçükoglu]

Hello,

AFAIK, FireDAC does not support Firebird 4.0 yet and your problem may just be that.

Thanks & Regards,

Ertan Küçükoğlu 

Sent from my iPhone

On 21 Nov 2021, at 10:20, Pavel Katalymov <pav...@gmail.com> wrote:



--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/72f755d7-9cd3-40ad-afd4-e4a6e7cf5316n%40googlegroups.com.

[Dimitry Sibiryakov]

Pavel Katalymov wrote 21.11.2021 6:58:
> /Unless ONLY is added, all the savepoints created after the named savepoint are
> released as well. /

Yes, but in your example savepoint N was created before so it is definitely a
Firebird bug. Create an issue in tracker, please.

--
WBR, SD.

[Pavel Katalymov]

I make a typo steps should be:

• Savepoint N
• Savepoint N+1
• Release Savepoint N

• Release Savepoint N+1 - this failed

[Dimitry Sibiryakov]

Pavel Katalymov wrote 21.11.2021 15:07:
>
> I make a typo steps should be:
>

> * Savepoint N
> * Savepoint N+1
> * Release Savepoint N
> * Release Savepoint N+1 - this failed

This is an expected behavior.

But in Firebird 4 fails even such script:

SQL> savepoint a;
SQL> savepoint b;
SQL> release savepoint b;
SQL> release savepoint a;
Statement failed, SQLSTATE = 3B000
Unable to find savepoint with name A in transaction context

I'll fill the issue.

--
WBR, SD.

[Chris LeFebvre]

Sorry I've been a little MIA here, work has been ferocious the last week. I was fiddling with the code a little more and I think I found the problem although again I'm not sure if this is a FireDAC problem or a Firebird 4.0 issue; in the code I had originally posted, since I switched to FireDAC I've used:

Var

  dbData: TFDConnection;

  trData: TFDTransaction; { The default transaction for dbData  }

  FDPhysFBDriverLink1: TFDPhysFBDriverLink;

  dsDataSet: TFDQuery;   

  trDataset: TFDTransaction;

  updDataSet: TFDUpdateSQL;

  If Not trDataSet.Active Then trDataset.StartTransaction;

  dsDataSet.Delete;

  dsDataSet.ApplyUpdates;

  dsDataSet.ComitUpdates;

Then dsDataSet.Close; caused the error to happen.

Since this problem appears to be transaction related I decided to try commenting out this line of code " If Not trDataSet.Active Then trDataset.StartTransaction;" and when I ran the program it worked and I did not have the "FireDAC Phys FB Unable to find savepoint with the name SP-17 in transaction context error" when I closed the dataset that I had just deleted or inserted a record into. So there is apparently no need to start a transaction manually in code before any insert or delete of a record in a dataset in Delphi 11 / FireDAC.

Thanks everyone for your input and ideas.

[Pavel Katalymov]

Hi All,

The Savepoint issue was fixed in 4.0.1.2672 (#7048: Release of user savepoint releases too much). My apps work.

Best regards,

Pavel