MarkB1
(Mark Booth)
June 16, 2020, 10:57am
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?
tom_l
June 16, 2020, 12:09pm
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:
Hi, after some hussel around sd card images (i.e. restoring snapshots) and some updates this errors occures:
2018-11-01 22:37:01 ERROR (SyncWorker_4) [homeassistant.components.recorder.util] Error executing query: (sqlite3.DatabaseError) database disk image is malformed [SQL: 'SELECT events.event_id AS events_event_id, events.event_type AS events_event_type, events.event_data AS events_event_data, events.origin AS events_origin, events.time_fired AS events_time_fired, events.created AS events_c…
1 Like
MarkB1
(Mark Booth)
June 16, 2020, 3:51pm
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?
tom_l
June 16, 2020, 4:24pm
4
Did you delete the DB temporary files as well?
Delete, config/home-assistant_v2.*
Then restart.
MarkB1
(Mark Booth)
June 16, 2020, 5:09pm
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?
tom_l
June 16, 2020, 6:58pm
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.
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
tom_l
June 16, 2020, 7:14pm
8
Yeah the MariaDB addon is definitely worth considering.
MarkB1
(Mark Booth)
June 16, 2020, 7:56pm
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?
tom_l
June 16, 2020, 8:08pm
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
MarkB1
(Mark Booth)
June 16, 2020, 8:31pm
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
MarkB1
(Mark Booth)
June 17, 2020, 9:40am
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
bschatzow
(Bill Schatzow)
June 17, 2020, 3:46pm
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.
tom_l
June 17, 2020, 4:00pm
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.
MarkB1
(Mark Booth)
June 17, 2020, 8:14pm
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
bschatzow
(Bill Schatzow)
June 18, 2020, 12:51am
16
Thanks. I’ll start working on it.
bschatzow
(Bill Schatzow)
June 18, 2020, 3:39pm
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.
bschatzow
(Bill Schatzow)
June 24, 2020, 9:06am
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?
MarkB1
(Mark Booth)
June 24, 2020, 9:36am
19
I do not know, have looked before but it must be stored on another partition?
bschatzow
(Bill Schatzow)
June 24, 2020, 9:45am
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.