Latest migration steps from default sql lite to MariaDB using Raspberry pi 4

[poponuts]

I wanted to migrate the default DB to MariaDB due to future-proofing for scability. I want to get this done now while I have few devices before doing heaps of re-factoring in the future.
I have read multiple articles and forums how to do it (but with dockers, NAS, etc.) but they seem to be a year ago. Some are not working for some and I don’t want to be rolling back if something goes wrong.

Not trying to be spoon-fed (and I will obviously try the year-old instructions if I don’t get any response) but was just wondering if anyone has done this recently. Here is my current setup:
Hardware: Raspberry pi 4 - 64
Add-ons:
MariaDB
Samba (to access the SD Card from my MacOS)

Most likely I would need to install a shell command add-on as well so I run sudo scripts for migration.

[nickrout]

Not sure whether you are using full homeassistant (what used to be hassio) but I run home assistant core in docker, and this worked for me

MariadB in docker issue - unconnected user unauthenticated host

I am going to document this before I forget what I did. My setup nuc running ubuntu 18.04 home assistant core 0.107.7 via docker. portainer 12. Aim move from sqlite to a proper database using MariaDB Important to know: MariaDB is a “drop in” replacement for the older MySQL project. That is why you will see references to mysql below. Backup your existing database The number of times I see “just delete your database file and start again” seems to show that people don’t much value their dat…

[poponuts]

I just installed HA core last weekend. Flashed the image via the SD Card and instructions on this:

Install Home Assistant

I did not containerise it via docker as I am not familiar with this procedure. I haven’t done extra research on the benefits of using Docker and if additional charges is required but probably worth looking at it now.

[poponuts]

I already added the MariaDB add-on. Will the sudo apt install mysql-client override it?

[francisp (Francis)]

You want be able to run apt, since you run HassOS. Check the add-on instructions.

[poponuts]

Ok I skipped the sudo apt install. I think the add-on would that for you automatically (Sorry still a noob).
I followed the yaml configuration on secret and configuration from @nickrout . I just changed the IP address (which I still don’t know to get at this point) to the one in the README file of the source core-mariadb:
mariadb_connection_url: mysql://ha:[email protected]/hadb?charset=utf8

I was wondering if the IP address is something configurable via the MariaDB add-on (which wasn’t clearly defined in the README file):
Network > Host

[francisp (Francis)]

The Ip-address is probably the IP of your Raspberry Pi

[poponuts]

Would you know a quick way to retrieve the IP address without going through the router settings or using the terminal / ssh add-on? Else, I will just use either of them. I am unable to find it via the HA UI.

Also, I am getting this error from the log so it may be due to the incorrect IP address:
2020-04-13 17:22:38 ERROR (MainThread) [homeassistant.components.ipp] Error fetching ipp data: Invalid response from API: Error occurred while communicating with IPP server.
2020-04-13 17:22:38 WARNING (MainThread) [homeassistant.config_entries] Config entry for ipp not ready yet. Retrying in 80 seconds.

[francisp (Francis)]

Ipp = inernet printer protocol. Nothing to do with mariadb.

The ip-address = the one of your pi. The one you see in the browser address bar when you access the HA webgui.

[poponuts]

Yep I get what you mean. I was just trying to find a way to retrieve it. Anyway, I found it via my router gateway as I was unable to find it via the HA frontend.

In terms of the MariaDB, the one that @nickrout provided seem to show the IP address with the following URI:
mysql://ha:[email protected]/hadb?charset=utf8

While in the example from the MariaDB repo, it shows this URI (it is just a bit confusing or rather strange they will use core-mariadb as an IP Addresss as an example:
mysql://homeassistant:[email protected]/homeassistant?charset=utf8

[francisp (Francis)]

Use the connectionstring from the repo.

[nickrout]

@francisp

You want be able to run apt, since you run HassOS. Check the add-on instructions.

Yes that is why I deleted my post, I realised he was on hassio.

[docsparks]

Ive spent the better part of the last 5 hours and three days trying to find the right way of setting up a fresh hassos core install on a rpi4, ssd, and mariadb. i have countless entries for what the db could be. countless similar but different error logs copied. for f*** sake cant anyone just keep any documentation on the installs and fine workings updated?

seriously. i would kill for some simple instructions for a stable hassio install with 300+ sensors and a 2 month history that doesnt lock up. please jesus, anyone. any suggestions are welcome.

[nickrout]

How about starting your own thread. Please tag me as I am happy to help. As long as you don’t f***ing swear at me…