MySQL Query

[Yes]

Hi,

Some time ago i was looking for a simple way to execute queries against a MySQL database from HA so that i could use the results in an automation.

To be more specific: i’m running FreePBX and wanted to announce some information when receiving inbound calls or establishing outgoing calls.

I came across the MySQL command component which inspired and guided me to build my first custom component.

Until now the MySQL Query component has been discussed in this topic.

Now and then there are some feature requests and issues that needs to be discussed. Also this component has not been introduced before.
So i decided to create this topic, instead of further hijacking the other component’s topic.

MySQL Query

A Home Assistant custom component that creates a ResponseData service to execute a query against a MySQL database. The result values become available as an iterable data structure

GitHub - IAsDoubleYou/homeassistant-mysql_query: A Home Assistant custom...

I’d really appreciate any feedback regarding this component.

[jandings]

Hi Yes,
thanks a lot for doing this; with my beginner’s knowledge I’d also like to query an external SQL DB for data within an automation and I was a bit turned off by the SQL components need to bury any SQL command in the configuration.yaml. Your solution should do the trick to read data much more conveniently.
You were also referring a lot to the “MySQL Command” component, which is allowing to WRITE data using INSERT commands to the SQL database.
Does this mean if I want to READ and WRITE data to an SQL database I would need to work with both components, “MySQL Command” and your “MySQL Query” or does your component also include/allow writing to the DB?
If not, did someone ever try if these two custom components peacefully coexist?

[Yes]

Hi jandings,

Thank you very much for your feedback.

The references i made to the MySQL Command are because it helped me a lot to develop this component.
At that time I was looking for a component that could execute dynamic queries.
But to be honest i don’t have a use case to write to a database directly from HA (yet), so i personally haven’t used MySQL Command yet.

Technically speaking the MySQL Query component should also be able to write data to a database, but since that was not the initial purpose of it and have also never been tested, i decided to let it only accept retrieval (SELECT) queries.

Regarding the coexistence of MySQL Query and MySQL Command, i don’t expect any problem with that. So yes, you should be able to use one for reading and the other one for writing.

In case you want to use data from MySQL as a sensor in a Lovelace dashboard so that it will automatically reflect changes, be sure to also check the HA MySQL component

[svidpet]

Yes,

I have the same problem as royf007 in the previous thread, namely, got empty response on query from HA. Connection from HA to db is OK:
[core-ssh ~]$ nc 192.168.1.19 3306
X
11.5.2-MariaDB-deb11Se3j@>Vwr-=X@xMETU10?+mysql_native_password
Can you please suggest where to start to solve the issue?

HA developer tools \ actions
Action
MySQL Query Service: query
action: mysql_query.query
data:
query: SELECT column FROM the.table WHERE year=2024 AND month=11;
response_variable: response

Response
result: [ ]

Core 2024.11.2
Supervisor 2024.11.2
Operating System 13.2
Frontend 20241106.2

Thank you in advance.

[Yes]

Hi,

I Just noticed your post, apologies.
I don’t understand why i haven’t received any notification before.

If the problem persists, could you please check if there are any errors related to the component in the home assistant logfile?

Also can you confirm receiving a response when trying a simple select like for example:

SELECT 1234 FROM DUAL;

[svidpet]

Hi,

No worries! Moreover, thx for your efforts.
Problem persists. This must be a stupid config issue on my side.

in Sytem\Logs\HOME ASSISTANT CORE
there’s
Could not connect to mysql server
Logger: custom_components.mysql_query
Source: custom_components/mysql_query/init.py:79

Is this a mariadb setting?
created a user:
[email protected].%
Grants
ALL PRIVILEGES

in configuration.yaml
mysql_query:
mysql_host: 192.168.1.19
mysql_port: 3306
mysql_username: dummy
mysql_password: pleasework
mysql_db: hassio

[svidpet]

found this:

Logger: homeassistant.util.loop
Source: util/loop.py:136
First occurred: 23:16:35 (5 occurrences)
Last logged: 23:16:35

