Recently Active (Team)

[happygeek]

The recently active list for admins and mods (team) appears broken, at least regarding myself. Currently it says I was last active 4 days ago, yet I've been on and active every day for the last 4 days (including posting and admin duties - the infractions list even shows my last infraction given 2 days ago for example).

Odd...

[happygeek]

And, of course, it now seems to have caught up with itself and has me down as being active a few minutes ago. Looks like admin/moderating activity didn't do anything, nor did replying to posts, but creating a new thread did.

Still odd... :-)

[Dani]

We discovered there has been a problem with cron jobs.

[happygeek]

Still is, by the looks of things. Apparently I've not been active for 2 days :)

[Dani]

I think this has something to do with Dazah. I'll fix soon.

[happygeek]

If it helps, at the start of yesterday it was saying I was last active 2 days ago - which then caught up and reported things correctly - and this morning it's now saying 3 days ago :)

[Dani]

I get that the cron jobs are most likely buggy, but I can't seem to figure out where the bug is.

There is a members table in the database, that we can assume has columns id and last_visit_timestamp. There is an articles_read table that records the articles read by each member, with columns member_id, article_id and timestamp. There is a forums_read table that records forums explicitely marked as read, with columns member_id, forum_id, and timestamp.

What am I doing wrong in this cron job that runs every couple of hours??

UPDATE members
SET members.last_visit_timestamp =
GREATEST(
    COALESCE((
        SELECT MAX(articles_read.timestamp)
        FROM articles_read
        WHERE articles_read.member_id = members.id
    ), members.last_visit_timestamp),
    COALESCE((
        SELECT MAX(forums_read.timestamp)
        FROM forums_read
        WHERE forums_read.member_id = members.id
    ), members.last_visit_timestamp)
)

[Dani]

bump

No one, eh? ;)

[invisal]

Would it be clearer to use this SQL instead?

UPDATE members INNER JOIN articles_read
ON members.member_id = articles_read.member_id AND
   members.last_visit_timestamp < articles_read.timestamp
SET members.last_visit_timestamp = articles_read.timestamp;

UPDATE members INNER JOIN forums_read
ON members.member_id = forums_read.member_id AND
   members.last_visit_timestamp < forums_read.timestamp
SET members.last_visit_timestamp = forums_read.timestamp;

[happygeek]

Still broken. According to https://www.daniweb.com/stats/moderators I've not been active for a week.

https://www.daniweb.com/infractions/index/0 would seem to contradict that :)

[Dani]

Yeah, I'm sorry ... I never had a chance to check out Invisal's SQL query. I will shortly.