Error on startup

Configuration
1

This is HomeAssistant on Docker on Ubuntu 20.04
It has been running fine, but latest update to 2021.3.0.

Now it takes 5-10 minutes to come up, and then shows errors, but runs fine it seems.
I see these errors in log:

2021-03-09 18:04:15 WARNING (Recorder) [homeassistant.components.recorder.util] The system could not validate that the sqlite3 database at //config/home-assistant_v2.db was shutdown cleanly.

2021-03-09 18:04:25 WARNING (MainThread) [homeassistant.setup] Setup of recorder is taking over 10 seconds.

2021-03-09 18:05:15 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder

2021-03-09 18:05:39 WARNING (Recorder) [homeassistant.components.recorder.migration] Database is about to upgrade. Schema version: 9

2021-03-09 18:05:39 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding index ix_states_old_state_id to database. Note: this can take several minutes on large databases and slow computers. Please be patient!

2021-03-09 18:05:52 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (sqlite3.DatabaseError) database disk image is malformed

[SQL: CREATE INDEX ix_states_old_state_id ON states (old_state_id)]

(Background on this error at: Error Messages — SQLAlchemy 1.3 Documentation)

2021-03-09 18:05:52 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup to sqlite:////config/home-assistant_v2.db: (sqlite3.DatabaseError) database disk image is malformed

[SQL: CREATE INDEX ix_states_old_state_id ON states (old_state_id)]

Here is configuration.yaml:

`mythuser@amethi:~/homeassistant$ cat configuration.yaml

Configure a default setup of Home Assistant (frontend, api, etc)

default_config:

Uncomment this if you are using SSL/TLS, running in Docker container, etc.

http:

base_url: example.duckdns.org:8123

Text to speech

tts:

  • platform: google_translate

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

panel_custom:

  • name: zwave-graph-panel
    url_path: zwave-graph
    sidebar_title: Z-Wave Graph
    sidebar_icon: mdi:z-wave
    module_url: /local/zwavegraph3.js
    config:

    ranker - pick one: network-simplex, tight-tree, longest-path

    ranker: network-simplex

    edge_visibility - pick one: relevant, all

    edge_visibility: relevant

    grouping - pick one: z-wave, ungrouped

    grouping: z-wave

Example configuration.yaml entry

logbook:

Basic configuration.yaml entry

history:
`

2

Your database is toast (not unheard of in terms of sqllite).

A simple fix is to shutdown HA, delete the database (//config/home-assistant_v2.db), then restart HA. You’ll lose all your history though.

A more complicated fix would be to shutdown HA, copy the database file to your local computer and then use a db tool for sqllite databases (there are tons out there for nearly every operating system) and try to do a repair on it and then copy it back to your HA instance. Then, restart HA. There’s no guarantee this will work however.

I would strongly recommend that you change from using Recorder with sqllite and instead move to a better database engine like Mysql or MariaDB.

3

Thank you very helpful. I will delete the database. Nothing there I need. I do have Mysql running on this machine not in docker, being used for mythtv. Would there be any issues using it also for homeassistant, and can this be done outside docker?

4

Nope. Just follow the instructions here: Recorder - Home Assistant

Basically, create a database in MySql along with a user. Mine is named ha_logger. Make sure to set the permissions so that the new user only has access to that database and nothing else. Then, change the recorder: entry in your configuration.yaml and you should be good to go!

:slight_smile:

5

How would that work if you have the default setup, with docker…?

Because I get this;

rm: cannot remove ‘//config/home-assistant_v2.db’: No such file or directory

6

Thank you, deleting the home-assistant_v2.db worked perfect.