feedbrowser (popularfeeds etc..) issue - resolved

Support requests, bug reports, etc. go here. Dedicated servers / VDS hosting only
virgo
Bear Rating Trainee
Bear Rating Trainee
Posts: 37
Joined: 12 Jun 2013, 22:14

Re: feedbrowser (popularfeeds etc..) issue

Postby virgo » 18 May 2015, 16:54

rainclouds wrote:#SELECT DISTINCT title, feed_url, site_url, COUNT(id) AS subscribers FROM ttrss_feeds
#WHERE (private IS TRUE OR auth_login != '' OR auth_pass != '' OR feed_url LIKE '%:%@%/%')
#GROUP BY title, feed_url, site_url
#ORDER BY subscribers DESC LIMIT 1000;

It is opposite of original. Even my version would be better.
Right now it would not return any feeds, that don't have either private set true or auth_login or auth_pass set or username:[email protected] type of url.
Your problem seems to be, that either all your feeds require username/password or they have private field set. I have no idea, when it is set.

User avatar
fox
^ me reading your posts ^
Posts: 6318
Joined: 27 Aug 2005, 22:53
Location: Saint-Petersburg, Russia
Contact:

Re: feedbrowser (popularfeeds etc..) issue

Postby fox » 18 May 2015, 17:02

why are you using this weird hash thing instead if code tag ffs

also yeah lol at the query

rainclouds
Bear Rating Trainee
Bear Rating Trainee
Posts: 12
Joined: 16 May 2015, 18:03

Re: feedbrowser (popularfeeds etc..) issue

Postby rainclouds » 18 May 2015, 17:58

@fox : sorry about the # using code quotes now i forgot.. (used to post in a forum that believe it or not is antique and does not have that )

Virgo i dont know what you are talking about. It returns the correct feeds. What SQL server are you using ? the query i used returns 11 unique feeds from the 28.
Your assumption is wrong else the query i suggested would not return 11 unique feeds. My feeds have NO auth, pwd or flag set (i can also give you a sqldump).
Bare a moment with me i just want to fix it, and if it happens on 4 different systems with different flavors out of the box then i can not be the only one unless nobody uses this feature. I know it seems you don't believe me (hence i can screenshot and provide you with the dumps)

your query returns absolutely nothing unless you change

Code: Select all

SELECT COUNT(id) = 0
into

Code: Select all

SELECT COUNT(id) != 0
and keep your entire query intact. I can give you screenshots (query + results ) in an email ,
In summary : your query no changes = no result on my machines (fresh untouched install)
your query with SELECT COUNT(id) != 0 results in 11 unique feeds (from the 28)
my little query (as a test no need to shame me for it it was just a test) = 11 unique feed as result.
I can only imagine somehow different sql flavors (postgress/mysql/mariadb are the cause ? ) or a setting wrong (doubt it) it is easy to run the query and show you the screenshots, as it would be nice to find out whats going on and why you have totally different results.

