Tiny Tiny RSS - might drop support for MySQL

[necrevistonnezr]

Apparently someone found a way in a newer thread: https://community.tt-rss.org/t/migrating-from-mysql-to-postgresql-different-server-versions/3623/20

# OPML import/export (including filters and some settings). Must be done before data_migration plugin if you want to keep feed categories
# on the original machine
sudo mkdir /var/www/tt-rss/export
sudo chown -R www-data:www-data /var/www/tt-rss/export/
sudo -u www-data php /var/www/tt-rss/update.php --opml-export "MYUSERNAME /var/www/tt-rss/export/export-2020-08-07.opml" # export feeds OPML
# on a client
rsync -avP my.original.machine.org:/var/www/tt-rss/export/export-2020-08-07.opml ./ # download opml export
# login to the new tt-rss instance from a browser, go to Preferences > Feeds, import OPML file

# migrate all articles from mysql to postgresql
# on the original machine
git clone https://git.tt-rss.org/fox/ttrss-data-migration
sudo chown -R root:www-data ttrss-data-migration/
sudo mv ttrss-data-migration/ /var/www/tt-rss/plugins.local/data_migration
sudo nano /var/www/tt-rss/config.php # enable data_migration in the PLUGINS array
sudo -u www-data php /var/www/tt-rss/update.php --data_user MYUSERNAME --data_export /var/www/tt-rss/export/export-2020-08-07.zip # export articles to database-agnostic format

# on the target machine
git clone https://git.tt-rss.org/fox/ttrss-data-migration
sudo chown -R root:www-data ttrss-data-migration/
sudo mv ttrss-data-migration/ /var/www/rss.example.org/plugins.local/data_migration
sudo nano /var/www/rss.example.org/config.php # enable data_migration in the PLUGINS array
rsync -avP my.original.machine.org:/var/www/tt-rss/export/export-2020-08-07.zip ./
sudo mkdir /var/www/rss.example.org/export
sudo mv export-2020-08-07.zip /var/www/rss.example.org/export
sudo chown -R root:www-data /var/www/rss.example.org/export
sudo chmod -R g+rX /var/www/rss.example.org/export/
sudo -u www-data php /var/www/rss.example.org/update.php --data_user MYUSERNAME --data_import /var/www/rss.example.org/export/export-2020-08-07.zip # it can take a while
sudo rm -r /var/www/rss.example.org/export/ # cleanup

Also see https://community.tt-rss.org/t/moving-to-docker-with-external-mysql-db/4474/8

[girish]

@JOduMonT said in Tiny Tiny RSS - might drop support for MySQL:

Dropping or not the MySQL I wonder why Cloudron Team choose MySQL over PostgreSQL when this one is available.

<rant> Speaking as a (self-hosted app) developer, providing multiple choices for databases is not a good idea to start with. But yet some apps go down that dangerous road. It just causes already small communities to split and bikeshed the virtues of each database to death. This is similar to nginx/apache etc. Should just pick one </rant>

Anyway, when we started, we just picked one. The upstream project had no preference and most PHP apps use MySQL for support in shared hosting environments.

But more concrete, in the case of TT-RSS with PostgreSQL it would be possible to mark similar articles as read (requires pg_trgm) == plugin: af_psql_trgm

That's a bummer....

I am open to creating a new package with PostgreSQL and deprecating the old one, if this is what upstream wants us to do. I think automatic migration from one database to another while possible is going to result in support tickets for us debugging ttrss database. Something which we are not experts at (and the upstream dev/forum is a frightening place ).

[girish]

@necrevistonnezr Do you happen to know what's the latest on MySQL support ? https://community.tt-rss.org/t/failed-updating-to-version-136/2096/24 atleast says "it won't happen" .

I can't find any db preference info in https://tt-rss.org/wiki/InstallationNotes atleast (many links there are broken). https://tt-rss.org/wiki/InstallationNotesHost says anything is OK but the document itself is deprecated.

[girish]

Digging the forum further:

