search

mdeweerd / zha-toolkit

🧰 Zigbee Home Assistant Toolkit - service for "rare" Zigbee operations using ZHA on Home Assistant
GNU General Public License v3.0
197 stars 30 forks source link

SQL error due to 🧰 icon in description #79

Closed mdeweerd closed 2 years ago

mdeweerd commented 2 years ago

( Reported by @jes1417 ) Seeing this in my logs

Logger: homeassistant.components.recorder.core
Source: components/recorder/core.py:760
Integration: Recorder (documentation, issues)
First occurred: 12:24:38 AM (1 occurrences)
Last logged: 12:24:38 AM

Unhandled database error while processing task EventTask(event=<Event state_changed[L]: entity_id=update.zha_toolkit_service_for_advanced_zigbee_usage_update, old_state=None, new_state=<state update.zha_toolkit_service_for_advanced_zigbee_usage_update=off; auto_update=False, installed_version=v0.8.11, in_progress=False, latest_version=v0.8.11, release_summary=None, release_url=https://github.com/mdeweerd/zha-toolkit/releases/v0.8.11, skipped_version=None, title=None, entity_picture=https://brands.home-assistant.io/_/zha_toolkit/icon.png, friendly_name=🧰 ZHA Toolkit - Service for advanced Zigbee Usage update, supported_features=21 @ 2022-08-15T00:22:31.560428-05:00>>): (MySQLdb.OperationalError) (1267, "Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb3_general_ci,COERCIBLE) for operation '='") [SQL: SELECT state_attributes.attributes_id FROM state_attributes WHERE state_attributes.hash = %s AND state_attributes.shared_attrs = %s] [parameters: (438846145, '{"auto_update":false,"installed_version":"v0.8.11","latest_version":"v0.8.11","release_url":"https://github.com/mdeweerd/zha-toolkit/releases/v0.8.11","skipped_version":null,"title":null,"friendly_name":"🧰 ZHA Toolkit - Service for advanced Zigbee Usage update"}')] (Background on this error at: https://sqlalche.me/e/14/e3q8)
Traceback (most recent call last):
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.10/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.10/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/usr/local/lib/python3.10/site-packages/MySQLdb/connections.py", line 254, in query
    _mysql.connection.query(self, query)
MySQLdb.OperationalError: (1267, "Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb3_general_ci,COERCIBLE) for operation '='")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 645, in _process_one_task_or_recover
    return task.run(self)
  File "/usr/src/homeassistant/homeassistant/components/recorder/tasks.py", line 211, in run
    instance._process_one_event(self.event)
  File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 741, in _process_one_event
    self._process_state_changed_event_into_session(event)
  File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 847, in _process_state_changed_event_into_session
    if attributes_id := self._find_shared_attr_in_db(attr_hash, shared_attrs):
  File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 760, in _find_shared_attr_in_db
    if attributes_id := self.event_session.execute(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 1712, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1631, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/lambdas.py", line 516, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1498, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1862, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2043, in _handle_dbapi_exception
    util.raise_(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
    raise exception
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.10/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.10/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/usr/local/lib/python3.10/site-packages/MySQLdb/connections.py", line 254, in query
    _mysql.connection.query(self, query)
sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1267, "Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb3_general_ci,COERCIBLE) for operation '='")
[SQL: SELECT state_attributes.attributes_id 
FROM state_attributes 
WHERE state_attributes.hash = %s AND state_attributes.shared_attrs = %s]
[parameters: (438846145, '{"auto_update":false,"installed_version":"v0.8.11","latest_version":"v0.8.11","release_url":"https://github.com/mdeweerd/zha-toolkit/releases/v0.8.11","skipped_version":null,"title":null,"friendly_name":"🧰 ZHA Toolkit - Service for advanced Zigbee Usage update"}')]
(Background on this error at: https://sqlalche.me/e/14/e3q8)

Originally posted by @jes1417 in https://github.com/mdeweerd/zha-toolkit/issues/77#issuecomment-1214650494

mdeweerd commented 2 years ago

@jes1417 (Created new issue as it was not really related to the #77)

My best guess: You are using MySql and the state_attributes table is using utf8mb3_general_ci encoding, while the provided data is using utf8mb4_unicode_ci encoding.

My recommendation is to change the encoding for the table to utf8mb4_unicode_ci .

This is because the friendly name has 🧰 in it and it is presented or detected as utf8mb4 encoding in the request. This highlights an encoding discrepency in the HA setup- when creating the MySql database it should have selected the proper endoding (...mb4) compatible with the requests that are made. IMHO this should be reported to HA (if the DB was created by HA), or elsewhere (if the database was created using a method/procedure not managed by HA core).

https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-conversion.html discusses some of that and shows example queries for converting a table/column - this may also be possible using a more userfriendly interface such as phpmyadmin.

Originally posted by @mdeweerd in https://github.com/mdeweerd/zha-toolkit/issues/77#issuecomment-1214796501