I’ve upgraded to Home Assistant 2021.5.5 and noticing these mysql errors. I have tried deleting all the tables to start fresh. It seems to work for a few hours but errors reappears.
mysql login:
MySQL_login: 'mysql://user_db:user_password@ip_address:port/db_name?charset=utf8mb4'
No one else are having issues with their database? My graphs goes flatline after a day.
I have tried dropping all tables are few times and recreated a new database name. Still errors come back.
I think I need to do something with Session.rollback().
Error executing query: Can’t reconnect until invalid transaction is rolled back. (Background on this error at: Error Messages — SQLAlchemy 1.4 Documentation)
I think I may have fixed it by doing a manual purge in developer tools. It has been a full day and after the purg, the error has not popped up. I will keep monitoring for the next few days if this really works.
It doesn’t make sense how a manual purge cleared the error when I have tried dropped all tables and have created a new database.
I guess I didn’t fix it after all. I am now on Version core-2021.6.0. It would be ok for a week and then all graphs flatline. I am clueless of how to fix this? I would think by dropping all tables and start new, it will work. Help?
config:
recorder:
db_url: !secret MySQL_login # External MySQL DB.
purge_keep_days: 4
exclude:
domains:
- group
- automation
- camera
- media_player
- scene
- script
entities:
- sun.sun
- sensor.date_time
Error:
SQLAlchemyError error processing event <Event time_changed[L]: now=2021-06-27T05:46:30.089012+09:00>: This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: (MySQLdb._exceptions.OperationalError) (2013, 'Lost connection to MySQL server during query') [SQL: INSERT INTO events (event_type, event_data, origin, time_fired, created, context_id, context_user_id, context_parent_id) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)] [parameters: ('state_changed', '{}', 'LOCAL', datetime.datetime(2021, 6, 26, 20, 46, 29, 837416, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 6, 26, 20, 46, 29, 837416, tzinfo=datetime.timezone.utc), '474361fe88daf6e70a82a8eec0c010ca', None, None)] (Background on this error at: http://sqlalche.me/e/14/e3q8) (Background on this error at: http://sqlalche.me/e/14/7s2a)
6:51:04 AM – (ERROR) Recorder - message first occurred at June 26, 2021, 8:27:13 PM and shows up 20 times
Database connection invalidated: Error executing query: (MySQLdb._exceptions.OperationalError) (2013, 'Lost connection to MySQL server during query') [SQL: INSERT INTO events (event_type, event_data, origin, time_fired, created, context_id, context_user_id, context_parent_id) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)] [parameters: ('state_changed', '{}', 'LOCAL', datetime.datetime(2021, 6, 26, 20, 46, 29, 837416, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 6, 26, 20, 46, 29, 837416, tzinfo=datetime.timezone.utc), '474361fe88daf6e70a82a8eec0c010ca', None, None)] (Background on this error at: http://sqlalche.me/e/14/e3q8). (retrying in 3 seconds)
6:51:01 AM – (ERROR) Recorder - message first occurred at June 26, 2021, 8:27:10 PM and shows up 20 times
Error handling request
6:33:01 AM – (ERROR) components/recorder/util.py - message first occurred at June 26, 2021, 8:21:01 PM and shows up 5 times
Error executing query: Can't reconnect until invalid transaction is rolled back. (Background on this error at: http://sqlalche.me/e/14/8s2b)
6:33:01 AM – (ERROR) Recorder - message first occurred at June 26, 2021, 8:21:01 PM and shows up 5 times
Error executing query: Can't reconnect until invalid transaction is rolled back. (Background on this error at: http://sqlalche.me/e/14/8s2b)
6:33:01 AM – (ERROR) Recorder - message first occurred at June 26, 2021, 8:21:01 PM and shows up 15 times
Error doing job: Task exception was never retrieved
6:22:22 AM – (ERROR) /usr/src/homeassistant/homeassistant/runner.py - message first occurred at 3:06:18 AM and shows up 6 times
Error handling request
6:21:27 AM – (ERROR) components/websocket_api/http.py - message first occurred at 3:05:23 AM and shows up 6 times
Exact Error message. How to issue a rollback?
SQLAlchemyError error processing event <Event time_changed[L]: now=2021-06-27T06:07:29.084823+09:00>: This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: (MySQLdb._exceptions.OperationalError) (2013, 'Lost connection to MySQL server during query') [SQL: INSERT INTO events (event_type, event_data, origin, time_fired, created, context_id, context_user_id, context_parent_id) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)] [parameters: ('state_changed', '{}', 'LOCAL', datetime.datetime(2021, 6, 26, 21, 7, 28, 825461, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 6, 26, 21, 7, 28, 825461, tzinfo=datetime.timezone.utc), '73143a2d8968c24bbe90a0bd4c564c54', None, None)] (Background on this error at: http://sqlalche.me/e/14/e3q8) (Background on this error at: http://sqlalche.me/e/14/7s2a)
SQLAlchemyError error processing event <Event time_changed[L]: now=2021-06-27T06:12:48.001046+09:00>: This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: (MySQLdb._exceptions.OperationalError) (2013, 'Lost connection to MySQL server during query') [SQL: INSERT INTO events (event_type, event_data, origin, time_fired, created, context_id, context_user_id, context_parent_id) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)] [parameters: ('state_changed', '{}', 'LOCAL', datetime.datetime(2021, 6, 26, 21, 12, 47, 874719, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 6, 26, 21, 12, 47, 874719, tzinfo=datetime.timezone.utc), '25395ea3efc7c47be058c1e08822867d', None, None)] (Background on this error at: http://sqlalche.me/e/14/e3q8) (Background on this error at: http://sqlalche.me/e/14/7s2a)
SQLAlchemyError error processing event <Event time_changed[L]: now=2021-06-27T06:12:59.001823+09:00>: This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: (MySQLdb._exceptions.OperationalError) (2013, "Lost connection to MySQL server at 'reading authorization packet', system error: 11") (Background on this error at: http://sqlalche.me/e/14/e3q8) (Background on this error at: http://sqlalche.me/e/14/7s2a)
SQLAlchemyError error processing event <Event time_changed[L]: now=2021-06-27T06:13:49.001829+09:00>: This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: (MySQLdb._exceptions.OperationalError) (2013, 'Lost connection to MySQL server during query') [SQL: INSERT INTO events (event_type, event_data, origin, time_fired, created, context_id, context_user_id, context_parent_id) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)] [parameters: ('call_service', '{"domain": "mqtt", "service": "publish", "service_data": {"topic": "/appliance/2009228823079551802948e1e934c753/subscribe", "payload": "{\\"header\\" ... (203 characters truncated) ... e1e934c753/system\\", \\"timestamp\\": 1609405973, \\"timestampMs\\": 980, \\"sign\\": \\"fd3d14744e3d07dfcfa0e3991d9ee3dc\\"}, \\"payload\\": {}}"}}', 'LOCAL', datetime.datetime(2021, 6, 26, 21, 13, 48, 422871, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 6, 26, 21, 13, 48, 422871, tzinfo=datetime.timezone.utc), '83c362c7517e369c918120ae21ca5082', None, None)] (Background on this error at: http://sqlalche.me/e/14/e3q8) (Background on this error at: http://sqlalche.me/e/14/7s2a)
SQLAlchemyError error processing event <Event time_changed[L]: now=2021-06-27T06:14:00.042106+09:00>: This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: (MySQLdb._exceptions.OperationalError) (2013, 'Lost connection to MySQL server during query') [SQL: INSERT INTO events (event_type, event_data, origin, time_fired, created, context_id, context_user_id, context_parent_id) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)] [parameters: ('state_changed', '{}', 'LOCAL', datetime.datetime(2021, 6, 26, 21, 13, 59, 314988, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 6, 26, 21, 13, 59, 314988, tzinfo=datetime.timezone.utc), '6e54bc4d5827ce42019ea61b70f4a03f', None, None)] (Background on this error at: http://sqlalche.me/e/14/e3q8) (Background on this error at: http://sqlalche.me/e/14/7s2a)
Your mysql connectivity seems shaky.
Look for network or mysql errors.
I do noticed in the past I saw some errors about not about to connect. Drop connections, but that would be a minute or so. I hope this issue isn’t the case about connection. I am running HA on an sd card and don’t want to have recorder writing data on it. if I can avoid it…
I’m 99.99% sure it is
Will put it back on the SD card and test. Will report back. thx.
Usually it indicates network connectivity trouble and you should check the condition of your network if this error occurs frequently. It might be because the MySQL server is bound to the loop-back IP (127.0.0.1 / localhost) which effectively cuts you off from connecting from “outside”. If this is the case, you need to upload the script to the webserver (which is probably also running the MySQL server) and keep your server host as ‘localhost’ Another common cause of connect timeouts is the reverse-DNS lookup that is necessary when authenticating clients. It is recommended to run MySQL with the config variable in my.cnf:
Open mysql configuration file named my.cnf and try to find “bind-address”, here replace the setting (127.0.0.1 OR localhost) with your live server ip (the ip you are using in mysql_connect function).
Restart service by command : service httpd restart
GRANT ALL PRIVILEGES ON yourDB.* TO 'username'@'YOUR_APPLICATION_IP' IDENTIFIED BY 'YPUR_PASSWORD' WITH GRANT OPTION;