So many sqlite-jdbc-tmp-xx.db files created automatically in C:\Windows\Temp folder

[wfs1900]

Describe the bug version: io.github.willena 3.46.1.0 There are so many sqlite-jdbc-tmp-xx.db files in C:\Windows\Temp folder if I use willena sqlite-jdbc driver, the file seems be created each minute. I try to change the driver to xerial, it's normal, no tmp file created;

To Reproduce Provide a sample code that reproduces the error.

Expected behavior A clear and concise description of what you expected to happen.

Logs If applicable, provide logs.

Environment (please complete the following information):

• Windows10
• x64
• 3.46.1.0

Additional context Add any other context about the problem here.

[Willena]

Hello,

On the native library side, it is by default compiled with SQLITE_TEMP_STORE=2 meaning "Use memory by default but allow the PRAGMA temp_store command to override"

So, unless the pragma is called, the native library is not the source and is not creating temporary files.

As for the JDBC driver side: the only place where it can happen is here: https://github.com/Willena/sqlite-jdbc-crypt/blame/a3be2cb1daa37355aa9d341e33ca119136715587/src/main/java/org/sqlite/SQLiteConnection.java#L310-L312

The extractResource method is the only place where a temp file with such name can be created and only if the URL is not like file: The extractResource itself is only called once and if all those conditions are met:

• The file name is not empty
• Is not an in memory database
• Does not start with file:
• File name contains the :resource: marker.

With a very basic test, I am not able to reproduce the issue you have. (Win 10 x64 3.46.1.0 JDK 11,17,19,21). Without more information and code samples that show how you are using the library, I'm not sure if I can help more.

[wfs1900]

Hello, thanks for your help. I find that maybe it has relationship with hikira connection pool. I set min-idle to 5 and max-lifetime to 60000，means the pool will at least keep 5 connections and these connections's life time is 1min。It seems the pool created 5 TEMP files once 1min, and the old TEMP file was not recyled/deleted.

Following are my configurations of hikira: spring: datasource: dynamic: hikari: connection-timeout: 5000 idle-timeout: 30000 min-idle: 5 max-pool-size: 16 max-lifetime: 60000 is-auto-commit: true

and my JDBC configuartions are driver-class-name: org.sqlite.JDBC username: password: url: jdbc:sqlite::resource:xx.db?cipher=chacha20&key=xx

the cypted db is packaged as a resource in jar file, so JDBC extract it.

[Willena]

Hello,

You are exactly in the condition I described in my previous message: using a db from resources. I am now able to reproduce the issue, but unfortunately, I tested with both JDBC driver (xerial and mine) and the behavior is exactly the same: a temp file gets created each time you open a new connection to the database.

I don't know about hikari, but given your settings, It seems like it is opening and closing database connections every minute. Which has the consequence of creating a new temp file each time. Combined with SQLite I'm not sure about the pertinence of this configuration (performance wise, SQlite is using mutex and serialize statements to ensure thread safety: https://www.sqlite.org/c3ref/c_config_covering_index_scan.html#sqliteconfigserialized)

Temporary files not being cleaned up is definitely something you can report in the original repository (this is a fork with only changes related to encrypted database with SQLite3Mc). I do regularly merge upstream changes, if it is corrected it will get corrected here too.

[wfs1900]

Hello, Yes, I moved db file out of resource and put it as a FILE，then disk is normal, no more TEMP files created, I'm using it as a temporary solution. And try to report it in original repo. Thanks.