core: Recorder - Incorrect string value when trying to insert unicode icons in MariaDB

The problem

Various errors in logs with regards to RECORDER component

Environment

Home Assistant release with the issue: 0.105.2
Last working Home Assistant release (if known): Issue has been there since a while
Operating environment (Hass.io/Docker/Windows/etc.): Hass.io
Integration causing this issue: Recorder with MariaDB (remote)
Link to integration documentation on our website: https://www.home-assistant.io/integrations/recorder/

Problem-relevant `configuration.yaml`

recorder:
  purge_keep_days: 1
  purge_interval: 1
  db_url: mysql://hass:xxxxxxxpassxxxx@192.168.X.X/homeassistant?charset=utf8

Traceback/Error logs

Error executing query: (MySQLdb._exceptions.OperationalError) (1366, "Incorrect string value: '\\xF0\\x9F\\x99\\x88\\xF0\\x9F...' for column `homeassistant`.`states`.`state` at row 1")
[SQL: INSERT INTO states (domain, entity_id, state, attributes, event_id, last_changed, last_updated, created, context_id, context_user_id) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)]
[parameters: ('sensor', 'sensor.ssid', '🙈💩', '{"friendly_name": "SSID", "icon": "mdi:wifi"}', 5076957, datetime.datetime(2020, 2, 13, 9, 25, 3, 422122, tzinfo=<UTC>), datetime.datetime(2020, 2, 13, 9, 25, 3, 422122, tzinfo=<UTC>), datetime.datetime(2020, 2, 13, 9, 26, 50, 82758), '1cb5d3d5bc1d4612a2b08d1cb4b4a672', None)]
(Background on this error at: http://sqlalche.me/e/e3q8)

Additional information

An additional related error message appears in the logs indicating that the recorder component is giving up the INSERT statement

Error in database update. Could not save after 11 tries. Giving up

Of course there is an easy workaround: exclude this sensor from the recorder and the problem is gone. I wanted to log this bug nevertheless since other unicode characters might pose the same problem.

About this issue

Original URL
State: closed
Created 4 years ago
Reactions: 3
Comments: 17 (9 by maintainers)

Most upvoted comments

It was discovered that db_url: mysql://db_user:db_pass@core-mariadb/homeassistant?charset=utf8mb4 fixes this:

https://github.com/home-assistant/core/issues/41422#issuecomment-705147373

bdraco on Oct 7, 2020

Seeing a similar error here. I use input_text entities to store this weeks dinner plan. My wife entered ‘Phở’ (vietnamese soup, correctly spelled) and I’m now getting errors from Recorder.

[SQL: INSERT INTO states (domain, entity_id, state, attributes, event_id, last_changed, last_updated, created, context_id, context_user_id) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)]
[parameters: ('input_text', 'input_text.friday_dinner', 'Phở ', '{"editable": false, "min": 0, "max": 100, "pattern": null, "mode": "text", "friendly_name": "Friday"}', 351392, datetime.datetime(2020, 2, 18, 11, 27, 28, 453167, tzinfo=<UTC>), datetime.datetime(2020, 2, 18, 11, 27, 28, 453167, tzinfo=<UTC>), datetime.datetime(2020, 2, 18, 11, 29, 16, 545271), 'ff9bab82f76d44e09ac3a243ced906e7', None)]
(Background on this error at: http://sqlalche.me/e/e3q8). (retrying in 3 seconds)

Checking the table:

SHOW CREATE TABLE homeassistant.states
....
) ENGINE=InnoDB AUTO_INCREMENT=254926 DEFAULT CHARSET=latin1
....

So I converted it to utf8:

ALTER TABLE states CONVERT TO CHARACTER SET utf8;
Query OK, 254979 rows affected (16.20 sec)
Records: 254979  Duplicates: 0  Warnings: 0

And the problem is gone. Not sure if I should consider this a problem with Home Assistant, or if this was a configuration mistake on my end. I haven’t configured anything in MySQL except creating a database and a user for Home Assistant.

arnefm on Feb 18, 2020