Empty prefs with default profile / feeds don't update (having problems with mariadb? come here)

Alright, thanks for having a look. I just browsed through the mariadb bug list and couldn’t find anything related to this on first sight. So this report will probably take a long time.

In the meantime, is there an easy way to transition to postgres nowadays? I found some migration tool on github (GitHub - hrk/tt-rss-mysql2pgsql: A quick & dirty tool to migrate an existing TT-RSS installation from MySQL to PostgreSQL.), which hasn’t seen updates for quite some time? Is this still the way to go?

I’d really recommend just exporting your feeds with the OPML option, then also using the import_export plugin to export your starred articles. This way you’re starting with a clean database and using TT-RSS to insert them into the database.

https://git.tt-rss.org/fox/ttrss-data-migration there’s this if you want to migrate your data

personally i would suggest OPML export/import.

That’s interesting.

Can you check what’s inside “ttrss_user_prefs.pref_name” and “ttrss_prefs.pref_name”?

Yet another strange follow up on this: I completely threw away my data directory and mariadb container and reimported the data into a fresh installation. Settings are working now, but feeds are still not updated.

even after update.php --force-updates? strange.

e: you’re right, it doesn’t seem to work properly with your imported dump. it seems to work if i seed the database with default tt-rss schema.

here’s the query in question:

SELECT DISTINCT ttrss_feeds.feed_url, ttrss_feeds.last_updated
                        FROM
                                ttrss_feeds, ttrss_users, ttrss_user_prefs
                        WHERE
                                ttrss_feeds.owner_uid = ttrss_users.id
                                AND ttrss_user_prefs.profile IS NULL
                                AND ttrss_users.id = ttrss_user_prefs.owner_uid
                                AND ttrss_user_prefs.pref_name = 'DEFAULT_UPDATE_INTERVAL'
                                AND ttrss_users.last_login >= DATE_SUB(NOW(), INTERVAL 120 DAY) AND ((
                                        ttrss_feeds.update_interval = 0
                                        AND ttrss_user_prefs.value != '-1'
                                        AND ttrss_feeds.last_updated < DATE_SUB(NOW(), INTERVAL CONVERT(ttrss_user_prefs.value, SIGNED INTEGER) MINUTE)
                                ) OR (
                                        ttrss_feeds.update_interval > 0
                                        AND ttrss_feeds.last_updated < DATE_SUB(NOW(), INTERVAL ttrss_feeds.update_interval MINUTE)
                                ) OR (ttrss_feeds.last_updated IS NULL
                                        AND ttrss_user_prefs.value != '-1')
                                OR (last_updated = '1970-01-01 00:00:00'
                                        AND ttrss_user_prefs.value != '-1'))
                                AND (ttrss_feeds.last_update_started IS NULL OR ttrss_feeds.last_update_started < DATE_SUB(NOW(), INTERVAL 10 MINUTE))
                                ORDER BY last_updated LIMIT 500

yeah i’m thinking it’s not just metadata, even after restoring the dump to a blank data directory things are screwed up.

select distinct ttrss_feeds.feed_url from ttrss_feeds, ttrss_users, ttrss_user_prefs where ttrss_feeds.owner_uid = ttrss_users.id AND ttrss_user_prefs.profile IS NULL AND ttrss_users.id = ttrss_user_prefs.owner_uid
 AND ttrss_user_prefs.pref_name = 'DEFAULT_UPDATE_INTERVAL';

there’s no way this would not produce results on a healthy database, the data is there.

we can even reduce it to the following and it still returns zero:

select distinct feed_url, f.owner_uid, value from ttrss_feeds f, ttrss_users u, ttrss_user_prefs p where f.owner_uid = u.id and pref_name = 'DEFAULT_UPDATE_INTERVAL' and p.owner_uid = u.id;

even though the individual parts of this query work.

MariaDB [ttrss]> select * from ttrss_user_prefs p where p.pref_name = 'DEFAULT_UPDATE_INTERVAL';
+-----------+-------------------------+-------+---------+
| owner_uid | pref_name               | value | profile |
+-----------+-------------------------+-------+---------+
|         1 | DEFAULT_UPDATE_INTERVAL | 30    |    NULL |
|         2 | DEFAULT_UPDATE_INTERVAL | 30    |    NULL |
|         3 | DEFAULT_UPDATE_INTERVAL | 30    |    NULL |
+-----------+-------------------------+-------+---------+
3 rows in set (0.01 sec)

