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:
- 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
- tried to run the accursed 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.
- 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.
- 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
- 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.