Second question why do you want to list 2 the same feeds one with auth and one without (if i understood you correctly as a user + password feed shouldn't be shared anyways normally, just my idea but i see the logic). Is there a reason for the second where clauses i left out?

True lol at the query as i said not that good in it however it does return the 11 unique feeds that are not private and not using a password which was i thought the point.

So Virgo maybe we can try to figure out what is happening as the original query in rssfunc form git does not work on my system yet the edited (yours with edit or my silly test) do return the unique feeds. i'll buy you good beer if you happen to travel to Belgium.
last dumb question (trying to find the differences here) are you using the latest git version ?

Thanks all,

systems tested : Centos 6 - 7 - fedora server 21 - 22 (cant help it). latest mysql server and latest mariadb server on all flavors and combinations : the original query fails. (sorry am not good in postgres to take well care of it).
always only used latest git version in clean directory.
@ virgo :Can i have your config virgo on which you did the query test ? (mine and yours) i would like to build exactly your config also as its intriguing to me and i could learn tt-rss well, always usefull as its a great system in my opinion.

virgo
Bear Rating Trainee
Bear Rating Trainee
Posts: 37
Joined: 12 Jun 2013, 22:14

Re: feedbrowser (popularfeeds etc..) issue

Postby virgo » 18 May 2015, 18:18

Just run following query on yout database and check the values of private, auth_login, auth_pass and feed_url fields in result.

Code: Select all

SELECT * FROM ttrss_feeds
WHERE (private IS TRUE OR auth_login != '' OR auth_pass != '' OR feed_url LIKE '%:%@%/%')

Second question why do you want to list 2 the same feeds one with auth and one without (if i understood you correctly as a user + password feed shouldn't be shared anyways normally, just my idea but i see the logic). Is there a reason for the second where clauses i left out?

It's not what I want. Let say that in ttrss_feeds table there are three records with same feed_url value (let say http://example.com/rss/), but one has private set to true and others have set to false.
Original version would not list that feed, because at least on record was filtered out.
With my suggested version, where condition is

Code: Select all

WHERE NOT (private IS TRUE OR auth_login != '' OR auth_pass != '' OR feed_url LIKE '%:%@%/%')

it would show that url with subscriber count 1.
Your version would also show with subscriber count 2.

virgo
Bear Rating Trainee
Bear Rating Trainee
Posts: 37
Joined: 12 Jun 2013, 22:14

Re: feedbrowser (popularfeeds etc..) issue

Postby virgo » 18 May 2015, 18:28

And that "private" is "Hide from popular feeds" checkbox.

rainclouds
Bear Rating Trainee
Bear Rating Trainee
Posts: 12
Joined: 16 May 2015, 18:03

Re: feedbrowser (popularfeeds etc..) issue

Postby rainclouds » 18 May 2015, 19:33

hi,

I just ran

Code: Select all

SELECT * FROM ttrss_feeds
WHERE (private IS TRUE OR auth_login != '' OR auth_pass != '' OR feed_url LIKE '%:%@%/%')

the result is 28 feeds (non unique). (i know because i crefully entered all feeds and carefgully verified that none are private, auith or pwd (also dumped the database).

We do want to list all unique url's i am not subscribed too (the tt-rss code actually takes care of that it will only show the feeds i am NOT subscribed to (but i do wish to see all the unique feeds whether 1 2 3 or more people are subscribed to it but not me. (so the query needs to return all unique feeds and then tt-rss displays only those i dont have (which i really does well !).

the query as is (from rssfunc.php returns 0 feeds which is not what we want. (the other options i mentioned dropping the nested where (not good i know) or only change the select count id = 0 into != 0 and leaving the entire old query intact works also. (tt-rss actually then verifies which i am subscribed to and display only those i have not yet subscribed to)

This code from rssfunc.php results for me into 0 feeds.

Code: Select all

 SELECT feed_url, site_url, title, COUNT(id) AS subscribers
           FROM ttrss_feeds WHERE (SELECT COUNT(id) = 0 FROM ttrss_feeds AS tf
            WHERE tf.feed_url = ttrss_feeds.feed_url
            AND (private IS true OR auth_login != '' OR auth_pass != '' OR feed_url LIKE '%:%@%/%'))
            GROUP BY feed_url, site_url, title ORDER BY subscribers DESC LIMIT 1000 


if i change

Code: Select all

(SELECT COUNT(id) = 0
to

Code: Select all

(SELECT COUNT(id) = !0
i get the correct desired results.

So what is the best solution now and why this oddity ? Does it work for you without change ?

virgo
Bear Rating Trainee
Bear Rating Trainee
Posts: 37
Joined: 12 Jun 2013, 22:14

Re: feedbrowser (popularfeeds etc..) issue

Postby virgo » 18 May 2015, 20:04

rainclouds wrote:hi,

I just ran

Code: Select all

SELECT * FROM ttrss_feeds
WHERE (private IS TRUE OR auth_login != '' OR auth_pass != '' OR feed_url LIKE '%:%@%/%')

the result is 28 feeds (non unique). (i know because i crefully entered all feeds and carefgully verified that none are private, auith or pwd (also dumped the database).

Is either auth_login or auth_pass NULL? Because that would expain the problem (field being empty, not having value of empty string).

Because that query only returns records when that record has either:
private equal true
or
auth_login not empty string
or
auth_pass not empty string
or
feed_url is something like username:[email protected]/rss

At least one of those conditions must be true.

virgo
Bear Rating Trainee
Bear Rating Trainee
Posts: 37
Joined: 12 Jun 2013, 22:14

Re: feedbrowser (popularfeeds etc..) issue

Postby virgo » 18 May 2015, 20:18

virgo wrote:Is either auth_login or auth_pass NULL? Because that would expain the problem (field being empty, not having value of empty string).

But that cannot be true, because those fields are NOT NULL. So only way that query can give any results is, if you are not telling truth about values of those 4 fields, that are used in where condition.

vidar
Bear Rating Trainee
Bear Rating Trainee
Posts: 13
Joined: 06 May 2013, 00:19

Re: feedbrowser (popularfeeds etc..) issue

Postby vidar » 18 May 2015, 20:38

I suggest this rewrite, but only for performance and clarity. The original query is already giving correct results here, I don't know why the OP is not getting the list of more feeds.

Code: Select all

SELECT feed_url, site_url, title, COUNT(id) AS subscribers
FROM ttrss_feeds
WHERE feed_url NOT IN (
    SELECT distinct feed_url FROM ttrss_feeds
    WHERE private IS true OR auth_login != '' OR auth_pass != '' OR feed_url LIKE '%:%@%/%'
)
GROUP BY feed_url, site_url, title ORDER BY subscribers DESC LIMIT 1000;

It should perform better on installations with many users and many shared feeds, and it already performs better on my server with only three users and not many shared feeds. We are talking milliseconds, though. And FWIW I'm on PostgreSQL.

rainclouds
Bear Rating Trainee
Bear Rating Trainee
Posts: 12
Joined: 16 May 2015, 18:03

Re: feedbrowser (popularfeeds etc..) issue

Postby rainclouds » 18 May 2015, 21:29

Hi,
BY the way the fact you mentioned postgres was extremely important i did some research and :

I tried that query just right now and causes 2 issues one it returns nothing however if you replace the '' with NULL the query works. This is apparently due to a difference between postgress, oracle and mysql.
Use default null. In MySQL (or MariaDB), null is very different from the empty string (""). The empty string specifically means that the value was set to be empty; null means that the value was not set, or was set to null. Different meanings, you see.

The database actually contains 0 for not private and not '' hence the reason the queries didn't work (mysql/mariadb).

Your query works indeed perfect on postgress but not on mysql. So i think we got close. (does using NULL matter in postgress or does it also still work then ?) changing

Code: Select all

auth_login != '' OR auth_pass != ''
into

Code: Select all

auth_login != NULL OR auth_pass != NULL


second issue : when i have 3 users each having the feed flowerstoday but one set for whatever reason the private flag the feed never gets displayed in popular feeds even while 2 other users have it as not private (am sure that's not what we want so we need more logic to make this happen)

Even the old query works if you correct the '' to NULL but still contains the error of not showing the feed even while others did NOT mark it private
(and thus invalidating the entire effort). (i was missing one feed so i started to manually verify what happened.)

interesting postgres / mysql story i guess ill have to learn postgres.

regards

ps : i am always telling (just to answer your question) the truth i write what i see hence i offered or screenshots or tabledump, the fields are populated with a 0 therefor '' will never in mysql result in anything. instead of using '' in mysql one has to use NULL (or a 0 ) (see the quote from a manual) . It seems we ran into a database flavor issue. (good to know).

User avatar
fox
^ me reading your posts ^
Posts: 6318
Joined: 27 Aug 2005, 22:53
Location: Saint-Petersburg, Russia
Contact:

Re: feedbrowser (popularfeeds etc..) issue

Postby fox » 18 May 2015, 21:40

feedbrowser works fine for me on mysql op probably seriously fucked something up

also both of those fields are not null, i'm sort of leaning towards a pebkac situation here tbh

virgo
Bear Rating Trainee
Bear Rating Trainee
Posts: 37
Joined: 12 Jun 2013, 22:14

Re: feedbrowser (popularfeeds etc..) issue

Postby virgo » 18 May 2015, 21:50

rainclouds wrote:I tried that query just right now and causes 2 issues one it returns nothing however if you replace the '' with NULL the query works. This is apparently due to a difference between postgress, oracle and mysql.

No, it is not. Those fields are auth_login and auth_pass are varchar fields with NOT NULL constraint and default value of empty string in mysql. So, if they are NULL (empty value), then your database is seriously screwed up. And in that part there is no difference with postgresql (I actually run those queries on my old mysql database).
Now the private is BOOL field, which in mysql is actually internally integer, where 0 is false. So that is OK, if you see it as 0.

virgo
Bear Rating Trainee
Bear Rating Trainee
Posts: 37
Joined: 12 Jun 2013, 22:14

Re: feedbrowser (popularfeeds etc..) issue

Postby virgo » 18 May 2015, 21:57

vidar wrote:I suggest this rewrite, but only for performance and clarity. The original query is already giving correct results here, I don't know why the OP is not getting the list of more feeds.

Code: Select all

SELECT feed_url, site_url, title, COUNT(id) AS subscribers
FROM ttrss_feeds
WHERE feed_url NOT IN (
    SELECT distinct feed_url FROM ttrss_feeds
    WHERE private IS true OR auth_login != '' OR auth_pass != '' OR feed_url LIKE '%:%@%/%'
)
GROUP BY feed_url, site_url, title ORDER BY subscribers DESC LIMIT 1000;



My question is, is this subselect even necessary? What would happen, if private or password protected feeds were to be excluded in main query? Because they would only appear in result, if someone has those feeds as non-private and/or not password protected. And it would make query much clearer.

rainclouds
Bear Rating Trainee
Bear Rating Trainee
Posts: 12
Joined: 16 May 2015, 18:03

Re: feedbrowser (popularfeeds etc..) issue - resolved

Postby rainclouds » 18 May 2015, 22:34

my opinion would be that it would be the best way (no subselect). And we want them to appear if someone has that feed but without private/no password protected. (As right now i have 1 user with one of those feeds set private but 2 users not and the feed doesn't appear but if you would kill the subselect it would work.

And @fox i will as you suggest on the wiki even make a build that you recommended there (debian/postgres) so its easier to support.

(will be some beers for virgo)

User avatar
fox
^ me reading your posts ^
Posts: 6318
Joined: 27 Aug 2005, 22:53
Location: Saint-Petersburg, Russia
Contact:

Re: feedbrowser (popularfeeds etc..) issue - resolved

Postby fox » 18 May 2015, 23:05

i'm not going to change anything because of some guy with possibly malfunctioning unknown software posting epic walls of text on the forum


Return to “Support”

Who is online

Users browsing this forum: No registered users and 8 guests