MariadB in docker issue - unconnected user unauthenticated host

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)

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!

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.

why not just use the mysql client?

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.

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.

Thanks for that. I will give that a try.

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

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

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

I am going to try this! Thanks again!!

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?

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?

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.

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?

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.

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.

FYI, I’ve discovered my “Aborted Connection” was related to my UDM (Ultimate Dream Machine) “Endpoint Scanner”, part of Unifi’s Threat Management system that looks for suspicious open ports.

I appreciate the guide @nickrout . I installed MariaDB on my QNap and was able to get HA connected up to that DB with no issues. :+1:

I have been meaning to come back to this thread to point out that when I reboot that machine, the mariadb docker container gets a different IP address and I need to xhange the secrets file. I am phasing out that machine in favour of a supervised setup. I also rarely reboot it.

You should be able to fix that by declaring the desired IP when specifying the exposed port:

-p 172.17.0.2:3306:3306


Default it is set to 0.0.0.0