martinrotter
[BUG]: Articles cannot be deleted from database
Brief description of the issue
Table 'Messages' has a foreign key relationship with table 'backup_Accounts' which does not exist. Attemping to delete rows using sqlite browser, for instance, results in "no such table: main.backup_Accounts". This was probably introduced when db_update_sqlite_1_2.sql renamed the Accounts table.
Tables Categories, Labels, and MessageFiltersInFeeds also have a FK to 'backup_Accounts'.
How to reproduce the bug?
Open database.db in sqlite browser. Select the Messages table. Select a row. Press delete button.
What was the expected result?
The row would be deleted.
What actually happened?
Error deleting record: Result: no such table: main.backup_Accounts
Debug log
N/A
Operating system and version
OS: Linux (Devuan Daedalus) RSS Guard version: 4.8.3 (locally compiled & installed). The database was likely created by v4.0.4.
It seems this would also mean that when an Account is deleted, "child" rows are not cascade-deleted as intended.
"backup_*" tables are generated when automatica DB schema migration happens. In your case, some bug or error probably happened when migration was running and was not completed properly, therefore your DB file now is inconsistent. Use backups which are automatically generated (before each migration happens) in the folder with your database file.
When Account was renamed to backup_Account, that changed the FK relationships too. Creating a new Account table doesn't change those relationships back. See sections 2 & 7 here.
I did
alter table Accounts rename to backup_Accounts;
and then
alter table backup_Accounts rename to Accounts;
and it fixed the FK relationships. This works even if an account had been deleted while the FK relationships were broken. In that case sqlite's foreign_key_check will show all the orphaned rows.
RSS Guard 5.0 will have completely new DB schema and yes, this kind of error could be there, but when foreign_key_check is explicitly turned off and then on after migration between 4.x line DB metaversions, it should all be fine.
I will close this and make sure to double test when there are any DB migrations in the future, thanks.