Core: ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (sqlite3.OperationalError) unable to open database file

Created on 6 Feb 2018  路  28Comments  路  Source: home-assistant/core

Home Assistant release (hass --version):
0.62.1

Python release (python3 --version):
Python 3.5.3

Component/platform:
homeassistant.components.recorder

Description of problem:
The Recorder component throws a DB error if run as a daemon

Feb 06 21:22:50 raspberrypi hass[4865]: 2018-02-06 21:22:50 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (sqlite3.OperationalError) unable to open database file (Background on this error at: http://sqlalche.me/e/e3q8) (retrying in 3 seconds)

as a result we see

Invalid config
The following components and platforms could not be set up:
    history
    recorder
    logbook

Expected:
Should work fine even with "Autostart using systemd"

Problem-relevant configuration.yaml entries and steps to reproduce:

Auto generated configuration.yaml after a fresh install.
  1. Install on Raspbian Stretch Lite following the instructions at https://home-assistant.io/docs/installation/raspberry-pi/
  2. Set to auto run using the instructions at https://home-assistant.io/docs/autostart/systemd/
  3. Reboot Pi
  4. Check Frontend, the following message will be displayed
Invalid config
The following components and platforms could not be set up:
    history
    recorder
    logbook
  1. Instead of setting this up as a daemon, if you run hass from bash it works fine!

Traceback (if applicable):

Feb 06 21:22:46 raspberrypi hass[4865]: 2018-02-06 21:22:46 INFO (MainThread) [homeassistant.setup] Setup of domain frontend took 0.1 seconds.
Feb 06 21:22:46 raspberrypi hass[4865]: 2018-02-06 21:22:46 INFO (MainThread) [homeassistant.core] Bus:Handling <Event component_loaded[L]: component=frontend>
Feb 06 21:22:47 raspberrypi hass[4865]: 2018-02-06 21:22:47 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (sqlite3.OperationalError) unable to open database file (Background on this error at: http://sqlalche.me/e/e3q8) (retrying in 3 seconds)
Feb 06 21:22:50 raspberrypi hass[4865]: 2018-02-06 21:22:50 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (sqlite3.OperationalError) unable to open database file (Background on this error at: http://sqlalche.me/e/e3q8) (retrying in 3 seconds)

Additional info:
Similar issue https://github.com/home-assistant/home-assistant/issues/4780
The changes at https://github.com/home-assistant/home-assistant/pull/6519/files are on my system.

recorder stale

Most helpful comment

I had a similar problem with homeassistant, running on my mac, and I used "opensnoop" to see which files it was trying to open at startup:

that showed it was trying to access:
/Users/ryan/dev/homeassistant/Users/ryan/.homeassistant/history.sqlite.db

... my configuration.yaml file has:
recorder:
db_url: sqlite:///Users/ryan/.homeassistant/history.sqlite.db

... clearly it's using a relative path (maybe because of virtualenv?)
Changing configuration.yaml to:
db_url: sqlite:///history.sqlite.db

fixed my problem.

All 28 comments

This seems to be a permission issue. Maybe you are running hass in console as root, while via systemd as non privileged user. Once you have created the sqlite file as root the other user cannot write it.

try:

chown -R homeassistant. /installation/directory

and restart

homeassistant@raspberrypi:/home/pi $ stat /srv/homeassistant/
  File: /srv/homeassistant/
  Size: 4096            Blocks: 8          IO Block: 4096   directory
Device: b302h/45826d    Inode: 128336      Links: 6
Access: (0755/drwxr-xr-x)  Uid: (  999/homeassistant)   Gid: (  996/homeassistant)
Access: 2018-02-06 17:55:46.647128687 +0000
Modify: 2018-02-06 17:57:45.814842102 +0000
Change: 2018-02-06 17:57:45.814842102 +0000
 Birth: -
homeassistant@raspberrypi:/home/pi $ stat /home/homeassistant/.homeassistant/home-assistant_v2.db
  File: /home/homeassistant/.homeassistant/home-assistant_v2.db
  Size: 143360          Blocks: 288        IO Block: 4096   regular file
Device: b302h/45826d    Inode: 128435      Links: 1
Access: (0644/-rw-r--r--)  Uid: (  999/homeassistant)   Gid: (  996/homeassistant)
Access: 2018-02-06 21:28:00.122877910 +0000
Modify: 2018-02-06 22:12:45.947201956 +0000
Change: 2018-02-06 22:12:45.947201956 +0000
 Birth: -

This happens also for me.
If I go into env and "source bin/activate" and then start "bin/hass" within e.g. screen everything is working.

Change user in systemd service configuration file:
User=homeassistant

Thanks, @dellamorte-dellamore :+1:

I'm getting the exact same error but after an upgrade. I tried what @dellamorte-dellamore suggested but it didn't work for me.

The upgrade was from 0.64.2 to 0.65.5.

Home Assistant release (hass --version):
0.62.1

Python release (python3 --version):
Python 3.6.4

You have a problem with file ownership and hass user most probably. Go into your .homeassistant directory and run:
ls -la
Paste results here...

That's what I get:

root@rpi:/home/homeassistant/.homeassistant# ls -la
total 21672
drwxr-xr-x 5 homeassistant homeassistant     4096 Mar 15 13:04 .
drwxr-xr-x 6 homeassistant homeassistant     4096 Mar 15 12:55 ..
-rw-r--r-- 1 homeassistant homeassistant        2 Mar  8 19:16 automations.yaml
drwxr-xr-x 2 homeassistant homeassistant     4096 Mar  8 22:10 .cloud
-rw-r--r-- 1 homeassistant homeassistant     2044 Mar 15 10:14 configuration.yaml
-rw-r--r-- 1 homeassistant homeassistant        1 Mar  9 18:07 customize.yaml
drwxr-xr-x 2 homeassistant homeassistant     4096 Mar  8 19:16 deps
-rw-r--r-- 1 homeassistant homeassistant     1305 Mar 15 11:56 entity_registry.yaml
-rw-r--r-- 1 homeassistant homeassistant        0 Mar  8 19:16 groups.yaml
-rw-r--r-- 1 homeassistant homeassistant     2087 Mar  8 19:21 harmony_harmony_hub.conf
-rw-r--r-- 1 homeassistant homeassistant        6 Mar 15 12:55 .HA_VERSION
-rw-r--r-- 1 homeassistant homeassistant     4218 Mar 15 13:14 home-assistant.log
-rw-r--r-- 1 homeassistant homeassistant 22105088 Mar 15 11:14 home-assistant_v2.db
-rw-r--r-- 1 homeassistant homeassistant     1037 Mar  9 22:19 .ios.conf
-rw-r--r-- 1 root          root                 0 Mar 15 10:16 known_devices.yaml
-rw-r--r-- 1 homeassistant homeassistant       74 Mar  8 22:16 phue-001788715d05.conf
-rw-r--r-- 1 homeassistant homeassistant      124 Mar 15 13:10 plex.conf
-rw-r--r-- 1 root          root               938 Mar 15 09:56 scripts.yaml
-rw-r--r-- 1 homeassistant homeassistant      167 Mar  9 10:09 secrets.yaml
drwxr-xr-x 2 homeassistant homeassistant     4096 Mar 15 11:09 tts
-rw-r--r-- 1 homeassistant homeassistant       44 Mar  8 19:17 .uuid
-rw-r--r-- 1 homeassistant homeassistant       52 Mar 15 11:08 webostv.conf

Also ls -lad can be useful

Seems fine. Also, can you paste content of systemd service file:

cat /etc/systemd/system/[email protected]

(or whatever is the name of the service)

@AlessandroLorenzi the result of ls -lad:

root@rpi:/home/homeassistant/.homeassistant# ls -lad
drwxr-xr-x 5 homeassistant homeassistant 4096 Mar 15 13:04 .

@dellamorte-dellamore the content of the service file:

root@rpi:/home/homeassistant/.homeassistant# cat /etc/systemd/system/[email protected]
[Unit]
Description=Home Assistant
After=network-online.target

[Service]
Type=simple
User=homeassistant
ExecStart=/srv/homeassistant/bin/hass -c "/home/homeassistant/.homeassistant"

[Install]
WantedBy=multi-user.target

@AlessandroLorenzi Everything seems OK here. You can eventually run:
#chown homeassitant:homeassistant /home/homeassistant/homeassistant/*
to fix those two files owned by root.

And to fix (if there's any) permitions in /srv/homeassistant directory run:
#chown -R homeassistant:homeassistant /srv/homeassistant/

I suggest you to use pi account with sudo when needed, and for upgrading /changing things in homeassitant to switch to homeassistant user and virtual enviroment:
$sudo su -s /bin/bash homeassistant
$source /srv/homeassistant/bin/activate
If you run everything as root user it will eventually create files which will be unaccessible by other users.

I tried both permissions commands but the issue is still there, same error as before about sqlite. Any idea about what I could do next?

I've got same problem, seems to be something related to DB init; when setting

recorder:
  purge_keep_days: 90
  db_url: sqlite:///tmp/rr.sqlite

user running it can create that file, yet I get errors:

hass[6231]: ERROR:homeassistant.components.recorder:Error during connection setup: (sqlite3.OperationalError) unable to open database file (Background on this error at: http://sqlalche.me/e/e3q8) (retrying in 3 seconds)
hass[6231]: INFO:homeassistant.core:Bus:Handling <Event system_log_event[L]: source=components/recorder/__init__.py, message=Error during connection setup: (sqlite3.OperationalError) unable to open database file (Background on this error at: http://sqlalche.me/e/e3q8) (retrying in 3 seconds), timestamp=1524185393.0858438, exception=, level=ERROR>

Now when i copy db from default location:

cp home-assistant_v2.db /tmp/rr.sqlite

it starts working, which leads me to believe that for some reason it is not initialized properly if location is non-default

I was having the same issue and found this:

https://github.com/sloria/TextBlob/issues/173

Basically run this install:
sudo apt-get install libsqlite3-dev

Now in the downloaded python source rebuild and install python with the following command:
./configure --enable-loadable-sqlite-extensions && make && sudo make install

After doing this I reinstalled homeassisant and the error was gone

There hasn't been any activity on this issue recently. Due to the high number of incoming GitHub notifications, we have to clean some of the old issues, as many of them have already been resolved with the latest updates.

Please make sure to update to the latest Home Assistant version and check if that solves the issue. Let us know if that works for you by adding a comment :+1:

I had a similar problem with homeassistant, running on my mac, and I used "opensnoop" to see which files it was trying to open at startup:

that showed it was trying to access:
/Users/ryan/dev/homeassistant/Users/ryan/.homeassistant/history.sqlite.db

... my configuration.yaml file has:
recorder:
db_url: sqlite:///Users/ryan/.homeassistant/history.sqlite.db

... clearly it's using a relative path (maybe because of virtualenv?)
Changing configuration.yaml to:
db_url: sqlite:///history.sqlite.db

fixed my problem.

I also have this problem with last releases of HA, 0,79.3, 0.80.0, 0,80.1 running on RPI3 (hassbian).
This morning in the HA log:

[homeassistant.components.recorder.util] Error executing query: (sqlite3.OperationalError) database is locked (Background on this error at: http://sqlalche.me/e/e3q8)

Is there a solution or fix?

I also have this problem with last releases of HA, 0,79.3, 0.80.0, 0,80.1 running on RPI3 (hassbian).
This morning in the HA log:

[homeassistant.components.recorder.util] Error executing query: (sqlite3.OperationalError) database is locked (Background on this error at: http://sqlalche.me/e/e3q8)

Is there a solution or fix?

Same here. Everything operates fine for about 1-3 days, and suddenly the GUI stops. Here's my status from systemctl. Version 0.79.3 (I haven't had a gap to upgrade and try on latest). Was never a problem on 0.69.x before.

Dec 04 20:40:19 xxxxxx hass[11985]:   File "/srv/homeassistant/lib/python3.5/site-packages/urllib3/connection.py", line 141, in _new_conn
Dec 04 20:40:19 xxxxxx hass[11985]:     (self.host, self.port), self.timeout, **extra_kw)
Dec 04 20:40:19 xxxxxx hass[11985]:   File "/srv/homeassistant/lib/python3.5/site-packages/urllib3/util/connection.py", line 83, in create_connection
Dec 04 20:40:19 xxxxxx hass[11985]:     raise err
Dec 04 20:40:19 xxxxxx hass[11985]:   File "/srv/homeassistant/lib/python3.5/site-packages/urllib3/util/connection.py", line 64, in create_connection
Dec 04 20:40:19 xxxxxx hass[11985]:     sock = socket.socket(af, socktype, proto)
Dec 04 20:40:19 xxxxxx hass[11985]:   File "/usr/lib/python3.5/socket.py", line 134, in __init__
Dec 04 20:40:43 xxxxxx hass[11985]: 2018-12-04 20:40:43 ERROR (MainThread) [homeassistant.components.telegram_bot.polling] Cannot connect to host api.telegram.org:443 ssl:None [Too many open files]
Dec 04 20:40:44 xxxxxx hass[11985]: 2018-12-04 20:40:44 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (sqlite3.OperationalError) unable to open database file (Background on this error
Dec 04 20:40:44 xxxxxx hass[11985]: 2018-12-04 20:40:44 ERROR (Recorder) [homeassistant.components.recorder] Error in database connectivity: (sqlite3.OperationalError) unable to open database file (Background on this e
~

This is still an active issue.

I'm using a fresh homeassistant installation in a python virtual env on ubuntu.

Me too have the error:
Error in database connectivity: (sqlite3.OperationalError) disk I/O error (Background on this error at: http://sqlalche.me/e/e3q8
on Hass.io installation, HA running is 0.87.0

had the same issue with sqlite running Hass.io installation version 0.87.0. I opened the DB file in DB browser for sqlite and executed the Vacuum command. I restarted homeassistant and it cleared my issue

Thanks @ryancnelson
I am migrating from HASSIO on PI3B+ to a docker environment and this fixed my Issue right up

Changing configuration.yaml to:
db_url: sqlite:///history.sqlite.db

There hasn't been any activity on this issue recently. Due to the high number of incoming GitHub notifications, we have to clean some of the old issues, as many of them have already been resolved with the latest updates.
Please make sure to update to the latest Home Assistant version and check if that solves the issue. Let us know if that works for you by adding a comment 馃憤
This issue now has been marked as stale and will be closed if no further activity occurs. Thank you for your contributions.

same error :(

For what it's worth: I struggled with this problem too. I had my db on a USB drive to limit the wear on the RasPi SD card. When I moved the db back to the SD, it worked fine. I then tried to copy it back to the USB drive, which gave me the error that there wasn't any space available - while I still had many GB's of space left. Turns out I reached my maximum inode limit, because I also used the drive to store the output of some logging scripts.

You can easily check disk and inode usage with:
df -h /path/to/foo
df -hi /path/to/foo

After increasing the number of inodes (did require a format), the issue was resolved for me.

@Epyon01P just using xfs solves that, if you already have to reformat might as well

There hasn't been any activity on this issue recently. Due to the high number of incoming GitHub notifications, we have to clean some of the old issues, as many of them have already been resolved with the latest updates.
Please make sure to update to the latest Home Assistant version and check if that solves the issue. Let us know if that works for you by adding a comment 馃憤
This issue now has been marked as stale and will be closed if no further activity occurs. Thank you for your contributions.

Was this page helpful?
0 / 5 - 0 ratings