SQL/MariaDB size sensor in MB?

Configuration
1

Sorry, I’m not very good in sql queries…
I was able to get my db size sensor working using the doc:

sensor:
  - platform: sql
    db_url: mysql://user:password@localhost/hass
    queries:
      - name: DB size
        query: 'SELECT table_schema "database", Round(Sum(data_length + index_length) / 1024, 1) "value" FROM information_schema.tables WHERE table_schema="hass" GROUP BY table_schema;'
        column: 'value'
        unit_of_measurement: kB

What should I change in this to get it in MB instead of KB?

1 Like
2

Divide the value by 1024 again:

'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;'
1 Like
3

Awesome! thank you!

1 Like
4

You could also use

SELECT table_schema "database", Round(Sum(data_length + index_length) / POWER(1024,2), 1) "value" FROM information_schema.tables WHERE table_schema="hass" GROUP BY table_schema;

Change to POWER(1024,3) if you want GB

2 Likes
5

I tried this but I get no results. Any idea on what’s going on? This is what the log tells me.

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

Thursday 1 August 2019

1 Like
6

I had to change the following to match the name of my database

image
image724×129 7.22 KB

I can then use a standard sensor card

image

1 Like