Large homeassistant database files

Configuration
281

I’ll try that and report back.

Does the query stay the same "WHERE table_schema=“hass”, or should it be "WHERE table_schema=“hass_db”?

282

And the url would be: db_url: mysql://root:[email protected]:3306/hass_db

283

according to your successful query in phpMyAdmin it should be table_schema=“hass_db”

284

I just noticed something, the root user in QNAP’s MySQL is not allowed access from an external host.

Can you copy paste your current sensor block ? (just replace the password in db_url for something else)

285

With root user I got the following error in the log:

2018-02-12 19:02:56 ERROR (Thread-5) [homeassistant.components.sensor.sql] Couldn't connect using mysql://root:[email protected]:3306/hass_db DB_URL: (_mysql_exceptions.OperationalError) (1045, "Access denied for user 'root'@'Hassbian_Pi3.home' (using password: YES)") (Background on this error at: http://sqlalche.me/e/e3q8)

286

I prviously tried without the url, as the docs state it was option, and the sensor would use the db url.

Sensor,yaml =

- platform: sql
  db_url: mysql://root:[email protected]:3306/hass_db
  queries:
    - name: HASS DB size
      query: 'SELECT table_schema "database", Round(Sum(data_length + index_length) / 1024, 1) "value" FROM information_schema.tables WHERE table_schema="hass" GROUP BY table_schema;'
      column: 'value'
      unit_of_measurement: kB
287

try:

- platform: sql
  db_url: mysql://hass_db:[email protected]/hass_db
  queries:
    - name: HASS DB size
      query: 'SELECT table_schema "database", Round(Sum(data_length + index_length) / 1024, 1) "value" FROM information_schema.tables WHERE table_schema="hass_db" GROUP BY table_schema;'
      column: 'value'
      unit_of_measurement: kB
1 Like
288

That’s what I had initially which gave similar errors.

289

sorry just update my post, just copy&paste with the right password (hass_db password)

290

Okay, all good now.

I granted the hass_db user full privileges to the mysql database and seem to have done the trick.

image

@dgomes thanks for your persistent assistance, you gave me the push I needed to go digging, which I try to avoid in fair of breaking something.

Thanks again.
image

1 Like
291

Trying not to start a new thread here…

I’ve been using HA for about a month - maybe 6 weeks now. my home-assistant_v2.db file is 2.1 GB. I know there was just recently a change (as of 0.64) to set the default purge days to 10. I do not have any recorder: entry in the configuration.yaml file and I simply have the default history: entry with no configuration options under it.

So a few questions:

  • Is this a normal db size for ~1 month (I have about 60 devices configured - most are z-wave, some MQTT stuff, weather, etc.)
  • is there a way to check if the purge is working?
  • I’m still in the “getting things set up” mode - so I restart HA very often - prob once a day or more - does that play into this?

Thanks

292

That depends on the number of entities and their type; booleans take less space than textual values

My db for a small setup is ~50Mb per day.

You could try a manual purge. I have done this and can see the db being resized from Samba.

Not really. The database is kept between restarts; this is it’s purpose. However, replacing a lot of entities (with new names) will create orphans (states for entities that no longer exist). I believe they will be removed by the purge.

The separate log file is reset at every restart.

293

I have about 250 or so entities in my entity list…

My real question, I suppose, is earlier in this thread it was stated that the purge event happens every ~2 days and the clock gets reset at every HA restart. So if I restart HA once/day, I’d theoretically never purge. Is this true?

Thanks so much for your reply!

294

That behaviour was fixed back in november. Check this PR for more info.

Additional improvements have been made since then also:

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