1

I think my filters table is corrupted. I’m seeing numerous errors in the error log, and sometimes my changes to filters do not save, or the changes show up in the wrong filter. I don’t know what caused the foreign key problems to start with, but I’d like to delete all filters, and clean up any relevant tables in the database, and start over. Could someone share some instructions for cleaning up the filters and any other related tables?

v20.11-242aa6e41

Uncaught PDOException: SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table “ttrss_filters2_rules” violates foreign key constraint “ttrss_filters2_rules_filter_id_fkey” DETAIL: Key (filter_id)=(1) is not present in table “ttrss_filters2”. in /var/www/html/tt-rss/classes/pref/filters.php:681 Stack trace: #0 /var/www/html/tt-rss/classes/pref/filters.php(681): PDOStatement->execute(Array) #1 /var/www/html/tt-rss/classes/pref/filters.php(617): Pref_Filters->saveRulesAndActions(‘1’) #2 /var/www/html/tt-rss/backend.php(113): Pref_Filters->editSave() #3 {main} thrown

2

take a full database backup before doing anything.

drop relevant tables and recreate them from the schema files? you will lose all your filters, obviously.

whatever corruption you have experienced might not be limited to filters though.

3

I didn’t make any changes outside of the UI, so I’m not sure what caused the corruption. I had a lot of filters that I’ll have to recreate, but I’m trying to avoid reinstalling tt-rss from scratch and losing all of thee feed subscriptions. This is a relatively new installation ~ 1wk old.

4

since its just a week old you would be better off exporting OPML, nuking the database and starting from scratch. this would also migrate the filters and settings, even though some filters will be corrupted.

the important part is figuring out why you had this problem in the first place. you haven’t mentioned what database you’re using (going by constraint names i’m assuming postgres) but this stuff is not normal even for mysql. this is the kind of situation where you run memtest, etc.

5

It is a docker container hosted in a Digital Ocean docker droplet, which I believe uses PostgreSQL. I’m probably going to destroy the droplet and start over.

6

After doing some digging I think the problem is at the web layer, not the DB layer. When editing a rule, it always tries POSTing with filter_id=1 regardless of the filter. Since filter_id=1 doesn’t exist, I get the error. Strangely, it fails most of the time, but works occasionally. I haven’t been able to determine the pattern.

Any ideas?

Uncaught PDOException: SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "ttrss_filters2_rules" violates foreign key constraint "ttrss_filters2_rules_filter_id_fkey" DETAIL: Key (filter_id)=(1) is not present in table "ttrss_filters2". in /var/www/html/tt-rss/classes/pref/filters.php:681 Stack trace: #0 /var/www/html/tt-rss/classes/pref/filters.php(681): PDOStatement->execute(Array) #1 /var/www/html/tt-rss/classes/pref/filters.php(617): Pref_Filters->saveRulesAndActions('1') #2 /var/www/html/tt-rss/backend.php(113): Pref_Filters->editSave() #3 {main} thrown

7

if you’re implying that this id is hardcoded or something do note that this would’ve broken filters for everyone, not just you. which is seemingly not the case.

i already told you what you need to do in my previous post. you decided to do something else instead so i can’t help you any further.

it’s entirely possible that i’m wrong and the problem is with tt-rss codebase somehow but in this case i’m going to need something more solid than vague guesses based on potentially broken database running on idk what a “droplet” even is.

good luck.

e: are even running official compose setup?

8

I installed TT-RSS using these instructions, in Docker on a Ubuntu 20.04 VM. I’m not implying anything, or accusing anyone of faulty code. I’m simply trying to solve a problem without having to start from scratch. I happened to notice that when I am unable to save filter changes (which is most but not all of the time), it was trying to save the filter as filter_id=1, regardless of the actual filter_if of the filter I edited. I thought that perhaps if I provided this new information, that someone might have experienced the same problem.

I understand this could be a problem with the OS or browser, and not a problem with the code. My guess is that it’s a browser issue. I’ve tested it in multiple browsers, but the results are inconsistent, and I haven’t noticed a pattern. I appreciate the help you’ve provided. I’ll report back if I figure it out.