Home Assistant recorder database is never able to be created

Configuration
1

Hi community,

I’ve been searching the forums and yet to find something to help.
In short, my SQLITE db is never able to be created. Every fresh restart, after clearing all corrupt .db files and the current .db file, always creates a new blank file and a bunch of corrupted ones.

The first error shown is
2024-12-23 13:23:01.282 ERROR (Recorder) [homeassistant.components.recorder.core] Error during connection setup: (retrying in 3 seconds)

Followed by the db is locked.
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) database is locked

2024-12-23 13:23:04.349 ERROR (Recorder) [homeassistant.components.recorder.util] The database at //config/home-assistant_v2.db is corrupt or malformed

sqlite3.OperationalError: no such table: states

2024-12-23 13:23:04.353 ERROR (Recorder) [homeassistant.components.recorder.util] The system will rename the corrupt database file //config/home-assistant_v2.db to //config/home-assistant_v2.db.corrupt.2024-12-23T13:23:04.353189+00:00 in order to allow startup to proceed

I have no data currently to ingest into the db. I just need a blank fresh working db.

All the current guidance is to clear the files and fresh restart but this always results in the same log output errors. Is there something else I need to do to get this to work?

Any help would be greatly appreciated.

Thanks,
Rob

1 Like
2

What kind of installation do you use? If HA in container - stop the HA container, delete db-files, start HA container.
Also, comment your “recorder” settings (if not default).
Also, start HA in a safe mode.

3

Yes its HA Core in container.

This is what I do each time. Stop HA, delete and start again.
My recorder settings are default.

I haven’t tried starting in safe mode.
I’ll try now and see if that helps.

4

Are you sure it has write permissions to the folder.

5

I’m pretty sure as I’m using a NAS shared folder and HA can write config changes to the directory ok. It creates the corrupted db files in there too.

6

Restarting in safe mode produces the same result unfortunately.

7

Just in case - check if the HDD is not corrupted.

8

Thank you for the continued help!

My NAS is not reporting any corruption and managing files from HA and other sources that access it seem to be ok from what I can tell.

9

I would try re-installing HA ))))
(using HA in docker myself, see no sense to use HA core honestly, but this is beyond the topic)

10

May I ask what image you are using please.
I’m on this one
https://hub.docker.com/r/homeassistant/home-assistant 2024.12.

I thought container and core were the same to be honest.

11 
sudo docker pull homeassistant/home-assistant:stable
12

This image is also giving me the same issue. :slightly_frowning_face:

Does anyone know if there is a default schema somewhere I can try putting in.

13

Grasping at straws but might it be that you have accessed the db from another tool and that one is still running? What docker command did you run to create the lot. As @Ildar_Gabdullin says, delete all config and restart?

14

Erm, I did not suggest to “delete all config & restart”, I finally suggested to reinstall HA ))). (of course with keeping the config except *.db)

15

I wouldn’t delete the config but I would create another folder and create/run a defaulted HA container.

Verify that works

Stop it and move your config over minus db or restore a backup

Doing this might help point out the issue or completely fix it

Also. How are you running db? Is it standard db or you run seperate container/service?

16

I am under the assumption that, since it is the OP’s first post, that there is nothing to be saved and starting 100% clean may be of added value.

17

Thanks for the reply!

Let me try that. As it is a container, there is no fresh install really. But I can run a clean container with no previous configs and see if I can get the db files that way.

I am just trying to run the default db.

18

Very strange.
I’m deploying this into a MicroK8s cluster on a 3 PI v4 cluster running Ubuntu.

I created a fresh PV and PVC, used the latest 2025.1 home assistant image in a fresh container and still the same error. I’m stumped. This points to something about the PV and PVC setup it seems.

Going to play around with the PV mount options and see if I can narrow it down.

19

I’ve just started a fresh container in a docker install on an old pi3 running Ubuntu which I had lying around, and it too does the exact same thing!

What on earth.

20

This is the full stack trace, completely outside of the Kubernetes land

2025-01-07 19:42:06.453 ERROR (Recorder) [homeassistant.components.recorder.core] Error during connection setup: (retrying in 3 seconds)
Traceback (most recent call last):
  File "/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 146, in __init__
    self._dbapi_connection = engine.raw_connection()
                             ~~~~~~~~~~~~~~~~~~~~~^^
  File "/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 3302, in raw_connection
    return self.pool.connect()
           ~~~~~~~~~~~~~~~~~^^
  File "/usr/local/lib/python3.13/site-packages/sqlalchemy/pool/impl.py", line 447, in connect
    return _ConnectionFairy._checkout(self, self._fairy)
           ~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.13/site-packages/sqlalchemy/pool/base.py", line 1263, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/usr/local/lib/python3.13/site-packages/sqlalchemy/pool/base.py", line 712, in checkout
    rec = pool._do_get()
  File "/usr/src/homeassistant/homeassistant/components/recorder/pool.py", line 95, in _do_get
    return super()._do_get()
           ~~~~~~~~~~~~~~~^^
  File "/usr/local/lib/python3.13/site-packages/sqlalchemy/pool/impl.py", line 429, in _do_get
    c = self._create_connection()
  File "/usr/local/lib/python3.13/site-packages/sqlalchemy/pool/base.py", line 390, in _create_connection
    return _ConnectionRecord(self)
  File "/usr/local/lib/python3.13/site-packages/sqlalchemy/pool/base.py", line 674, in __init__
    self.__connect()
    ~~~~~~~~~~~~~~^^
  File "/usr/local/lib/python3.13/site-packages/sqlalchemy/pool/base.py", line 914, in __connect
    )._exec_w_sync_on_first_run(self.dbapi_connection, self)
      ~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.13/site-packages/sqlalchemy/event/attr.py", line 483, in _exec_w_sync_on_first_run
    self(*args, **kw)
    ~~~~^^^^^^^^^^^^^
  File "/usr/local/lib/python3.13/site-packages/sqlalchemy/event/attr.py", line 497, in __call__
    fn(*args, **kw)
    ~~^^^^^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 1373, in _setup_recorder_connection
    if database_engine := setup_connection_for_dialect(
                          ~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
        self,
        ^^^^^
    ...<2 lines>...
        not self._completed_first_database_setup,
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    ):
    ^
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 512, in setup_connection_for_dialect
    execute_on_connection(dbapi_connection, "PRAGMA journal_mode=WAL")
    ~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 335, in execute_on_connection
    cursor.execute(statement)
    ~~~~~~~~~~~~~~^^^^^^^^^^^
