Home assistant database locked again

Configuration
1

This morning I noticed database lock errors in my log. So I deleted the db and restarted. Now it’s happening again this afternoon:

Error in database connectivity: (sqlite3.OperationalError) database is locked (Background on this error at: http://sqlalche.me/e/e3q8). (retrying in 3 seconds)

3:46 PM components/recorder/__init__.py (ERROR)

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

3:46 PM components/recorder/util.py (ERROR)

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

2:54 PM components/recorder/util.py (ERROR)

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

2:54 PM components/recorder/util.py (ERROR)

Any tips for resolving this?

The database is growing at about 4MB per hour. Is this excessive? I wouldn’t have thought so.

2

I assume you have looked at the url in the error message.

sqlite is a single user and quite simple database. It can get overwhelmed by too many operations. Particularly on a RPI (if that is what you are using).

Sounds like your system is quite busy. I got this when I started using the ubiquiti presence detector, there was constant writing to the db and I gave up on it.

Solutions?

  • a more mature db engine like mariadb.
  • a better equipped machine (I have gone to a NUC).
  • make your machine do less.
3

I wouldn’t have thought so:

And as I said about 4MB per hour for DB growth.

Yes it is a raspi 3B.

4

Well something is preventing home assistant from accessing the db. There are plenty of reasons that might be so, failing disk, system too busy, too many processes trying to write simultaneously. Hard to say.

5

I’ve been watching the db files. They do take a long time to write sometimes.

So I installed the MariaDB addon. And could not get permission for HA to connect to it. Can anyone see what I’ve done wrong?

configuration.yaml

recorder:
  db_url: mysql://hass:[my_password]@core-mariadb/homeassistant
  purge_interval: 1
  purge_keep_days: 7

MariaDB addon config

{
  "databases": [
    "homeassistant"
  ],
  "logins": [
    {
      "username": "hass",
      "host": "core-mariadb",
      "password": "[my_password]"
    }
  ],
  "rights": [
    {
      "username": "hass",
      "host": "core-mariadb",
      "database": "homeassistant",
      "grant": "ALL PRIVILEGES ON"
    }
  ]
}

EDIT: OK, solved that by using ‘%’ as the host.

Now I get the following error:

Database requires upgrade. Schema version: None

6

It gives that error only on first restart because it’s a new dB.

I used homeassistant as the host as per the docs… I don’t want anything else to access it.

1 Like
7

Hmmm the docs say:

If you want to only connect from inside Home Assistant use core-mariadb as the host address.

8

See I took that to mean only in the URL. I just followed the default config and then they give the URL to use for that configuration.

9

Ah, OK. I changed it and restarted the DB and HA. All is well.

In fact it is better than well.

For the first time in longer than I can remember the only warnings I have in the log are for custom components. No SSL errors. No xxxx is taking longer than 10s to set up etc…

2 Likes
10

Yeah… my errors in the log are down to nothing as well… so my interpretation was correct then. One of the issues I often have with docs is they will have some random setting and then give no example of how to use it or if there is an example, they don’t use the setting. A classic is when they telly you there are a few different ways to configure something and then give the 3 configurations and they are all ‘different’ which makes it hard to follow… anyway I’m sure I sucked at explaining that… Glad you got it working.

I did agonise over the host but in the end just followed verbatim the example (except username/password). Your approach could just as easily have been right.

11

Since installing MariaDB I have noticed a very worrying trend:

The only other addon I installed was Mosquito mqtt broker v3. Could be that too I suppose.

12

I had been on 0.77.2 and recently added a few history_stats sensors. I did not see this problem. Then yesterday I updated to 0.81.6 and boom!, my log was loaded with these errors! I’ve since disabled discovery (which I have no use for) and slowed down the history_stats sensors (by specifying larger scan_intervals.) Now I still see these errors, but not nearly as often. Maybe finally time to bite the bullet and switch to something other than SQLite.

BTW, now that I’m on 0.81.6 all the timer out of sync errors are gone that I used to see a lot. But of course, they’ve just morphed to events, which I still see. Interestingly, now that the events show how much the timer tick was delayed, I’m amazed that the times are so big. Before turning off discovery it was not uncommon to see delayed ticks of up to 10 seconds! Now I’m seeing fewer and they’re more around 2 seconds. Still, for something that is supposed to happen every second, to be delayed by 2 seconds is nuts! FWIW, I’m on a pi 3B. And here are the events from the last 10 minutes:

pi@raspberrypi:/home/homeassistant/.homeassistant $ grep timer_out_of_sync home-assistant.log
2018-11-08 09:46:04 DEBUG (MainThread) [homeassistant.core] Bus:Handling <Event timer_out_of_sync[L]: seconds=2.3558670060010627>
2018-11-08 09:46:18 DEBUG (MainThread) [homeassistant.core] Bus:Handling <Event timer_out_of_sync[L]: seconds=1.135453613998834>
2018-11-08 09:50:41 DEBUG (MainThread) [homeassistant.core] Bus:Handling <Event timer_out_of_sync[L]: seconds=2.214339808000659>
2018-11-08 09:54:04 DEBUG (MainThread) [homeassistant.core] Bus:Handling <Event timer_out_of_sync[L]: seconds=2.320807561998663>
2018-11-08 09:56:04 DEBUG (MainThread) [homeassistant.core] Bus:Handling <Event timer_out_of_sync[L]: seconds=2.096397927001817>
13

just so I may try that too, why exactly are your settings now? In the addon and for the db_url please…

14

FYI, I went ahead and changed to MariaDB. I don’t use hassio so I had to manually install it and create the database, which took a bit of reading/Googling. After restarting things do seem better. It’s only been a short time, but already I don’t see database errors anymore. Also, I’ve only seen one timer_out_of_sync event each time I restart (which so far only seems to happen during startup), and they’ve been just over 2 seconds. I just enabled the memory_use_percent and processor_use sensors, so I can update you on what I see after a while.

15

@pnbruckner My memory use has levelled off at between 50% and 55% (pi3B with 1GB).

@Mariusthvdb, settings below:

configuration.yaml:

 recorder:
  db_url: !secret mariadb_url
  purge_interval: 1
  purge_keep_days: 7

secrets.yaml:

mariadb_url: mysql://hass:my_redacted_password@core-mariadb/homeassistant

Mariadb config:

{
  "databases": [
    "homeassistant"
  ],
  "logins": [
    {
      "username": "hass",
      "host": "homeassistant",
      "password": "my_redacted_password"
    }
  ],
  "rights": [
    {
      "username": "hass",
      "host": "homeassistant",
      "database": "homeassistant",
      "grant": "ALL PRIVILEGES ON"
    }
  ]
}
16

thanks, i have same settings except use % for host. Thought that to be necessary when using more than 1 instance accessing the db. (I have 2 HA setups)

do you logbook and history tabs work alright? Mine grind the Ha instance to hold due to memory depletion…

17

They load but take longer than I’d like (10-20s). Loading them takes my memory use from ~ 55% to ~ 65%.

I do make extensive use of the exclude option in both history and logbook though.

18

A yes, tried that too.
but I think I learned that doesn’t make any difference on the back end , only doesn’t show it in the frontend, while backend DB records anything. not sure though.

I use includes in recorder and history to try to calm it down, but it doesn’t really help at all.

19

Where do you get that nonsense from?

1 Like
20

FWIW, here’s my memory and CPU usage over the last week:

image
image1427×166 18.3 KB

Memory usage is, of course, the top line, and starts around 36% and ends around 42%. Haven’t paid close enough attention to know what was happening during the temporarily increased usage periods.

1 Like