Recorder fails to store states or open database (sqlite3.OperationalError)

[Arjen2403]

The problem

When using a clean Home Assistant OS installation with the recorder integration enabled and pointed to a valid db_url, the system either:

• Fails to create the home-assistant_v2.db file entirely, or
• Creates it, but no states or events tables are generated, or
• (When the DB is manually initialized), state records are written with entity_id = NULL

The event bus confirms that state_changed events are being fired, and a debug automation confirms correct events including entity_id. However, the recorder either shuts down or fails silently, and the database remains empty or unusable.

What version of Home Assistant Core has the issue?

core-2025.6.1

What was the last working version of Home Assistant Core?

Unknown — this behavior appeared from first install (2025.6.1)

What type of installation are you running?

• Home Assistant OS
• Home Assistant Supervised
• Home Assistant Container
• Home Assistant Core

Integration causing the issue

recorder

Link to integration documentation on our website

https://www.home-assistant.io/integrations/recorder/

Example YAML snippet from configuration.yaml

recorder:
  db_url: sqlite:////homeassistant/home-assistant_v2.db
  purge_keep_days: 3
  commit_interval: 2

logger:
  default: warning
  logs:
    homeassistant.components.recorder: debug
    sqlalchemy.engine.Engine: info

input_number:
  test_value:
    name: Test Value
    initial: 0
    min: 0
    max: 100
    step: 1

script:
  test_logging_script:
    alias: Test Logging Script
    sequence:
      - repeat:
          count: 5
          sequence:
            - service: input_number.set_value
              data:
                entity_id: input_number.test_value
                value: 10
            - delay: "00:00:01"
            - service: input_number.set_value
              data:
                entity_id: input_number.test_value
                value: 20
            - delay: "00:00:01"
    mode: restart
    icon: mdi:repeat

Traceback/Error logs

sqlite3.OperationalError: unable to open database file
...
Recorder setup failed, recorder shutting down
...
No such table: states

When manually creating the database file, this happens:

SELECT entity_id, state FROM states;
-- returns multiple rows with entity_id = NULL

Additional information

• The directory /homeassistant exists and has 755 permissions (confirmed via SSH).

• When using the default config without db_url, Home Assistant still fails to create the default DB in /config/.

• Event bus confirms state_changed events are fired correctly (confirmed via automation).

• Environment:

  • SQLite 3.48
  • Python 3.13
  • Home Assistant OS 15.2
  • Core version: 2025.6.1

[home-assistant]

Hey there @home-assistant/core, mind taking a look at this issue as it has been labeled with an integration (recorder) you are listed as a code owner for? Thanks!

Code owner commands

Code owners of recorder can trigger bot actions by commenting:

• @home-assistant close Closes the issue.
• @home-assistant rename Awesome new title Renames the issue.
• @home-assistant reopen Reopen the issue.
• @home-assistant unassign recorder Removes the current integration label and assignees on the issue, add the integration domain after the command.
• @home-assistant add-label needs-more-information Add a label (needs-more-information, problem in dependency, problem in custom component) to the issue.
• @home-assistant remove-label needs-more-information Remove a label (needs-more-information, problem in dependency, problem in custom component) on the issue.

_{^{(message by CodeOwnersMention)}}

---

recorder documentation
recorder source
_{^{(message by IssueLinks)}}

[Mikett30]

EDIT: (Resolved)

Leaving this here hopefully to help others. The actual issue was with flock not being able to lock the database file properly. To test this, on the docker host server I went to my root directory and ran touch test.db and then tried locking the test file with flock test.db "sleep 1" to see if it threw an error. It didn't throw an error.. I deleted the test file and repeated this process within the /config mapped volume directory I was using for the Home Assistant docker container which lived on an NFS share. Flock failed this time showing no locks available. The NFS share this folder was on was on a Synology Server I have. I logged into it and changed the maximum NFS protocol accepted from NFS v3 up to NFS v4.1 and then updated my /etc/fstab file on my docker host machine to mount the NFS share on boot using version 4 instead of version 3 and this solved all my file locking issues.

