Recorder SQLite DB on network share -> 0 bytes & corrupted

Configuration
1

Hi,
I am new to Home Assistant - I am moving over from OpenHab.

Maybe someone can point me in the right direction regarding this problem:

  • I am running the Home Assistant OS on a Raspberry
  • I set up a network share “nas_data” using “Settings → System → Storage” (my UI is configured to German, this might not be the exaxt terms)
  • that share is set up as a “Share” and is acessed through SMB
  • I have configured the Recorder SQLite DB to live there:
recorder:
  db_url: sqlite:////share/nas_data/home-assistant_v2.db

When HA starts, Recorder creates a 0 byte home-assistant_v2.db in that directory and then a few 0 byte home-assistant_v2.db.corrupt.2024-12-31T001540.318051+0000 files.

The log says:

Logger: homeassistant.components.recorder.util
Quelle: components/recorder/util.py:272
Integration: Recorder (Dokumentation, Probleme)
Erstmals aufgetreten: 01:21:45 (9 Vorkommnisse)
Zuletzt protokolliert: 01:22:49

The database at //share/nas_data/home-assistant_v2.db is corrupt or malformed
Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 272, in validate_sqlite_database
    run_checks_on_open_db(dbpath, conn.cursor())
    ~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 283, in run_checks_on_open_db
    sanity_check_passed = basic_sanity_check(cursor)
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 259, in basic_sanity_check
    cursor.execute(
    ~~~~~~~~~~~~~~^
        f"SELECT * FROM {table} LIMIT 1;"  # noqa: S608 # not injection
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
sqlite3.OperationalError: no such table: states

What’s going wrong here?
As Recorder is able to create a file, it should not have any problems to populate it with data - at least that’s what I assume.

I’d be happy to get some pointers to what it is, that’s going wrong here.
I can provide more debuigging info, if you can tell me, what’s needed.

Addition:
I am wrong, it does seem to be some problem with Samba (Samba-permissions?). The Samba log says this:

[2024/12/31 01:13:03.252036,  0] ../../source3/param/loadparm.c:3448(process_usershare_file)
  process_usershare_file: stat of /var/lib/samba/usershares/daten13_homeassistant_data failed. Permission denied

Addition 2:
If I change permissions on /var/lib/samba/usershares/daten13_homeassistant_data to 777, I don’t get the error in the log, but Recorder still shows the same behaviour, so probably it has nothing to do with this.
I also verified, that I can write to the share with that user - the only difference is, that I am doing this from a windows machine.
Is there a way to check the share from the linux level within HA?

Addition 3 (it’s the last one - I should have been in bed two hours ago…)
Using the “Terminal & SSH” Addon, I can echo "hallo" > /share/nas_data/test and the content is written to the file test without a problem.
This now makes me rather certain, that the problem lies not with Samba or permissions.

Cheers,
Bastian

2

I guess it’s a timing issue: HA needs it’s DB at startup, while the share will only be mounted later.

Anyway, it’s likely not a good idea to store a sqlite DB on a network share.

3

I see, what you mean.
What is the best practice for placement of the Recorder SQLite DB? Without any changes, it’ll sit on the Micro SD Card. I assumed, that this was not an ideal place, given that there will be many write-accesses to the DB.