Have been following the forum for years, but this is my first posting.
Describe the problem you’re having:
After running on a shared host for years (since 2015) with and old version with simpleupdate mode, I have now moved to a new VPS server. Now running on git download version.
The database dump was over 110 MB, and it did take some time to get it imported on the new server.
I found a lot of record in ttrss_enclosures, that referred to deleted records in ttrss_entries.
No problem updating the database to the new version.
After running the new setup for around a week, I analyzed the database again, and found lot of orphaned records in 3 tabels: ttrss_enclosures, ttrss_tags and ttrss_user_labels2. More than 240000 orphaned records in total, 230000 of them in ttrss_enclosures.
I have searched the code and there don’t seems to be any delete statements handling these orphaned records.
There is code for deleting orphaned record in ttrss_entries table in function purge_orphans() in article.php, and I think this could be expanded to handle orphaned record in ttrss_enclosures, ttrss_tags and ttrss_user_labels2 also.
…
If possible include steps to reproduce the problem:
Missing cleanup after deleting records in ttrss_entries. Only visible after long time use of Tiny Tiny Rss.
…
It’s almost guaranteed that you’re not using InnoDB, which means your database is probably in an inconsistent state.
You should dump your feeds into an OPML file using the option inside the UI. Drop the database. Run through the TT-RSS installer. Import your OPML file.
You can continue to use TT-RSS, but realize that at some point things are just going to break.
Also, if you’re going to follow my advice, I’d also suggest removing MariaDB and using PostgreSQL; unless you have a specific need for the MariaDB.
All tables are InnoDB, both on the old server and after import to the new server, created back with DB version 129, either by Tiny Tiny Rss installer or running Schema in phpMyAdmin (can’t remember which).
My complete database dump from phpMyAdmin in connection with server change, does not contain any FOREIGN KEY definitions with ON DELETE CASCADE. And with 230000 orphaned record in ttrss_enclusure table, indicate that they probably never existed (was in the version 129 schema file).
I now installed PostgreSql on the server, and found the next problem the export of articles stops after 10000 records, but that was easily fixed in the plugin (upped the limit to 100000).
The import handled the large importfile with no problem.
Now I’m up and running on PostgreSql with my more than 29000 starred articles imported.
In a case like this it would have been nice to have all active articles marked unread=0 included also, as the database was flooded with these articles right away, as soon as the update demon was started again, but now as unread, and it took some time to get them marked correct again.
foreign keys and everything were there from the beginning, however i wasn’t aware that mysql was such a hopeless pile of shit it could simply choose to ignore these definitions sometimes without telling you.
allowing myself to get talked into supporting mysql was the largest mistake i made for tt-rss.
Yes, it was the import_export plugin, but that I didn’t know at first. I just clicked in preference under feeds, the link in the bottom “Import and export”.
It was when I searched for the limit 10000, that I found that it was in import_export plugin.
There was no reference to find a data-migration plugin in stead… so how should I know?