JDBC database shutdown on disconnect.

[travis-leith]

A duckdb connector has been implemented recently on DBeaver. However, the database does not shutdown on disconnect. Is there a JDBC driver feature similar to the R package where we can say shutdown = TRUE?

[travis-leith]

Related issues https://github.com/duckdb/duckdb/issues/1516 https://github.com/duckdb/duckdb/issues/1216

[travis-leith]

Comment from dbeaver folks

this caused by loosy JDBC API support in DuckDB driver. Table is created in mixed case (as driver says it uses mixed case). But in fact it is then converted into lower case. And the dbeaver can't find this table when tries to re-read metadata.

Creating tables in lower case helps. Metadata refresh helps.

[cdeil]

I also ran into this bug and couldn't open my duckdb from Python, not even with read_only=True.

Disconnect in DBeaver didn't help, only closing the DBeaver application completely resolved the issue.

Is there anyone that can fix the DBeaver connector to disconnect properly?

In [82]: con = duckdb.connect("my-db.duckdb", read_only=True)
---------------------------------------------------------------------------
RuntimeError                              Traceback (most recent call last)
Input In [82], in <cell line: 1>()
----> 1 con = duckdb.connect("my-db.duckdb", read_only=True)

RuntimeError: IO Error: Could not set lock on file "my-db.duckdb": Resource temporarily unavailable

[Mause]

@cdeil are you able to test if this still occurs with the latest master build? We now shutdown the database instance when the last connection is closed

[cdeil]

I'm not sure how easy it would be for me to reproduce the issue and then build and try the master. If you or others in the dev team can't easily do that, I could try, let me know. Or alternatively - when is your next stable release? Could also wait for that and try again.

[Mause]

@cdeil are you able to confirm if this behaves as expected for you with 0.6.0?

[cdeil]

I updated the Python version as well as the driver in DBeaver to duckdb 0.6.0.

I can confirm that after closing the DB connection in DBeaver, I can now open the file from Python. So that bug is not there any more.

---

However I'm still seeing that after opening a DuckDB in DBeaver, I still can't open it in Python in read-only mode:

In [2]: duckdb.connect("/Users/cdeil/code/hc/hpred/data/cache/sensor.duckdb", read_only=True)
---------------------------------------------------------------------------
IOException                               Traceback (most recent call last)
Input In [2], in <cell line: 1>()
----> 1 duckdb.connect("/Users/cdeil/code/hc/hpred/data/cache/sensor.duckdb", read_only=True)

IOException: IO Error: Could not set lock on file "/Users/cdeil/code/hc/hpred/data/cache/sensor.duckdb": Resource temporarily unavailable

---

And now I noticed a purely DBeaver-related bug (without Python) which can be reproduced like this:

1. Open a DuckDB (works).
2. Disconnect (works).
3. Try to connect again - fails with error:

Connection Error: Instance with path: /Users/cdeil/code/hc/hpred/data/cache/sensor.duckdb already exists.

So it's still the case that I completely have to close the DBeaver app to continue working. Somehow the JDBC driver is left in a bad state after disconnecting that prevents it to re-connect later.

[Mause]

Are you opening the database in read only mode in DBeaver when you open it in Python (also in read only)?

We've had a couple of reports of that DBeaver issue yes, there were a number of changes that might have caused it so I'll need to do some investigation

[cdeil]

I was using normal read/write mode in DBeaver and read-only mode in Python.

[Mause]

Unfortunately you can only have one client connected to a database if you're using a write connection. Multiple connections is only (presently) supported in read only mode, unless you're connecting from different threads in the same process

Looks like your other issue is a duplicate of #5371

[cdeil]

OK then I think this issue should be closed.

@travis-leith as original reporter - do you agree?

---

Is it a known or valid feature request for DuckDB to allow other readers even if a writer has a connection open? Or will it never be possible with how DuckDB works?

My use case is simply that I use DBeaver as a DB GUI to check content, while I have a Python script writing to it, that's when I ran into these issues.

[travis-leith]

I have not had the pleasure of using duckdb for a while now. But going by the above discussion, looks like this can be closed, and possibly a feature request for read only connections in the presence of a read/write connection.