Bug ? Can't use an SQL sensor against home-assistant_v2.db when using docker

I’ve got under ‘sensor’

- platform: sql
#  db_url: sqlite:///home/homeassistant/.homeassistant/home-assistant_v2.db
  db_url: sqlite:///config/home-assistant_v2.db
  scan_interval: 120
  queries:
    - name: Hass DB events
      query: 'SELECT count(*) number_events FROM events;'
      column: number_events
      unit_of_measurement: "#"

It used to work when not using docker, cf the line which is commented out.
Now using docker I’ve got a different path, /config … and an error :

2018-06-06 13:35:23 ERROR (SyncWorker_13) [homeassistant.components.sensor.sql] Couldn't connect using sqlite:///config/home-assistant_v2.db DB_URL: (sqlite3.OperationalError) unable to open database file (Background on this error at: http://sqlalche.me/e/e3q8)

I’m not sure why, the file is there and should be readable.
From docker I can see

homeassistant@hassbian:~/.homeassistant $ docker exec -ti 74197f6b5e4a_home-assistant /bin/bash
bash-4.4# ls -l /config/home-assistant_v2.db
-rw-r--r--    1 1001     1001      75997184 Jun  6 13:47 /config/home-assistant_v2.db
bash-4.4#

any ideas?

from the SQL Sensor page:
“The SQL sensor platform enables you to use values from an SQL database”
the .db file is not a SQL DB so I’m not sure it’ll work, though would love to be corrected

well it was working with

db_url: sqlite:///home/homeassistant/.homeassistant/home-assistant_v2.db

when I wasn’t using docker

ok, then you’d need to change your URL to reflect the location of the file within docker container as your container won’t have access to your OS’ files.
I’m guessing config/home-assistant_v2.db is the location of your HASS config?
I’m also wondering whether you need additional libraries to connect to a SQL DB within the HA Docker…

Yes this is the correct path from inside docker

bash-4.4# ls -l /config/home-assistant_v2.db
-rw-r--r--    1 1001     1001      75997184 Jun  7 18:14 /config/home-assistant_v2.db
bash-4.4#

As you can see it’s readable by everyone (-rw-r–r--) so I expected
db_url: sqlite:///config/home-assistant_v2.db
to work

So there’s maybe some sort of library missing inside the docker image, as it was working on the docker host itself.
I’m just not sure how to troubleshoot it…

You need to use ----> db_url: sqlite:////config/home-assistant_v2.db

I found that you need four //// in the url

1 Like