Having trouble with my db and cannot fix it
Error executing query: (sqlite3.DatabaseError) database disk image is malformed [SQL: SELECT statistics_short_term.metadata_id, statistics_short_term.start_ts, statistics_short_term.mean, statistics_short_term.min, statistics_short_term.max, statistics_short_term.last_reset_ts, statistics_short_term.state, statistics_short_term.sum FROM statistics_short_term JOIN (SELECT statistics_short_term.metadata_id AS metadata_id, max(statistics_short_term.start_ts) AS start_max FROM statistics_short_term WHERE statistics_short_term.metadata_id IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) GROUP BY statistics_short_term.metadata_id) AS anon_1 ON statistics_short_term.metadata_id = anon_1.metadata_id AND statistics_short_term.start_ts = anon_1.start_max] [parameters: (5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 26, 27, 28, 29, 30, 34, 35, 36, 37, 38, 39, 40, 159, 123, 124, 129, 130, 135, 136, 91, 94, 98, 100, 106, 108, 110, 85, 87, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 117, 118, 140, 152)] (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/base.py", line 1964, in _exec_single_context
self.dialect.do_execute(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 748, 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/util.py", line 123, in session_scope
yield session
File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 1971, in get_latest_short_term_statistics
stats = cast(Sequence[Row], execute_stmt_lambda_element(session, stmt))
File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 207, in execute_stmt_lambda_element
executed = session.execute(stmt)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2229, in execute
return self._execute_internal(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2124, in _execute_internal
result: Result[Any] = compile_state_cls.orm_execute_statement(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/context.py", line 253, in orm_execute_statement
result = conn.execute(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1414, in execute
return meth(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/lambdas.py", line 600, in _execute_on_connection
return connection._execute_clauseelement(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1638, in _execute_clauseelement
ret = self._execute_context(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1842, in _execute_context
return self._exec_single_context(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1983, in _exec_single_context
self._handle_dbapi_exception(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2326, in _handle_dbapi_exception
raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1964, in _exec_single_context
self.dialect.do_execute(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 748, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.DatabaseError: (sqlite3.DatabaseError) database disk image is malformed
[SQL: SELECT statistics_short_term.metadata_id, statistics_short_term.start_ts, statistics_short_term.mean, statistics_short_term.min, statistics_short_term.max, statistics_short_term.last_reset_ts, statistics_short_term.state, statistics_short_term.sum
FROM statistics_short_term JOIN (SELECT statistics_short_term.metadata_id AS metadata_id, max(statistics_short_term.start_ts) AS start_max
FROM statistics_short_term
WHERE statistics_short_term.metadata_id IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) GROUP BY statistics_short_term.metadata_id) AS anon_1 ON statistics_short_term.metadata_id = anon_1.metadata_id AND statistics_short_term.start_ts = anon_1.start_max]
[parameters: (5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 26, 27, 28, 29, 30, 34, 35, 36, 37, 38, 39, 40, 159, 123, 124, 129, 130, 135, 136, 91, 94, 98, 100, 106, 108, 110, 85, 87, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 117, 118, 140, 152)]
(Background on this error at: https://sqlalche.me/e/20/4xp6)
i have tried this:
sqlite3 ./home-assistant_v2.db ".recover" | sqlite3 ./home-assistant_v2.db.fix
mv ./home-assistant_v2.db ./home-assistant_v2.db.broken
mv ./home-assistant_v2.db.fix ./home-assistant_v2.db
and
sqlite3 ./home-assistant_v2.db ".dump" | sqlite3 ./home-assistant_v2.db.fix
mv ./home-assistant_v2.db ./home-assistant_v2.db.broken
mv ./home-assistant_v2.db.fix ./home-assistant_v2.db
but with this commands nothing happens.
I hope someone can help me to recover my 6gb big database - i hope not to lose over 1 year of data