Core: Recorder component unable to reconnect to MySQL after lost connection

[sebk-666]

Home Assistant release with the issue:
0.91.2

Last working Home Assistant release (if known):
I only noticed the issue starting with 0.91.x.

Operating environment (Hass.io/Docker/Windows/etc.):
Docker on x86

Component/platform:
Recorder

Description of problem:
When HA is running about a day or so, I noticed that the sensor graphs in the UI started to flat out although the read values were correct.
Further investigation showed that the recorder component seems to lose the connection to the MySQL(MariaDB) server at one point and then is unable to reconnect due to some transaction that needs to be rolled back first (according to the logs).

Problem-relevant configuration.yaml entries and (fill out even if it seems unimportant):

recorder:
  purge_keep_days: 14
  purge_interval: 1
  db_url: !secret mysql_url

Traceback (if applicable):

2019-04-14 09:39:34 ERROR (SyncWorker_5) [homeassistant.components.recorder.util] Error executing query: (MySQLdb._exceptions.OperationalError) (2006, 'MySQL server has gone away')
2019-04-14 09:39:34 ERROR (SyncWorker_5) [homeassistant.components.recorder.util] Error executing query: (sqlalchemy.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back
2019-04-14 09:39:34 ERROR (SyncWorker_5) [homeassistant.components.recorder.util] Error executing query: (sqlalchemy.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back
2019-04-14 09:39:34 ERROR (SyncWorker_5) [homeassistant.components.recorder.util] Error executing query: (sqlalchemy.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back

Additional information:
I don't see any issues in the MySQL server logs; the server is running for weeks now (since last system reboot) and I see active HA sessions in the show processlist; output.

See this thread in the community forum for more details:
https://community.home-assistant.io/t/sensor-cards-not-showing-graph-anymore/110507

[xbmcnut]

I see this too under 0.92.x. I was using MariaDB10 on Synology and when initially set up it flew. Lately, I noticed some of my template sensors reporting weird values and after dropping the database, everything came back to life. Sadly, this only lasts for <12hrs then the logbook fails to recall anything and the history just sits there forever.

Switched to local MariaDB add-on under HassOS (Intel NUC) yesterday and again, flew for the first 12hrs and has now stopped.

[kevank]

Do you have anything like this in your log?

17-03-12 15:31:09 ERROR (Thread-9) [homeassistant.components.recorder] Error during query: 'latin-1' codec can't encode character '\u2013' in position 7: ordinal not in range(256)

I was having the same issue you are seeing and it came out of nowhere. I added ?charset=utf8 to the end of my db_url and restarted. The error no longer shows up in the log and my recorder no longer stops recording data.

[xbmcnut]

I already had that suffix in both instances.
3307/hassdb?charset=utf8 (Synology)
/homeassistant?charset=utf8 (local add-on)

For now, I've switched back to the default internal database although this is not ideal as it grows to 1.8GB pretty quickly which causes Dropbox backup management issues for me.

[sebk-666]

I did see those "ordinal not in range" errors, too. I appended the suggested charset option to my db_url yesterday and so far it looks like that fixed the issue for me.
I'll wait a little longer, but usually the graphs started to flat out within 24h for me.

EDIT: I'm now on day two after adding the charset option to the db_url string and have not seen the error since. :)

[rbflurry]

created my own issue https://github.com/home-assistant/home-assistant/issues/24028 This for me happens when there is a location update coming from the IOS app. I am not sure what "state" has a dash in it but for me it is coming from IOS. These are the last events recorder sees before it drops connection.

[xbmcnut]

FYI, my issues turned out to be the bug when stream: and shell_commands: are both in use. I disabled my shell commands and HA has settled down and is nice and stable again. I'm now using the MariaDB add-on.

[tanus10]

on 0.91.4, I occasionally getting this error. I am not using HASS.IO. I am using manually installed Docker Homeassistant. MariaDB is also manually installed Docker Mariadb.

2019-06-05 15:03:46 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (MySQLdb._exceptions.OperationalError) (2006, 'MySQL server has gone away')

I changed DB connection to TCP/IP to Socket, but did not help.
I changed wiat_time to 8 hours in my.cnf, but did not help much. I see the error a lot less.

Since I am using Socket to connect to DB, "MySQL server has gone away' does not really make much sense. It seems Homeassistant bug.

when stream: and shell_commands: are both in use

I am not sure what these mean, though I don't think I use them, since I am not on Hass.io.

[tanus10]

[mysqld]
wait_timeout        = 86400

Increasing the said entry to over 20000 seems to mitigate the error occurrence. I don't have any idea about the negative side-effect, I mean whether it exists or not.

It was originally 600. (I am using linuxserver/mariadb image form Docker hub)

Bellow is my speculation.
In HA, I only record a few switches and scripts so, It would be common for my server not producing any db inserts within 10 minutes window. After the period, I image HA looses SQL connection, hence the error.

It would be far more ideal to fix this with pool_recycle via SQLAlchemy, something like bellow.

INFO sqlalchemy.pool.QueuePool Connection <_mysql.connection open to '127.0.0.1' at 2adcf90> exceeded timeout; recycling 
INFO:sqlalchemy.pool.QueuePool:Connection <_mysql.connection open to '127.0.0.1' at 2adcf90> exceeded timeout; recycling

log example from https://yongho1037.tistory.com/569

[stale[bot]]

There hasn't been any activity on this issue recently. Due to the high number of incoming GitHub notifications, we have to clean some of the old issues, as many of them have already been resolved with the latest updates.
Please make sure to update to the latest Home Assistant version and check if that solves the issue. Let us know if that works for you by adding a comment 👍
This issue now has been marked as stale and will be closed if no further activity occurs. Thank you for your contributions.