SQL Integration not working - SQL Query invalid

Configuration
1

Hi all,
I’m trying to get a temperature of a sensor 24 hours ago using SQL Integration, but it doesn’t work.
I’,m getting: SQL Query invalid

Here what filled in:
Name: Temperatura Esterna Ieri
Database URL: empty - default HA DB is Mysql and is ok. Tried using the DB uri as well
Column: state
Select Query:

SELECT * FROM states WHERE entity_id = 'sensor.0x00158d000486dcb3_temperature' AND last_updated BETWEEN DATE_SUB(NOW(), INTERVAL 1 DAY) AND NOW() ORDER BY last_updated LIM
IT 1;

Tried even only SELECT * FROM states, nothing.
Unit: tried empty or °C

Ideas why?

2

Where are you running this statement? phpMyAdmin? If not, I would try that first to make sure you get the results.

3

Hello @CO_4X4 , I’m getting results testing the query on SQL directly using an SQL Client directly connected to the sql server.
Home Assistant Core 2022.6.1

4

Hi @CO_4x4 again.
No idea why, refreshing all, now it works. I would like to know, how often will this query executed? Every page refresh of sensors or when?
Thanks, Simon

5

Hello, I have a database from which I want to read values.

db_url: mysql://homeassistant:[email protected]:3307/arduino_02
column: CzujnikPiecaPalenisko
query: SELECT * FROM czujniki_pieca ORDER BY id DESC LIMIT 1

I don’t know why but I keep getting the message → SQL Query invalid

please help me, i’m losing my patience

sorry for poor english

6

Hi,
Have you tried to connect manually to the MySQL server and execute the query? Does it work?
Cheers, Simon

7

Hey! Yes, it works fine.

I also tried like this

db_url: mysql://homeassistant:[email protected]:3307/arduino_02
column: CzujnikPiecaPalenisko
query: SELECT "CzujnikPiecaPalenisko" AS temperature FROM "czujniki_pieca" ORDER BY "id" DESC LIMIT 1;

Zrzut ekranu 2023-01-8 o 02.01.12
Zrzut ekranu 2023-01-8 o 02.01.121200×1594 94 KB

but i still get the message → SQL Query invalid

1 Like
8

Same issue w/ postgresql

SELECT (pg_database_size("homeassistant")/1024/1024) as "db_size"';

works fine in pgAdmin

image
image1280×1044 63.8 KB

but fails in the integration menu, w/ and without db url.

9

Hi,
Try unsing single quote. Here my setup:
Database URL:
mysql://homeassistant:[email protected]/homeassistant?charset=utf8
Select query:

SELECT * FROM states WHERE entity_id = 'sensor.0x00158d000486dcb3_temperature' AND last_updated BETWEEN DATE_SUB(NOW(), INTERVAL 1 DAY) AND NOW() ORDER BY last_updated LIMIT 1

Column:
state
Unit:
°C

Hope that helps, Simon