taproot-assets icon indicating copy to clipboard operation
taproot-assets copied to clipboard

Published 20 hours ago • verified publisher icon lightninglabs

[feature]: add sqlite database integrity checks to aid troubleshooting

Open ffranr opened this issue 1 year ago • 2 comments

A user on Slack encountered a SQLite database foreign key constraint violation error when starting tapd. To address this, it's proposed that tapd should optionally perform SQLite database integrity checks during startup. This enhancement will be controlled by a new command line argument flag.

Proposed Changes

  • Modify the sql.Open call in the function NewSqliteStore to include debug/integrity check pragma options. This function is instantiated at startup in the tapcfg/server.go file (source).

  • Implement SQLite "pragma" options for integrity checks. Relevant options are foreign_key_check and integrity_check. These two checks do not seem to overlap and can be included for comprehensive validation.

  • Introduce a command line argument flag to control the execution of these checks, as they may significantly slow down tapd startup.

A list of SQLite "pragma" options can be found here.

ffranr avatar Jan 08 '24 11:01 ffranr

Was this due to a partially botched migration? We already have the pragama to enforce the foreign key constraints. Did they try to manually modify the db?

Roasbeef avatar Jan 08 '24 19:01 Roasbeef

@Roasbeef

Was this due to a partially botched migration? We already have the pragama to enforce the foreign key constraints.

I'm not sure. Possibly. This is why I think it would be good to have a user side check that the user could run.

Is this feature a good idea? (cc @GeorgeTsagk @jharveyb @guggero )

Did they try to manually modify the db?

No, I don't think so.

Here's the related slack thread: https://lightningcommunity.slack.com/archives/C03B3556HQ8/p1704690702094769

The user has performed a foreign key check on the db directly using a db tool. The check passed successfully. Which suggests that the db is not faulty but that tapd is running a query at startup which violates a foreign key constraint. I've asked the user to re-run with trace level logging to see if we can identify which query is broken, if any.

I think this issue would still be useful for troubleshooting user bugs. It's extra debug tooling on the user's side.

ffranr avatar Jan 09 '24 14:01 ffranr