SQL Query Sensor - MariaDB Size not working

Configuration
1

I’m trying to create a sensor to measure my MariaDB size as per the docs. I’m using the mariadb addon and the same query works fine in the phpmyaddon when i execute it - i get a value as expected. I changed the db name to ‘homeassistant’ in the query.

However, the sensor shows as unknown and i get the following in my logs. What am I missing? The mariadb is used as my only recorder.

- platform: sql
  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

Error executing query SELECT table_schema “database”, Round(Sum(data_length + index_length) / 1024, 1) “value” FROM information_schema.tables WHERE table_schema=“homeassistant” GROUP BY table_schema LIMIT 1;: (sqlite3.OperationalError) no such table: information_schema.tables [SQL: SELECT table_schema “database”, Round(Sum(data_length + index_length) / 1024, 1) “value” FROM information_schema.tables WHERE table_schema=“homeassistant” GROUP BY table_schema LIMIT 1;] (Background on this error at: Error Messages — SQLAlchemy 1.4 Documentation)

1 Like
2 
  - platform: sql
    db_url: mysql://username:password@localhost/hass_db?charset=utf8
    queries:
      - name: hass_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

Try this. Change the URL to match your setup and “hass_db” to the name of your DB.

2 Likes
3

Thank you - got it working for me with

mysql://username:password@core-mariadb/homeassistant?charset=utf8

2 Likes
4

Hi,

I am struggling to get this working

Logins
- username: homeassistant
  password: password

Rights
- username: homeassistant
  database: homeassistant

Hostname
core-mariadb

    db_url: mysql://homeassistant:password@core-mariadb/homeassistant?charset=utf8
    queries:
      - name: hass_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='homeassistant'"
        column: 'size_mb'
        unit_of_measurement: MB

Any assistance please?

5

It is kind of problematic with no further information regarding what is happening or what is not working.

I have running exactly the same query without a problem. The only difference I see is that I use as charset in the db_url “utf8mb4”.

But have you installed the phpMyAdmin addon and tried the query there? Perhaps you see better errors you can share.