Lots of problems after restoring snapshot. What to do?

Configuration
1

Hello, I ran into a weird issue that prevented me from restarting my server… after some hours of trying to resolve the issue I concluded that I could not, I therefore decided to restore a snapshot I made earlier same day.

However, I’m not sure this was a good idea, I am seeing a ton of other issue now :scream:

Many things that relies on date time seems to be broken, for example time charts didn’t work properly. Also the log book doesn’t show anything anymore. I thought it would just be a bit out of sync, but it’s now two days later and its still not showing anything. when i change the search time interval, no cigar. the log book is empty.

When I look at the logs I also see a lot of errors that I haven’t seen before. One in particular sounds very scary… “database disk image is malformed”

I hope someone can tell me what to do. In advance thanks!

Logger: homeassistant.components.recorder
Source: components/recorder/init.py:509
Integration: Recorder (documentation, issues)
First occurred: October 29, 2020, 12:30:32 AM (6414 occurrences)
Last logged: 9:05:52 AM

  • Error saving events: (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-10-29 23:34:42.469303’, ‘2020-10-29 23:34:42.469303’, ‘fa8050fe3a4xxx064a52ff6fe7184842’, None, None)] (Background on this error at: Error Messages — SQLAlchemy 1.3 Documentation)
  • Error saving events: (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-10-29 23:34:44.347480’, ‘2020-10-29 23:34:44.347480’, ‘536814155cxxx647ddda8e70c31bd39b’, None, None)] (Background on this error at: Error Messages — SQLAlchemy 1.3 Documentation)
  • Error saving events: (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-10-29 23:34:53.170589’, ‘2020-10-29 23:34:53.170589’, ‘d619a5920ab817xxxaaac4c6f5e21b58’, None, None)] (Background on this error at: Error Messages — SQLAlchemy 1.3 Documentation)
  • Error saving events: (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-10-29 23:35:12.448356’, ‘2020-10-29 23:35:12.448356’, ‘4f271cedc870xxxe2a70633240c479cf’, None, None)] (Background on this error at: Error Messages — SQLAlchemy 1.3 Documentation)
  • Error saving events: (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-10-29 23:35:14.541055’, ‘2020-10-29 23:35:14.541055’, ‘aa1a80e078d4xxxc543f525e7a2dc0f8’, None, None)] (Background on this error at: Error Messages — SQLAlchemy 1.3 Documentation)

