limbo transactions

[Nick Upson]

Hi

In the last few days I've had 2 occasions when the firebird database has declared a limbo transaction exists, just before this a new piece of software was deployed.

The documentation talks about connections to 2 databases but we only have one, however it may be that the new software has 2 (or more) connections open to the same database - could this have the same effect and cause the limbo transaction?

gfix -list just confirmed the transaction was in limbo and "Transaction description item unknown"

--

Nick

[Jason (PN)]

Hi Nick,

 

I have only ever managed this when I have 2 x connections to 2 different database, had one transaction shared between them and managed to kill the connection before a commit or rollback completed.  This was doing replication over dial up, so there were lots of lost connections and it happened, but rarely.

 

From what you say, is there a chance that you are using the same transaction across the 2 database connections (to the same database)?  This could theoretically cause the problem.

 

I have not seen:  "Transaction description item unknown", but there again, I haven’t seen a dial up connection in a few years.

 

Cheers,

 

Jason Chapman

JAC2 Consultancy Limited

--
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/CAJa8MdkNV8UaGxdbsC%2BS1CYZ6meYVc-Nxsdq%2Bd3-N7tt345Jxg%40mail.gmail.com.

[Nick Upson]

Hi Jason

I've never seen a limbo transaction before.

does the connection need to fail or would I get the same effect if on connection did an insert that failed (attempting to insert a duplicate into a unique constraint, for example)

--

Nick

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/006701d6faa7%247e07c850%247a1758f0%24%40jac2.co.uk.

[Mark Rotteveel]

Limbo transaction are created by two-phase commits where the transaction
is prepared, but not committed or rolled back. This doesn't necessarily
involve two databases, but the common use-case for two-phase commit are
distributed transactions (transactions involving multiple data sources).
So, either your software is unnecessarily using two-phase commits, or it
has an actual use case for distributed transactions you're not aware of,
and for some reason it didn't finish (commit or rollback) its
distributed transaction correctly.

Mark

[Mark Rotteveel]

On 2021-02-04 09:20, Nick Upson wrote:
> I've never seen a limbo transaction before.
>
> does the connection need to fail or would I get the same effect if on
> connection did an insert that failed (attempting to insert a duplicate
> into a unique constraint, for example)

