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.