MariaDB [ttrss]> select u.id, p.value from ttrss_users u, ttrss_user_prefs p where p.owner_uid = u.id and p.pref_name = 'DEFAULT_UPDATE_INTERVAL';
Empty set (0.00 sec)

:face_with_raised_eyebrow:

blank database works as expected:

MariaDB [fox_test]> select u.id, p.value from ttrss_users u, ttrss_user_prefs p where p.owner_uid = u.id and p.pref_name = 'DEFAULT_UPDATE_INTERVAL';
+----+-------+
| id | value |
+----+-------+
|  1 | 30    |
+----+-------+
1 row in set (0.01 sec)  

again i must suggest reporting this to mariadb developers, with the dumps and everything.

I’ve just converted my installation to postgres :wink: maybe I find sometime the other day to report this.

that’s definitely a silver lining. i wish everyone else would do the same so i could finally drop mysql support.

Hi, recently dumped via mysqlworkbench , and got a fail dump, a “functions” (events) mysql table seems corrupted, sadly miss full message. Dumped again with mysqldump cli, only tables, erased ttrss database, recreated, imported from dump and everything work again.

Has anyone tested with JOIN notation rather than COMMA?

Eh, honestly I don’t find any difference in the kind of content of the two fields.

I can say that when I “patch” the query in the sources with the like in place of =, prefs are correctly visualized for the default profile.

I am not endorsing it as a solution. I realize it just doesn’t make sense.

Hi again, still getting:
Scheduled 0 feeds to update…

after dumping and importing database… :frowning: even after:
server:/…/ttrss # sudo -u wwwrun bash -c “php ./update.php --force-update”
[03:10:20/21528] Lock: update.lock
[03:10:20/21528] marking all feeds as needing update…

maybe try an older version of mariadb? this one is obviously broken.

you can also try migrating to a new database using OPML instead of importing the dump.

e:

also broken on the affected database.

MariaDB [ttrss]> select u.id, p.value from ttrss_users u LEFT JOIN ttrss_user_prefs p ON (p.owner_uid = u.id) WHERE p.pref_name = 'DEFAULT_UPDATE_INTERVAL';
Empty set (0.00 sec)

MariaDB [ttrss]> use fox_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [fox_test]> select u.id, p.value from ttrss_users u LEFT JOIN ttrss_user_prefs p ON (p.owner_uid = u.id) WHERE p.pref_name = 'DEFAULT_UPDATE_INTERVAL'
    -> ;
+----+-------+
| id | value |
+----+-------+
|  1 | 30    |
+----+-------+
1 row in set (0.18 sec)

i’ve imported @languitar database dump on a mariadb:10.3 container, everything seems to work properly. feeds update, above queries return results consistently, etc.

i’ve also experimented some more with a 10.4 container - if i drop and recreate the database, queries seem to work properly right after importing a dump, but if i try running update.php feeds are not selected properly and further queries start returning zero results.

Thanks for reply. Can I broke my databases after a mysql_upgrade if I revert from 10.4 to 10.3? Currently your query
select u.id, p.value from ttrss_users u LEFT JOIN ttrss_user_prefs p ON (p.owner_uid = u.id) WHERE p.pref_name = ‘DEFAULT_UPDATE_INTERVAL’;
throws
id, value
‘1’, ‘30’
I have several other databases, so really I won’t miss my current data.

i have no idea if you can go directly to an older database version (it might work but is it a good idea?) but i had no problems importing 10.4 dump on both 10.3 and even 10.1.

this stuff is inconsistent. if it runs the first time (right after data import) it returns data afterwards but a similar query somehow doesn’t.

I’ve had this issue too for a while, but after I did a bit of cleanup on my feeds, removing or marking as “do not update” those that are dead, it went away so I didn’t consider it relevant to report and didn’t link it to the upgrade from 10.3 to 10.4

ok, some new finding.

according to https://jira.mariadb.org/browse/MDEV-19820

when I prepend

set session optimizer_switch=‘rowid_filter=off’;

to the reference query that @fox gave, the result is correct, without needing to change the = to LIKE.

Moved all my databases to 10.3 and ttrss seems work fine now.

Posting just for making known that with the release of MariaDB 10.4.7 the issue related to empty prefs is gone.
It might also benefit the other issues referred in this thread.