Getting a database error: database disk image is malformed

oliwa · April 10, 2020, 3:22pm

Logger: homeassistant.components.recorder
Source: components/recorder/init.py:375
Integration: recorder (documentation, issues)
First occurred: April 8, 2020, 5:51:27 PM (22352 occurrences)
Last logged: 10:19:09 AM

* Error adding event: (sqlite3.DatabaseError) database disk image is malformed [SQL: INSERT INTO events (event_type, event_data, origin, time_fired, created, context_id, context_user_id) VALUES (?, ?, ?, ?, ?, ?, ?)] [parameters: ('state_changed', '{"entity_id": "sensor.load_1m", "old_state": {"entity_id": "sensor.load_1m", "state": "0.11", "attributes": {"unit_of_measurement": " ", "friendly_na ... (441 characters truncated) ... :00", "last_updated": "2020-04-10T15:18:43.053084+00:00", "context": {"id": "d9e69c4cd0f14e1ea38e89b56a29b5f7", "parent_id": null, "user_id": null}}}', 'LOCAL', '2020-04-10 15:18:43.053304', '2020-04-10 15:18:43.134815', 'd9e69c4cd0f14e1ea38e89b56a29b5f7', None)] (Background on this error at: http://sqlalche.me/e/4xp6)
* Error adding event: (sqlite3.DatabaseError) database disk image is malformed [SQL: INSERT INTO events (event_type, event_data, origin, time_fired, created, context_id, context_user_id) VALUES (?, ?, ?, ?, ?, ?, ?)] [parameters: ('state_changed', '{"entity_id": "switch.wemo_2", "old_state": {"entity_id": "switch.wemo_2", "state": "on", "attributes": {"current_power_w": 30.82, "today_energy_kwh" ... (754 characters truncated) ... :00", "last_updated": "2020-04-10T15:18:47.919416+00:00", "context": {"id": "53da7400960740918e39b28ba92cde26", "parent_id": null, "user_id": null}}}', 'LOCAL', '2020-04-10 15:18:47.919539', '2020-04-10 15:18:47.938885', '53da7400960740918e39b28ba92cde26', None)] (Background on this error at: http://sqlalche.me/e/4xp6)
* Error adding event: (sqlite3.DatabaseError) database disk image is malformed [SQL: INSERT INTO events (event_type, event_data, origin, time_fired, created, context_id, context_user_id) VALUES (?, ?, ?, ?, ?, ?, ?)] [parameters: ('state_changed', '{"entity_id": "sun.sun", "old_state": {"entity_id": "sun.sun", "state": "above_horizon", "attributes": {"next_dawn": "2020-04-11T10:37:12+00:00", "ne ... (958 characters truncated) ... :00", "last_updated": "2020-04-10T15:18:50.020133+00:00", "context": {"id": "06afbae68e2f45bdb2e9dd000c2f0fcb", "parent_id": null, "user_id": null}}}', 'LOCAL', '2020-04-10 15:18:50.020321', '2020-04-10 15:18:50.040779', '06afbae68e2f45bdb2e9dd000c2f0fcb', None)] (Background on this error at: http://sqlalche.me/e/4xp6)
* Error adding event: (sqlite3.DatabaseError) database disk image is malformed [SQL: INSERT INTO events (event_type, event_data, origin, time_fired, created, context_id, context_user_id) VALUES (?, ?, ?, ?, ?, ?, ?)] [parameters: ('state_changed', '{"entity_id": "switch.wemo_2", "old_state": {"entity_id": "switch.wemo_2", "state": "on", "attributes": {"current_power_w": 30.82, "today_energy_kwh" ... (755 characters truncated) ... :00", "last_updated": "2020-04-10T15:18:58.937117+00:00", "context": {"id": "d450cb24ad1a455f9dcb679ba26de767", "parent_id": null, "user_id": null}}}', 'LOCAL', '2020-04-10 15:18:58.937241', '2020-04-10 15:18:58.952473', 'd450cb24ad1a455f9dcb679ba26de767', None)] (Background on this error at: http://sqlalche.me/e/4xp6)
* Error adding event: (sqlite3.DatabaseError) database disk image is malformed [SQL: INSERT INTO events (event_type, event_data, origin, time_fired, created, context_id, context_user_id) VALUES (?, ?, ?, ?, ?, ?, ?)] [parameters: ('state_changed', '{"entity_id": "switch.wemo_2", "old_state": {"entity_id": "switch.wemo_2", "state": "on", "attributes": {"current_power_w": 30.855, "today_energy_kwh ... (756 characters truncated) ... :00", "last_updated": "2020-04-10T15:19:09.014278+00:00", "context": {"id": "18bab0fb2c744dd18514e2351254b90b", "parent_id": null, "user_id": null}}}', 'LOCAL', '2020-04-10 15:19:09.014390', '2020-04-10 15:19:09.031028', '18bab0fb2c744dd18514e2351254b90b', None)] (Background on this error at: http://sqlalche.me/e/4xp6)

Traceback (most recent call last): File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1246, in _execute_context cursor, statement, parameters, context File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 588, 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 375, in run self.event_session.flush() File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2479, in flush self._flush(objects) File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2617, in _flush transaction.rollback(_capture_exception=True) File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__ compat.reraise(exc_type, exc_value, exc_tb) File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 153, in reraise raise value File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2577, in _flush flush_context.execute() File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/unitofwork.py", line 422, in execute rec.execute(self) File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/unitofwork.py", line 589, in execute uow, File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py", line 245, in save_obj insert, File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py", line 1137, in _emit_insert_statements statement, params File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 982, in execute return meth(self, multiparams, params) File "/usr/local/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 293, in _execute_on_connection return connection._execute_clauseelement(self, multiparams, params) File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1101, in _execute_clauseelement distilled_params, File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1250, in _execute_context e, statement, parameters, cursor, context File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1476, in _handle_dbapi_exception util.raise_from_cause(sqlalchemy_exception, exc_info) File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=cause) File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 152, in reraise raise value.with_traceback(tb) File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1246, in _execute_context cursor, statement, parameters, context File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 588, 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) VALUES (?, ?, ?, ?, ?, ?, ?)] [parameters: ('service_registered', '{"domain": "recorder", "service": "purge"}', 'LOCAL', '2020-04-08 22:50:24.001668', '2020-04-08 22:51:27.839603', '99c567527e2e4b7982d04d3f634f5ce4', None)] (Background on this error at: http://sqlalche.me/e/4xp6)

francisp · April 10, 2020, 3:38pm

Delete your database and restart HA.

oliwa · April 10, 2020, 3:45pm

Thanks! I was an idiot and didn’t search first.

barbatrukko · December 4, 2020, 2:20pm

But so i lost all my data (temp graph, etc?)

francisp · December 4, 2020, 3:27pm

That is a side-effect from deleting the database. I keep the data I want to store long-time in influx-db.

barbatrukko · December 4, 2020, 4:50pm

I just now switched to SSD (Pi3 + SSD) becouse lost last sdcard.
Also i would use more stable db system like postgres or other.
Now with ssd in can install some else. what you suggest?
inclux-db can run into pi3+hassos on ssd?

francisp · December 4, 2020, 4:58pm

Probably. I use an old fashioned 2.5 harddisk.

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

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!

It 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.

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