Migration problem from Raspberry Pi to Virtualbox

madbrain · June 5, 2023, 1:38am

I have been running HAOS for about a year on a Raspberry Pi 3B+.
I have other more powerful PC hardware, and decided to run HAOS in Virtualbox.
I did an HAOS backup on the Pi which took about 35 minutes and was 878MB.
I then restored it on the Virtualbox instance. So far, so good.
I’ll skip the part about setting up my USB devices - Z-Wave stick and serial adapter for X10 - those went smoothly to in the VM.
The devices and automations I tested so far all appear to work fine.

Where I start to see a problem is that sensor data history seems to be all gone.
When I take a new backup under Virtualbox, it is just 6MB, instead of the 878MB on the Pi.

I suspect part (most) of the backup data didn’t get restored properly.
The data is important to me, for example solar/energy history, so I don’t want to lose it.
Is there anything I can do to retain it besides go back to using HAOS on my Raspberry Pi ?

francisp · June 5, 2023, 3:21am

If you use the standard database, it is home-assistant_v2.db . Stop HA, copy it over from your Pi, restart HA. It might work if both versions are the same.

madbrain · June 5, 2023, 5:04am

Thanks. I’m using HAOS, so it’s not as simple a matter to extract the file and copy it.
I was expecting the standard backup/restore facility to work, as it has in the past, at least when backing up and restoring on the same device. I’m frankly surprised that the restore partially failed.

francisp · June 5, 2023, 5:05am

Database can become corrupted during the backup.

Using the Samba add-on, it is just a copy-and-paste operation to copy the db over.

madbrain · June 5, 2023, 7:04am

Is there a way to check if the backup is corrupt ? I have restored backup on multiple occasions, one just on Friday, and never experienced this before. But I was restoring from a backup made on the Pi onto the Pi, not restoring on another platform (migrating).

francisp · June 5, 2023, 7:12am

Check your /config directory. There is usually a home-assistant_v2_xxx.db file if HA decided your database is corrupt.

madbrain · June 5, 2023, 7:18am

Thanks. On the SSD on my Pi 3B+, there is no “corrupt” DB file in /config .
On the VM, I do see this :

What could cause the corruption ? And how do I ensure I get a clean, restorable backup ?

madbrain · June 5, 2023, 5:23pm

So, I did another backup last night from the Pi, and restored it onto the VM.
It showed the database in /config was not corrupt, and I had all my history visible properly. I went to sleep happy around at 1am as this meant both the backup and restores had succeeded.

Then, this morning, I logged in to HA and all the prior sensor history prior to 4am was gone. And the database had been moved to corrupt state at 4:12am. Seems like there is a bug in HA, and it looks like I have to go back to my Pi one more time.

edit: there is an automatic backup at 1:55am of about 900MB on the VM (made by the Samba backup add-on), which proves the DB had been initially restored properly.

edit: found this in the system log on the VM :

Logger: homeassistant.components.recorder.core
Source: components/recorder/purge.py:250
Integration: Recorder (documentation, issues)
First occurred: 04:12:27 (1 occurrences)
Last logged: 04:12:27

