Upgraded to 0.94.4 and started getting the below errors in the log. I was using
db_url=mysql://SERVER_IP/DB_NAME?charset=utf8 with MariaDB (migrated from MySQL to MariaDB ages ago). Update to the correct MariaDB url to resolve the issue: db_url=mysql+pymysql://user:password@SERVER_IP/DB_NAME?charset=utf8
Recorder cannot write the state table in MariaDB 10.4.6. Recorders take a very long time to start up after which the log is fulled with sql errors. Thought my DB was corrupted and dropped all tables. HA correctly recreate the tables, but still fail to insert into the state table.
Traceback (most recent call last):
File "/usr/lib64/python3.7/site-packages/homeassistant/components/recorder/__init__.py", line 309, in run
event.data.get('new_state'))
File "/usr/lib64/python3.7/contextlib.py", line 119, in __exit__
next(self.gen)
File "/usr/lib64/python3.7/site-packages/homeassistant/components/recorder/util.py", line 28, in session_scope
session.commit()
File "/opt/hass/.homeassistant/deps/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 1026, in commit
self.transaction.commit()
File "/opt/hass/.homeassistant/deps/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 493, in commit
self._prepare_impl()
File "/opt/hass/.homeassistant/deps/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 472, in _prepare_impl
self.session.flush()
File "/opt/hass/.homeassistant/deps/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2451, in flush
self._flush(objects)
File "/opt/hass/.homeassistant/deps/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2589, in _flush
transaction.rollback(_capture_exception=True)
File "/opt/hass/.homeassistant/deps/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
File "/opt/hass/.homeassistant/deps/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 129, in reraise
raise value
File "/opt/hass/.homeassistant/deps/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2549, in _flush
flush_context.execute()
File "/opt/hass/.homeassistant/deps/lib/python3.7/site-packages/sqlalchemy/orm/unitofwork.py", line 422, in execute
rec.execute(self)
File "/opt/hass/.homeassistant/deps/lib/python3.7/site-packages/sqlalchemy/orm/unitofwork.py", line 589, in execute
uow,
File "/opt/hass/.homeassistant/deps/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py", line 245, in save_obj
insert,
File "/opt/hass/.homeassistant/deps/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py", line 1120, in _emit_insert_statements
statement, params
File "/opt/hass/.homeassistant/deps/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 988, in execute
return meth(self, multiparams, params)
File "/opt/hass/.homeassistant/deps/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "/opt/hass/.homeassistant/deps/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1107, in _execute_clauseelement
distilled_params,
File "/opt/hass/.homeassistant/deps/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
e, statement, parameters, cursor, context
File "/opt/hass/.homeassistant/deps/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1466, in _handle_dbapi_exception
util.raise_from_cause(sqlalchemy_exception, exc_info)
File "/opt/hass/.homeassistant/deps/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 383, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "/opt/hass/.homeassistant/deps/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 128, in reraise
raise value.with_traceback(tb)
File "/opt/hass/.homeassistant/deps/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context
cursor, statement, parameters, context
File "/opt/hass/.homeassistant/deps/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute
cursor.execute(statement, parameters)
File "/usr/lib64/python3.7/site-packages/MySQLdb/cursors.py", line 206, in execute
res = self._query(query)
File "/usr/lib64/python3.7/site-packages/MySQLdb/cursors.py", line 312, in _query
db.query(q)
File "/usr/lib64/python3.7/site-packages/MySQLdb/connections.py", line 224, in query
_mysql.connection.query(self, query)
sqlalchemy.exc.IntegrityError: (MySQLdb._exceptions.IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails (`homeassist`.`states`, CONSTRAINT `states_ibfk_1` FOREIGN KEY (`event_id`) REFERENCES `events` (`event_id`))')
[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.processor_use', '2', '{"unit_of_measurement": "%", "friendly_name": "CPU Usage", "icon": "mdi:memory"}', 21353351, datetime.datetime(2019, 6, 23, 2, 4, 6, 48914, tzinfo=<UTC>), datetime.datetime(2019, 6, 23, 2, 4, 6, 48914, tzinfo=<UTC>), datetime.datetime(2019, 6, 23, 2, 4, 6, 459648), '01700d1b9e094f5896823a07a8bfa1aa', None)]
(Background on this error at: http://sqlalche.me/e/gkpj)