Recorder errors

Configuration
1

Keep getting this error in my Log

Log Details (ERROR)
Logger: homeassistant.components.recorder.util
Source: components/recorder/util.py:87
Integration: Recorder (documentation, issues)
First occurred: 11:11:38 (24 occurrences)
Last logged: 11:23:34

Error executing query: (sqlite3.OperationalError) database is locked (Background on this error at: http://sqlalche.me/e/e3q8)

and my yaml

recorder:
  purge_keep_days: 1
  exclude:
    domains:
      - automation
      - updater
    entities:
      - sun.sun # Don't record sun data

Any ideas?

2

Your database is corrupt. Delete it (config/home-assistant_v2.db) and restart is the easy way, though you will lose your history data.

Or if your data is important enough to you that you want to attempt to repair the database there are some tips lower down in this topic:

1 Like
3

Hi,

Did as you said but it has come back.

Log Details (ERROR)

Logger: homeassistant.components.recorder.util
Source: components/recorder/util.py:87
Integration: Recorder (documentation, issues)
First occurred: 13:55:15 (41 occurrences)
Last logged: 16:20:09

Error executing query: (sqlite3.OperationalError) database is locked (Background on this error at: http://sqlalche.me/e/e3q8)

What could be causing it?

4

Did you delete the DB temporary files as well?

Delete, config/home-assistant_v2.*

Then restart.

5

I see
home-assistant_v2.db

And these two keep coming and going every few seconds

home-assistant_v2.db-wal
home-assistant_v2.db-shm

are these the temporary files?

image

6

They are the ones but if they are disappearing then everything is working as expected. I thought one might be stuck and not being removed automatically after the database has been written to. So leave them.

Do you have any other particularly chatty sensors that could be hogging the database writes that you could exclude?

Other than that, I’m out of ideas.

7

That is very strange. Searching the forums a bit I turned up this post with some thoughts. Did you use the sqlite3 command to dig into the db by any chance? Sounds like that was the root cause of the issue there.

Also alternatively have you considered using the mariadb addon instead? I switched a while ago and it got rid of a lot of start-up performance warnings for me so it might be a win-win to just take this opportunity to switch.

1 Like
8

Yeah the MariaDB addon is definitely worth considering.

9

Thanks for the feedback.

I have InfluxDB installed for Grafana and MariaDB installed as a prerequisite for NGINX proxy manager.

What are the advantages of Using the MariaDB, I know logbook and History take so long to load that they are next to useless?

10

MariaDB seems to use more memory to cache, producing a more responsive database. I noticed a significant improvement when I was using a pi3.

Also far less prone to corruption.

1 Like
11

So how do I go about it, any special configuration? like I said it is already running.for the NGINX proxy manager.

Is it just

recorder:
  db_url: mysql://homeassistant:password@core-mariadb/homeassistant?charset=utf8

in the config.yaml?

recorder:
  purge_keep_days: 1
  exclude:
    domains:
      - automation
      - updater
    entities:
      - sun.sun # Don't record sun data
12

Well what can I say, switched to MariaDB and the difference is like night and day.

logbook, History load much faster and even my wall mounted tablet is far more responsive AND no more errors.

Thanks,

3 Likes
13

What directions did you follow to switch to mariadb? I know how to install the Maria add-on. Not sure how to turn the SQL off and what is recommended other than the default settings for Mariadb.

14

It’s just a matter of changing your recorder URL. Look in the MariaDB addon, at the bottom of the documentation page it gives an example.

15

Just default config in MariaDB

databases:
  - homeassistant
logins:
  - username: homeassistant
    password: password
rights:
  - username: homeassistant
    database: homeassistant

and configuration.yaml

recorder:
  db_url: mysql://homeassistant:password@core-mariadb/homeassistant?charset=utf8

  purge_keep_days: 1
  exclude:
    domains:

      - updater
      - media_player

    entities:
      - sun.sun # Don't record sun data

Regards,

1 Like
16

Thanks. I’ll start working on it. :worried:

17

It worked, very easy. I need to understand the excludes, includes? For some reason none of my Wyze sensors are showing in the logbook. Then I can follow Tom_I guide on how to reduce size and extend life of SD card.

18

I set up my DB using your recorder statement:

recorder:
  db_url: mysql://homeassistant:password@core-mariadb/homeassistant?charset=utf8

Where is the database stored?

19

I do not know, have looked before but it must be stored on another partition?

20

Looking with both SSH and Samba I can’t find it.Maybe someone else can help? I wanted to see the size compared to the SQLlite I was using.