Trying to get the db size with sql sensor in hass.io with mariadb

Tags: #<Tag:0x00007fc40c8a6bf0>

I’m trying to set up an sql sensor to get hass.io databse size
I am running the mariadb addon

I have followed the example in https://www.home-assistant.io/components/sensor.sql/ but I only get this error:

SELECT table_schema “database”, Round(Sum(data_length + index_length) / 1024 / 1024, 1) “value” FROM information_schema.tables WHERE table_schema=“hass” GROUP BY table_schema LIMIT 1; returned no results

This is the sensor configuration:

  - platform: sql
    db_url: !secret url_mysql
    scan_interval: 3600
    queries:
      - name: db_size
        query: 'SELECT table_schema "database", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "value" FROM information_schema.tables WHERE table_schema="hass" GROUP BY table_schema;'
        column: 'value'
        unit_of_measurement: MB

The recorder component is working great with the same db_url.

Hi, if like me you followed the example from the mariadb addon page for hass.io to create your DB, you will need to change the table_schema=“hass” to table_schema=“homeassistant” in your SQL.

I had the exact same problem until I noticed it was trying to pull info for the wrong DB name.

- platform: sql
  db_url: !secret mysql_url
  icon: mdi:database
  queries:
    - name: HASS DB size
      query: 'SELECT table_schema "database", Round(Sum(data_length + index_length) / 1048576, 2) "value" FROM information_schema.tables WHERE table_schema="homeassistant" GROUP BY table_schema;'
      column: 'value'
      unit_of_measurement: MB
3 Likes

Thank you! Your solution worked like a charm. I supposed it was related with the db name.

1 Like

Any idea how to fix this? Sensor still works. Thanks for the config.

DeepinScreenshot_select-area_20190525220925

I guess your icon option should be moved to customize.yaml

you need to post your yaml

I am running the mariadb addon
i will get time but show unknow

SELECT TIMEDIFF(created , last_changed) AS ActiveTime FROM homeassistant.states WHERE entity_id = 'vacuum.living_room_vacuum' AND state = 'cleaning' GROUP BY HOUR(created) ORDER BY state_id DESC LIMIT 0 , 1;




Where is the Maria DB. I want to see the size of the db but cannot find it. I am using HASSIO.

install the addon from the addon store