[Bug]: DuckDB - if a table output fails, the destination duckdb database file remains locked and can no longer be used. about hop HOT 4 OPEN

Ciao @usbrandon the behavior seems to be correct and not related to any issue in Apache Hop, according to what we are reading in this thread duckdb file being used by another process.

We need to perform further investigations to understand what we can do to overcome this limitations.

from hop.

Looking at what I detailed in previous message, to have things working we have 2 options that must be managed:

1. If we want to RW the DuckDB database we must only use a single connection. And that is solved by using a pipeline run configuration that is defined as transactional because that is the only way to achive a single connection per pipeline
2. If we only want to Read the DuckDB database we can use a normal pipeline run configuration BUT we must open the connection in read only mode by adding the appropriate flag to connection configuration as shown in the image below.

This option will work immediately after the feature #3661 will be applied so that the parameters specified will be applied in the connection in the proper way

from hop.

My bad, the idea of using transactional run configuration to solve the file locking problem seems not to be a valuable solution because the problem still remain. On the other side, the suggestion on using Read Only connections is valuable.

from hop.

The DuckDB file seems to remain locked only when an exception is raised during the execution of the table output. I'm trying to understand what's the difference.

from hop.