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

With the quoted query I have empty result.

With this query:

SELECT DISTINCT
ttrss_user_prefs.pref_name,value,type_name,
ttrss_prefs_sections.order_id,
def_value,section_id,owner_uid
FROM ttrss_prefs,ttrss_prefs_types,ttrss_prefs_sections,ttrss_user_prefs
WHERE type_id = ttrss_prefs_types.id AND
(profile = NULL OR (NULL IS NULL AND profile IS NULL)) AND
section_id = ttrss_prefs_sections.id AND
ttrss_user_prefs.pref_name LIKE ttrss_prefs.pref_name AND
owner_uid = 2
ORDER BY ttrss_prefs_sections.order_id,pref_name;

I have result:

Don’t ask me why, and I don’t know if it’s a viable solution.
Still talking of MariaDB 10.4.6 of course.
As a matter of fact, I better go to bed.

Take care

it would probably be a good idea for someone to report this to mariadb developers, i think.

an update: thanks to @languitar i have a physical dump of a mariadb installation exhibiting this issue. no idea why it happens though. i think sending this to mariadb developers would be a good idea.

i did some tinkering with the database dump. i don’t claim to know much about mysql but it looks like a database metadata corruption of some kind, likely caused by an upgrade.

here’s the summary of what i did:

  1. started the container with supplied data dir exhibiting the issue without password checking (posting here for posterity in case someone else is wondering how to easily do this, i had to look it up)
docker run -p 127.0.0.1:4306:3306 -v /opt/mariadb-test/mysql:/var/lib/mysql --name mariadb-10.4 mariadb:10.4 --skip-grant-tables
  1. tried to run the accursed :face_with_raised_eyebrow: query using mysql shell from inside the container
MariaDB [ttrss]> SELECT DISTINCT
    -> ttrss_user_prefs.pref_name,value,type_name,                                                                          -> ttrss_prefs_sections.order_id,
    -> def_value,section_id,owner_uid                                                                                       -> FROM ttrss_prefs,ttrss_prefs_types,ttrss_prefs_sections,ttrss_user_prefs
    -> WHERE type_id = ttrss_prefs_types.id AND                                                                             ->         (profile = NULL OR (NULL IS NULL AND profile IS NULL)) AND
    ->         section_id = ttrss_prefs_sections.id AND                                                                     ->         ttrss_user_prefs.pref_name = ttrss_prefs.pref_name AND
    ->         owner_uid = 2                                                                                                -> ORDER BY ttrss_prefs_sections.order_id,pref_name;
Empty set (0.019 sec)                                               

yay, issue reproduced.

  1. i’ve noticed log messages like this on startup (also reported in PM):

2019-06-25 4:20:07 10 [Warning] InnoDB: Table mysql/innodb_index_stats has length mismatch in the column name table_name. Please run mysql_upgrade

tried running mysql_upgrade, it didn’t help. restarted the server just in case, ran it again, restarted container with disabled permissions, no change.

  1. the only other idea i had was dumping/restoring the database:
root@303a22fd7f7d:/# mysqldump ttrss |gzip > /var/lib/mysql/ttrss.sql.gz
root@303a22fd7f7d:/# zcat /var/lib/mysql/ttrss.sql.gz| mysql ttrss
  1. which seemed to do the needful and unfucked everything:
root@303a22fd7f7d:/# mysql ttrss
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.4.6-MariaDB-1:10.4.6+maria~bionic mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [ttrss]> SELECT DISTINCT
    -> ttrss_user_prefs.pref_name,value,type_name,
    -> ttrss_prefs_sections.order_id,
    -> def_value,section_id,owner_uid
    -> FROM ttrss_prefs,ttrss_prefs_types,ttrss_prefs_sections,ttrss_user_prefs
    -> WHERE type_id = ttrss_prefs_types.id AND
    ->         (profile = NULL OR (NULL IS NULL AND profile IS NULL)) AND
    ->         section_id = ttrss_prefs_sections.id AND
    ->         ttrss_user_prefs.pref_name = ttrss_prefs.pref_name AND
    ->         owner_uid = 2
    -> ORDER BY ttrss_prefs_sections.order_id,pref_name;
+-----------------------------+-------------------------------------------------------------------+-----------+----------+-------------------------------------------------------------------+------------+-----------+
| pref_name                   | value                                                             | type_name | order_id | def_value                                                         | section_id | owner_uid |
+-----------------------------+-------------------------------------------------------------------+-----------+----------+-------------------------------------------------------------------+------------+-----------+
| ALLOW_DUPLICATE_POSTS       | false                                                             | bool      |        0 | false                                                             |          1 |         2 |
| DEFAULT_UPDATE_INTERVAL     | 30                                                                | integer   |        0 | 30                                                                |          1 |         2 |
| ENABLE_API_ACCESS           | true                                                              | bool      |        0 | false                                                             |          1 |         2 |
| PURGE_OLD_DAYS              | 300                                                               | integer   |        0 | 60                                                                |          1 |         2 |
| USER_TIMEZONE               | Automatic                                                         | string    |        0 | Automatic 

unfortunately, after restarting the server the issue was back. not sure why. maybe recreating the database after dumping would help.

so far this looks like a mariadb problem after all, not really related to tt-rss, other than my code executing a query which exhibits this issue while the database is in this half-broken state.

i’d like to finish this post with stating, again, that i should have never implemented support for this half-assed abortion of a database server and i pity people who have to deal with it on a regular basis.

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.