HA ignoring mysql config

Configuration
1

i recently switched from hdd to ssd and had to reinstall everything, copied over all config files that always worked before as i have with previous re-installs so everything should be the same as the previous setup. everything works except HA wont connect to mysql as it did before, there’s no errors in the logfile or anything, looks like its just ignoring it. all dependecies are installed and all mysql users work.
did i overlook something? anyone else have the same problem? i’m running on debian in a venv.

my config entry in configuration.yaml (comented out for now since its ignoring it anyway) , tried all 3 options without any luck. also tried localhost instead of 127.0.0.1
db_url: mysql://user:[email protected]/homeassistant always worked before.

 recorder:
 purge_interval: 1
 purge_keep_days: 7
 #db_url: mysql://user:[email protected]/homeassistant
 #db_url: mysql+pymysql://user:[email protected]/homeassistant?charset=utf8
 #db_url: mysql://user:[email protected]/homeassistant?charset=utf8

installed python packages / modules:

Package Version

acme 0.39.0
aiodns 2.0.0
aiofiles 0.4.0
aiogithubapi 0.4.2
aiohttp 3.6.1
aiohttp-cors 0.7.0
astral 1.10.1
async-timeout 3.0.1
attrs 19.3.0
backoff 1.9.2
bcrypt 3.1.7
boto3 1.10.34
botocore 1.13.34
caldav 0.6.1
certifi 2019.11.28
cffi 1.13.2
chardet 3.0.4
cryptography 2.8
distro 1.4.0
docopt 0.6.2
docutils 0.15.2
ecdsa 0.14.1
envs 1.3
ephem 3.7.7.0
future 0.18.2
geojson 2.5.0
gTTS-token 1.1.3
hacs-frontend 18
hass-nabucasa 0.29
hbmqtt 0.9.5
holidays 0.9.11
home-assistant-frontend 20191204.1
homeassistant 0.103.0
idna 2.8
ifaddr 0.1.6
importlib-metadata 0.23
integrationhelper 0.2.2
Jinja2 2.10.3
jmespath 0.9.4
josepy 1.2.0
jsonrpc-async 0.6
jsonrpc-base 1.0
jsonrpc-websocket 0.6
lxml 4.4.2
MarkupSafe 1.1.1
mock 3.0.5
more-itertools 8.0.2
multidict 4.6.1
mutagen 1.42.0
mysqlclient 1.4.6
netdisco 2.6.0
openhomedevice 0.6.3
paho-mqtt 1.5.0
passlib 1.7.2
pip 18.1
pkg-resources 0.0.0
psutil 5.6.7
pushbullet.py 0.11.0
pycares 3.0.0
pycparser 2.19
pycryptodome 3.9.4
PyJWT 1.7.1
PyMetno 0.4.6
PyMySQL 0.9.3
PyNaCl 1.3.0
pyOpenSSL 19.1.0
pyotp 2.3.0
pyowm 2.10.0
PyQRCode 1.2.1
pyRFC3339 1.1
python-dateutil 2.8.0
python-jose-cryptodome 1.3.2
python-magic 0.4.15
python-slugify 4.0.0
pytz 2019.3
PyYAML 5.1.2
requests 2.22.0
requests-toolbelt 0.9.1
ruamel.yaml 0.15.100
s3transfer 0.2.1
semantic-version 2.8.3
setuptools 40.8.0
six 1.13.0
snitun 0.20
speedtest-cli 2.1.2
SQLAlchemy 1.3.11
text-unidecode 1.3
transitions 0.7.1
transmissionrpc 0.11
tzlocal 2.0.0
urllib3 1.25.7
vobject 0.9.6.1
voluptuous 0.11.7
voluptuous-serialize 2.3.0
warrant 0.6.1
websocket-client 0.56.0
websockets 8.1
wheel 0.33.6
xmltodict 0.12.0
yarl 1.4.2
zeroconf 0.23.0
zipp 0.6.0

2

Your indenting under recorder is wrong…

3

if the identation was the problem it shouldn’t have worked before and at least throw an error.

4

It’s still wrong!

5

As @DavidFW1960 said the indentation is incorrect. I use MySQL as a the db and my config is as follows;

recorder:
    purge_keep_days: 60
    db_url: !secret db_url

With the format of the connection being;

mysql://user:[email protected]/hassio?charset=utf8

Check that you have set up the DB and configured MySQL and the user for the DB correctly.

6

i fixed the identation and it’s still the same.
i have 3 mysql users, all 3 can connect from whatever ip and they all work correctly so it must be a HA thing or i missed a dependency.

7

In your mysqld.cnf file what did you set as the bind-address?

8

nextcloud on the same machine and both kodi’s on the network can connect to mysql.
i can connect with every user except the nextcloud one from a remote machine on the same network using heidsql.

bind adress is commented out to allow external conections

#bind-address = 127.0.0.1
#skip-networking=0
#skip-bind-address

9

Try changing your HA MySQL line to that of you DB machines actual IP Address.

If you do that then the user for the DB will have to be added or amended to reflect that.
or try

bind-address = 0.0.0.0

Also which version of MySQL are you using?

Also if you run

lsof -Pni :3306

What gets returned?

10

sudo mysqld --version
mysqld Ver 10.3.18-MariaDB-0+deb10u1 for debian-linux-gnu on i686 (Debian 10)

