MariaDB Addon Questions

jusdwy · January 7, 2019, 2:17pm

Just enabled the MariaDB addon, and it seems to be working great – HA and history seem to be loading much faster. The addon sure made things simple since I’ve been trying to get MariaDB on my local server running Linux Mint, but can’t get HA to connect. I’d still like to migrate the database off my Pi (and eventually the whole HA system) to reduce writes to the SD card.

Questions

can I use the MariaDB addon to point to the external server?
if using Hassio and not the MariaDB addon, do I need to do anything special to get HA to connect to external MariaDB instance? I see lots of posts about installing libmysqlclient-dev on the Pi, is this the step I’m missing because I’m not sure how to do that when using Hassio. Do I need phpmyadmin or similar running on my Mint server, because I’ve just been creating the database manually in the prompt.
Now that MariaDb is running, can I just delete the old db file? Where’s the new db file being stored?

DoctorOctagonapus · May 14, 2019, 3:26pm

Hey. The MariaDB addon just runs an instance of MariaDB Server as a separate image that Home Assistant can talk to. You don’t need it if you want to host your DB on a separate server, just change “core-mariadb” in your db_url value in configuration.yaml to the IP address or hostname of your new server.

On your DB server you need to configure MariaDB to allow connections from external sources (ie not localhost), and add an exception on that server’s firewall if you’ve configured one on there. (Most Linux distros don’t have one by default.)

By and large a generic MariaDB install should be fine for hosting, so any guide to installing it on Raspbian/Ubuntu/whatever will suffice. HomeAssistant is coded to create the DB structure if it doesn’t already exist, so you don’t need to do anything other than create a new DB named “homeassistant” or whatever you decide to call it.

Once it’s working you can safely kill the old .db file if you want.

zolric · May 21, 2020, 7:24am

hello I’m not able to connect to MariaDb sever…
i’m able to connect with phpmyadmin addon, but I’m not from node red and sqldevelopper tool…
it was working before the last update and Nginx installation. But I’m not sure about the issue.
I read from the mariadb change log that the 3306 port is not forwarding anymore… could be that? but how tu eneable the forwarding?
can you help to understand why?

I become mad…

phixion · May 21, 2020, 5:35pm

In the Supervisor check if you have a port mapped from the container to the host.

zolric · May 21, 2020, 5:48pm

thank you so much!!!
Host was empty! now it works very fine!

bschatzow · February 5, 2021, 10:37am

On my db (currently using Mariadb) , I in getting some 1366, “Incorrect string value: \xF0\x9F\x98\x80\xF0\x9F…’ for column ‘name’ at row 1”) in the error logs. I believe this started with the security update was done. Any idea on how to fix this? Searching on the internet it seems this is a common problem and on stackoverflow it says this can be fixed by editing the python code. I don’t know how to do this?

https://stackoverflow.com/questions/3220031/how-to-filter-or-replace-unicode-characters-that-would-take-more-than-3-bytes

	homeassistant	utf8mb4_unicode_ci	Check privileges Check privileges
	information_schema	utf8_general_ci	Check privileges Check privileges
	mysql	utf8mb4_unicode_ci	Check privileges Check privileges
	performance_schema	utf8_general_ci	Check privileges Check privileges
	phpmyadmin	utf8_bin	Check privileges Check privileges

Are the character sets being used.
I cross posted this on discord and if it gets answered there I’ll post a solution here.

febalci · February 5, 2021, 10:54am

@bschatzow, Did you check this out?

https://community.home-assistant.io/t/sql-error-saving-states-from-mobile-apps/267497

bschatzow · February 5, 2021, 10:59am

Thank. I didn’t read this but I did read the docs on setting up the recorder. I now see that the string was changed from charset=utf8 to utf8mb4.
Thanks.

Oligarch · March 20, 2021, 4:16pm

How can I grant access to all databases like “MyVideos116”, “MyVideos115”? Can I use regex in configuration? Tried as in the example below. But without success …

rights:
  - username: kodi
    database: MyVideos\d+
    host: '%'
    grant: ALL PRIVILEGES ON

Misak · July 7, 2021, 9:50pm

Hello, have you had any success with this? I am trying to run Kodi library on MariaDB addon and it can’t connect.

Oligarch · July 8, 2021, 7:21am

