Database size sensor (MariaDB) : no long term history

Configuration
1

Hello,

I have a sensor to track the size of my database (defined in a YAML file for quite some time).

It has never really bothered me, but recently I noticed that I only have the last 10 days of history. I can’t go back any further, unlike all my other sensors for which I have history/stats from the beginning (2-3 years).

sql:
  - name: MariaDB Database Size
    db_url: !secret mariadb_url
    query: 'SELECT table_schema "homeassistant", Round(Sum(data_length + index_length) / POWER(1024,3), 3) "value" FROM information_schema.tables WHERE table_schema="homeassistant" GROUP BY table_schema;'
    column: "value"
    unit_of_measurement: GiB
    device_class: data_size
    state_class: measurement
    icon: mdi:database

homeassistant:
  customize:
    sensor.mariadb_database_size:
      scan_interval: 3600

The recorder settings are by default, without any include/exclude filters.

recorder:
  db_url: !secret mariadb_url
  auto_purge: true
  auto_repack: true

I’d really like to understand why I don’t have a complete history.
Any ideas?
Thanks.

2

Go to Developer Tools → Statistics and check if there is a Fix Issue button to the right of the entity.

3

“No issue” in dev tools / statistics

4

What are the attributes of the device listed in Developer Tools → States (right hand column)?

5

Here’s the sensor details :

image
image1107×273 20.6 KB

6

That homeassistant: homeassistant attribute is odd. It should be database: homeassistant.

Also that is not how you define a custom scan interval. This is: SQL - Home Assistant

7

I agree. Don’t know where the homeassistant attribute comes from …

I created a new sensor with the SQL integration UI, and not from a YAML file, and it also creates that attribute :

image
image1115×431 34.2 KB

8

SQL integration creates an attribute for each column in the output. From what I can see, you can just remove table_schema "homeassistant", from the select command as it is not needed for anything anyway.
Edit: or rename it to “database”: SELECT table_schema "database", Round...

9

You’re right. I wasn’t paying attention at the SQL request itself (and the GROUP BY is no longer necessary).

SELECT Round(Sum(data_length + index_length) / POWER(1024,3), 3) 'value' FROM information_schema.tables WHERE table_schema='homeassistant';

Now the sensors are “clean”. Let’s see if this will change something with the long term history. Wait and see…

If not, I’m not gonna search an explaination any longer. I think I’ll create an input sensor, which will replicate the value on every change.

10

The developer tools statistics again after making the update. Should be ok as long as you did not change the name or unique id.

11

11 days have passed, and it seems that correcting the SQL query to a single output value, has resolved the issue.
I now have long term statistics with this sensor, whether it’s created within the YAML file, or from the SQL UI integration.