Restored backup does not boot -- error in line 10 of configurations.yaml

[emefff]

Good morning,

yesterday’s upgrade of HA core has given me nothing but trouble. It seems it crashed during the upgrade, after a fresh install of virtualbox I got it running again. I overlooked that all energy data history was lost.
Today I wanted to restore an old backup to get the energy data back, but that did not work. I keep up to 20 backups going a few days back, I tried restoring three times, nothing worked.

I always get the same error:

I haven’t looked at the configurations.yaml for months now, I do not see how this could be wrong:

# Loads default set of integrations. Do not remove.
default_config:

# Text to speech
tts:
  - platform: google_translate

automation: !include automations.yaml
script: !include scripts.yaml
scene: !include scenes.yaml

energy:
  type: energy-solar-consumed-gauge
  

recorder:
  purge_keep_days: 60

Any help appreciated, unfortunately I do not have much time to solve this,

thanks,

Mario.

[emefff]

If I comment line 10 and 11 (include scripts and include scenes line) it boots, but a lot of stuff isn’t loaded:

So, obviously, this can’t be a solution…

[emefff]

In the meantime I created a new virtual machine with the newest .vdi file. When restoring a backup to this, I get the same error! Please help, I am at the end of my capabilites and understanding what is happening here. If I have to recreate all from scratch, this will take me a week.

Thanks,

Mario.

[emefff]

Ok, I created an empty scripts.yaml and an empty scenes.yaml. It is booting.

Starting up the recorder takes forever, and looking at the log I think that my db is somehow corrupted.

So I will try to repair the database file.

2024-01-14 13:15:12.027 WARNING (SyncWorker_2) [homeassistant.loader] We found a custom integration noaa_space_weather which has not been tested by Home Assistant. This component might cause stability problems, be sure to disable it if you experience issues with Home Assistant
2024-01-14 13:15:13.340 WARNING (Recorder) [homeassistant.components.recorder.util] The system could not validate that the sqlite3 database at //config/home-assistant_v2.db was shutdown cleanly
2024-01-14 13:15:13.380 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (sqlite3.DatabaseError) database disk image is malformed
[SQL: SELECT states.state_id 
FROM states 
WHERE states.context_id_bin IS NULL
 LIMIT ? OFFSET ?]
