Sensor MariaDB size not working

Tsar · April 2, 2022, 4:46pm

Hi,

I copied this of another topic, but it’s not working

I’m sure it must be something stupid, but…

- platform: sql
  db_url: mysql://homeassistant:9SxxxxxxWt@core-mariadb/homeassistant?charset=utf8
  queries:
    - name: DB size
      query: "SELECT table_schema AS 'db_name', ROUND(SUM( data_length + index_length ) / 1024 / 1024, 2) AS 'size_mb' FROM information_schema.TABLES WHERE table_schema='hass_db'"
      column: "size_mb"
      unit_of_measurement: MB

But after restart of HA I get nothing :

Janneman · April 2, 2022, 5:05pm

Schouldn’t ‘hass_db’ be ‘homeassistant’ in your case?

Tsar · April 2, 2022, 5:09pm

Yes, so stupid

Thanks !

Janneman · April 2, 2022, 5:23pm

There are no stupid questions. You just overlooked it.
and someone else with a fresh look doesn’t.

jwderoode · January 5, 2023, 3:43pm

Nope Glad you asked so now I can build upon your work Thanks Tsar & Janneman
For me this worked:

Settings → Devices → Add Integration → SQL
Now fill in all the fields there like this:

And the Sensor appears… just for future reference I thought give the steps an update

Wxll

oneseventhree · February 2, 2023, 9:27pm

this helped a lot! thank you!

Ritter-Runkel · March 19, 2023, 5:07pm

Hi ho communinity,
I don’t know what to change here. The database is reachable (it worked as the recorder) but the query seems to be wrong ;-(

User+pw will be exchanged by real secrets

Here is the code:

  ### MARIA DB SIZE ##############################################################
  - platform: sql
    db_url: mysql://user:[email protected]:3307/homeassistant?charset=utf8mb4;ssl=true
    queries:
      - name: MariaDB size
        query: "SELECT table_schema AS 'homeassistant', ROUND(SUM( data_length + index_length ) / 1024 / 1024, 2) AS 'size_mb' FROM information_schema.TABLES WHERE table_schema='homeassistant'"
        column: "value"
        unit_of_measurement: MB

Thanks in advance!
//Erik

Pepeanuts · April 18, 2023, 1:41pm

Read the documentation : SQL - Home Assistant

Replace size_mb by value.

xbmcnut · April 30, 2023, 1:32am

Been using HA for 7yrs and didn’t even know that was a thing! Thank you.

xbmcnut · April 30, 2023, 1:40am

Did you get it working?

Ritter-Runkel · May 3, 2023, 10:30am

Hmmm,
I put this code to confi…yaml but the sensor is not being created?
I’m lost ;-(

  ### MARIA DB SIZE ##############################################################
  - platform: sql
    db_url: mysql://user:[email protected]:3307/homeassistant?charset=utf8mb4;ssl=true
    queries:
      - name: MariaDB size
        query: "SELECT table_schema AS 'homeassistant', ROUND(SUM( data_length + index_length ) / 1024 / 1024, 2) AS 'value' FROM information_schema.TABLES WHERE table_schema='homeassistant'"
        column: 'value'
        unit_of_measurement: MB

[/quote]

Does anybody have a better idea?

Best regards
Erik

BertrumUK · May 3, 2023, 2:56pm

Does this work ?

### MARIA DB SIZE ##############################################################
  - platform: sql
    db_url: mysql://user:[email protected]:3307/homeassistant?charset=utf8mb4;ssl=true
    queries:
      - name: MariaDB size
        query: "SELECT table_schema "database", Round(Sum(data_length + index_length) / POWER(1024,2), 1) "value" FROM information_schema.tables WHERE table_schema="homeassistant" GROUP BY table_schema;"
        column: 'value'
        unit_of_measurement: MB

jonatanpradasn · May 9, 2023, 4:48pm

I have been trying recently and although the console works from phpmyadmin, the sensor does not appear in home assistant.

recorder:
    db_url: mysql://user_hadb:[email protected]/db_ha?charset=utf8mb4
    purge_keep_days: 365

sensor:
    - platform: sql
      db_url: mysql://user_hadb:[email protected]/db_ha?charset=utf8
      queries:
        - name: MariaDB size
          query: 'SELECT table_schema "database", Round(Sum(data_length + index_length) / 1048576, 2) "value" FROM information_schema.tables WHERE table_schema="db_ha" GROUP BY table_schema;'
          column: 'value'
          unit_of_measurement: MB

X4V1 · June 20, 2023, 5:31pm

For the next ones who could pass here. I struggled with that too.

There are my findings:

Nesting the config in the sensor array did not worked for me. Putting the config outside of sensor worked.

Example:

NOT WORKING:

sensor:
  - platform: sql
    db_url: !secret maria_db_url
    scan_interval: 3600
    queries:
      - name: MariaDB DB Size
        query: 'SELECT table_schema "database", Round(Sum(data_length + index_length) / POWER(1024,2), 1) "value" FROM information_schema.tables WHERE table_schema="ha-record" GROUP BY table_schema;'
        column: "value"
        unit_of_measurement: MB

WORKING:

sql:
  - name: MariaDB Database Size
    db_url: !secret maria_db_url
    query: 'SELECT table_schema "database", Round(Sum(data_length + index_length) / POWER(1024,2), 1) "value" FROM information_schema.tables WHERE table_schema="ha-record" GROUP BY table_schema;'
    column: "value"
    unit_of_measurement: MB
    device_class: data_size

LIMITATION:
There is no way to set the “scan_interval” from sensor. To be honest I have no idea how often it will update. I don’t see any config that allow me to define that.

But at least the sensor is there. Maybe not exactly how I wanted at first but it’s already a beginning. If I find a way to configure the update interval I’ll update this post.

Deoptim · July 4, 2023, 6:59am

you can use customize configuration to set scan_interval
for example:

homeassistant:
  name: Home
  unit_system: metric
  ...
  customize:
    sensor.mariadb_database_size:
      scan_interval: 3600

Ben-Da · February 5, 2024, 8:09pm

The answer from X4V1 was not working for me, but gave me the hint to use sql instead of sensor.

I use this code

sql:
  - name: MariaDB Database Size
    db_url: !secret mariadb_url
    query: SELECT table_schema AS 'db_name', ROUND(SUM( data_length + index_length ) / 1024 / 1024, 2) AS 'size_mb' FROM information_schema.TABLES WHERE table_schema='homeassistant'
    column: "size_mb"
    unit_of_measurement: MB
    device_class: data_size

grumete · December 9, 2024, 12:10pm

Gracias. Dos dias buscando y lo tienes tu la solución.