• It seem OPML is the way to migrate - https://community.tt-rss.org/t/upgrade-path-from-a-very-old-version/5081/2 . This might be a bit lossy though.
• MySQL support flakiness - https://community.tt-rss.org/t/looks-like-mysql-in-tt-rss-is-over-upd-or-is-it/4368 and https://community.tt-rss.org/t/errors-on-edit-feed-and-edit-filter-dialogs/4365 .

So, any opposition for just creating a new TTRSS package with Postgres and deprecate the current one?

[necrevistonnezr]

@girish Not from me…

[nebulon]

We've released a new package with a new appstore id for the postgresql version of tinytinyrss. This is a whole new package, so automatic updates will not migrate to postgres, as there is no good way to automatically migrate.

We've written the steps to migrate over via OPML at https://docs.cloudron.io/apps/tinytinyrss/#migrating-from-mysql-to-postgresql

[necrevistonnezr]

My suggestion is to include migrating steps for your plugins as well! If people forget, they might need to reconfigure their plugins (incl. Fever to connect to a whole bunch of apps) as well....

[necrevistonnezr]

After reimporting the OPML (mind you: including settings), none of my feeds update anymore:

Update process failed with exit code: 110 ()

[nebulon]

do you have any more context to that error? Worked here for my instance, it took the app a bit to catch up with all feeds though.

[necrevistonnezr]

Could be the - built-in! - plugin af_psql_trgm: Marks similar articles as read (requires pg_trgm):

Apr 22 14:31:51 [14:31:51/8] Base feed: https://roundcube.net/feeds/atom.xml
Apr 22 14:31:51 [14:31:51/8] => Roundcube Webmail Project News (ID: 71, U: kdj [2]), last updated: never
Apr 22 14:31:51 [14:31:51/164] Lock: update_daemon-feed-71.lock
Apr 22 14:31:52 PHP Warning: PDOException: SQLSTATE[42883]: Undefined function: 7 ERROR: function "similarity" does not exist
Apr 22 14:31:52 LINE 1: select 'similarity'::regproc
Apr 22 14:31:52 ^ in /app/code/plugins/af_psql_trgm/init.php:287
Apr 22 14:31:52 Stack trace:
Apr 22 14:31:52 #0 /app/code/plugins/af_psql_trgm/init.php(287): PDO->query()
Apr 22 14:31:52 #1 /app/code/classes/PluginHost.php(348): Af_Psql_Trgm->hook_article_filter()
Apr 22 14:31:52 #2 /app/code/classes/RSSUtils.php(919): PluginHost->chain_hooks_callback()
Apr 22 14:31:52 #3 /app/code/update.php(231): RSSUtils::update_rss_feed()
Apr 22 14:31:52 #4 {main} in /app/code/classes/PluginHost.php on line 351
Apr 22 14:31:52 [14:31:52/164] Exception while updating feed 71: SQLSTATE[25P02]: In failed sql transaction: 7 ERROR: current transaction is aborted, commands ignored until end of transaction block (/app/code/classes/RSSUtils.php:1061)
Apr 22 14:31:52 [14:31:52/8] <= 1.0067 (sec) exit code: 110
Apr 22 14:31:52 [14:31:52/8] !! Last error: Update process failed with exit code: 110 ()
Apr 22 14:31:52 PHP Notice: Update process for feed 71 (Roundcube Webmail Project News, owner UID: 2) failed with exit code: 110 (Update process failed with exit code: 110 ()). in /app/code/classes/Logger.php on line 50

[necrevistonnezr]

Sure enough, after disabling the plugin, the feeds are populated...
So, we do have PostgreSQL but some function seems to be missing to use the similarity check: https://gitlab.tt-rss.org/tt-rss/tt-rss/-/blob/master/plugins/af_psql_trgm/init.php

[nebulon]

Cloudron's postgres already supports pg_trgm extension, maybe tinytinyrss does not create it during the db migrations?

[nebulon]

Right so after running CREATE EXTENSION pg_trgm; manually, it starts to work. I've pushed a change which ensures the extension is created, waiting for the app tests to suceed.

[nebulon]

Package update is out now. Took a bit to get the tests sorted.