search

zigpy / zigpy-cli

Command line interface for zigpy
GNU General Public License v3.0
44 stars 12 forks source link

Create a database recovery tool #12

Closed puddly closed 1 year ago

puddly commented 2 years ago

https://github.com/home-assistant/core/issues/71351

@DeadlySin2 @Frank3501 @wolfgangpurrer I've created a tool to recover your zigbee.db and skip the broken rows that are preventing ZHA from properly migrating your database.

You will need a Python environment to install this package, along with access to sqlite3. macOS and Linux are preferable but you can probably find a pre-compiled SQLite binary for Windows if that's all you have available.

Here are some installation instructions for getting a Python virtual environment set up on every common platform: https://github.com/zigpy/zigpy-znp/blob/dev/TOOLS.md#table-of-contents. Ignore the pip install zigpy-znp part and instead substitute the command below:

$ pip install git+https://github.com/zigpy/zigpy-cli.git

If you're running Home Assistant OS, you will have to run apk add sqlite to install the sqlite3 utility.

You can then try recovering it:

$ zigpy -v db recover /path/to/your/zigbee.db fixed.db

Please post your corrupted zigbee.db in this issue as well, I'd like to keep track of what exactly is breaking and possibly include this functionality into zigpy in the future. Thanks!

DeadlySin2 commented 2 years ago

I receive an error No such file or directory: 'sqlite3'

puddly commented 2 years ago

What platform are you running this on? SQLite should be pre-installed just about everywhere except Windows.

DeadlySin2 commented 2 years ago

I’m using home assistant os with SSH & Web Terminal addon

puddly commented 2 years ago

Looks like it doesn't come with sqlite3 either. You can install it:

apk add sqlite
DeadlySin2 commented 2 years ago

Everything is working fine now. Thanks for your support and time.

davidbb commented 2 years ago

For months now I've been seeing the error below in my HA startup logs: 

Logger: zigpy.appdb
Source: components/zha/core/gateway.py:169 
First occurred: July 28, 2022, 2:45:12 PM (1 occurrences) 
Last logged: July 28, 2022, 2:45:12 PM

SQLite database file is corrupted! row 84 missing from index attributes_idx_v7

Everything works well, or at least appears to. I've attempted to run the database recovery script above on a recent copy of my zigbee database, and get a runtime error logged to the console:

2022-07-29 10:00:03.631 pegasus zigpy_cli.database INFO Pragma user version is 10
2022-07-29 10:00:03.631 pegasus zigpy_cli.database INFO Maximum table version is 10
2022-07-29 10:00:03.861 pegasus zigpy_cli.database ERROR Failed to insert INSERT INTO "sqlite_sequence"("_rowid_", "name", "seq") VALUES( 1, 'network_backups_v10', 48 );: IntegrityError('UNIQUE constraint failed: sqlite_sequence.rowid')
2022-07-29 10:00:03.872 pegasus zigpy_cli.database INFO Done

Any ideas on what is going wrong and possibly how to troubleshoot? I can share my zigbee database directly. Thanks!

puddly commented 2 years ago

Can you email it to me? I can try to take a look.

puddly commented 2 years ago

Thanks.

The generated database is correct, you can ignore the warning. I've excluded those tables from the backup and have added validation. All 61 devices are present in the recovered database and it loads properly.

davidbb commented 2 years ago

Thank you @puddly

davidbb commented 2 years ago

Just posting to update that after running the db recovery tool, replacing my home assistant zigbee database with the fixed database, I am no longer seeing the error:

SQLite database file is corrupted! row 84 missing from index attributes_idx_v7

at startup. Thanks again!