We have multiple concurrent processes (up to a 100) which are creating and writing to a new database files. Each process creates and writes to its own db file. The process is terminated when write of the batch is done. For the next batch new process is created. Only one create/write per db and no readers. All db files are located in the same folder and they have unique file names. At some point we get unable to open database file. Tmp folder is located on the same mount and has enough space. We try to catch the error, delete file and re-try again but it does not help. We also use sqlite3 for python for our task.

Did anybody run into the same situation?

Thx!

Too many open files/handles?

We try to catch the error, delete file and re-try again but it does not help

What have you tried that does help? ie, what resolves this error?

All db files are located in the same folder

On a local directory, or a remote directory?

What operating system?

We also use sqlite3 for python

What version of SQLite?

1. Too many open files/handles?

Its only one db file per process and up to 100 processes in the worst case. I assume Linux can handle this volume.

1. What have you tried that does help? ie, what resolves this error?

Nothing helps the moment we start hitting this error it stays. Does not matter whether we delete DB file and re-try again still the same error. So its a blocking issue

1. On a local directory, or a remote directory?

Local folder with more than enough disk space (at least twice the needed size of our solution)

1. What operating system?

Amazon Linux 2

1. What version of SQLite?

SQLite version 3.7.17 2013-05-20 00:56:22 and Python 3.10/sqlite3

A ten-year-old piece of software? Have you tried using a more up-to-date version, eg, the latest?

I'd suggest using strace to capture more detail about the failure. There is no obvious reason it might fail, and there is insufficient information in this report to suggest what non-obvious behaviour is occurring.

We have tried with SQLite version 3.43.2 2023-10-10 12:14:04

Still failing with the same error: SQLite error 'unable to open database file'

Looks like opening, writing, closing and deleting text file (with all the checks) in the same folder does not change the situation. We are still getting 'unable to open DB' error. So we can rule out handles/OS resources issue

You really should use strace since it will answer exactly what happened at the operating system level. I typically use it with the -o option to send output to a file, the -s option to set how long strings (such as filenames) are truncated at, and the -f option that follows forks and similar calls.

It is also a good idea to use sqlite3_config to get SQLite's log messages if you are not doing so already.

Here is some C code to get you started.

void sqlite_log_receiver(void *context, int code, const char *message)
{
  fprintf(stderr, "SQLITE LOG [%d] %s\n", code, message);
}

/* to register the logger */
sqlite3_config(SQLITE_CONFIG_LOG, sqlite_log_receiver, NULL);

Yup, thx, will try with logging and strace.

Meanwhile want to share additional info (which we just got from the stack trace):

1. Looks like connection completes successfully however sqlite3.connection.executescript() fails to create table structure for the empty db file. Here is sql transaction statements we call in the executescript():

BEGIN;

PRAGMA page_size = {page_size};

CREATE TABLE metadata ( name TEXT, value TEXT );

    CREATE UNIQUE INDEX name ON metadata (
        name
    );

    CREATE TABLE images (
        tile_data BLOB,
        tile_id TEXT PRIMARY KEY ON CONFLICT IGNORE
    );

    CREATE UNIQUE INDEX images_id ON images (
        tile_id
    );

    CREATE TABLE map (
        zoom_level INTEGER,
        tile_column INTEGER,
        tile_row INTEGER,
        tile_id TEXT,
        UNIQUE (zoom_level, tile_column, tile_row) ON CONFLICT IGNORE
    );

    CREATE UNIQUE INDEX map_index ON map (
        zoom_level,
        tile_column,
        tile_row
    );

    CREATE VIEW tiles AS
        SELECT
            map.zoom_level AS zoom_level,
            map.tile_column AS tile_column,
            map.tile_row AS tile_row,
            images.tile_data AS tile_data
        FROM
            map
        JOIN
            images on images.tile_id = map.tile_id;

    COMMIT;

P.S.:

1. That's how we create connection: sqlite3.connect(f"file:{self._path}?mode={query_args}", uri=True) and default isolation_level is 'DEFERRED'
2. Our scenario: we are using process pool where each process creates a single connection and creates/writes to a single db file. When whole chunk of data is written to db, processes is retuned back to the pool. Process from the pool are re-used until no more batches of data left. There might be up to 100 processes executing simultaneously and thus up to 100 connections (with create/write permissions) opened at the same time but one connection per process.

Hopefully this brings more clarity. Thx!

Also looks like sqlite3_config() is not exposed in Python :(

Try using the apsw module. That gives you access to sqlite3_config() and almost everything else you want or need.

Given your statement at the top, you first need to do debugging to figure out what is actually going on with your code. We have nothing to observe or to reproduce, and the functionality you are using has worked in SQLite for two decades.

If you are using Python only then as the other anonymous mentioned you can use APSW to get logging and other gotchas taken care of. (Disclosure: I am the APSW author.)

Since you are using multiple processes and Linux, there is also a danger that you are using SQLite across forks. APSW includes a fork checker which will detect that.

The symptoms you are now describing sometimes happen when the current working directory is not what the developer expects, and so operations aren't happening on the files you think.

Yup probably makes sense to try APSW. Btw we've also checked fork possibility and looks like by default python creates process via fork. We've explicitly set "spawn" as a process creation method however it still didn't help. I'll update thread if we have more data. Thx for your help!

Will do! Thx!