MariadB in docker issue - unconnected user unauthenticated host

Configuration
1

Hi,

I have home assistant in a docker container currently running great in DSM. I wanted to setup MariadB to integrate with HA so what I did is to first install Portainer in docker and then use the “App Templates” of Portainer to install mariadB.

However, no matter what i tried, I kept getting these error logs and i cannot connect to mariadB (currently port 3306).

2020-04-04 1:01:49 10 [Warning] Aborted connection 10 to db: ‘unconnected’ user: ‘unauthenticated’ host: ‘172.17.0.1’ (This connection closed normally without authentication)

I have searched google for days but could not come to a solution.

Appreciate any help please.

Thanks

2

You haven’t shown us

  • the docker command you used (yeah I know it is a portainer template, but what is the configuration it runs mariadb in?
  • your home assistant setup for mariadb
  • where that one line of logs comes from

or really much useful at all!

3

Hi nickrout, sorry for not supplying my original config info.

I have not setup home assistant to talk to mariadb yet as i am still fighting to get mariadb to even run properly.

My mariadb info: I am not sure how to copy and paste the mariadb docker create command from within portainer, so I am hoping the following screenshots would help.

Imgur

Imgur

Imgur

Let me know if the config is not clear and what i need to do please.

Thanks!

4

I hate screenshots, but I think I got the hang of it!

I just installed from the portainer template, specifying a root password. It certainly starts.

What are you doing that is trying to connect to the mariadb server? It looks like something is trying to connect but not succeeding. What is prompting that?

5

Hi nickrout,

I got that error message when i try to, on a web browser, browse to 192.168.1.111:3306.
Is this a normal behaviour?

I checked that I am able to, from the host (192.168.1.111), connect to mariadB on the docker container. so I can:

# docker exec -it mariadb-test /bin/bash
now I can get into the container

#mysql -u root -p
and after i key in the password

#show databases;

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.002 sec)
6

mariadb is not web based, so if you try to access it via a web browser, it is gonna do that yeah.

It is a database, not a web server!

7

Thanks. If I install mariadB via the synology packages and i browse to 192.168.1.111:3306, it will show a webpage with some info on it. I naively thought installing mariadB via docker will be the same.

I will install phymyadmin to see if It can connect to the mariadB.

Thanks for clearing that up.

8

why not just use the mysql client?

9

from searching the web, it seems phpmyadmin is widely used so I thought I will give that a go. But thanks for your suggestion I will also explore mysql client.

10

If all you are doing is connecting to home assistant there are only half a dozen commands ot type (if that). Don’t fear the CLI :slight_smile:

Thanks for the prompt, I have been meaning to move to a proper database for a while, and have bugger all else to do in the lockdown [1], and what would Saturday be without breaking HA.

[1] OK eat too much, drink too much, binge watch TV, binge read and play with HA.

11

Thanks for that. I will give that a try.

12

And it is done! Message back in this thread if you need any help!

13

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 data in home assistant. Kinda counter-intuitive. Nevertheless if you want to do so, simply copy the home-assistant_v2.db somewhere else.

Deploy MariaDB in Docker via Portainer

  • In portainer navigate to App Templates, scroll down to MariaDB and click it.
  • In the next page leave everything as default except fill in root password. Fill that in with something you can remember. Important this becomes the password for the root MariaDB user, it has nothing to do with the root system user of your computer.
  • Click “Deploy the Container”.
  • MariaDB should now be running in a container. It uses a bridged network and it’s IP address will be reported by portainer. Mine is 172.17.0.2.

Setting up MariaDB

  • SSH into your operating system, ie in my case ubuntu 18.04.
  • Make sure you have the mysql client installed
sudo apt install mysql-client
  • Connect to mysql
mysql -h 172.17.0.2 -u root -p
  • You will be prompted for that root database password you set up when you deployed MariaDB in portainer. Your prompt should change to mysql>. You are now the root (or master) user of MariaDB.
  • You now need to set up a user to access MariaDB from home assistant, a database for home assistant to use, and some passwords/permissions. The exact name of the user and the name of the database can be anything, but I used stuff that was easy to type. Don’t forget the semicolon at the end of each line when using mysql/MariaDB. Also the single quotes are important.
mysql> CREATE DATABASE hadb;
mysql> CREATE USER 'ha' IDENTIFIED BY 'some_password';
mysql> GRANT ALL PRIVILEGES ON hadb.* TO 'ha';
mysql> FLUSH PRIVILEGES;
mysql> type ctrl-d or exit
  • Now we need to configure home assistant to use the MariaDB database instead of sqlite. Edit your configuration.yaml to include the following:
recorder:
  db_url: !secret mariadb_connection_url

mariadb_connection_url: mysql://ha:[email protected]/hadb?charset=utf8
  • Check your config in the home assistant frontend.
  • Cross your fingers.
  • Restart home assistant.
  • I think that is it. I’m off to bed. G’night all.
3 Likes
14

Wow. I have been struggling just now. Good timing to see your post!

I am going to try this! Thanks again!!

15

Is there a way to link to the container rather than using the IP address? I am of the understanding that this IP address may be changed by docker at any time?

p.s. By the way I follow your method above to specify the IP address so far things seem ok but on the log file I am seeing this:

Setup of recorder is taking over 10 seconds.

Any idea how to get rid of this error?

16

I am looking into that.

I am not seeing that. More importantly, is it working? Does HA start? Do your Logbook and History panels work?

17

Yes the logbook and history panel both works. I am just wondering why HA throw this error. It is still there even if i restart HA.

18

I’m getting the same error - but it’s coming from my router. Maybe that’s where you’re is coming from, too? Are you using any Ubiquiti equipment?

19

What error?

A mariadb error won’t be coming off your router. This Setup of recorder is taking over 10 seconds. does not come from your router, it comes from home assistant.

20 
2020-08-11 17:09:01 54 [Warning] Aborted connection 54 to db: 'unconnected' user: 'unauthenticated' host: '192.168.1.1' (This connection     closed normally without authentication)
2020-08-11 17:13:53 55 [Warning] Aborted connection 55 to db: 'unconnected' user: 'unauthenticated' host: '192.168.3.1' (This connection closed normally without authentication)

192.168.1.1 and .3.1 are both my router.