Sensor MariaDB size not working

Hi,

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

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 :

1 Like

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

Yes, so stupid :pleading_face:

Thanks !

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

1 Like

Nope Glad you asked so now I can build upon your work :slight_smile: Thanks Tsar & Janneman :slight_smile:
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 :slight_smile:

Wxll

11 Likes

this helped a lot! thank you!

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 :wink:

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

2 Likes

Read the documentation : SQL - Home Assistant

Replace size_mb by value. :slight_smile:

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

Did you get it working?

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

1 Like

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
1 Like

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

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.

2 Likes

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
2 Likes

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