sqlite3.OperationalError: database is locked

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 914, in _setup_recorder
    self._setup_connection()
    ~~~~~~~~~~~~~~~~~~~~~~^^
  File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 1429, in _setup_connection
    migration.pre_migrate_schema(self.engine)
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 322, in pre_migrate_schema
    inspector = sqlalchemy.inspect(engine)
  File "/usr/local/lib/python3.13/site-packages/sqlalchemy/inspection.py", line 140, in inspect
    ret = reg(subject)
  File "/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/reflection.py", line 312, in _engine_insp
    return Inspector._construct(Inspector._init_engine, bind)
           ~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/reflection.py", line 245, in _construct
    init(self, bind)
    ~~~~^^^^^^^^^^^^
  File "/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/reflection.py", line 256, in _init_engine
    engine.connect().close()
    ~~~~~~~~~~~~~~^^
  File "/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 3278, in connect
    return self._connection_cls(self)
           ~~~~~~~~~~~~~~~~~~~~^^^^^^
  File "/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 148, in __init__
    Connection._handle_dbapi_exception_noconnection(
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
        err, dialect, engine
        ^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 2442, in _handle_dbapi_exception_noconnection
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 146, in __init__
    self._dbapi_connection = engine.raw_connection()
                             ~~~~~~~~~~~~~~~~~~~~~^^
  File "/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 3302, in raw_connection
    return self.pool.connect()
           ~~~~~~~~~~~~~~~~~^^
  File "/usr/local/lib/python3.13/site-packages/sqlalchemy/pool/impl.py", line 447, in connect
    return _ConnectionFairy._checkout(self, self._fairy)
           ~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.13/site-packages/sqlalchemy/pool/base.py", line 1263, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/usr/local/lib/python3.13/site-packages/sqlalchemy/pool/base.py", line 712, in checkout
    rec = pool._do_get()
  File "/usr/src/homeassistant/homeassistant/components/recorder/pool.py", line 95, in _do_get
    return super()._do_get()
           ~~~~~~~~~~~~~~~^^
  File "/usr/local/lib/python3.13/site-packages/sqlalchemy/pool/impl.py", line 429, in _do_get
    c = self._create_connection()
  File "/usr/local/lib/python3.13/site-packages/sqlalchemy/pool/base.py", line 390, in _create_connection
    return _ConnectionRecord(self)
  File "/usr/local/lib/python3.13/site-packages/sqlalchemy/pool/base.py", line 674, in __init__
    self.__connect()
    ~~~~~~~~~~~~~~^^
  File "/usr/local/lib/python3.13/site-packages/sqlalchemy/pool/base.py", line 914, in __connect
    )._exec_w_sync_on_first_run(self.dbapi_connection, self)
      ~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.13/site-packages/sqlalchemy/event/attr.py", line 483, in _exec_w_sync_on_first_run
    self(*args, **kw)
    ~~~~^^^^^^^^^^^^^
  File "/usr/local/lib/python3.13/site-packages/sqlalchemy/event/attr.py", line 497, in __call__
    fn(*args, **kw)
    ~~^^^^^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 1373, in _setup_recorder_connection
    if database_engine := setup_connection_for_dialect(
                          ~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
        self,
        ^^^^^
    ...<2 lines>...
        not self._completed_first_database_setup,
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    ):
    ^
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 512, in setup_connection_for_dialect
    execute_on_connection(dbapi_connection, "PRAGMA journal_mode=WAL")
    ~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 335, in execute_on_connection
    cursor.execute(statement)
    ~~~~~~~~~~~~~~^^^^^^^^^^^
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) database is locked
(Background on this error at: https://sqlalche.me/e/20/e3q8)
2025-01-07 19:42:09.555 ERROR (Recorder) [homeassistant.components.recorder.util] The database at //config/home-assistant_v2.db is corrupt or malformed
Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 274, in validate_sqlite_database
    run_checks_on_open_db(dbpath, conn.cursor())
    ~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 285, in run_checks_on_open_db
    sanity_check_passed = basic_sanity_check(cursor)
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 261, in basic_sanity_check
    cursor.execute(
    ~~~~~~~~~~~~~~^
        f"SELECT * FROM {table} LIMIT 1;"  # noqa: S608 # not injection
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
sqlite3.OperationalError: no such table: states
2025-01-07 19:42:09.560 ERROR (Recorder) [homeassistant.components.recorder.util] The system will rename the corrupt database file //config/home-assistant_v2.db to //config/home-assistant_v2.db.corrupt.2025-01-07T19:42:09.559983+00:00 in order to allow startup to proceed
2025-01-07 19:42:11.381 WARNING (MainThread) [homeassistant.setup] Setup of recorder is taking over 10 seconds.
2025-01-07 19:42:14.587 ERROR (Recorder) [homeassistant.components.recorder.core] Error during connection setup: (retrying in 3 seconds)

Could this be Ubuntu related then I wonder. I’m going to try flashing a diff OS. maybe even try from a windows laptop.