Detected blocking call to open with args (‘/dev/null’, ‘w’) inside the event loop by custom integration ‘mysql_query’ at custom_components/mysql_query/init.py, line 70: _cnx = mysql.connector.connect( (offender: /usr/local/lib/python3.12/site-packages/mysql/connector/utils.py, line 419: with open(os.devnull, “w”, encoding=“utf-8”) as devnull:), please create a bug report at Issues · IAsDoubleYou/homeassistant-mysql_query · GitHub For developers, please see Blocking operations with asyncio | Home Assistant Developer Docs Traceback (most recent call last): File “”, line 198, in _run_module_as_main File “”, line 88, in _run_code File “/usr/src/homeassistant/homeassistant/main.py”, line 223, in sys.exit(main()) File “/usr/src/homeassistant/homeassistant/main.py”, line 209, in main exit_code = runner.run(runtime_conf) File “/usr/src/homeassistant/homeassistant/runner.py”, line 189, in run return loop.run_until_complete(setup_and_run_hass(runtime_config)) File “/usr/local/lib/python3.12/asyncio/base_events.py”, line 674, in run_until_complete self.run_forever() File “/usr/local/lib/python3.12/asyncio/base_events.py”, line 641, in run_forever self._run_once() File “/usr/local/lib/python3.12/asyncio/base_events.py”, line 1990, in _run_once handle._run() File “/usr/local/lib/python3.12/asyncio/events.py”, line 88, in _run self._context.run(self._callback, *self._args) File “/usr/src/homeassistant/homeassistant/setup.py”, line 165, in async_setup_component result = await _async_setup_component(hass, domain, config) File “/usr/src/homeassistant/homeassistant/setup.py”, line 416, in _async_setup_component result = await task File “/config/custom_components/mysql_query/init.py”, line 70, in async_setup _cnx = mysql.connector.connect(
Detected blocking call to open with args (‘/etc/os-release’,) inside the event loop by custom integration ‘mysql_query’ at custom_components/mysql_query/init.py, line 70: _cnx = mysql.connector.connect( (offender: /usr/local/lib/python3.12/site-packages/mysql/connector/utils.py, line 381: with open(os_release_file, encoding=“utf-8”) as file_obj:), please create a bug report at Issues · IAsDoubleYou/homeassistant-mysql_query · GitHub For developers, please see Blocking operations with asyncio | Home Assistant Developer Docs Traceback (most recent call last): File “”, line 198, in _run_module_as_main File “”, line 88, in _run_code File “/usr/src/homeassistant/homeassistant/main.py”, line 223, in sys.exit(main()) File “/usr/src/homeassistant/homeassistant/main.py”, line 209, in main exit_code = runner.run(runtime_conf) File “/usr/src/homeassistant/homeassistant/runner.py”, line 189, in run return loop.run_until_complete(setup_and_run_hass(runtime_config)) File “/usr/local/lib/python3.12/asyncio/base_events.py”, line 674, in run_until_complete self.run_forever() File “/usr/local/lib/python3.12/asyncio/base_events.py”, line 641, in run_forever self._run_once() File “/usr/local/lib/python3.12/asyncio/base_events.py”, line 1990, in _run_once handle._run() File “/usr/local/lib/python3.12/asyncio/events.py”, line 88, in _run self._context.run(self._callback, *self._args) File “/usr/src/homeassistant/homeassistant/setup.py”, line 165, in async_setup_component result = await _async_setup_component(hass, domain, config) File “/usr/src/homeassistant/homeassistant/setup.py”, line 416, in _async_setup_component result = await task File “/config/custom_components/mysql_query/init.py”, line 70, in async_setup _cnx = mysql.connector.connect(
Detected blocking call to load_default_certs with args (<ssl.SSLContext object at 0xffff841044d0>, <Purpose.SERVER_AUTH: _ASN1Object(nid=129, shortname=‘serverAuth’, longname=‘TLS Web Server Authentication’, oid=‘1.3.6.1.5.5.7.3.1’)>) inside the event loop by custom integration ‘mysql_query’ at custom_components/mysql_query/init.py, line 70: _cnx = mysql.connector.connect( (offender: /usr/local/lib/python3.12/ssl.py, line 713: context.load_default_certs(purpose)), please create a bug report at Issues · IAsDoubleYou/homeassistant-mysql_query · GitHub For developers, please see Blocking operations with asyncio | Home Assistant Developer Docs Traceback (most recent call last): File “”, line 198, in _run_module_as_main File “”, line 88, in _run_code File “/usr/src/homeassistant/homeassistant/main.py”, line 223, in sys.exit(main()) File “/usr/src/homeassistant/homeassistant/main.py”, line 209, in main exit_code = runner.run(runtime_conf) File “/usr/src/homeassistant/homeassistant/runner.py”, line 189, in run return loop.run_until_complete(setup_and_run_hass(runtime_config)) File “/usr/local/lib/python3.12/asyncio/base_events.py”, line 674, in run_until_complete self.run_forever() File “/usr/local/lib/python3.12/asyncio/base_events.py”, line 641, in run_forever self._run_once() File “/usr/local/lib/python3.12/asyncio/base_events.py”, line 1990, in _run_once handle._run() File “/usr/local/lib/python3.12/asyncio/events.py”, line 88, in _run self._context.run(self._callback, *self._args) File “/usr/src/homeassistant/homeassistant/setup.py”, line 165, in async_setup_component result = await _async_setup_component(hass, domain, config) File “/usr/src/homeassistant/homeassistant/setup.py”, line 416, in _async_setup_component result = await task File “/config/custom_components/mysql_query/init.py”, line 70, in async_setup _cnx = mysql.connector.connect(
Detected blocking call to load_default_certs with args (<ssl.SSLContext object at 0xffff841044d0>,) inside the event loop by custom integration ‘mysql_query’ at custom_components/mysql_query/init.py, line 70: _cnx = mysql.connector.connect( (offender: /usr/local/lib/python3.12/site-packages/mysql/connector/network.py, line 587: context.load_default_certs()), please create a bug report at Issues · IAsDoubleYou/homeassistant-mysql_query · GitHub For developers, please see Blocking operations with asyncio | Home Assistant Developer Docs Traceback (most recent call last): File “”, line 198, in _run_module_as_main File “”, line 88, in _run_code File “/usr/src/homeassistant/homeassistant/main.py”, line 223, in sys.exit(main()) File “/usr/src/homeassistant/homeassistant/main.py”, line 209, in main exit_code = runner.run(runtime_conf) File “/usr/src/homeassistant/homeassistant/runner.py”, line 189, in run return loop.run_until_complete(setup_and_run_hass(runtime_config)) File “/usr/local/lib/python3.12/asyncio/base_events.py”, line 674, in run_until_complete self.run_forever() File “/usr/local/lib/python3.12/asyncio/base_events.py”, line 641, in run_forever self._run_once() File “/usr/local/lib/python3.12/asyncio/base_events.py”, line 1990, in _run_once handle._run() File “/usr/local/lib/python3.12/asyncio/events.py”, line 88, in _run self._context.run(self._callback, *self._args) File “/usr/src/homeassistant/homeassistant/setup.py”, line 165, in async_setup_component result = await _async_setup_component(hass, domain, config) File “/usr/src/homeassistant/homeassistant/setup.py”, line 416, in _async_setup_component result = await task File “/config/custom_components/mysql_query/init.py”, line 70, in async_setup _cnx = mysql.connector.connect(
Detected blocking call to import_module with args (‘.mysql_native_password’, ‘mysql.connector.plugins’) inside the event loop by custom integration ‘mysql_query’ at custom_components/mysql_query/init.py, line 70: _cnx = mysql.connector.connect( (offender: /usr/local/lib/python3.12/site-packages/mysql/connector/plugins/init.py, line 151: plugin_module = importlib.import_module(f".{plugin_name}", package)), please create a bug report at Issues · IAsDoubleYou/homeassistant-mysql_query · GitHub For developers, please see Blocking operations with asyncio | Home Assistant Developer Docs Traceback (most recent call last): File “”, line 198, in _run_module_as_main File “”, line 88, in _run_code File “/usr/src/homeassistant/homeassistant/main.py”, line 223, in sys.exit(main()) File “/usr/src/homeassistant/homeassistant/main.py”, line 209, in main exit_code = runner.run(runtime_conf) File “/usr/src/homeassistant/homeassistant/runner.py”, line 189, in run return loop.run_until_complete(setup_and_run_hass(runtime_config)) File “/usr/local/lib/python3.12/asyncio/base_events.py”, line 674, in run_until_complete self.run_forever() File “/usr/local/lib/python3.12/asyncio/base_events.py”, line 641, in run_forever self._run_once() File “/usr/local/lib/python3.12/asyncio/base_events.py”, line 1990, in _run_once handle._run() File “/usr/local/lib/python3.12/asyncio/events.py”, line 88, in _run self._context.run(self._callback, *self._args) File “/usr/src/homeassistant/homeassistant/setup.py”, line 165, in async_setup_component result = await _async_setup_component(hass, domain, config) File “/usr/src/homeassistant/homeassistant/setup.py”, line 416, in _async_setup_component result = await task File “/config/custom_components/mysql_query/init.py”, line 70, in async_setup _cnx = mysql.connector.connect(

[Yes]

The error messages are most likely caused by the fact that it takes too long trying to establish the connection. The component works in synchronous mode and as the log suggests it would be better if it wasn’t. At the time I created it I wasn’t aware of that.
But that said, if the setup is ok it will not hold up the rest of HA processes.

Anyway, it looks like the database cannot be connected.
Is it a standalone server or are you using the MySQL add-on for HA?

Your configuration looks fine, however I’m not able to see if the lines following the mysql_query: line are indented?

If it’s a standalone MySQL or MariaDB server, please also check possible firewall settings.

I would also suggest an explicit grant on the database to the user:
GRANT ALL ON database.* TO user@% IDENTIFIED BY password;

I haven’t tried it with the HA MySQL add-on myself, so if that’s what you’re using I can try to duplicate that setup and see if I can reproduce your issue.

[svidpet]

@ server
This is a standalone server (MariaDB) on 192.168.1.19 hassio is on 192.168.1.25.

@configuration
Those are intended: 2 white spaces at the beginnings, from the 2nd row and on. Couldn’t make the format right in the post.

@ firewall
command in HA terminal returns:

[core-ssh ~]$ nc 192.168.1.19 3306
X
11.5.2-MariaDB-deb11Se3j@>Vwr-=X@xMETU10?+mysql_native_password

I understand this as the port is open.
I’ve also tried to remove the HA PC from the LAN and introduce an other PC as 192.168.1.25. Communication to the DB is OK.

@ grants
purged MariaDB & created DB,user and grants as suggested. By using these credentials the DB is accessible from any other PC on the network using DBeaver.

outta ideas right now…

[Yes]

I can see that you’ve already tried a lot to figure out the cause
As far as i can see, things indeed looks ok at your site.

To help troubleshooting I’ve updated the component and added extended logging.
Could you please update to v1.2.2 and switch on debugging for the component by adding custom_components.mysql_query line to the logger section of your configuration.yaml:

logger:
  default: info
  logs:
    custom_components.mysql_query: debug

After retrying, you will find extended logging of the component by searching the HA logfile for the text:

custom_components.mysql_query

Then please let me know what’s in there.

In the meantime you might also give my other component HA MySQL a try . That one is a sensor component with and also provides a set_query command to define the query for the sensor. I wonder if that one succeeds connecting to the database.

[svidpet]

Thanks.
hope this is what you are looking for:

This error originated from a custom integration.

Logger: custom_components.mysql_query
Source: custom_components/mysql_query/__init__.py:71
integration: MySQL Query Service (documentation, issues)
First occurred: 17:32:24 (1 occurrences)
Last logged: 17:32:24

Could not connect to mysql server: 1273 (HY000): Unknown collation: 'utf8mb4_0900_ai_ci'
Traceback (most recent call last):
  File "/config/custom_components/mysql_query/__init__.py", line 71, in async_setup
    _cnx = mysql.connector.connect(
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/mysql/connector/pooling.py", line 323, in connect
    return MySQLConnection(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/mysql/connector/connection.py", line 179, in __init__
    self.connect(**kwargs)
  File "/usr/local/lib/python3.12/site-packages/mysql/connector/abstracts.py", line 1438, in connect
    self._post_connection()
  File "/usr/local/lib/python3.12/site-packages/mysql/connector/abstracts.py", line 1378, in _post_connection
    self.set_charset_collation(charset=self._charset_id)
  File "/usr/local/lib/python3.12/site-packages/mysql/connector/abstracts.py", line 1353, in set_charset_collation
    self._execute_query(f"SET NAMES '{charset_name}' COLLATE '{collation_name}'")
  File "/usr/local/lib/python3.12/site-packages/mysql/connector/connection.py", line 1333, in _execute_query
    self.cmd_query(query)
  File "/usr/local/lib/python3.12/site-packages/mysql/connector/opentelemetry/context_propagation.py", line 97, in wrapper
    return method(cnx, *args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/mysql/connector/connection.py", line 872, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/mysql/connector/connection.py", line 648, in _handle_result
    raise get_exception(packet)
mysql.connector.errors.DatabaseError: 1273 (HY000): Unknown collation: 'utf8mb4_0900_ai_ci'

[Yes]

Thanks for the logging and yes that was what i was looking for.

It seems the component tries to connect to your database with the collating sequence utf8mb4_0900_ai_ci that was introduced in MySQL 8.0.
It might be that your database server doesn’t support this collation sequence.

It works in DBeaver, because it probably falls back to an older, compatible collation sequence if connecting with the default collation fails.

Things we can try:

1. You can try and see if you can alter the servers default collation sequence.

• Show the current collation settings (make a note in case you want to restore their values)

SHOW GLOBAL VARIABLES LIKE 'character_set%';
SHOW GLOBAL VARIABLES LIKE 'collation%';

• Check the available collations of the server:

SHOW COLLATION LIKE 'utf8mb4%';
SHOW CHARACTER SET;

• Pick and set one of the available collations and charactersets. as the default on the MySQL server. You can do that by issuing the following in MySQL>

SET GLOBAL collation_server = 'utf8mb4_unicode_ci';
SET GLOBAL character_set_server = 'utf8mb4';

2. I can and probably should add configuration settings for the component so that a combination of collation and characterset can be configured for the connection. With those options the custom component can then be instructed to use one that is available on the database server.
   But before i make that modification i would like to request you to check the first option before in order to confirm that explicitly specifying the collation will resolve the problem.

3. You could try to update your database server to a more recent version of MariaDB/MySQL which supports utf8mb4_0900_ai_ci by default

[svidpet]

The host machine for the MariaDB is a Debian 11.

@1

SHOW COLLATION LIKE 'utf8mb4%';

lists ~ 200 collations, utf8mb4_unicode_ci included.

setting is strange: 0 rows affected

MariaDB [(none)]> SET GLOBAL collation_server = 'utf8mb4_unicode_ci';
Query OK, 0 rows affected (0.000 sec)

& connection / database / server should be set individually to be able to return like

unfortunately, giving the same error:

@3
MariaDB updated to 11.6.2. - no luck.
Moreover, there’s no utf8mb4_0900_ai_ci in the collation list.

[Yes]

After changing the global to

you should restart HA, because only new connections to the database will pick up those settings.

If it still doesn’t,t work after restarting, I will provide you with a patched version of the custom component with a hardcoded collation setting that is in your database’ list to see if that resolves the problem.

[svidpet]

@ restart
was like that. any change in the db is followed by a restart. then a check, if the changes have been applied then a restart of the HA.

[Yes]

Ok,

i’ve added two optional configuration parameters to the component.
With those one can explicitly specify the charset and collation if needed.

I can reproduce the error you’re facing when i specify a collation that is not supported by my database server.
If i chose one that is available on my database server the connection succeeds.

Please update MySQL Query component to v1.3.0 and add following to your configuration.yaml in the mysql_query section:

  mysql_charset: utf8mb4
  mysql_collation: utf8mb4_unicode_ci

As usual restart HA after updating the component and also after updating the config.

[svidpet]

Sir, Yes Sir.

Happy to report that the component is working. Thank you so much for your work.

for those are facing the same issue, a hint: to set the collation of the MariaDB permanent:
edit /etc/mysql/conf.d/mysql.cnf & insert

[mysql]
default-character-set=utf8mb4

[mysqld]
collation-server = utf8mb4_unicode_ci
character-set-server = utf8mb4

a note: a warning in the HA log is still persist:
Logger: homeassistant.util.loop
Source: util/loop.py:136
First occurred: 19:44:04 (5 occurrences)
Last logged: 19:44:05

Detected blocking call to open with args (‘/dev/null’, ‘w’) inside the event loop by custom integration ‘mysql_query’ at custom_components/mysql_query/init.py, line 91: _cnx = mysql.connector.connect(**connect_kwargs) (offender: /usr/local/lib/python3.12/site-packages/mysql/connector/utils.py, line 419: with open(os.devnull, “w”, encoding=“utf-8”) as devnull:), please create a bug report at Issues · IAsDoubleYou/homeassistant-mysql_query · GitHub For developers, please see Blocking operations with asyncio | Home Assistant Developer Docs Traceback (most recent call last): File “”, line 198, in _run_module_as_main File “”, line 88, in _run_code File “/usr/src/homeassistant/homeassistant/main.py”, line 223, in sys.exit(main()) File “/usr/src/homeassistant/homeassistant/main.py”, line 209, in main exit_code = runner.run(runtime_conf) File “/usr/src/homeassistant/homeassistant/runner.py”, line 189, in run return loop.run_until_complete(setup_and_run_hass(runtime_config)) File “/usr/local/lib/python3.12/asyncio/base_events.py”, line 674, in run_until_complete self.run_forever() File “/usr/local/lib/python3.12/asyncio/base_events.py”, line 641, in run_forever self._run_once() File “/usr/local/lib/python3.12/asyncio/base_events.py”, line 1990, in _run_once handle._run() File “/usr/local/lib/python3.12/asyncio/events.py”, line 88, in _run self._context.run(self._callback, *self._args) File “/usr/src/homeassistant/homeassistant/setup.py”, line 165, in async_setup_component result = await _async_setup_component(hass, domain, config) File “/usr/src/homeassistant/homeassistant/setup.py”, line 416, in _async_setup_component result = await task File “/config/custom_components/mysql_query/init.py”, line 91, in async_setup _cnx = mysql.connector.connect(**connect_kwargs)
Detected blocking call to open with args (‘/etc/os-release’,) inside the event loop by custom integration ‘mysql_query’ at custom_components/mysql_query/init.py, line 91: _cnx = mysql.connector.connect(**connect_kwargs) (offender: /usr/local/lib/python3.12/site-packages/mysql/connector/utils.py, line 381: with open(os_release_file, encoding=“utf-8”) as file_obj:), please create a bug report at Issues · IAsDoubleYou/homeassistant-mysql_query · GitHub For developers, please see Blocking operations with asyncio | Home Assistant Developer Docs Traceback (most recent call last): File “”, line 198, in _run_module_as_main File “”, line 88, in _run_code File “/usr/src/homeassistant/homeassistant/main.py”, line 223, in sys.exit(main()) File “/usr/src/homeassistant/homeassistant/main.py”, line 209, in main exit_code = runner.run(runtime_conf) File “/usr/src/homeassistant/homeassistant/runner.py”, line 189, in run return loop.run_until_complete(setup_and_run_hass(runtime_config)) File “/usr/local/lib/python3.12/asyncio/base_events.py”, line 674, in run_until_complete self.run_forever() File “/usr/local/lib/python3.12/asyncio/base_events.py”, line 641, in run_forever self._run_once() File “/usr/local/lib/python3.12/asyncio/base_events.py”, line 1990, in _run_once handle._run() File “/usr/local/lib/python3.12/asyncio/events.py”, line 88, in _run self._context.run(self._callback, *self._args) File “/usr/src/homeassistant/homeassistant/setup.py”, line 165, in async_setup_component result = await _async_setup_component(hass, domain, config) File “/usr/src/homeassistant/homeassistant/setup.py”, line 416, in _async_setup_component result = await task File “/config/custom_components/mysql_query/init.py”, line 91, in async_setup _cnx = mysql.connector.connect(**connect_kwargs)
Detected blocking call to load_default_certs with args (<ssl.SSLContext object at 0xffff934b01d0>, <Purpose.SERVER_AUTH: _ASN1Object(nid=129, shortname=‘serverAuth’, longname=‘TLS Web Server Authentication’, oid=‘1.3.6.1.5.5.7.3.1’)>) inside the event loop by custom integration ‘mysql_query’ at custom_components/mysql_query/init.py, line 91: _cnx = mysql.connector.connect(**connect_kwargs) (offender: /usr/local/lib/python3.12/ssl.py, line 713: context.load_default_certs(purpose)), please create a bug report at Issues · IAsDoubleYou/homeassistant-mysql_query · GitHub For developers, please see Blocking operations with asyncio | Home Assistant Developer Docs Traceback (most recent call last): File “”, line 198, in _run_module_as_main File “”, line 88, in _run_code File “/usr/src/homeassistant/homeassistant/main.py”, line 223, in sys.exit(main()) File “/usr/src/homeassistant/homeassistant/main.py”, line 209, in main exit_code = runner.run(runtime_conf) File “/usr/src/homeassistant/homeassistant/runner.py”, line 189, in run return loop.run_until_complete(setup_and_run_hass(runtime_config)) File “/usr/local/lib/python3.12/asyncio/base_events.py”, line 674, in run_until_complete self.run_forever() File “/usr/local/lib/python3.12/asyncio/base_events.py”, line 641, in run_forever self._run_once() File “/usr/local/lib/python3.12/asyncio/base_events.py”, line 1990, in _run_once handle._run() File “/usr/local/lib/python3.12/asyncio/events.py”, line 88, in _run self._context.run(self._callback, *self._args) File “/usr/src/homeassistant/homeassistant/setup.py”, line 165, in async_setup_component result = await _async_setup_component(hass, domain, config) File “/usr/src/homeassistant/homeassistant/setup.py”, line 416, in _async_setup_component result = await task File “/config/custom_components/mysql_query/init.py”, line 91, in async_setup _cnx = mysql.connector.connect(**connect_kwargs)
Detected blocking call to load_default_certs with args (<ssl.SSLContext object at 0xffff934b01d0>,) inside the event loop by custom integration ‘mysql_query’ at custom_components/mysql_query/init.py, line 91: _cnx = mysql.connector.connect(**connect_kwargs) (offender: /usr/local/lib/python3.12/site-packages/mysql/connector/network.py, line 587: context.load_default_certs()), please create a bug report at Issues · IAsDoubleYou/homeassistant-mysql_query · GitHub For developers, please see Blocking operations with asyncio | Home Assistant Developer Docs Traceback (most recent call last): File “”, line 198, in _run_module_as_main File “”, line 88, in _run_code File “/usr/src/homeassistant/homeassistant/main.py”, line 223, in sys.exit(main()) File “/usr/src/homeassistant/homeassistant/main.py”, line 209, in main exit_code = runner.run(runtime_conf) File “/usr/src/homeassistant/homeassistant/runner.py”, line 189, in run return loop.run_until_complete(setup_and_run_hass(runtime_config)) File “/usr/local/lib/python3.12/asyncio/base_events.py”, line 674, in run_until_complete self.run_forever() File “/usr/local/lib/python3.12/asyncio/base_events.py”, line 641, in run_forever self._run_once() File “/usr/local/lib/python3.12/asyncio/base_events.py”, line 1990, in _run_once handle._run() File “/usr/local/lib/python3.12/asyncio/events.py”, line 88, in _run self._context.run(self._callback, *self._args) File “/usr/src/homeassistant/homeassistant/setup.py”, line 165, in async_setup_component result = await _async_setup_component(hass, domain, config) File “/usr/src/homeassistant/homeassistant/setup.py”, line 416, in _async_setup_component result = await task File “/config/custom_components/mysql_query/init.py”, line 91, in async_setup _cnx = mysql.connector.connect(**connect_kwargs)
Detected blocking call to import_module with args (‘.mysql_native_password’, ‘mysql.connector.plugins’) inside the event loop by custom integration ‘mysql_query’ at custom_components/mysql_query/init.py, line 91: _cnx = mysql.connector.connect(**connect_kwargs) (offender: /usr/local/lib/python3.12/site-packages/mysql/connector/plugins/init.py, line 151: plugin_module = importlib.import_module(f".{plugin_name}", package)), please create a bug report at Issues · IAsDoubleYou/homeassistant-mysql_query · GitHub For developers, please see Blocking operations with asyncio | Home Assistant Developer Docs Traceback (most recent call last): File “”, line 198, in _run_module_as_main File “”, line 88, in _run_code File “/usr/src/homeassistant/homeassistant/main.py”, line 223, in sys.exit(main()) File “/usr/src/homeassistant/homeassistant/main.py”, line 209, in main exit_code = runner.run(runtime_conf) File “/usr/src/homeassistant/homeassistant/runner.py”, line 189, in run return loop.run_until_complete(setup_and_run_hass(runtime_config)) File “/usr/local/lib/python3.12/asyncio/base_events.py”, line 674, in run_until_complete self.run_forever() File “/usr/local/lib/python3.12/asyncio/base_events.py”, line 641, in run_forever self._run_once() File “/usr/local/lib/python3.12/asyncio/base_events.py”, line 1990, in _run_once handle._run() File “/usr/local/lib/python3.12/asyncio/events.py”, line 88, in _run self._context.run(self._callback, *self._args) File “/usr/src/homeassistant/homeassistant/setup.py”, line 165, in async_setup_component result = await _async_setup_component(hass, domain, config) File “/usr/src/homeassistant/homeassistant/setup.py”, line 416, in _async_setup_component result = await task File “/config/custom_components/mysql_query/init.py”, line 91, in async_setup _cnx = mysql.connector.connect(**connect_kwargs)

[Yes]

Now, that’s really good news!
And let me thank you for your cooperation, patience and feedback.

As i mentioned before, HA issues a warning because the actions the component performs should be done asynchronously, which it doesn’t.

Since it won’t stop the component from functioning and TBH i don’t have any idea how many people are using the component, I wasn’t sure if it would be worth the time needed to adjust it.
But i might spend a couple of hours to make the necessary adjustments soon.

Anyway, thanks again!
And…i must admit that i like your sql statement

[svidpet]

next question
is it possible to use a variable (eg from a sensor) in the sql statement?
something like

i’ve tried to find some solution in the thread

MySQL Command

Hi! I like storing data in my own MySQL database. But - whether it is hosted via Home Assistant’s MariaDB addon or externally - I was always baffled by the fact that there was no sleek way to insert data from Home Assistant. In past times I used a shell_command to call the MySQL Command-Line Client. But after switching to Home Assistant OS this was becoming tricky. So the idea of creating my own custom component grew in my head. I knew that the code wouldn’t be that complex. So one day I sat d…

but was unable to decode the right syntax.

[Yes]

You should use a template for that.
You can test the template beforehand on the Developers Template page.

Example query with sensor’s state value (in this case ‘on’ or ‘off’):

action: mysql_query.query
data:
  query: >
    select distinct
           t2.entity_id 
    from   states t1
    ,      states_meta t2 
    where  t1.metadata_id = t2.metadata_id 
    and    t2.entity_id like '%contact%'
    and    state = '{{ states.binary_sensor.hue_secure_contact_sensor_2_sabotage.state }}' 
    limit 4

The query results the first 4 entity_id’s with entity_id like ‘%contact%’ that have the same state as the sensor from the query.