Hi all,
I’m a big fan of the new energy management dashboard and have bought a SlimmeLezer to read my meter values accurately.
Unfortunately every time I need to revert back to an older snapshot, my database (SQLlite) gets malformed and I have to delete it, meaning I lose all my precious long term statistics.
In order to remedy this I’ve started to use the mariadb addon for the HA recorder. I was already using mariadb for the nginx proxy manager addon.
I decided to follow a tip and configure the Google Drive addon to stop the mariadb addon automatically prior to making a backup just to make sure I never lose my data again.
This went well for 2 days. After the third day, exactly around the time the backup was taken, the recorder started showing weird behaviour. I get 3 error messages about rollbacks every hour.
Because of this the energy management dashboard stopped showing any new data, but the historical data was still available. I’ve gone so far as to completely drop all the tables in the homeassistant db and uninstalled/reinstalled the addon. Unfortunately these errors just keep coming back.
The energy management dashboard is thus still not working for me, unfortunately.
The only way I’ve been able to solve this so far was to revert back to the internal sqllite database. While that is a solution I would be very happy if someone could help me get me mariadb working with the recorder again because I would just prefer to have a stable environment without risk of losing my data.
The errors:
Logger: homeassistant.components.recorder.util
Source: components/recorder/util.py:134
Integration: Recorder (documentation, issues)
First occurred: September 6, 2021, 5:12:01 PM (48 occurrences)
Last logged: 8:12:01 AM
Error executing query: 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: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (MySQLdb._exceptions.ProgrammingError) nan can not be used with MySQL [SQL: INSERT INTO statistics (created, metadata_id, start, mean, min, max, last_reset, state, sum) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)] [parameters: (datetime.datetime(2021, 9, 7, 4, 12, 1, 832889, tzinfo=datetime.timezone.utc), 14, datetime.datetime(2021, 9, 7, 3, 0, tzinfo=datetime.timezone.utc), None, None, None, None, nan, nan)] (Background on this error at: https://sqlalche.me/e/14/f405) (Background on this error at: https://sqlalche.me/e/14/7s2a)
Error executing query: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (MySQLdb._exceptions.ProgrammingError) nan can not be used with MySQL [SQL: INSERT INTO statistics (created, metadata_id, start, mean, min, max, last_reset, state, sum) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)] [parameters: (datetime.datetime(2021, 9, 7, 5, 12, 1, 235219, tzinfo=datetime.timezone.utc), 15, datetime.datetime(2021, 9, 7, 4, 0, tzinfo=datetime.timezone.utc), None, None, None, None, nan, nan)] (Background on this error at: https://sqlalche.me/e/14/f405)
Error executing query: 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: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (MySQLdb._exceptions.ProgrammingError) nan can not be used with MySQL [SQL: INSERT INTO statistics (created, metadata_id, start, mean, min, max, last_reset, state, sum) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)] [parameters: (datetime.datetime(2021, 9, 7, 5, 12, 1, 235219, tzinfo=datetime.timezone.utc), 15, datetime.datetime(2021, 9, 7, 4, 0, tzinfo=datetime.timezone.utc), None, None, None, None, nan, nan)] (Background on this error at: https://sqlalche.me/e/14/f405) (Background on this error at: https://sqlalche.me/e/14/7s2a)
Error executing query: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (MySQLdb._exceptions.ProgrammingError) nan can not be used with MySQL [SQL: INSERT INTO statistics (created, metadata_id, start, mean, min, max, last_reset, state, sum) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)] [parameters: (datetime.datetime(2021, 9, 7, 6, 12, 1, 222629, tzinfo=datetime.timezone.utc), 16, datetime.datetime(2021, 9, 7, 5, 0, tzinfo=datetime.timezone.utc), None, None, None, None, nan, nan)] (Background on this error at: https://sqlalche.me/e/14/f405)
Error executing query: 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: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (MySQLdb._exceptions.ProgrammingError) nan can not be used with MySQL [SQL: INSERT INTO statistics (created, metadata_id, start, mean, min, max, last_reset, state, sum) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)] [parameters: (datetime.datetime(2021, 9, 7, 6, 12, 1, 222629, tzinfo=datetime.timezone.utc), 16, datetime.datetime(2021, 9, 7, 5, 0, tzinfo=datetime.timezone.utc), None, None, None, None, nan, nan)] (Background on this error at: https://sqlalche.me/e/14/f405) (Background on this error at: https://sqlalche.me/e/14/7s2a)
Logger: homeassistant.components.recorder.util
Source: components/recorder/util.py:71
Integration: Recorder (documentation, issues)
First occurred: September 6, 2021, 5:12:01 PM (16 occurrences)
Last logged: 8:12:01 AM
Error executing query: 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: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (MySQLdb._exceptions.ProgrammingError) nan can not be used with MySQL [SQL: INSERT INTO statistics (created, metadata_id, start, mean, min, max, last_reset, state, sum) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)] [parameters: (datetime.datetime(2021, 9, 7, 2, 12, 1, 262371, tzinfo=datetime.timezone.utc), 12, datetime.datetime(2021, 9, 7, 1, 0, tzinfo=datetime.timezone.utc), None, None, None, None, nan, nan)] (Background on this error at: https://sqlalche.me/e/14/f405) (Background on this error at: https://sqlalche.me/e/14/7s2a)
Error executing query: 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: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (MySQLdb._exceptions.ProgrammingError) nan can not be used with MySQL [SQL: INSERT INTO statistics (created, metadata_id, start, mean, min, max, last_reset, state, sum) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)] [parameters: (datetime.datetime(2021, 9, 7, 3, 12, 1, 691491, tzinfo=datetime.timezone.utc), 13, datetime.datetime(2021, 9, 7, 2, 0, tzinfo=datetime.timezone.utc), None, None, None, None, nan, nan)] (Background on this error at: https://sqlalche.me/e/14/f405) (Background on this error at: https://sqlalche.me/e/14/7s2a)
Error executing query: 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: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (MySQLdb._exceptions.ProgrammingError) nan can not be used with MySQL [SQL: INSERT INTO statistics (created, metadata_id, start, mean, min, max, last_reset, state, sum) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)] [parameters: (datetime.datetime(2021, 9, 7, 4, 12, 1, 832889, tzinfo=datetime.timezone.utc), 14, datetime.datetime(2021, 9, 7, 3, 0, tzinfo=datetime.timezone.utc), None, None, None, None, nan, nan)] (Background on this error at: https://sqlalche.me/e/14/f405) (Background on this error at: https://sqlalche.me/e/14/7s2a)
Error executing query: 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: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (MySQLdb._exceptions.ProgrammingError) nan can not be used with MySQL [SQL: INSERT INTO statistics (created, metadata_id, start, mean, min, max, last_reset, state, sum) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)] [parameters: (datetime.datetime(2021, 9, 7, 5, 12, 1, 235219, tzinfo=datetime.timezone.utc), 15, datetime.datetime(2021, 9, 7, 4, 0, tzinfo=datetime.timezone.utc), None, None, None, None, nan, nan)] (Background on this error at: https://sqlalche.me/e/14/f405) (Background on this error at: https://sqlalche.me/e/14/7s2a)
Error executing query: 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: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (MySQLdb._exceptions.ProgrammingError) nan can not be used with MySQL [SQL: INSERT INTO statistics (created, metadata_id, start, mean, min, max, last_reset, state, sum) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)] [parameters: (datetime.datetime(2021, 9, 7, 6, 12, 1, 222629, tzinfo=datetime.timezone.utc), 16, datetime.datetime(2021, 9, 7, 5, 0, tzinfo=datetime.timezone.utc), None, None, None, None, nan, nan)] (Background on this error at: https://sqlalche.me/e/14/f405) (Background on this error at: https://sqlalche.me/e/14/7s2a)
Logger: homeassistant.components.recorder
Source: components/recorder/util.py:115
Integration: Recorder (documentation, issues)
First occurred: September 6, 2021, 5:12:01 PM (16 occurrences)
Last logged: 8:12:01 AM
SQLAlchemyError error processing event StatisticsTask(start=datetime.datetime(2021, 9, 7, 1, 0, tzinfo=datetime.timezone.utc)): 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: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (MySQLdb._exceptions.ProgrammingError) nan can not be used with MySQL [SQL: INSERT INTO statistics (created, metadata_id, start, mean, min, max, last_reset, state, sum) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)] [parameters: (datetime.datetime(2021, 9, 7, 2, 12, 1, 262371, tzinfo=datetime.timezone.utc), 12, datetime.datetime(2021, 9, 7, 1, 0, tzinfo=datetime.timezone.utc), None, None, None, None, nan, nan)] (Background on this error at: https://sqlalche.me/e/14/f405) (Background on this error at: https://sqlalche.me/e/14/7s2a)
SQLAlchemyError error processing event StatisticsTask(start=datetime.datetime(2021, 9, 7, 2, 0, tzinfo=datetime.timezone.utc)): 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: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (MySQLdb._exceptions.ProgrammingError) nan can not be used with MySQL [SQL: INSERT INTO statistics (created, metadata_id, start, mean, min, max, last_reset, state, sum) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)] [parameters: (datetime.datetime(2021, 9, 7, 3, 12, 1, 691491, tzinfo=datetime.timezone.utc), 13, datetime.datetime(2021, 9, 7, 2, 0, tzinfo=datetime.timezone.utc), None, None, None, None, nan, nan)] (Background on this error at: https://sqlalche.me/e/14/f405) (Background on this error at: https://sqlalche.me/e/14/7s2a)
SQLAlchemyError error processing event StatisticsTask(start=datetime.datetime(2021, 9, 7, 3, 0, tzinfo=datetime.timezone.utc)): 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: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (MySQLdb._exceptions.ProgrammingError) nan can not be used with MySQL [SQL: INSERT INTO statistics (created, metadata_id, start, mean, min, max, last_reset, state, sum) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)] [parameters: (datetime.datetime(2021, 9, 7, 4, 12, 1, 832889, tzinfo=datetime.timezone.utc), 14, datetime.datetime(2021, 9, 7, 3, 0, tzinfo=datetime.timezone.utc), None, None, None, None, nan, nan)] (Background on this error at: https://sqlalche.me/e/14/f405) (Background on this error at: https://sqlalche.me/e/14/7s2a)
SQLAlchemyError error processing event StatisticsTask(start=datetime.datetime(2021, 9, 7, 4, 0, tzinfo=datetime.timezone.utc)): 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: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (MySQLdb._exceptions.ProgrammingError) nan can not be used with MySQL [SQL: INSERT INTO statistics (created, metadata_id, start, mean, min, max, last_reset, state, sum) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)] [parameters: (datetime.datetime(2021, 9, 7, 5, 12, 1, 235219, tzinfo=datetime.timezone.utc), 15, datetime.datetime(2021, 9, 7, 4, 0, tzinfo=datetime.timezone.utc), None, None, None, None, nan, nan)] (Background on this error at: https://sqlalche.me/e/14/f405) (Background on this error at: https://sqlalche.me/e/14/7s2a)
SQLAlchemyError error processing event StatisticsTask(start=datetime.datetime(2021, 9, 7, 5, 0, tzinfo=datetime.timezone.utc)): 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: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (MySQLdb._exceptions.ProgrammingError) nan can not be used with MySQL [SQL: INSERT INTO statistics (created, metadata_id, start, mean, min, max, last_reset, state, sum) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)] [parameters: (datetime.datetime(2021, 9, 7, 6, 12, 1, 222629, tzinfo=datetime.timezone.utc), 16, datetime.datetime(2021, 9, 7, 5, 0, tzinfo=datetime.timezone.utc), None, None, None, None, nan, nan)] (Background on this error at: https://sqlalche.me/e/14/f405) (Background on this error at: https://sqlalche.me/e/14/7s2a)
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/components/recorder/__init__.py", line 657, in _process_one_event_or_recover
self._process_one_event(event)
File "/usr/src/homeassistant/homeassistant/components/recorder/__init__.py", line 763, in _process_one_event
self._run_statistics(event.start)
File "/usr/src/homeassistant/homeassistant/components/recorder/__init__.py", line 746, in _run_statistics
if statistics.compile_statistics(self, start):
File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 306, in wrapper
return job(instance, *args, **kwargs)
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 215, in compile_statistics
metadata_id = _update_or_add_metadata(
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 151, in _update_or_add_metadata
old_metadata_dict = _get_metadata(hass, session, [statistic_id], None)
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 257, in _get_metadata
result = execute(baked_query(session).params(statistic_ids=statistic_ids))
File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 115, in execute
result = list(qry)
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/ext/baked.py", line 382, in __iter__
return self._iter().__iter__()
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/ext/baked.py", line 412, in _iter
result = self.session.execute(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1688, in execute
conn = self._connection_for_bind(bind)
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1529, in _connection_for_bind
return self._transaction._connection_for_bind(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 721, in _connection_for_bind
self._assert_active()
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 601, in _assert_active
raise sa_exc.PendingRollbackError(
sqlalchemy.exc.PendingRollbackError: 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: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely)
(MySQLdb._exceptions.ProgrammingError) nan can not be used with MySQL
[SQL: INSERT INTO statistics (created, metadata_id, start, mean, min, max, last_reset, state, sum) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)]
[parameters: (datetime.datetime(2021, 9, 6, 15, 12, 1, 129869, tzinfo=datetime.timezone.utc), 1, datetime.datetime(2021, 9, 6, 14, 0, tzinfo=datetime.timezone.utc), None, None, None, None, nan, nan)]
(Background on this error at: https://sqlalche.me/e/14/f405) (Background on this error at: https://sqlalche.me/e/14/7s2a)