Traceback (most recent call last): File “/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py”, line 1276, in _execute_context self.dialect.do_execute( File “/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py”, line 593, in do_execute cursor.execute(statement, parameters) sqlite3.DatabaseError: database disk image is malformed The above exception was the direct cause of the following exception: Traceback (most recent call last): File “/usr/src/homeassistant/homeassistant/components/recorder/init.py”, line 454, in _commit_event_session_or_retry self._commit_event_session() File “/usr/src/homeassistant/homeassistant/components/recorder/init.py”, line 509, in _commit_event_session self.event_session.flush() File “/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py”, line 2536, in flush self._flush(objects) File “/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py”, line 2678, in flush transaction.rollback(capture_exception=True) File “/usr/local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py”, line 68, in exit compat.raise( File “/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py”, line 182, in raise raise exception File “/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py”, line 2638, in flush flush_context.execute() File “/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py”, line 419, in execute n.execute_aggregate(self, set) File “/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py”, line 521, in execute_aggregate self.execute(uow) File “/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py”, line 586, in execute persistence.save_obj( File “/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py”, line 239, in save_obj _emit_insert_statements( File “/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py”, line 1135, in _emit_insert_statements result = cached_connections[connection].execute( File “/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py”, line 1011, in execute return meth(self, multiparams, params) File “/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py”, line 298, in _execute_on_connection return connection._execute_clauseelement(self, multiparams, params) File “/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py”, line 1124, in _execute_clauseelement ret = self._execute_context( File “/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py”, line 1316, in _execute_context self.handle_dbapi_exception( File “/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py”, line 1510, in handle_dbapi_exception util.raise( File “/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py”, line 182, in raise raise exception File “/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py”, line 1276, in _execute_context self.dialect.do_execute( File “/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py”, line 593, in do_execute cursor.execute(statement, parameters) sqlalchemy.exc.DatabaseError: (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-10-28 23:29:06.226915’, ‘2020-10-28 23:29:06.226915’, ‘228c9b85b56dexxxd5e83fc9b0ec3a78’, None, None)] (Background on this error at: Error Messages — SQLAlchemy 1.3 Documentation)

2

This is the important thing to take away from that mess of text.

Unfortunately the database rarely survives a restore (and there’s not much point backing it up).

Delete your home assistant database and restart. A new one will be created. You will lose your history data.

If you are particularly attached to your history data you can search the forum for SQL repair options.

3

arh, crap… :grimacing:
I’m not familiar with the data model. what is stored in the database? Are entities, automations, integrations, settings etc. stored? will I have to re setup everything ?

4

No, nothing so drastic. It only stores the state history of entities. Think “history graphs”.

1 Like
5

pheeew. greatful to hear that :slight_smile:
Now how do I delete the database, and will home assistant automatically create a new one when it realizes the current one is missing ?

Edit: I found it. thanks for helping :slight_smile:

6

Yes. Just delete the existing and restart Home Assistant.

1 Like
7

I can confirm. it doesn’t survive restore I executed too.
Just for a record: Don’t you know this issue filed in github and being worked on?

BTW all graphs works. only history page can’t load. Will it fix on its own after purge_keep_days at the latest?

8

I am strongly 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 really 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.

1 Like
9

No need to cross post exactly the same thing.

10

You´re right. There also should be no need to have hundreds of topics which have all the same root cause: a malformed SQLite database.

I´ll have a look at it and if I find indications valid enough to bring them on the table I´ll open a GitHub issue for this. I mean it´s just basic stuff, we should be able to trust the snapshots.

11

Just FYI: Fix corrupted / malformed SQLite database (home-assistant_v2.db)

12

As I said in the other post, you will get no traction here. Post an issue on github. I had a look, surprisingly no one feels as strongly as you. There is no existing issue. So it’s up to you to make your voice herd.

13

you could at least give the link to original post xposted here. I didn’t see it
TBH using arguments about xposting or other formal things instead of supporting the issue reporter doesn’t help HA development.

Also there are mods who can merge doubled posts if they decide so. I Don’t understand why you are adding unnecessary side-topic to discussion

You also have to consider that history while not important to you, might be crucial for others. At the end this is what the history is designed for.

But the fact alone that history doesn’t survive snapshot/restore is indication that something is wrong. I suppose it might be consequence if sqllite being no transactional rdbms. but if there is no way to solve that then it should be stressed enough in docs

1 Like
14

I gave help towards getting a solution in my post. Read it again. Maybe get someone to help you if you have trouble with the English language.

15

Why would it be crucial? It is only storing the states…

16

Cannot be those states important?
For me those are important when tracing back some issues or building automations. If I lost history I lost not only data but the exact sample which might be hard to reproduce.
I assume there are persons who might have different needs.

17

Explain please because none of that makes much sense

18

I could but I have a feeling it’s futile. Those are MY data and it’s pretty normal that I might want to not lost them. Doen’t matter what do they contain.

Just inagine you are losing files from your pc. Are you expecting someone will argue an importance of lost content or you would rather get your computer fixed?

19

I store the data I want to keep long time in influxdb. So it does not matter if sometime I have to delete my HA database.

20

it’s for you.
Keep in mind that some people are looking for reliable tool, not something which “must be turned off and on to work”. (regardless it’s free, open source, community driven or other dev model - in case you would like to mention it).

If default database is considered unstable amd unreliable, it should be stated tin documentation at least.
From sw development pov it’s wrong if something fails. Even if some users are ok with that.

Merry Christmas :slight_smile:

1 Like