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…
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?
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…
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…
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.
After reading this thread, I just migrated my tt-rss data from an old (376fe6) MySQL-backed install, to a Postgres-backed install on latest master and it went well (~120 feeds, ~50k articles). Here is how I did it
# 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
So far everything I care about has been correctly preserved/migrated. Thanks!!
Edit: it doesn’t preserve the read/unread article status, but I can live with it
Hmmm well the migration not keeping articles read/unread status is in fact a problem… I’m still looking for a way to migrate this (by the way I can’t find the read/unread status in the DB, where is it?). Any help is appreciated
It seems I was wrong. The unread status is stored in ttrss_user_entries in the database, it’s indeed exported and correctly imported. My manual comparison of read/unread status was wrong. All good.
QQ for ya, how’d you do this on the docker side? Did you have to do ttrss-data-migration on one docker image and import db on the ttrss-docker_db_1 image?