Yes. It is working fine for me. Please, show your MariaDB addon config and advancedsettings.xml

Misak · July 8, 2021, 3:59pm

# cat advancedsettings.xml 
<advancedsettings>
  <videodatabase>
    <type>mysql</type>
    <host>10.0.1.8</host>
    <port>3306</port>
    <user>kodi</user>
    <pass>password</pass>
  </videodatabase> 
  <musicdatabase>
    <type>mysql</type>
    <host>10.0.1.8</host>
    <port>3306</port>
    <user>kodi</user>
    <pass>password</pass>
  </musicdatabase>
  <videolibrary>
    <importwatchedstate>true</importwatchedstate>
    <importresumepoint>true</importresumepoint>
  </videolibrary>
</advancedsettings>

And I found in the logs ERROR: Unable to create new database, not sure why.

Oligarch · July 10, 2021, 7:52pm

Share your Maria DB config
My conf

databases:
  - homeassistant
logins:
  - username: hauser
    password: hapass
  - username: kodiuser
    password: kodipass
    host: '%'
rights:
  - username: hauser
    database: homeassistant
  - username: kodiuser
    database: MyVideos119

Misak · July 11, 2021, 10:23am

This is my config:

databases:
  - homeassistant
  - kodi
  - MyVideos\d+
logins:
  - username: homeassistant
    password: hasspass
  - username: kodi
    password: pass
    host: '%'
    skip-netwroking: 0
    skip-bind-address: 0
rights:
  - username: homeassistant
    database: homeassistant
  - username: kodi
    database: MyVideos
    host: '%'
    grant: ALL PRIVILEGES ON

But even when I try your config, it isn’t working. In logs, it was asking for db MyVideos116 first, so I even changed config for that, result the same. I have no idea why. I am able to connect to db from my computer, but seems that kodi can’t for some reason (port 3306 is open from kodi).

root@w541:~# mysql -ukodi -p -h 10.0.1.8
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25
Server version: 5.5.5-10.4.19-MariaDB MariaDB Server

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| MyVideos           |
| MyVideos116        |
| information_schema |
| kodi               |
+--------------------+
4 rows in set (0,00 sec)

Oligarch · July 11, 2021, 10:55am

You don’t need to create a base for Kodi. It is created by itself on first launch.

Try to remove MyVideos116 from SQL and start Kodi again…

Misak · July 11, 2021, 11:34am

So I removed the db and after kodi reboot it is not created. In logs, there is still this message:

2019-04-11 18:28:42.101 T:2881561456   ERROR: Unable to open database: MyVideos116 [2002](Can't connect to MySQL server on '10.0.1.8' (101))
2019-04-11 18:28:42.101 T:2881561456   ERROR: Unable to create new database

Db config:

databases:
  - homeassistant
logins:
  - username: homeassistant
    password: pass
  - username: kodi
    password: pass
    host: '%'
rights:
  - username: homeassistant
    database: homeassistant
  - username: kodi
    database: MyVideos116

kodi config:

<advancedsettings>
  <videodatabase>
    <type>mysql</type>
    <host>10.0.1.8</host>
    <port>3306</port>
    <user>kodi</user>
    <pass>pass</pass>
  </videodatabase> 
  <musicdatabase>
    <type>mysql</type>
    <host>10.0.1.8</host>
    <port>3306</port>
    <user>kodi</user>
    <pass>pass</pass>
  </musicdatabase>
  <videolibrary>
    <importwatchedstate>true</importwatchedstate>
    <importresumepoint>true</importresumepoint>
  </videolibrary>
</advancedsettings>

Hass is running as VM on proxmox, but it shouldn’t be a problem.

Oligarch · July 11, 2021, 11:56am

Did you also try with grant: ALL PRIVILEGES ON?

Misak · July 11, 2021, 11:58am

Yes, I tried, no effect
I’ll try to spawn new VM and run MariaDB in a separate container now.
Btw, thank you for helping me!

Oligarch · July 11, 2021, 12:04pm

Do you have a port mapped from the container to the host?
scr

Misak · July 12, 2021, 8:23pm

Yes, I have it open. I am able to connect to db from other computer, so that is ok.
Small progress right now, in LibreELEC I have set “wait for network” option and it has at least created two databases (Video116 nad Music72), but I can’t still update the library. I think I am on the right track now