SQL Sensor driving me crazy

Configuration
1

I am trying to get my mariadb database size into HA via the sql sensor. HA 2022.6.4 and the mariadb addon. The addon works fine, but the SQL sensor won’t configure. It keeps telling me the SQL query is invalid in the dialog. (Never mind the aggravation that every time that happens you have to complete the full form again - who thought of that as a positive UX?)

The query is straight out of the docs, for the size of the database

'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;'

the only difference with the docs is specifying table_schema="homeassistant" , which is what is says to do in the docs, to take into account what I named my database.

Change table_schema="hass" to the name that you use as the database name, to ensure that your sensor will work properly.

The other panels in the setup dialog are filled in as follows:

Select SQL Query - DB Size
Database URL - left blank as this is the default recorder database

The SQL integration will connect to default recorder if Database URL is not specified. (docs)

Column - value

Use value as column for value.

I have tried to use single quotes 'SELECT... ' double quotes "SELECT..." and no quotes SELECT... in the query - the docs use all three, but none of them work. I is always the same response.

If I enter the mariadb addon container, the query works fine

MariaDB [(none)]> 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;
+---------------+-----------+
| database      | value     |
+---------------+-----------+
| homeassistant | 9951920.0 |
+---------------+-----------+
1 row in set (0.001 sec)

Any help appreciated!

2

According to the docs it should be:

'SELECT ROUND(page_count * page_size / 1024 / 1024, 1) as size FROM pragma_page_count(), pragma_page_size();'

Correct.

Should be:

size
3

That is for SQLite …Maria/MySQL is the other one.
I have the same issues 2022.6.1 and just now also tried via yaml but this is not accepted…it wants to go through GUI so it seems

4

got it working but donot like it at all…on the dburl one must specify access.
alike:

mysql://username:password@localhost/hass_db?charset=utf8

So… I am probably not going to use this unless someone finds a way how to connect without the user/pwd…checking now if I can create a read-only use for specific tables only

5

OK, I tried every possible combination, with “AS” and some other things, nothing is working.

I also tried to run the different queries in six different SQL validation tools, all of them said the query is valid.

I even tried it with setting the url and credentials in the form, but no success… :frowning:

I’m sorry, but I’m out of ideas now… :expressionless: At least you know, you’re not the only one. :slight_smile:

6

did you read my reply? you need to specify the url with user and password…it works

7

please vote here if you agree
SQL sensor should also allow ‘blank’ dburl in case of non SQLite in case non-SQLite is default - Feature Requests - Home Assistant Community (home-assistant.io)

8

I read that, yes, but in my case it doesn’t work… :slight_smile:

I still get the query error. And looking at the code of the sensor, it shouldn’t be necessary to setup the credentials and the url. It clearly relies on the config entry in HA, so if connection works outside that sensor, it should work inside as well…

9

What query are you using?
I now added a read-only user (still too much access but it is a start) and this works for me

image
image604×678 30.1 KB

10

I used the query from above/the documentation. But I’m just testing, I’m not the one with the initial problem, I don’t use an SQL sensor.

But what I find interesting, is the window you’re showing. Mine looks different (the order):

ha_sql_sensor_01
ha_sql_sensor_01404×749 17.7 KB

But that just may be that you already have a working sensor, where as mine shows the initial setup of a sensor. :slight_smile:

11

Mine shows the SQL sensor after (!) it was accepted so that explains the diff. The only way I got it to work is with proper mysql db_url incl the hard-coded user/pwd

12

Thanks, I’ll try that when I am back at the keyboard. The docs are either wrong, misleading or ambiguous. Cheers to everyone who worked so hard while I was asleep.

13

Sleeping the sleep of the just :rofl: :rofl:

But at the other end of the world… :smiley:

1 Like
14

Yes because Nick said this:

The default is SQLite, but I just realised he also said:

So it is unclear which he is using, if it is MariaDB, ignore everything I said.

15

It is mariadb. I assumed that when the docs said

The SQL integration will connect to default recorder if Database URL is not specified

it referred to the recorder setting that I had made the default in my configuration.yaml ie YOUR default reorder

But even if I manually put in the database url mysql://username:passwd@core-mariadb/homeassistant?charset=utf8mb4 which is what is set in my configuration.yaml, and works for recorder, I still get the same result. So bloody frustrating.

16

Yeah mariadb is not the default recorder.

Here’s my working config if it helps:

Untitled
Untitled613×686 30.2 KB 

mysql://user:password@core-mariadb/homeassistant?charset=utf8mb4

SELECT table_schema "database", Round(Sum(data_length + index_length) / 1048576, 2) "value" FROM information_schema.tables WHERE table_schema="homeassistant" GROUP BY table_schema
6 Likes
17

Right, just to finish this off and thank you all again, the secrets were:

  1. I was wrong about the need to supply mysql url, even if it is set in recorder. Use the same url as you specified in configuration.yaml

  2. My final gotcha was not to put any quotes around the query.

Thanks all. I marked @tom_l as the solution because this one put me on to the lack of need for quoting, but others helped too.

1 Like
18

Great you got it sorted! :slight_smile:

1 Like
19

FWIW I opened a doc discussion SQL Sensor · Issue #23066 · home-assistant/home-assistant.io · GitHub