Unrecoverable sqlite3 database corruption detected: (sqlite3.DatabaseError) database disk image is malformed (Background on this error at: https://sqlalche.me/e/20/4xp6)
Traceback (most recent call last):
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/cursor.py", line 1140, in fetchall
    rows = dbapi_cursor.fetchall()
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/core.py", line 895, in _process_one_task_or_recover
    return task.run(self)
  File "/usr/src/homeassistant/homeassistant/components/recorder/tasks.py", line 113, in run
    if purge.purge_old_data(
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 608, in wrapper
    return job(instance, *args, **kwargs)
  File "/usr/src/homeassistant/homeassistant/components/recorder/purge.py", line 89, in purge_old_data
    has_more_to_purge |= _purge_states_and_attributes_ids(
  File "/usr/src/homeassistant/homeassistant/components/recorder/purge.py", line 193, in _purge_states_and_attributes_ids
    state_ids, attributes_ids = _select_state_attributes_ids_to_purge(
  File "/usr/src/homeassistant/homeassistant/components/recorder/purge.py", line 250, in _select_state_attributes_ids_to_purge
    ).all():
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/result.py", line 1388, in all
    return self._allrows()
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/result.py", line 557, in _allrows
    rows = self._fetchall_impl()
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/result.py", line 2291, in _fetchall_impl
    return list(self.iterator)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/loading.py", line 191, in chunks
    fetch = cursor._raw_all_rows()
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/result.py", line 548, in _raw_all_rows
    rows = self._fetchall_impl()
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/cursor.py", line 2108, in _fetchall_impl
    return self.cursor_strategy.fetchall(self, self.cursor)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/cursor.py", line 1144, in fetchall
    self.handle_exception(result, dbapi_cursor, e)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/cursor.py", line 1085, in handle_exception
    result.connection._handle_dbapi_exception(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2339, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/cursor.py", line 1140, in fetchall
    rows = dbapi_cursor.fetchall()
sqlalchemy.exc.DatabaseError: (sqlite3.DatabaseError) database disk image is malformed
(Background on this error at: https://sqlalche.me/e/20/4xp6)


Logger: homeassistant.components.recorder.util
Source: components/recorder/purge.py:250
Integration: Recorder (documentation, issues)
First occurred: 04:12:27 (1 occurrences)
Last logged: 04:12:27

Error executing query: (sqlite3.DatabaseError) database disk image is malformed (Background on this error at: https://sqlalche.me/e/20/4xp6)
Traceback (most recent call last):
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/cursor.py", line 1140, in fetchall
    rows = dbapi_cursor.fetchall()
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/util.py", line 130, in session_scope
    yield session
  File "/usr/src/homeassistant/homeassistant/components/recorder/purge.py", line 89, in purge_old_data
    has_more_to_purge |= _purge_states_and_attributes_ids(
  File "/usr/src/homeassistant/homeassistant/components/recorder/purge.py", line 193, in _purge_states_and_attributes_ids
    state_ids, attributes_ids = _select_state_attributes_ids_to_purge(
  File "/usr/src/homeassistant/homeassistant/components/recorder/purge.py", line 250, in _select_state_attributes_ids_to_purge
    ).all():
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/result.py", line 1388, in all
    return self._allrows()
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/result.py", line 557, in _allrows
    rows = self._fetchall_impl()
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/result.py", line 2291, in _fetchall_impl
    return list(self.iterator)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/loading.py", line 191, in chunks
    fetch = cursor._raw_all_rows()
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/result.py", line 548, in _raw_all_rows
    rows = self._fetchall_impl()
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/cursor.py", line 2108, in _fetchall_impl
    return self.cursor_strategy.fetchall(self, self.cursor)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/cursor.py", line 1144, in fetchall
    self.handle_exception(result, dbapi_cursor, e)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/cursor.py", line 1085, in handle_exception
    result.connection._handle_dbapi_exception(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2339, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/cursor.py", line 1140, in fetchall
    rows = dbapi_cursor.fetchall()
sqlalchemy.exc.DatabaseError: (sqlite3.DatabaseError) database disk image is malformed
(Background on this error at: https://sqlalche.me/e/20/4xp6)

madbrain · June 6, 2023, 12:32am

I restored my 1:55am backup around noon. HAOS on the VM has been OK with no database corruption since, but it’s only 5 hours. Still very strange that this corruption happened in the first place.

madbrain · June 6, 2023, 10:04pm

Sadly, at 04:12am exactly this morning - the exact same time to the minute as the day before - the database corrupt itself again.

I don’t have any automation running at 04:12am, so I really don’t know what’s special about that time.
The log statements immediately prior to the database corruption don’t offer any insight. They were about Google Cast devices (my Chromecast audios).

I guess I need to file an issue.

francisp · June 7, 2023, 4:14am

It is the time the database purge normally runs.

madbrain · June 7, 2023, 8:02pm

Thanks, that explains it !
Questions :

Is there a way to manually force a database purge to happen, so that one can check the current state of the database, rather than wait until 4am ?
is there a way to salvage the content of my database even though it’s being detected as corrupt by the 4am purge ? Everything actually seems to work fine in HA prior to the database purge. There is about 3GB of data uncompressed that I’m not keen on abandoning.
is there a way to stop the 4am database purge from happening ?

Thanks.

madbrain · June 8, 2023, 3:00am

I have been doing a lot of Googling and found threads that point to ways to try to fix corrupt databases using the command-line sqlite3 tool and the commands “.dump” or “.recover”. It looks like if I go back to a 6/3 snapshot, I am able to fix the sqlite DB, for some definition of “fix”.

I then scp’ed it to HAOS and overwrote the db file in /config with this new version. But when I restarted HA, it decided to create a brand new db. I’m assuming it’s not a good thing to overwrite a live DB. What’s the proper procedure to do it ?

I even tried to put that DB file into an existing backup archive, but then my Frankenbackup.tar file was rejected when uploading it with “400 bad request”. I assume there is some sort of integrity check going on, hash, signature or just size check. I didn’t try to dig to see which it was.

Any help on fixing the broken DB on HAOS is appreciated.