Database location in usb stick

Wallace75 · January 2, 2021, 7:38am

hi Guys,

getting crazy with this issue. I googled, I searched in the forum, tried many things but nothing helped

Installed home assistant on debian on raspberry 4. I moved the file from the original location to the new location. Additionally I give the the following information

homeassistant@rpi4-20201112:/$ ls -lr /mnt/usbdisk_db/
-rw-r--r-- 1 root root 184320 Jan  1 22:37 home-assistant_v2.db

recorder:
  db_url: sqlite:////mnt/usbdisk_db/
  include:
    entities:
      - switch.luce_bagno

I tried several different number of / in the db_url as well, either I get nothing or I get the following error

" Logger: homeassistant.components.recorder
Source: components/recorder/__init__.py:279
Integration: Recorder ([documentation](https://www.home-assistant.io/integrations/recorder), [issues](https://github.com/home-assistant/home-assistant/issues?q=is%3Aissue+is%3Aopen+label%3A%22integration%3A+recorder%22))
First occurred: 9:09:25 AM (9 occurrences)
Last logged: 9:09:49 AM
Error during connection setup: (sqlite3.OperationalError) unable to open database file (Background on this error at: http://sqlalche.me/e/13/e3q8) (retrying in 3 seconds)"

still does not work. Do I have rights problem?

thanks in advance to those who’ll support me

regards

m0wlheld · January 2, 2021, 9:12am

According to the integration docs, the SQLite path must end with the file‘s name, not a directory.

Also, if you use HA Core in Docker, don’t forget to provide the USB drive as volume

Wallace75 · January 2, 2021, 9:22am

@m0wlheld thanks for the feedback. I forgot to mention I tried as well

  db_url: sqlite:///mnt/usbdisk_db/home-assistant_v2.db

Can you please tell me more about the second point?

m0wlheld · January 2, 2021, 10:27am

Heads Up: In your reply, you use /// (3x) in your URL, the documentation uses //// (4x). While I consider 3 being correct, 4 is according to the docs.

My remark on Docker is irrelevant unless you use it to run Home Assistant. Do you?

Wallace75 · January 2, 2021, 7:50pm

yes, it is the case. Thanks for the hint. I’ll try to have a closer look

m0wlheld · January 2, 2021, 9:49pm

Docker containers have no access to the host’s filesystem, unless you make it available as Volume.

Easiest would be to remove db_url attribute from your configuration and „mount“ the DB at it‘s expected location.

Add the following argument to your docker run command:

-v /mnt/usbdisk_db/home-assistant_v2.db:/config/home-assistant_v2.db

Wallace75 · January 2, 2021, 10:21pm

I really appreciate your help - thanks. It seems it slowly goes beyond my know-how on this field . Well usually when I start raspberry everything already runs and I can login in homeassistant via web. I believe I need to stop the container and then to start it again adding the line you suggested. Do I get it right?

m0wlheld · January 2, 2021, 10:43pm

Are you using Home Assistant OS (aka HASS.IO) or Home Assistant Core on Docker? Is there a „Supervisor“ item on your HA sidebar?

I am not familiar with Home Assistant OS, I do use HA Core on Docker/Docker Compose.

Usually, since containers are designed to be ephemeral, you simply delete that container and start a new one with updated arguments.

Wallace75 · January 2, 2021, 10:54pm

I’m using a home assistant supervised:

I started with Home Assistant OS but I moved away as I want to be more flexible. Now I’m slowly progressing

This is the output of docker ps


homeassistant@rpi4-20201112:~$ sudo docker ps
CONTAINER ID   IMAGE                                                   COMMAND                  CREATED          STATUS          PORTS                                                                NAMES
f4f6ae2d3f2c   homeassistant/aarch64-addon-configurator:5.2.0          "/init"                  19 minutes ago   Up 19 minutes                                                                        addon_core_configurator
065b2a62f24d   hassioaddons/sqlite-web-aarch64:2.3.2                   "/init"                  20 minutes ago   Up 20 minutes   0.0.0.0:xxxx->xxxx/tcp                                               addon_a0d7b954_sqlite-web
eea61c75686d   homeassistant/aarch64-addon-samba:9.3.0                 "/init"                  20 minutes ago   Up 20 minutes                                                                        addon_core_samba
59427ad7f179   homeassistant/aarch64-addon-mosquitto:5.1               "/run.sh"                20 minutes ago   Up 20 minutes   0.0.0.0:xxxx-xxxx->xxxx-xxxx/tcp, 0.0.0.0:xxxx-xxxx->xxxx-8884/tcp   addon_core_mosquitto
27ff16b332ec   homeassistant/aarch64-hassio-multicast:3                "/init"                  20 minutes ago   Up 20 minutes                                                                        hassio_multicast
0e48d45129fa   homeassistant/aarch64-hassio-cli:2020.11.1              "/init /bin/bash -c …"   20 minutes ago   Up 20 minutes                                                                        hassio_cli
f84e4e737f77   homeassistant/aarch64-hassio-audio:17                   "/init"                  20 minutes ago   Up 20 minutes                                                                        hassio_audio
63911dc77982   homeassistant/aarch64-hassio-dns:2020.11.0              "/init"                  20 minutes ago   Up 20 minutes                                                                        hassio_dns
07888ccb4c8c   homeassistant/raspberrypi4-64-homeassistant:2020.12.2   "/init"                  38 hours ago     Up 20 minutes                                                                        homeassistant
b828967a30b2   homeassistant/aarch64-hassio-observer:2020.10.1         "/init"                  38 hours ago     Up 21 minutes   0.0.0.0:xxxx->xx/tcp                                                 hassio_observer
9a6b57dcc10f   homeassistant/aarch64-hassio-supervisor                 "/init"                  38 hours ago     Up 21 minutes

m0wlheld · January 2, 2021, 11:45pm

Sorry, but as I said, I don’t know how to do this with Home Assistant OS. Don‘t know your intention when moving the DB to another disk, but obviously you can also move the entire „data“ direction to another location.

m0wlheld · January 3, 2021, 10:24am

@Wallace75: Somebody’s recently asked a similar question, that might be useful. There is no response yet, but you might want to track this thread.

Wallace75 · January 4, 2021, 8:12pm

Thanks. I’m still convinced it shoud be possible the solution you suggested. I played a bit with

sudo docker inspect container_name

removing the container and adding new ones with the additional arguments you suggested.

FYI I then decided to change approach and I installed HASSIO in a python environment on raspbian. This could give me the flexibility I expect, I already installed mqtt, samba and moved the db on the usb stick all just some hours work. As I said I’m still in the learning phase, it may change soon but it seems to be the most practicable. I never worked concretely with dockers and I’m afraid of it…

gondro · November 17, 2023, 12:35am

Hi Christoph!
May you tell me where I find the docker run command to “mount” the USB drive?
Background: I’m also using HA in docker on a Synology NAS,
but don’t succeed in shifting the database access to an external drive.
When using

recorder:
  db_url: sqlite:////volumeUSB2/usbshare/homeassistant/home-assistant_v2.db

I always get an cryptic error
"Error during connection setup: (sqlite3.OperationalError) unable to open database file (Background on this error at: Error Messages — SQLAlchemy 2.0 Documentation) "

m0wlheld · November 23, 2023, 3:06pm

You need to „bind-mount“ the device to a path of the Docker container upfront and than use the container’s path as recorder URL.

docker run -v /volumeUSB2/usbshare:/db …

The use

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