0.89 and MariaDB connection problems?

Configuration
1

These just started after the upgrade to 0.89. Prior no such log entries. These are on the MariaDB server side. Almost exactly every 2 hours.

Doing some debugging it seems when the SQL sensor runs to query DB size this error is generated. I check the DB size every 2 hours.

2019-03-08  2:10:19 29 [Warning] Aborted connection 29 to db: 'homeassistant' user: 'hass' host: '192.168.XXX.YYY' (Got timeout reading communication packets)

2019-03-08  4:10:21 30 [Warning] Aborted connection 30 to db: 'homeassistant' user: 'hass' host: '192.168.XXX.YYY' (Got timeout reading communication packets)

2019-03-08  6:10:23 31 [Warning] Aborted connection 31 to db: 'homeassistant' user: 'hass' host: '192.168.XXX.YYY' (Got timeout reading communication packets)

2019-03-08  8:10:24 32 [Warning] Aborted connection 32 to db: 'homeassistant' user: 'hass' host: '192.168.XXX.YYY' (Got timeout reading communication packets)

2019-03-08 10:10:26 33 [Warning] Aborted connection 33 to db: 'homeassistant' user: 'hass' host: '192.168.XXX.YYY' (Got timeout reading communication packets)

2019-03-08 12:10:28 34 [Warning] Aborted connection 34 to db: 'homeassistant' user: 'hass' host: '192.168.XXX.YYY' (Got timeout reading communication packets)
2

Checked my mariadb logs right now and i donā€™t see such warnings.
I use a command_line sensor that runs a python script to fetch the database size.

3

Would you mind sharing the script?

I use:

- platform: sql
  scan_interval: 7200
  db_url: mysql://USER:[email protected]:3306/homeassistant?charset=utf8
  queries:
    - name: DB Size
      query: 'SELECT table_schema "homeassistant", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "value" FROM information_schema.tables WHERE table_schema="homeassistant" GROUP BY table_schema'
      column: 'value'
      unit_of_measurement: MB
4

Sure, here it is. The query is the same.

import pymysql.cursors

mysql_host = '127.0.0.1'
mysql_user = 'user'
mysql_pass = 'pass'
mysql_db   = 'homeassi'

db = pymysql.connect (host=mysql_host, user=mysql_user, passwd=mysql_pass, db=mysql_db)
cursor = db.cursor(pymysql.cursors.DictCursor)

sql = """SELECT Sum(Round(( ( data_length + index_length ) / 1024 / 1024 ), 1)) AS "Size in MB"
         FROM information_schema.tables WHERE table_schema = '%s'""" % (mysql_db)

cursor.execute(sql)
result = cursor.fetchall()
db.close()

print(result[0]['Size in MB'])
5

Running MariaDB + 0.89.1 here, no DB errors in my log however, but Iā€™m not checking the size of my DB, I just have it purge data that is older than 3 days

6

Hello @jwelter,

have you find a solution please?

Same error and the database seems to become buggy after a purgeā€¦normaly i am at about 5-8% CPU usage but when its buggy i am between 22 - 40% CPU usage.

Desinstall addon MariaDb and reinstall and the problem is goneā€¦

Thanks