Tiny Tiny RSS: Community

Migrating from MySQL to PostgreSQL different server versions

I have an old installation of the server, version 1.8, that I seem to be unable to update using the ./update.php --self-update script. This installation uses the MySQL database and I now want to move all old articles to another server running the latest server version from github and using PostgreSQL.

I have installed the data_migration script on the new server to import data but am failing in installing the same script on the old server. There are no messages telling me it cannot be installed on the old server version but thought I would try to update if first and see if that fixes it. Alas, the update does not work with the messages:
[22:36:31/1747] Work directory: /var/www/html/tt-rss
[22:36:31/1747] Checking for tar…
[22:36:31/1747] Checking for gunzip…
[22:36:31/1747] Checking for latest version…
[22:36:31/1747] Unable to fetch version information.

The old version has an import/export plugin but that seems to use a different data format not compatible with the data_migration plugin, correct?

I’d suggest installing the latest TT-RSS version using git and configure it appropriately for access to your existing MySQL database. Then run (with an appropriately privileged user):

$ttrss_location/update.php --update-schema

This will get your database upgraded to the latest schema version (the schema changes required for each incremental upgrade will be applied in order until it’s current – all those updates are stored in schema/versions/mysql for your install). This will get you on the current version of TT-RSS, with your existing data, using MySQL.

And only then worry about migrating it to PostgreSQL.

Obviously backup your existing data first. But don’t try to directly migrate from the TT-RSS of several years ago on MySQL, to the TT-RSS of today on PostgreSQL.

Thank you, that was an excellent suggestion. Unfortunately I discovered that the latest version of tt-rss server requires php 5.6+, I only have php 5.5 on the old server.

However, I am now planning to install another instance of tt-rss on the new server - which has both MariaDB and PostgreSQL installed - then move the MySQL database from the old server to the new one and let this second instance of tt-rss server upgrade the schema, finally use the data_migration module to export the MySQL database and import into PostgreSQL…

^ this looks like a reasonable plan.

OK, ran into some problems. The first one was that after I did a mysql-dump from the old database and then sourced the datafile into the new installation with MySQL as the database the update-schema script complained that three tables were still using ISAM engine: ttrss_cat_counters_cache, ttrss_counters_cache, ttrss_feedbrowser_cache.

To fix this I opened the dump file in an editor and simply changed the engine manually for these three tables from MyISAM to InnoDB. I did see that the first two used latin-1 character set and the third one utf-8 but I changed all to utf-8.

Importing this modified sql file now went fine and the number of unread articles looks correct. I then ran the update-schema script that seemed to complete correctly.

The next step was installing the data_migration plugin and to export the data. Now, I ran into another problem:

[22:10:12/16880] processing articles, offset: 10000
PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 2011136 bytes) in /var/www/html/ttrssold/plugins.local/data_migration/init.php on line 151

Can I change the memory php can use somewhere? Would this be unique to tt-rss or for apache itself?

This is a bad idea. You should check the schema for the original database if you still have that running. If that’s MyISAM then you really should forget about migrating and export an OMPL of your feeds (or a dump of just the feed_url column along with batch subscribe on the new install) to bring them over. The reason is that MyISAM doesn’t support foreign key constraints and there’s a strong possibility that your database is in an inconsistent state. I wouldn’t want to bring any potential corruption over to the new install because this kind of thing could create problems that are difficult to identify.

This is just an out of memory error. If you’re running the migration from the CLI it’s weird that you would get this because (at least on Debian) PHP CLI runs without (self-imposed) memory limits. Either way, check the .ini file for PHP CLI and adjust the memory_limit value.

When I updated from MySQL to PostgreSQL, I did an export to OMPL and imported. There was no pain whatsoever. Just use the new docker image (or do it the old fashion way) and import OMPL. It will save you pain. Unless you have dozens of users of course…

If I just export/import OPML I will lose all old articles and I have over 32000 articles in my database.

But is not the MySQL database already in an “inconsistent state”? I have some vague recollection that MyISAM does not support “ON DELETE CASCADE” or some such?

In any case, I presume that if I take my current - for some reason mixed InnoDB/MyISAM - database and eventually import it into PostgreSQL this inconsistency would carry over. Perhaps there is a SQL statement that I can run on the database to clear out any old cruft that should have been deleted had I used PostgreSQL to begin with?

You are right, running php --memory_limit=512M did the trick, the default was only 128M.

data_migration plugin might do the trick, you won’t be able to insert invalid data in the new database because it’s pgsql. you might end up with some articles that should be deleted.

there’s likely data that is not properly referenced in your current database but data migration plugin would ignore it.

Good and the import seemed to work. I can now access my new installation of tt-rss using the PostgreSQL database fine on the web.

I still have two problems to resolve though:

  • The tt-rss Android app cannot log in but generates a server 500 error that looking at my error logs could not let me resolve.
  • The update_daemon2.php script does not want to run complaining about the default php version of 5.4.16 on my system even though I do have php 7.2 installed which apache uses and any terminal session I open also uses. I have not yet figured out why the update script persists in using the old version…

The path to the PHP executable as defined in the config.php file might be referencing a different, older version.

Yes, you are absolutely correct! It defaulted to /usr/bin/php which was the old version on my system…
Fixed so now running update_daemon2.php from a terminal window works fine but the tt-rss.service file I created to be able to use systemd still defaults to php54…

This RedHat article suggests that serviced operates differently with respect to using eg php installed from Software Collections:

stuff like this is why i made docker images for tt-rss. unless you have very specific needs, you really shouldn’t install tt-rss directly on a host anymore.

After some more googling I found out I can modify the line with ExecStart line in the system unit for tt-rss to begin with the explicit location of the php72 executable rather than let it default to the OS default of php54.

So, now on to the, hopefully, last issue: being able to log in using the Android app where I am currently getting a 500 error from the server…

As I stated before, I am unable to access tt-rss using the Android app although the browser works fine (I have removed IP information below):

In httpd/access_log:
[24/Jun/2020:00:42:15 +0200] “POST /tt-rss//api/ HTTP/1.1” 500 3351 “-” "Tiny Tiny RSS (Android) 1.283 (517) Dalv…

In httpd/ssl_access_log:
… [24/Jun/2020:00:41:31 +0200] “GET /tt-rss/api HTTP/1.1” 301 240
… [24/Jun/2020:00:41:31 +0200] “GET /tt-rss/api/ HTTP/1.1” 200 59

In httpd/ssl_request_log:
[24/Jun/2020:00:41:31 +0200] … TLSv1.2 ECDHE-RSA-AES128-GCM-SHA256 “GET /tt-rss/api HTTP/1.1” 240
[24/Jun/2020:00:41:31 +0200] … TLSv1.2 ECDHE-RSA-AES128-GCM-SHA256 “GET /tt-rss/api/ HTTP/1.1” 59

If I access tt-rss/api from my browser I get:

Does this give a clue where the problem may lie and what I should investigate? Is this an access issue to tt-rss/api directory?

I think I solved the above problem - it seems I may have a misconfigured .conf file for another apache application that interfered together with password protection…

The Android app now works, will solve the two issues above later. So far, tt-rss server and app are good and I can now get rid of my old MySQL installation on the old server and use the PostgreSQL installation on the new server instead. All articles have been migrated.

Thank you for everyone’s help!