@Yes Thank you for creating this custom service. Installed and working on my side with Home Assistant 2023.12.1 against Mariadb.
You’re welcome.
Glad to hear it works for you too.
And many thanks for your time to respond.
I was wondering if I can seek your advice. I am able to do a test mysql_query in dev tools.
But wondering how to call your service and display the mysql response in lovelace UI? (2nd screenshot shows entity display with desired mysql response as secondary info.)
Happy to use a custom UI component if that is required.
Thank you in advance for your help. Happy holidays!
If you pick a card that supports templating, you should be able to show the result by using:
{{ response.result.ts }}
to show the item.
I’m using the custom:button-card for my dashboards.
1 Like
@Yes Can you give an example of how I can call your service in lovelace UI without having to click a button (or do anything)?
I’m not sure, but afaik, you’ll always have to respond to a trigger in order to call any HA service.
You could create a Template Helper and use that in Lovelace.
But you’ll still have to call the service now and then to update the Template Helper’s underneath value.
The trigger to call the service could be a timer or other event such as an entity’s state that is changing.
I’m not sure if this approach makes sense for you.
Maybe if you could explain your use case in more detail, hopefully someone else might come up with an other approach.
My use case: As you can see from my lovelace screenshot above, I display the last_change timestamp for each of my entities. However the last_change timestamp does not always reflect the last change time if I reboot HA, last change timestamp would then be the reboot timestamp. (This is a well-known gripe for users Persist last_changed between HA restarts)
The idea is to do a mysql select like shown in my first screenshot to retrieve the correct last_change value for each entity and display with each entity in lovelace.
I hope that makes sense.
Ah, i see what you’re trying to achieve.
As said, you’ll have to call the MySQL service from a trigger. In your case the trigger will be HomeAssistant starting up.
So you need to follow these steps:
-
First create a Helper entity of type Date and time and give it a name, for example Door1TimeStampHelper
-
Next create an Automation that populates the Helper entity when HA starts:
alias: refresh_last_change_timestamp
description: ""
trigger:
- platform: homeassistant
event: start
- platform: homeassistant
event: start
condition: []
action:
- variables:
response: null
- service: mysql_query.query
data:
query: select last_updated_ts from states where entity_id = 'binary_sensor.front_door'
response_variable: response
- service: input_datetime.set_datetime
target:
entity_id: input_datetime.Door1TimeStampHelper
data:
value: "{{ response.result[0].last_updated_ts }}"
mode: single
- Finally you should now be able to use Door1TimeStampHelper entity in Lovelace
I haven’t tested above literally, so you may have to adjust the entity names and the SQL query. But that should be no problem, since I saw you already had an advanced query in your screenshot 
One more thing:
You will actually also have to update the Helper entity at the moment the garagedoor is being opened or closed.
For that you can follow one of below scenarios:
- Add an extra trigger to the automation above, to also fire when the state of the garagadoor changes.
- Create a separate automation to set the Helper’s entity value to the state of the garagedoor when that changes
@Yes Hmmm, I was trying to avoid having to create more entities like input_datetime.Door1TimeStampHelper to store timestamp values that are already in DB.
Is there no way to display the results of a mysql select for each entity in lovelace? (where the user does not have to tap/click anything)
I do understand wat you’re looking for, but unfortunately i have no clue right now how to achieve this.
1 Like
I was browsing through the list of available official HA integrations when
I came across the SQL integration. Could this be what you’re looking for?
I haven’t tried it myself (yet), but it seems it lets one create sensors based on SQL Queries. Not just for MySQL databases but for other DB engines as well.
With sensors you should be able to display the retrieved values in Lovelace without a user having to press a button etc.
You will still need a query per entity I guess.
Forum thread that discusses how to handle query updates.
Yes, I thought about the above SQL sensor but adding more entities simply to store a timestamp that is already in DB seems to be inefficient.
First of all, this is a fantastic integration. I’m using it to pull energy usage statistics for a given date range.
I wrote a complex query in my automation using the SQL WITH clause against MariaDB. It always returns an empty response.
The query runs fine directly on the database. Below is an example of this type of query (this simple example shows the issue). The “response” is always empty though.
description: >-
Test SQL With Clause
mode: single
trigger:
- platform: state
entity_id:
- input_button.run_energy_cost_report_button
condition: []
action:
- service: mysql_query.query
data:
query: |
WITH ABC AS (SELECT 'Hello' FROM DUAL)
SELECT * FROM ABC
response_variable: response
- service: input_text.set_value
data:
value: |
{{ response }}
target:
entity_id: input_text.sql_result
I’m not sure if this is a bug? I’ve since re-written my query to use a sub-select, instead of the WITH clause.
On another note… I don’t think the next issue is an integration bug, but maybe the way Home Assistant works. For testing, I’m trying to set the same input text helper, called input_text.sql_result, (configured for a max of 3000 characters) with the result of a query, and the set_value service is not storing the result in the input text helper.
I’m not sure if Home Assistant can’t handle the length, newlines, special characters, etc. Below is the SQL response JSON I’m trying to set, which is returned from my query:
{'result': [{'date': datetime.date(2024, 5, 29), 'datetime_local':
datetime.datetime(2024, 5, 29, 22, 0, 10, 354330), 'latest_created_ts':
1717038010.3387063, 'statistics_id': 17, 'stat_id_name':
'sensor.4244_consumed_energy_kwh_cost', 'total_sum': 0.41204799999999797,
'mean': None, 'min': None, 'max': None, 'last_reset': None,
'last_reset_ts': 1717032229.294907, 'state': 0.263755999999998, 'sum':
0.41204799999999797}, {'date': datetime.date(2024, 5, 30),
'datetime_local': datetime.datetime(2024, 5, 30, 0, 0, 10, 346312),
'latest_created_ts': 1717041610.3463125, 'statistics_id': 1197,
'stat_id_name': 'sensor.consumed_energy_kwh_cost', 'total_sum':
1.421792, 'mean': None, 'min': None, 'max': None, 'last_reset': None,
'last_reset_ts': 1717032229.294907, 'state': 1.2735, 'sum': 1.421792}]}
I would think that any text can be stored as long as it doesn’t exceed the size limit?
Hi all,
This thread originally belongs to the MySQL Command and although not on purpose, meanwhile it feels like i’ve hijacked it.
I decided to create a separate topic where the MySQL Query component can be discussed.
Please continue over there for MySQL Query related questions or requests.
Thanks for your kindness, tolerating the usage of this thread qrioniclabs
Hi, Thanks for the create components. Works very well and opens up a whole new area of possibilities.
One thing that would be good is to allow for calling stored procedures. I have changed my notify.py file to have this line
cursor.callproc(message)
on line 83.
Then I can just put the name of the stored procedure in the message attribute in the service call.
Would be good if there was an option to allow this. Thanks
Dear qrioniclabs,
I run into a collation error, see HA log:
Logger: homeassistant.helpers.script.websocket_api_script
Source: helpers/script.py:526
First occurred: 22:27:35 (1 occurrences)
Last logged: 22:27:35
websocket_api script: Error executing script. Unexpected error for call_service at pos 1: 1273 (HY000): Unknown collation: 'utf8mb4_0900_ai_ci'
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/helpers/script.py", line 526, in _async_step
await getattr(self, handler)()
File "/usr/src/homeassistant/homeassistant/helpers/script.py", line 764, in _async_call_service_step
response_data = await self._async_run_long_action(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/helpers/script.py", line 727, in _async_run_long_action
return await long_task
^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/core.py", line 2802, in async_call
response_data = await coro
^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/core.py", line 2845, in _execute_service
return await target(service_call)
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/components/notify/legacy.py", line 256, in _async_notify_message_service
await self.async_send_message(**kwargs)
File "/usr/src/homeassistant/homeassistant/components/notify/legacy.py", line 236, in async_send_message
await self.hass.async_add_executor_job(
File "/usr/local/lib/python3.12/concurrent/futures/thread.py", line 58, in run
result = self.fn(*self.args, **self.kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/config/custom_components/mysql_command/notify.py", line 74, in send_message
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'
as i did it with the query component.
Would it be possible to you to add such a parameter to your component?
after some googling, probably the library generating the issue*.
*edit. in the case of MariaDB. So the improvement would help those with MariaDB.
With MySQL, the component is sound.
Thank you in advance.
I have the same issue too 
Working solution :
Go into : custom_components/mysql_command/notify.py
Find the function send_message and add two lines :
charset='utf8mb4',
collation='utf8mb4_unicode_ci',
Before :
def send_message(self, message="", **kwargs):
"""Send a message as command to a MySQL server."""
cnx = mysql.connector.connect(
host=self.host,
port=self.port,
user=self.username,
password=self.password,
database=self.db,
connect_timeout=self.timeout,
)
The fix :
def send_message(self, message="", **kwargs):
"""Send a message as command to a MySQL server."""
cnx = mysql.connector.connect(
host=self.host,
port=self.port,
user=self.username,
password=self.password,
database=self.db,
connect_timeout=self.timeout,
charset='utf8mb4',
collation='utf8mb4_unicode_ci',
)
Restart HA and it should work.