sudo mysql --version
mysql Ver 15.1 Distrib 10.3.18-MariaDB, for debian-linux-gnu (i686) using readline 5.2

sudo lsof -Pni :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 20060 mysql 17u IPv6 3011513 0t0 TCP *:3306 (LISTEN)
mysqld 20060 mysql 159u IPv6 3013406 0t0 TCP 192.168.123.2:3306->192.168.123.160:57322 (ESTABLISHED)

11

Try using ::1 instead of using 127.0.0.1

12

ipv6 should be disabled.
i give up, i’ll just the internal one for now.

13

As you wish but I can see from your lsof output that it isn’t disabled. A little while back there was an issue with Maria where connections to localhost failed if both IPV4 and IPV6 were enabled.

If you want to try again another day PM me and Ill see if I can help.

14

ipv6 should be disabled, i just have to reboot the machine… still

15

ipv6 is disabled now and the output of sudo lsof -Pni :3306 is now.
still no luck.

sudo lsof -Pni :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 543 mysql 15u IPv4 19888 0t0 TCP *:3306 (LISTEN)
mysqld 543 mysql 153u IPv4 33022 0t0 TCP 192.168.123.2:3306->192.168.123.160:60279 (ESTABLISHED)

16

OK,
now what do you see in /var/log/mysql/error.log?

Also do you have any special characters in you password? An ! can make a difference in success or not.

17

no special characters in the passwords, 150 is kodi, 160 is the laptop with heidisql and no idea what 247 is.

mysql log:

2019-12-15 13:29:57 0 [Note] InnoDB: Using Linux native AIO
2019-12-15 13:29:57 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-12-15 13:29:57 0 [Note] InnoDB: Uses event mutexes
2019-12-15 13:29:57 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2019-12-15 13:29:57 0 [Note] InnoDB: Number of pools: 1
2019-12-15 13:29:57 0 [Note] InnoDB: Using generic crc32 instructions
2019-12-15 13:29:57 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2019-12-15 13:29:57 0 [Note] InnoDB: Completed initialization of buffer pool
2019-12-15 13:29:57 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2019-12-15 13:29:58 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2019-12-15 13:29:58 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2019-12-15 13:29:58 0 [Note] InnoDB: Setting file ‘./ibtmp1’ size to 12 MB. Physically writing the file full; Please wait …
2019-12-15 13:29:58 0 [Note] InnoDB: File ‘./ibtmp1’ size is now 12 MB.
2019-12-15 13:29:59 0 [Note] InnoDB: Waiting for purge to start
2019-12-15 13:29:59 0 [Note] InnoDB: 10.3.18 started; log sequence number 5463701651; transaction id 2629817
2019-12-15 13:29:59 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2019-12-15 13:29:59 0 [Note] Plugin ‘FEEDBACK’ is disabled.
2019-12-15 13:29:59 0 [Note] Server socket created on IP: ‘0.0.0.0’.
2019-12-15 13:29:59 0 [Note] Reading of all Master_info entries succeeded
2019-12-15 13:29:59 0 [Note] Added new Master_info ‘’ to hash table
2019-12-15 13:29:59 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: ‘10.3.18-MariaDB-0+deb10u1’ socket: ‘/run/mysqld/mysqld.sock’ port: 3306 Debian 10
2019-12-15 13:30:02 0 [Note] InnoDB: Buffer pool(s) load completed at 191215 13:30:02
2019-12-15 13:36:00 39 [Warning] IP address ‘192.168.123.150’ could not be resolved: Name or service not known
2019-12-15 13:37:18 57 [Warning] IP address ‘192.168.123.160’ could not be resolved: Name or service not known
2019-12-15 14:32:47 157 [Warning] IP address ‘192.168.123.247’ could not be resolved: Name or service not known

18

Hmm, I don’t know your experience with MySQL/maria and don’t want to teach you to suck eggs but follow this guide to separate out the noise;

https://www.debianadmin.com/how-to-enable-mysql-database-server-logs-in-debian.html

We can the specifically then look at just the errors.

From your post I can see that Maria is listening on all ports IPV4 so one step closer.

Furthermore, do you have an internal DNS server? If not then perhaps editing the hosts file will assist in noise reduction.

127.0.0.1       localhost
127.0.1.1       hostname.fqdn.example.com    hostname

Adding in the same format.

Also do a scan and find out what the missing IP ending in 247 is. It’s always good to know.

Just as a caveat,

I don’t know your experience or your network I’m just trying to help you troubleshoot. and hopefully resolve your issue. My hassio connected (on the same node) without an issue. but I have nextcloud, zoneminder on the same node with no issues.

Would you also have a look at your Maria and tell me the exact format of the hassio user for your DB? for example: [email protected] or hassio@%. You can pm me any info you don’t want to post publicly.

19

i’ll have a look at that link later.
thx for helping me out already so far.

no internal dns servers and dont really want one.
i did a scan and it didn’t show up but it’s probably just my phone.
i studied networking but i fu**ing hate mysql pretty much as it hates me hehe.
my users are all @%

switching isp’s tommorow cos the modem of the current one doesn’t even do hairpining wich is a pain in the a$$ for nextcloud syncing. ill see how things go tommorow after i redid the modem config.

20

Just a quick one to try instead of [email protected] try user@%