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

[foraster]

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 SQL - Home Assistant 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.

[allan]

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

[foraster]

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

[eh50 (Ezra)]

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

[foraster]

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

[HypnoToad]

you need to post your yaml

[wanball]

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;

[rlust]

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

[Matthi_Trb (Matthi)]

install the addon from the addon store

[flotteemse]

Not sure if you got you question answered - but you can use the above yaml code to get a sensor that shows the db size