[parameters: (1, 0)]
(Background on this error at: https://sqlalche.me/e/20/4xp6)
2024-01-14 13:15:13.481 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (sqlite3.DatabaseError) database disk image is malformed
[SQL: SELECT states.state_id 
FROM states 
WHERE states.context_id_bin IS NULL
 LIMIT ? OFFSET ?]
[parameters: (1, 0)]
(Background on this error at: https://sqlalche.me/e/20/4xp6)
2024-01-14 13:15:13.582 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (sqlite3.DatabaseError) database disk image is malformed
[SQL: SELECT states.state_id 
FROM states 
WHERE states.context_id_bin IS NULL
 LIMIT ? OFFSET ?]
[parameters: (1, 0)]
(Background on this error at: https://sqlalche.me/e/20/4xp6)
2024-01-14 13:15:13.582 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (sqlite3.DatabaseError) database disk image is malformed
[SQL: SELECT states.state_id 
FROM states 
WHERE states.context_id_bin IS NULL
 LIMIT ? OFFSET ?]
[parameters: (1, 0)]
(Background on this error at: https://sqlalche.me/e/20/4xp6)
Traceback (most recent call last):
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1969, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute
    cursor.execute(statement, parameters)
sqlite3.DatabaseError: database disk image is malformed

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

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 139, in session_scope
    yield session
  File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 793, in _activate_and_set_db_ready
    or execute_stmt_lambda_element(
       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 228, in execute_stmt_lambda_element
    executed = session.execute(stmt)
               ^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 2308, in execute
    return self._execute_internal(
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 2190, in _execute_internal
    result: Result[Any] = compile_state_cls.orm_execute_statement(
                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/context.py", line 293, in orm_execute_statement
    result = conn.execute(
             ^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1416, in execute
    return meth(
           ^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/sql/lambdas.py", line 604, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1639, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1848, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1988, in _exec_single_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2343, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1969, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.DatabaseError: (sqlite3.DatabaseError) database disk image is malformed
[SQL: SELECT states.state_id 
FROM states 
WHERE states.context_id_bin IS NULL
 LIMIT ? OFFSET ?]
[parameters: (1, 0)]
(Background on this error at: https://sqlalche.me/e/20/4xp6)
2024-01-14 13:15:13.615 ERROR (Recorder) [homeassistant.components.recorder.core] Recorder._run threw unexpected exception, recorder shutting down
Traceback (most recent call last):
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1969, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute
    cursor.execute(statement, parameters)
sqlite3.DatabaseError: database disk image is malformed

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 699, in run
    self._run()
  File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 742, in _run
    self._activate_and_set_db_ready()
  File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 793, in _activate_and_set_db_ready
    or execute_stmt_lambda_element(
       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 228, in execute_stmt_lambda_element
    executed = session.execute(stmt)
               ^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 2308, in execute
    return self._execute_internal(
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 2190, in _execute_internal
    result: Result[Any] = compile_state_cls.orm_execute_statement(
                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/context.py", line 293, in orm_execute_statement
    result = conn.execute(
             ^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1416, in execute
    return meth(
           ^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/sql/lambdas.py", line 604, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1639, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1848, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1988, in _exec_single_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2343, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1969, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.DatabaseError: (sqlite3.DatabaseError) database disk image is malformed
[SQL: SELECT states.state_id 
FROM states 
WHERE states.context_id_bin IS NULL
 LIMIT ? OFFSET ?]
[parameters: (1, 0)]
(Background on this error at: https://sqlalche.me/e/20/4xp6)
2024-01-14 13:15:23.321 WARNING (MainThread) [homeassistant.setup] Setup of recorder is taking over 10 seconds.
2024-01-14 13:16:12.333 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
2024-01-14 13:17:12.418 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
2024-01-14 13:18:12.489 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
2024-01-14 13:19:12.558 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
2024-01-14 13:20:12.638 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
2024-01-14 13:20:13.321 ERROR (MainThread) [homeassistant.setup] Setup of 'recorder' is taking longer than 300 seconds. Startup will proceed without waiting any longer
2024-01-14 13:20:19.472 ERROR (MainThread) [homeassistant.setup] Unable to set up dependencies of 'filter'. Setup failed for dependencies: recorder
2024-01-14 13:20:19.472 ERROR (MainThread) [homeassistant.setup] Unable to prepare setup for platform 'filter.sensor': (DependencyError(...), 'Could not setup dependencies: recorder')
2024-01-14 13:20:19.836 ERROR (MainThread) [homeassistant.setup] Unable to set up dependencies of 'logbook'. Setup failed for dependencies: recorder
2024-01-14 13:20:19.837 ERROR (MainThread) [homeassistant.setup] Setup failed for 'logbook': (DependencyError(...), 'Could not setup dependencies: recorder')
2024-01-14 13:20:19.889 ERROR (MainThread) [homeassistant.setup] Unable to set up dependencies of 'history'. Setup failed for dependencies: recorder
2024-01-14 13:20:19.889 ERROR (MainThread) [homeassistant.setup] Setup failed for 'history': (DependencyError(...), 'Could not setup dependencies: recorder')
2024-01-14 13:20:22.071 ERROR (MainThread) [homeassistant.setup] Unable to set up dependencies of 'energy'. Setup failed for dependencies: history, recorder
2024-01-14 13:20:22.071 ERROR (MainThread) [homeassistant.setup] Setup failed for 'energy': (DependencyError(...), 'Could not setup dependencies: history, recorder')
2024-01-14 13:20:24.955 ERROR (MainThread) [homeassistant.setup] Unable to set up dependencies of 'default_config'. Setup failed for dependencies: energy, history, logbook
2024-01-14 13:20:24.956 ERROR (MainThread) [homeassistant.setup] Setup failed for 'default_config': (DependencyError(...), 'Could not setup dependencies: energy, history, logbook')
2024-01-14 13:20:25.306 WARNING (MainThread) [aioesphomeapi.reconnect_logic] Can't connect to ESPHome API for esphome--14 @ 192.168.178.165: Error connecting to [AddrInfo(family=<AddressFamily.AF_INET: 2>, type=<SocketKind.SOCK_STREAM: 1>, proto=6, sockaddr=IPv4Sockaddr(address='192.168.178.165', port=6053))]: [Errno 111] Connect call failed ('192.168.178.165', 6053) (SocketAPIError)
2024-01-14 13:20:25.427 WARNING (MainThread) [homeassistant.components.sensor] Entity sensor.universitat_graz_sonnenschein (<class 'homeassistant.components.scrape.sensor.ScrapeSensor'>) is using native unit of measurement 'min/h' which is not a valid unit for the device class ('duration') it is using; expected one of ['h', 's', 'ms', 'd', 'min']; Please update your configuration if your entity is manually configured, otherwise create a bug report at https://github.com/home-assistant/core/issues?q=is%3Aopen+is%3Aissue+label%3A%22integration%3A+scrape%22
2024-01-14 13:20:25.431 WARNING (MainThread) [homeassistant.components.sensor] Entity sensor.universitat_graz_niederschlag (<class 'homeassistant.components.scrape.sensor.ScrapeSensor'>) is using native unit of measurement 'mm/h' which is not a valid unit for the device class ('precipitation') it is using; expected one of ['mm', 'in', 'cm']; Please update your configuration if your entity is manually configured, otherwise create a bug report at https://github.com/home-assistant/core/issues?q=is%3Aopen+is%3Aissue+label%3A%22integration%3A+scrape%22
2024-01-14 13:20:25.445 WARNING (MainThread) [homeassistant.components.sensor] Entity sensor.schockl_sonnenschein (<class 'homeassistant.components.scrape.sensor.ScrapeSensor'>) is using native unit of measurement 'min/h' which is not a valid unit for the device class ('duration') it is using; expected one of ['h', 's', 'ms', 'd', 'min']; Please update your configuration if your entity is manually configured, otherwise create a bug report at https://github.com/home-assistant/core/issues?q=is%3Aopen+is%3Aissue+label%3A%22integration%3A+scrape%22
2024-01-14 13:20:25.448 WARNING (MainThread) [homeassistant.components.sensor] Entity sensor.schockl_niederschlag (<class 'homeassistant.components.scrape.sensor.ScrapeSensor'>) is using native unit of measurement 'mm/h' which is not a valid unit for the device class ('precipitation') it is using; expected one of ['mm', 'in', 'cm']; Please update your configuration if your entity is manually configured, otherwise create a bug report at https://github.com/home-assistant/core/issues?q=is%3Aopen+is%3Aissue+label%3A%22integration%3A+scrape%22
2024-01-14 13:20:26.271 WARNING (MainThread) [homeassistant.components.sensor] Entity sensor.abstellraum_mq_2_gas_und_rauch_analog (<class 'homeassistant.components.esphome.sensor.EsphomeSensor'>) is using native unit of measurement '%' which is not a valid unit for the device class ('voltage') it is using; expected one of ['mV', 'V']; Please update your configuration if your entity is manually configured, otherwise create a bug report at https://github.com/home-assistant/core/issues?q=is%3Aopen+is%3Aissue+label%3A%22integration%3A+esphome%22
2024-01-14 13:20:26.281 WARNING (MainThread) [homeassistant.components.sensor] Entity sensor.wohnzimmer_mq_2_gas_und_rauch_analog (<class 'homeassistant.components.esphome.sensor.EsphomeSensor'>) is using native unit of measurement '%' which is not a valid unit for the device class ('voltage') it is using; expected one of ['mV', 'V']; Please update your configuration if your entity is manually configured, otherwise create a bug report at https://github.com/home-assistant/core/issues?q=is%3Aopen+is%3Aissue+label%3A%22integration%3A+esphome%22
2024-01-14 13:20:26.382 WARNING (MainThread) [homeassistant.components.sensor] Entity sensor.kueche_mq_2_gas_und_rauch_analog (<class 'homeassistant.components.esphome.sensor.EsphomeSensor'>) is using native unit of measurement '%' which is not a valid unit for the device class ('voltage') it is using; expected one of ['mV', 'V']; Please update your configuration if your entity is manually configured, otherwise create a bug report at https://github.com/home-assistant/core/issues?q=is%3Aopen+is%3Aissue+label%3A%22integration%3A+esphome%22
2024-01-14 13:20:26.466 WARNING (MainThread) [homeassistant.components.sensor] Entity sensor.schlafzimmer_mq_2_gas_und_rauch_analog (<class 'homeassistant.components.esphome.sensor.EsphomeSensor'>) is using native unit of measurement '%' which is not a valid unit for the device class ('voltage') it is using; expected one of ['mV', 'V']; Please update your configuration if your entity is manually configured, otherwise create a bug report at https://github.com/home-assistant/core/issues?q=is%3Aopen+is%3Aissue+label%3A%22integration%3A+esphome%22
2024-01-14 13:20:28.062 WARNING (MainThread) [aioesphomeapi.reconnect_logic] Can't connect to ESPHome API for esphome--8 @ 192.168.178.158: Error connecting to [AddrInfo(family=<AddressFamily.AF_INET: 2>, type=<SocketKind.SOCK_STREAM: 1>, proto=6, sockaddr=IPv4Sockaddr(address='192.168.178.158', port=6053))]: [Errno 113] Connect call failed ('192.168.178.158', 6053) (SocketAPIError)
2024-01-14 13:20:28.318 WARNING (MainThread) [aioesphomeapi.reconnect_logic] Can't connect to ESPHome API for esphome--remote @ 192.168.178.161: Error connecting to [AddrInfo(family=<AddressFamily.AF_INET: 2>, type=<SocketKind.SOCK_STREAM: 1>, proto=6, sockaddr=IPv4Sockaddr(address='192.168.178.161', port=6053))]: [Errno 113] Connect call failed ('192.168.178.161', 6053) (SocketAPIError)
2024-01-14 13:20:28.318 WARNING (MainThread) [aioesphomeapi.reconnect_logic] Can't connect to ESPHome API for esphome--9 @ 192.168.178.159: Error connecting to [AddrInfo(family=<AddressFamily.AF_INET: 2>, type=<SocketKind.SOCK_STREAM: 1>, proto=6, sockaddr=IPv4Sockaddr(address='192.168.178.159', port=6053))]: [Errno 113] Connect call failed ('192.168.178.159', 6053) (SocketAPIError)
2024-01-14 13:20:28.319 WARNING (MainThread) [aioesphomeapi.reconnect_logic] Can't connect to ESPHome API for esphome--7 @ 192.168.178.157: Error connecting to [AddrInfo(family=<AddressFamily.AF_INET: 2>, type=<SocketKind.SOCK_STREAM: 1>, proto=6, sockaddr=IPv4Sockaddr(address='192.168.178.157', port=6053))]: [Errno 113] Connect call failed ('192.168.178.157', 6053) (SocketAPIError)
2024-01-14 13:20:28.383 WARNING (MainThread) [aioesphomeapi.reconnect_logic] Can't connect to ESPHome API for esphome--15 @ 192.168.178.166: Error connecting to [AddrInfo(family=<AddressFamily.AF_INET: 2>, type=<SocketKind.SOCK_STREAM: 1>, proto=6, sockaddr=IPv4Sockaddr(address='192.168.178.166', port=6053))]: [Errno 113] Connect call failed ('192.168.178.166', 6053) (SocketAPIError)
2024-01-14 13:20:28.383 WARNING (MainThread) [aioesphomeapi.reconnect_logic] Can't connect to ESPHome API for esphome--13 @ 192.168.178.164: Error connecting to [AddrInfo(family=<AddressFamily.AF_INET: 2>, type=<SocketKind.SOCK_STREAM: 1>, proto=6, sockaddr=IPv4Sockaddr(address='192.168.178.164', port=6053))]: [Errno 113] Connect call failed ('192.168.178.164', 6053) (SocketAPIError)
2024-01-14 13:20:35.156 WARNING (MainThread) [homeassistant.components.homeassistant.triggers.numeric_state] Error initializing 'Schlafzimmer Lampe grün (Heizkörper Batterie)' trigger: In 'numeric_state' condition: unknown entity sensor.schlafzimmer_heizkorper_battery
2024-01-14 13:20:35.157 WARNING (MainThread) [homeassistant.components.homeassistant.triggers.numeric_state] Error initializing 'Batterie Warnung' trigger: In 'numeric_state' condition: unknown entity sensor.schlafzimmer_heizkorper_battery

[emefff]

In the meantime I dowloaded the db file and tried the following on the host machine:

DB image malformed. How to fix it?

@eddriesen thanks for this! Unfortunately like some others, I was getting a 0 byte fixed database with this method. Using the newer sqlite3 (v3.29.0+) .recover command though, I managed to retrieve a sizable amount. Leaving this here for others (and myself inevitably) in the future: sqlite3 ./home-assistant_v2.db ".recover" | sqlite3 ./home-assistant_v2.db.fix mv ./home-assistant_v2.db ./home-assistant_v2.db.broken mv ./home-assistant_v2.db.fix ./home-assistant_v2.db

I cannot even execute the first command successfully

sqlite3 ./home-assistant_v2.db ".recover" | sqlite3 ./home-assistant_v2.db.fix

it leads to an ‘out of memory’ error. Then I tried the same on a machine with 384GB RAM and got the same error. What a load of crap. This is getting ridiculous.

[emefff]

Due to the database not being recoverable, I ended up deleting it and starting a new one. Better to lose the history than everything else.

A problem persists: My scenes have not been stored in scenes.yaml, I ended up having exactly the same problem like here:

Scenes.yaml blank yet scenes exist and function

I am pulling my hair out at what could be wrong. Note I’ve never put scenes in configuration.yaml. I’ve only ever created scenes via the UI (new user to HA). I created a bunch of scenes and they are in use and working. Today all of a sudden via the UI if I go to the Scene Editor they are all listed but not editable (getting the message that only scenes defined in scenes.yaml are editable). When I look at scenes.yaml it’s blank. The scenes again are not in configuration.yaml either. I can’t find…

So essentially I have to recreate all scenes and probably link the new ones in all automations. Thanks HA!