Potential problems when upgrading to schema 137

Tiny Tiny RSS Support
1

Schema 137 adds a composite key which enforces subscribed feeds being unique per-user. Previously tt-rss UI would prevent this in most cases but one way or another it was possible to have duplicate feeds (for example, through recently fixed bug in batch subscribe).

In this case upgrade to schema 137 will fail with an error similar to this one:

Error: SQLSTATE[23505]: Unique violation: 7 ERROR: could not create unique index “ttrss_feeds_feed_url_owner_uid_key” DETAIL: Key (feed_url, owner_uid)=(http://example.com/feed, 1) is duplicated.

How to resolve this:

  1. Delete problematic feeds directly from the database (easiest):
DELETE FROM ttrss_feeds WHERE id NOT IN
    (SELECT DISTINCT ON (feed_url, owner_uid) id FROM ttrss_feeds);

Edit: mysql variant below, untested but seemingly works (as usual I suggest backing up your database!):

DELETE f1 FROM ttrss_feeds f1 INNER JOIN ttrss_feeds f2 
    WHERE f1.id < f2.id AND f1.feed_url = f2.feed_url 
       AND f1.owner_uid = f2.owner_uid;

This will only delete duplicate feed entries. Then re-run database updater.

  1. Through tt-rss UI aka the hard way:

You’ll need to go to a previous changeset first, for example dd9e93384:

git checkout dd9e93384

Open tt-rss as usual and unsubscribe from duplicate feeds indicated in the above error message. Then go back to master branch

git checkout master

And re-run database updater.

3

Deleting a subscription won’t delete the starred article for this subscription?

4

it will if you delete directly from the database, to keep starred articles it would probably be better to either

  • unsubscribe through the UI or
  • get the list of affected feed IDs and set feed_id in ttrss_user_entries to NULL (where marked is true) thus effectively moving articles to archived feed; then running delete query above to remove the feeds. so, two queries instead of one.

e: in retrospect i guess this particular constraint in ttrss_user_entries should have used ON DELETE SET NULL instead of cascade delete which would have been a bit safer. oh well.

5

In my case updating to version 137 failed with the following message:

alter table ttrss_feeds add constraint ttrss_feeds_feed_url_owner_uid_key unique (feed_url(255), owner_uid)

Error: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1' for key 'ttrss_feeds_feed_url_owner_uid_key'

My tt-rss instance has only a single user, but surprisingly there was a duplicate in the ttrss_feeds table! Fox’s script worked for me.