A limbo transaction is functionally complete, but not committed or
rolled back (hence the term limbo, its state is undecided, so it is 'in
limbo'). This is not about things like inserts failing or anything, this
is about your applications starting a two-phase commit by preparing the
transaction (marking it as 'done'), but never committing or rolling
back.

Mark

[tiberiu...@gmail.com]

Hi all, 

FB 3.0.7 , Delphi 10.4 .

I have a database with a table that stores pictures of some items, in BLOB format. 

CREATE TABLE POZA_PRODUS (

    ID_POZA_PRODUS INTEGER NOT NULL,

    POZA BLOB SUB_TYPE 0 SEGMENT SIZE 80

)

Because one of my clients have hundreds of pictures (1.5 .. 3 MB each) stored in the database, the file size is 2,3+ TB. I tried some other ways to store these pictures. Is there a way to use an external file ? Is it worth it ? 

I tried

CREATE TABLE POZA_PRODUS_TEST EXTERNAL 'C:\TEMP\PozaProdus.TXT' (
    ID_POZA_PRODUS  INTEGER NOT NULL,
    POZA            BLOB SUB_TYPE 0 SEGMENT SIZE 80
)

triggers this error : "Data type BLOB is not supported for EXTERNAL TABLES. Relation 'POZA_PRODUS_TEST', field '<Missing arg #3 - possibly status vector overflow>'."

Should I store only the link to the file in the database and deal with it via http:// or ftp:// ? 

Thank you, 

Tiberiu

[Dimitry Sibiryakov]

06.02.2021 08:17, tiberiu...@gmail.com wrote:
> Is there a way to use an external file ? Is it worth it ?

Yes, there is a way. No, it doesn't worth it: too much problems on this way.

> Should I store only the link to the file in the database and deal with it via http:// or ftp:// ?

If database file size is your concern then yes.

--
WBR, SD.

[tiberiu...@gmail.com]

Thank you !



Tiberiu

>-- 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/751d8e4c-ba47-6772-588c-2326f25b09fb%40ibphoenix.com.

[Zoran]

What I did was to have a special folder somewhere (on same or different hard disk) and have a path in THE table. For file name I use unique ID from the table with original extension, there are no duplicate names in that folder.

--

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/em759072c8-faeb-4317-b392-021ede252bb7%40tibi.

[Jason (PN)]

We used to have second databases.  We had to get 10M (wild estimate) documents into a Interbase 5.6 DB on windows NT – 1990’s.  We used a proprietary image compression and all images went into the main DB.  There was a background process on the server to move them to archive DB’s over time and the main DB tracked which one they were in. 

 

Worked really well and you decide how to sort out the archive DB’s that just have images in them.  You find that some of the DBs over time hardly ever got opened.  In that system the client app got the DB info from the main connection and opened the image DB’s as it needed to.

 

+ have you ever tried to move 1M smallish image files from one machine to another.  Copying one large DB a lot quicker..

 

Jason Chapman

JAC2 Consultancy Limited

 

From: firebird...@googlegroups.com <firebird...@googlegroups.com> On Behalf Of tiberiu...@gmail.com
Sent: 06 February 2021 07:18
To: firebird...@googlegroups.com
Subject: [firebird-support] Pictures in external table

 

Hi all, 

--

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/em759072c8-faeb-4317-b392-021ede252bb7%40tibi.

[Tomasz Tyrakowski]

On 06.02.2021 at 16:31, Jason (PN) wrote:
> We used to have second databases. We had to get 10M (wild estimate) documents into a Interbase 5.6 DB on windows NT – 1990’s. We used a proprietary image compression and all images went into the main DB. There was a background process on the server to move them to archive DB’s over time and the main DB tracked which one they were in.

> [...]

We've used a similar setup, but for PDF documents, not images (doesn't
matter much, though). A second database acting as a "virtual filesystem"
(in fact just a single table, with keys and blobs) plus document keys
stored in the ERP database. Has been working like a charm for 10+ years
now, no serious problems. All document metadata is stored in the main
ERP database, with the second one acting just as a binary storage for PDFs.
We've tried SFTP to store and access the documents, and eventually
switched to the database solution as it gave the best results with
minimum effort.
The only thing you need to work out is access control (who can access
which images / documents), to prevent side-loading them by smart
clients. But that generally depends on your system architecture - if the
clients don't talk to the FB server directly, but for example via your
API, that's not an issue. Otherwise I'd make sure the storage keys are
really hard to guess.

cheers
Tomasz

> Because one of my clients have hundreds of pictures (1.5 .. 3 MB each) stored in the database, the file size is 2,3+ TB. I tried some other ways to store these pictures. Is there a way to use an external file ? Is it worth it ?
>
>
>
> I tried
>
>
>
> CREATE TABLE POZA_PRODUS_TEST EXTERNAL 'C:\TEMP\PozaProdus.TXT' (
> ID_POZA_PRODUS INTEGER NOT NULL,
> POZA BLOB SUB_TYPE 0 SEGMENT SIZE 80
> )
>
> triggers this error : "Data type BLOB is not supported for EXTERNAL TABLES. Relation 'POZA_PRODUS_TEST', field '<Missing arg #3 - possibly status vector overflow>'."
>
>
>
> Should I store only the link to the file in the database and deal with it via http:// or ftp:// ?
>
>
>
>
>
>
>
> Thank you,
>
>
>
> Tiberiu
>


--

=======================================
Tomasz Tyrakowski
SOL-System sp. z o.o.
https://www.sol-system.pl

KRS: 0000234025
Sąd Rejonowy w Poznaniu,
VIII Wydział Gospodarczy KRS
NIP: 972-111-30-64, REGON: 300067880
Kapitał zakładowy: 100 000,00 złotych
=======================================
________________________________


Ta wiadomość zawiera poufne informacje przeznaczone tylko dla adresata. Jeżeli nie jesteście Państwo jej adresatem, bądź otrzymaliście ją przez pomyłkę, prosimy o powiadomienie o tym nadawcy oraz trwałe jej usunięcie.