---

Not sure if this is related, but I am having a similar issue. I am trying to use Docker Compose with image: homeassistant/home-assistant:latest

I tried running this image bare bones without any mapped volumes and also with a mapped volume. Either way I am getting a database locked error when I try to run it on an amd64 system, but when I run it on an arm64 system it works fine. Image says it's suppose to work on both!

Log:

2025-08-21 19:45:51.872 ERROR (Recorder) [homeassistant.components.recorder.util] The database at //config/home-assistant_v2.db is corrupt or malformed
homeassistant  | Traceback (most recent call last):
homeassistant  |   File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 265, in validate_sqlite_database
homeassistant  |     run_checks_on_open_db(dbpath, conn.cursor())
homeassistant  |     ~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^
homeassistant  |   File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 276, in run_checks_on_open_db
homeassistant  |     sanity_check_passed = basic_sanity_check(cursor)
homeassistant  |   File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 252, in basic_sanity_check
homeassistant  |     cursor.execute(
homeassistant  |     ~~~~~~~~~~~~~~^
homeassistant  |         f"SELECT * FROM {table} LIMIT 1;"  # noqa: S608 # not injection
homeassistant  |         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
homeassistant  |     )
homeassistant  |     ^
homeassistant  | sqlite3.OperationalError: database is locked
homeassistant  | 2025-08-21 19:45:51.880 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-08-21T23:45:51.880620+00:00 in order to allow startup to proceed
homeassistant  | 2025-08-21 19:45:56.677 WARNING (MainThread) [homeassistant.setup] Setup of recorder is taking over 10 seconds.
homeassistant  | 2025-08-21 19:45:57.073 ERROR (Recorder) [homeassistant.components.recorder.core] Error during connection setup: (retrying in 3 seconds)
homeassistant  | Traceback (most recent call last):
homeassistant  |   File "/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 145, in __init__
homeassistant  |     self._dbapi_connection = engine.raw_connection()
homeassistant  |                              ~~~~~~~~~~~~~~~~~~~~~^^
homeassistant  |   File "/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 3297, in raw_connection
homeassistant  |     return self.pool.connect()
homeassistant  |            ~~~~~~~~~~~~~~~~~^^
homeassistant  |   File "/usr/src/homeassistant/homeassistant/components/recorder/pool.py", line 126, in connect
homeassistant  |     return super().connect()
homeassistant  |            ~~~~~~~~~~~~~~~^^
homeassistant  |   File "/usr/local/lib/python3.13/site-packages/sqlalchemy/pool/impl.py", line 447, in connect
homeassistant  |     return _ConnectionFairy._checkout(self, self._fairy)
homeassistant  |            ~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^
homeassistant  |   File "/usr/local/lib/python3.13/site-packages/sqlalchemy/pool/base.py", line 1264, in _checkout
homeassistant  |     fairy = _ConnectionRecord.checkout(pool)
homeassistant  |   File "/usr/local/lib/python3.13/site-packages/sqlalchemy/pool/base.py", line 713, in checkout
homeassistant  |     rec = pool._do_get()
homeassistant  |   File "/usr/src/homeassistant/homeassistant/components/recorder/pool.py", line 96, in _do_get
homeassistant  |     return super()._do_get()
homeassistant  |            ~~~~~~~~~~~~~~~^^
homeassistant  |   File "/usr/local/lib/python3.13/site-packages/sqlalchemy/pool/impl.py", line 429, in _do_get
homeassistant  |     c = self._create_connection()
homeassistant  |   File "/usr/local/lib/python3.13/site-packages/sqlalchemy/pool/base.py", line 390, in _create_connection
homeassistant  |     return _ConnectionRecord(self)
homeassistant  |   File "/usr/local/lib/python3.13/site-packages/sqlalchemy/pool/base.py", line 675, in __init__
homeassistant  |     self.__connect()
homeassistant  |     ~~~~~~~~~~~~~~^^
homeassistant  |   File "/usr/local/lib/python3.13/site-packages/sqlalchemy/pool/base.py", line 915, in __connect
homeassistant  |     )._exec_w_sync_on_first_run(self.dbapi_connection, self)
homeassistant  |       ~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
homeassistant  |   File "/usr/local/lib/python3.13/site-packages/sqlalchemy/event/attr.py", line 483, in _exec_w_sync_on_first_run
homeassistant  |     self(*args, **kw)
homeassistant  |     ~~~~^^^^^^^^^^^^^
homeassistant  |   File "/usr/local/lib/python3.13/site-packages/sqlalchemy/event/attr.py", line 497, in __call__
homeassistant  |     fn(*args, **kw)
homeassistant  |     ~~^^^^^^^^^^^^^
homeassistant  |   File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 1392, in _setup_recorder_connection
homeassistant  |     if database_engine := setup_connection_for_dialect(
homeassistant  |                           ~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
homeassistant  |         self,
homeassistant  |         ^^^^^
homeassistant  |     ...<2 lines>...
homeassistant  |         not self._completed_first_database_setup,
homeassistant  |         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
homeassistant  |     ):
homeassistant  |     ^
homeassistant  |   File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 471, in setup_connection_for_dialect
homeassistant  |     execute_on_connection(dbapi_connection, "PRAGMA journal_mode=WAL")
homeassistant  |     ~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
homeassistant  |   File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 326, in execute_on_connection
homeassistant  |     cursor.execute(statement)
homeassistant  |     ~~~~~~~~~~~~~~^^^^^^^^^^^
homeassistant  | sqlite3.OperationalError: database is locked
homeassistant  |
homeassistant  | The above exception was the direct cause of the following exception:
homeassistant  |
homeassistant  | Traceback (most recent call last):
homeassistant  |   File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 927, in _setup_recorder
homeassistant  |     self._setup_connection()
homeassistant  |     ~~~~~~~~~~~~~~~~~~~~~~^^
homeassistant  |   File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 1448, in _setup_connection
homeassistant  |     migration.pre_migrate_schema(self.engine)
homeassistant  |     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^
homeassistant  |   File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 331, in pre_migrate_schema
homeassistant  |     inspector = sqlalchemy.inspect(engine)
homeassistant  |   File "/usr/local/lib/python3.13/site-packages/sqlalchemy/inspection.py", line 140, in inspect
homeassistant  |     ret = reg(subject)
homeassistant  |   File "/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/reflection.py", line 313, in _engine_insp
homeassistant  |     return Inspector._construct(Inspector._init_engine, bind)
homeassistant  |            ~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
homeassistant  |   File "/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/reflection.py", line 246, in _construct
homeassistant  |     init(self, bind)
homeassistant  |     ~~~~^^^^^^^^^^^^
homeassistant  |   File "/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/reflection.py", line 257, in _init_engine
homeassistant  |     engine.connect().close()
homeassistant  |     ~~~~~~~~~~~~~~^^
homeassistant  |   File "/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 3273, in connect
homeassistant  |     return self._connection_cls(self)
homeassistant  |            ~~~~~~~~~~~~~~~~~~~~^^^^^^
homeassistant  |   File "/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 147, in __init__
homeassistant  |     Connection._handle_dbapi_exception_noconnection(
homeassistant  |     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
homeassistant  |         err, dialect, engine
homeassistant  |         ^^^^^^^^^^^^^^^^^^^^
homeassistant  |     )
homeassistant  |     ^
homeassistant  |   File "/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 2436, in _handle_dbapi_exception_noconnection
homeassistant  |     raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
homeassistant  |   File "/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 145, in __init__
homeassistant  |     self._dbapi_connection = engine.raw_connection()
homeassistant  |                              ~~~~~~~~~~~~~~~~~~~~~^^
homeassistant  |   File "/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 3297, in raw_connection
homeassistant  |     return self.pool.connect()
homeassistant  |            ~~~~~~~~~~~~~~~~~^^
homeassistant  |   File "/usr/src/homeassistant/homeassistant/components/recorder/pool.py", line 126, in connect
homeassistant  |     return super().connect()
homeassistant  |            ~~~~~~~~~~~~~~~^^
homeassistant  |   File "/usr/local/lib/python3.13/site-packages/sqlalchemy/pool/impl.py", line 447, in connect
homeassistant  |     return _ConnectionFairy._checkout(self, self._fairy)
homeassistant  |            ~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^
homeassistant  |   File "/usr/local/lib/python3.13/site-packages/sqlalchemy/pool/base.py", line 1264, in _checkout
homeassistant  |     fairy = _ConnectionRecord.checkout(pool)
homeassistant  |   File "/usr/local/lib/python3.13/site-packages/sqlalchemy/pool/base.py", line 713, in checkout
homeassistant  |     rec = pool._do_get()
homeassistant  |   File "/usr/src/homeassistant/homeassistant/components/recorder/pool.py", line 96, in _do_get
homeassistant  |     return super()._do_get()
homeassistant  |            ~~~~~~~~~~~~~~~^^
homeassistant  |   File "/usr/local/lib/python3.13/site-packages/sqlalchemy/pool/impl.py", line 429, in _do_get
homeassistant  |     c = self._create_connection()
homeassistant  |   File "/usr/local/lib/python3.13/site-packages/sqlalchemy/pool/base.py", line 390, in _create_connection
homeassistant  |     return _ConnectionRecord(self)
homeassistant  |   File "/usr/local/lib/python3.13/site-packages/sqlalchemy/pool/base.py", line 675, in __init__
homeassistant  |     self.__connect()
homeassistant  |     ~~~~~~~~~~~~~~^^
homeassistant  |   File "/usr/local/lib/python3.13/site-packages/sqlalchemy/pool/base.py", line 915, in __connect
homeassistant  |     )._exec_w_sync_on_first_run(self.dbapi_connection, self)
homeassistant  |       ~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
homeassistant  |   File "/usr/local/lib/python3.13/site-packages/sqlalchemy/event/attr.py", line 483, in _exec_w_sync_on_first_run
homeassistant  |     self(*args, **kw)
homeassistant  |     ~~~~^^^^^^^^^^^^^
homeassistant  |   File "/usr/local/lib/python3.13/site-packages/sqlalchemy/event/attr.py", line 497, in __call__
homeassistant  |     fn(*args, **kw)
homeassistant  |     ~~^^^^^^^^^^^^^
homeassistant  |   File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 1392, in _setup_recorder_connection
homeassistant  |     if database_engine := setup_connection_for_dialect(
homeassistant  |                           ~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
homeassistant  |         self,
homeassistant  |         ^^^^^
homeassistant  |     ...<2 lines>...
homeassistant  |         not self._completed_first_database_setup,
homeassistant  |         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
homeassistant  |     ):
homeassistant  |     ^
homeassistant  |   File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 471, in setup_connection_for_dialect
homeassistant  |     execute_on_connection(dbapi_connection, "PRAGMA journal_mode=WAL")
homeassistant  |     ~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
homeassistant  |   File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 326, in execute_on_connection
homeassistant  |     cursor.execute(statement)
homeassistant  |     ~~~~~~~~~~~~~~^^^^^^^^^^^
homeassistant  | sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) database is locked
homeassistant  | (Background on this error at: https://sqlalche.me/e/20/e3q8)
homeassistant  | 2025-08-21 19:46:05.279 ERROR (Recorder) [homeassistant.components.recorder.util] The database at //config/home-assistant_v2.db is corrupt or malformed