Record: / SQLite broken

HorizonKane · August 16, 2020, 8:22am

Hi all,

Few days ago, my HA suddenly started to appear frozen intermittently and when it worked it ofteb was very slow. Additinally the green LED on the Pi 4 was on almost all the time.

I suspected the SD card to be broken after one year of use. So what I did is that I took a new one, installed HA and recovered my last snapshot. Everything is working fine and fast again now, howwever I dont have any historical data (i.e. temperature) and get errors like this in the log:

Protokolldetails ( ERROR )
Logger: homeassistant.components.recorder
Source: components/recorder/__init__.py:395
Integration: Recorder (documentation, issues)
First occurred: 10:07:42 (1334 occurrences)
Last logged: 10:16:14

Error adding state change: 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: (sqlite3.DatabaseError) database disk image is malformed [SQL: INSERT INTO events (event_type, event_data, origin, time_fired, created, context_id, context_user_id, context_parent_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?)] [parameters: ('state_changed', '{}', 'LOCAL', '2020-08-16 08:16:01.268593', '2020-08-16 08:16:01.278976', '241a2b026cea4c63a554bb75e7b3cd94', None, None)] (Background on this error at: http://sqlalche.me/e/4xp6) (Background on this error at: http://sqlalche.me/e/7s2a)
Error adding state change: 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: (sqlite3.DatabaseError) database disk image is malformed [SQL: INSERT INTO events (event_type, event_data, origin, time_fired, created, context_id, context_user_id, context_parent_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?)] [parameters: ('state_changed', '{}', 'LOCAL', '2020-08-16 08:16:03.069091', '2020-08-16 08:16:03.080810', '65c00b4b5ca84b55b9d0e135e613ccc6', None, None)] (Background on this error at: http://sqlalche.me/e/4xp6) (Background on this error at: http://sqlalche.me/e/7s2a)
Error adding state change: 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: (sqlite3.DatabaseError) database disk image is malformed [SQL: INSERT INTO events (event_type, event_data, origin, time_fired, created, context_id, context_user_id, context_parent_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?)] [parameters: ('state_changed', '{}', 'LOCAL', '2020-08-16 08:16:07.398915', '2020-08-16 08:16:07.414522', '76639292b2b8484c9982a03c3a1b5fb2', None, None)] (Background on this error at: http://sqlalche.me/e/4xp6) (Background on this error at: http://sqlalche.me/e/7s2a)
Error adding state change: 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: (sqlite3.DatabaseError) database disk image is malformed [SQL: INSERT INTO events (event_type, event_data, origin, time_fired, created, context_id, context_user_id, context_parent_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?)] [parameters: ('state_changed', '{}', 'LOCAL', '2020-08-16 08:16:10.413440', '2020-08-16 08:16:10.427725', '03027548a9e942f089e159da673f3cce', None, None)] (Background on this error at: http://sqlalche.me/e/4xp6) (Background on this error at: http://sqlalche.me/e/7s2a)
Error adding state change: 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: (sqlite3.DatabaseError) database disk image is malformed [SQL: INSERT INTO events (event_type, event_data, origin, time_fired, created, context_id, context_user_id, context_parent_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?)] [parameters: ('state_changed', '{}', 'LOCAL', '2020-08-16 08:16:14.156877', '2020-08-16 08:16:14.209109', '0be352bbfc77438ca4f4b1db1a065b36', None, None)] (Background on this error at: http://sqlalche.me/e/4xp6) (Background on this error at: http://sqlalche.me/e/7s2a)
Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/__init__.py", line 395, in run
    self.event_session.flush()
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2523, in flush
    self._flush(objects)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2619, in _flush
    subtransactions=True
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 953, in begin
    self.transaction = self.transaction._begin(nested=nested)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 317, in _begin
    self._assert_active()
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 296, in _assert_active
    code="7s2a",
sqlalchemy.exc.InvalidRequestError: 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: (sqlite3.DatabaseError) database disk image is malformed
[SQL: INSERT INTO events (event_type, event_data, origin, time_fired, created, context_id, context_user_id, context_parent_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?)]
[parameters: ('service_registered', '{"domain": "recorder", "service": "purge"}', 'LOCAL', '2020-08-16 08:04:16.179479', '2020-08-16 08:07:42.538943', '3284f71f428b4732a9ad145c0e949e26', None, None)]
(Background on this error at: http://sqlalche.me/e/4xp6) (Background on this error at: http://sqlalche.me/e/7s2a)

As I think the database might be corrupted I reverted to an older snapshot where I am sure stuff was still working but it remains the same.

Any easy way to fix this? I dont care for the historical data being lost, but want to have it wotking again.

Thank you for any help or hint!

tom_l · August 16, 2020, 8:26am

Delete the database and restart is the easy way. You will lose your history data.

There are a few topics on repairing the database if you would prefer that route.

Just FYI, the database rarely survives a restore from snapshot.

HorizonKane · August 16, 2020, 3:18pm

Okay, thank you.

Just searched up how to delte it and found this: Delete homeassistant database which was working fine.

I think when I find the time I will move my DB to my QNAP to keep the SD alive for more than a year

HorizonKane · August 21, 2020, 9:01am

Move to QNAP Maria DB done

e-raser · December 19, 2020, 5:16pm

Oh wow, I am really wondering how everyone can accept the “just delete that damn sqlite db and your good to go!” workaround so all history data is gone.

I restored a snapshot and ended up in the same situation, things didn´t work. I tested it 3 times, in every test case the SQLite database got corrupted. Either during backup (creating the snapshot) or restore process.

Just not acceptable to me to loose all my history data every now and then. That´s not a reliable backup and restore progress at all!

Would be nice to see some core devs working on that issue because it´s a MASSIVE one and widely spread, see all the topics. For me personal I think I´m gonna switch to MySQL, i don´t trust that SQLite stuff anymore.

tom_l · December 19, 2020, 5:28pm

I’m just not that obsessed with short term data.

If my influxdb was lost, that would be a different story.

Have you opened an issue?

Without that it is unlikely to be looked at.

e-raser · December 19, 2020, 6:37pm

Just for your information: Fix corrupted / malformed SQLite database (home-assistant_v2.db)

tom_l · December 19, 2020, 8:49pm

Post that in an issue on github. It won’t be actioned here.