Collation error

[GoNzCiD]

I've noticed that nowadays an exception is thrown when executing a query due to a collation error.

I've also found this entry in the forum: https://community.home-assistant.io/t/mysql-command/539839/43

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 this user said, other components have added a parameter to explicitly specify the collation to be used, it could be a good approach to solve it. https://community.home-assistant.io/t/mysql-query/734346/16