Large homeassistant database files

Configuration
295

Hey, just upgraded to 0.65.2 and the SQL Sensor creates the following error, any ideas?

2018-03-11 11:40:42 ERROR (MainThread) [homeassistant.components.sensor] sql: Error on device update!
Traceback (most recent call last):
  File "/srv/homeassistant/lib/python3.5/site-packages/homeassistant/helpers/entity_platform.py", line 188, in _async_add_entity
    await entity.async_device_update(warning=False)
  File "/srv/homeassistant/lib/python3.5/site-packages/homeassistant/helpers/entity.py", line 327, in async_device_update
    yield from self.hass.async_add_job(self.update)
  File "/usr/lib/python3.5/asyncio/futures.py", line 380, in __iter__
    yield self  # This tells Task to wait for completion.
  File "/usr/lib/python3.5/asyncio/tasks.py", line 304, in _wakeup
    future.result()
  File "/usr/lib/python3.5/asyncio/futures.py", line 293, in result
    raise self._exception
  File "/usr/lib/python3.5/concurrent/futures/thread.py", line 55, in run
    result = self.fn(*self.args, **self.kwargs)
  File "/srv/homeassistant/lib/python3.5/site-packages/homeassistant/components/sensor/sql.py", line 147, in update
    value = float(decimal)
TypeError: float() argument must be a string or a number, not 'module'
296

This bug has already been fixed, waiting for 0.65.3 release

297

Could I change “value = float(decimal)” to “value = float(value)” for now?

298

Yes! sure you can

299

:+1: Thanks, it okay now.

300

I know this is a bit late, but here’s the SQL to get the top 20 entities contributing to the state table without having to do piping and string processing:

SELECT
 entity_id,
 COUNT(entity_id)
FROM
 states
GROUP BY
 entity_id
ORDER BY COUNT(entity_id) DESC
LIMIT 20;
4 Likes
301

I did a similar thing, however I am using a remote PostgreSQL instance. I wrote a script that connects to the PostgreSQL instance and polls the home assistant database size and prints it in bytes.

Created a similar command_line sensor as @ntalekt

Maybe it will be useful to someone in the future :wink:

302

I’m using a PostgreSQL instance on my NAS for the Home Assistant recorder. To monitor its size, I use the recently added SQL Sensor:

- platform: sql
  db_url: !secret RECORDER_DB_URL
  queries:
    - name: "HA database size"
      query: "SELECT (pg_database_size('HOME_ASSISTANT_db')/1024/1024) as db_size;"
      column: "db_size"
      unit_of_measurement: MB
1 Like
303

That’s cool. I didn’t know that. :slight_smile: I should change my sensor to this.

304

Not sure what happened after an update to Qnap 4.3.4.0516, no changes have been made to my HA config, but HA can’t seem to connect to sql database, connection is refused.

192.168.200.210 is my Pi3 IP Address.

2018-04-16 21:19:49 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (_mysql_exceptions.OperationalError) (1129, "Host '192.168.200.210' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'") (Background on this error at: http://sqlalche.me/e/e3q8) (retrying in 3 seconds)

306

is it better to use grafana/influxdb than MySQL?

307

It must have changed something…

Same setup as yours (RPi3 + QNAP with MySQL) and nothing is wrong :frowning:

308

Different purposes… can’t use grafana for the history component due to the relational structure used by this component (I actually looked into developing that feature)

309

Any idea how I can troubleshoot?

It works if I flush host or reboot Qnap and then restart HA, but If I restart HA anytime after that, I get this error.

310

You need to activate logging in the mysql server configuration in QNAP

I don’t know how to do that through the QNAP GUI… maybe you can login through the console, find mysql.conf and edit there?

311

Under Status->Advisor I see a couple of the following entries.

Issue:
Too many connections are aborted.

Recommendation:
Connections are usually aborted when they cannot be authorized. This article might help you track down the source.

Justification:
Aborted connections rate is at 4.44 per minute, this value should be less than 1 per hour

Used variable / formula:
Aborted_connects / Uptime

Test:
value * 60 * 60 > 1

312

From that information it is clear why it works after you restart mysql (reboot QNAP) -> basically restart rate limiter

Question now is why are you hitting that limit

run in your mysql CLI: show processlist

my default mysql install in QNAP shows 5 processes only.

313

image

314

Are you using SQL sensor with user root ? (works but is not advisable for security reasons)

If you are there are processes missing in there… mine looks like:

+----+-------+--------------------+-------+---------+------+-------+------------------+----------+
| Id | User  | Host               | db    | Command | Time | State | Info             | Progress |
+----+-------+--------------------+-------+---------+------+-------+------------------+----------+
| 26 | hass  | 192.168.1.20:36082 | hass  | Sleep   |    4 |       | NULL             |    0.000 |
| 27 | hass  | 192.168.1.20:36084 | hass  | Sleep   |  941 |       | NULL             |    0.000 |
| 28 | hass  | 192.168.1.20:36086 | hass  | Sleep   |  340 |       | NULL             |    0.000 |
| 29 | hass  | 192.168.1.20:36176 | hass  | Sleep   |    4 |       | NULL             |    0.000 |
| 31 | hass  | 192.168.1.20:54862 | NULL  | Query   |    0 | NULL  | show processlist |    0.000 |
+----+-------+--------------------+-------+---------+------+-------+------------------+----------+
315

There is also the following:
image