Unread Comment Issue ← FREE KRAUT!

Unread Comment Issue 73

19 Sep 2012  |  nevermoor  |Kraut on Kraut

This time I REALLY think I have things back to normal. Let me know if they aren’t.

If any of you get an error message at the top of the screen, please post it in here (or email to me)

73 thoughts on “Unread Comment Issue”

  1. DFA Sep 19,2012 1:05 am

    WordPress database error: [The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay]
    SELECT ID, post_date, lastvisit, is_done, comment_count, post_title, count(ID) as newcomments from wp_posts LEFT OUTER JOIN (select * from wp_unreadcomments WHERE userid = 24) as filterTable ON ID = postid LEFT OUTER JOIN (select comment_date, comment_post_ID from wp_comments WHERE wp_comments.comment_date > '2012-08-10 14:22:23' AND user_id <> 24) as filterTableTwo ON filterTableTwo.comment_post_ID = wp_posts.ID WHERE post_type = 'post' and post_status = 'publish' and (is_done IS NULL OR is_done = 0) AND filterTableTwo.comment_date > COALESCE(lastvisit,'2012-08-10 14:22:23') GROUP BY IDUNIONSELECT DISTINCT ID, post_date, lastvisit, is_done, comment_count, post_title, 0 as newcomments from wp_posts LEFT OUTER JOIN (select * from wp_unreadcomments WHERE userid = 24) as filterTable ON ID = postid WHERE lastvisit IS NULL and post_date > '2012-08-10 14:22:23' AND post_type = 'post' and post_status = 'publish'

    In play, run(s)! Talk dirty to me gamecast, talk dirty. - Nevermoor
  2. ptbnl Sep 19,2012 1:20 am

    WordPress database error: [The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay]
    SELECT ID, post_date, lastvisit, is_done, comment_count, post_title, count(ID) as newcomments from wp_posts LEFT OUTER JOIN (select * from wp_unreadcomments WHERE userid = 17) as filterTable ON ID = postid LEFT OUTER JOIN (select comment_date, comment_post_ID from wp_comments WHERE wp_comments.comment_date > ‘2012-06-13 13:41:27’ AND user_id <> 17) as filterTableTwo ON filterTableTwo.comment_post_ID = wp_posts.ID WHERE post_type = ‘post’ and post_status = ‘publish’ and (is_done IS NULL OR is_done = 0) AND filterTableTwo.comment_date > COALESCE(lastvisit,’2012-06-13 13:41:27′) GROUP BY ID

    If this is His will, He's a son of a bitch.
  3. ptbnl Sep 19,2012 1:22 am

    WordPress database error: [The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay]
    SELECT ID, post_date, lastvisit, is_done, comment_count, post_title, count(ID) as newcomments from wp_posts LEFT OUTER JOIN (select * from wp_unreadcomments WHERE userid = 17) as filterTable ON ID = postid LEFT OUTER JOIN (select comment_date, comment_post_ID from wp_comments WHERE wp_comments.comment_date > ‘2012-06-13 13:41:27’ AND user_id <> 17) as filterTableTwo ON filterTableTwo.comment_post_ID = wp_posts.ID WHERE post_type = ‘post’ and post_status = ‘publish’ and (is_done IS NULL OR is_done = 0) AND filterTableTwo.comment_date > COALESCE(lastvisit,’2012-06-13 13:41:27′) GROUP BY ID UNION SELECT DISTINCT ID, post_date, lastvisit, is_done, comment_count, post_title, 0 as newcomments from wp_posts LEFT OUTER JOIN (select * from wp_unreadcomments WHERE userid = 17) as filterTable ON ID = postid WHERE lastvisit IS NULL and post_date > ‘2012-06-13 13:41:27’ AND post_type = ‘post’ and post_status = ‘publish’and comment_count=0

    If this is His will, He's a son of a bitch.
  4. nevermoor Sep 19,2012 1:29 am

    Can you guys try again? I think I’ve finally got it.

    "There's never enough time to do all the nothing you want"
  5. ptbnl Sep 19,2012 1:34 am

    Still getting

    WordPress database error: [The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay]
    SELECT ID, post_date, lastvisit, is_done, comment_count, post_title, count(ID) as newcomments from wp_posts LEFT OUTER JOIN (select * from wp_unreadcomments WHERE userid = 17) as filterTable ON ID = postid LEFT OUTER JOIN (select comment_date, comment_post_ID from wp_comments WHERE wp_comments.comment_date > ‘2012-06-13 13:41:27’ AND user_id <> 17) as filterTableTwo ON filterTableTwo.comment_post_ID = wp_posts.ID AND filterTableTwo.comment_date > COALESCE(lastvisit,’2012-06-13 13:41:27′) WHERE post_type = ‘post’ and post_status = ‘publish’ and (is_done IS NULL OR is_done = 0) AND filterTableTwo.comment_date > COALESCE(lastvisit,’2012-06-13 13:41:27′) GROUP BY ID UNION SELECT DISTINCT ID, post_date, lastvisit, is_done, comment_count, post_title, 0 as newcomments from wp_posts LEFT OUTER JOIN (select * from wp_unreadcomments WHERE userid = 17) as filterTable ON ID = postid WHERE lastvisit IS NULL and post_date > ‘2012-06-13 13:41:27’ AND post_type = ‘post’ and post_status = ‘publish’and comment_count=0

    If this is His will, He's a son of a bitch.
  6. nevermoor Sep 19,2012 2:15 am

    Temporary fix: I just allowed the big select.

    If anyone knows anyone with more MySQL talent than I have, I’d love to talk about why this select is so big.

    "There's never enough time to do all the nothing you want"
  7. MikeV Sep 19,2012 7:35 am

    I tried calling but didn’t get an answer.

    And I have to say: mikev is one of my favorite people on here -slusser.

    Thanks, and go As.

  8. MikeV Sep 19,2012 7:53 am

    K, unread comments seem to be working inside threads again, but not on the FK main page just like yesterday.

    And I have to say: mikev is one of my favorite people on here -slusser.

    Thanks, and go As.

    • nevermoor Sep 19,2012 9:24 am || Up

      But no SQL error reported?

      "There's never enough time to do all the nothing you want"
      • MikeV Sep 19,2012 9:26 am || Up

        Nope, no errors whatsoever.

        And I have to say: mikev is one of my favorite people on here -slusser.

        Thanks, and go As.

        • nevermoor Sep 19,2012 10:08 am || Up

          And when you say not working, you mean you’re just getting “…loading…”? Or it is misreporting results?

          "There's never enough time to do all the nothing you want"
          • MikeV Sep 19,2012 10:18 am || Up

            Just getting …loading…

            And I have to say: mikev is one of my favorite people on here -slusser.

            Thanks, and go As.

            • nevermoor Sep 19,2012 10:33 am || Up

              That is so strange. There shouldn’t be any difference whatsoever between the front page and interior pages.

              "There's never enough time to do all the nothing you want"
  9. ozzman99 Sep 19,2012 8:01 am

    Everything is working properly for me again, but very slowly.

    • FreeSeatUpgrade Sep 19,2012 8:47 am || Up

      Same here, full functionality, kinda slow. Both Mac OSX with Firefox and Windows 7 with IE.

      "Kraut will get you through times of no money better than money will get you through times of no kraut."
      • nevermoor Sep 19,2012 9:25 am || Up

        One temporary fix on your side would be to “mark all read” at some point.

        "There's never enough time to do all the nothing you want"
        • gonealookin Sep 19,2012 9:28 am || Up

          I wonder if the ozzs and FSUs ever log out of FK, delete their FK cookie, clear their browser history, and log back in and start over. I’m a habitual log-outer and browser clearer and I’m not seeing the issues they’re having.

          What I discovered Blew. My. Mind. -- Pat Boone
          • 5Aces Sep 19,2012 9:34 am || Up

            I was having all the issues last night and I do log out, etc on at least a somewhat regular basis. But, not that you say it, I realize I never bothered trying that last night.

            Camelot sure fell apart, didn't it? -Steve McCatty
          • ozzman99 Sep 19,2012 9:48 am || Up

            I logged out and cleared my browser history, but I didn’t delete cookies.

          • nevermoor Sep 19,2012 10:10 am || Up

            I don’t think that’s it. The slowness is because I’ve gotten us down to the one query, but that query is inefficient. I’m seeking help from devshed, but we’ll have to see how it goes.

            One way I filter the results is based upon the last time all are marked unread, so it should partially de-shit-ify the current query. It wouldn’t matter much to the ideal query I’m assuming exists.

            "There's never enough time to do all the nothing you want"
          • andeux Sep 19,2012 10:21 am || Up

            Cookies and browser history are client-side and this is all server-side code.

            TINSTAAFK
  10. SPWC Sep 19,2012 10:40 am

    Does getting to the end of unread comments by actually reading them have the same effect as marking them all as read?

    \"Weren\'t you already aware the Kay is already writing everyone\'s story? We\'re all just characters who believe we are real. Things make more sense now, don\'t they. Be honest.\"- DMOAS
    • andeux Sep 19,2012 10:48 am || Up

      Based on nm’s comments above, I don’t think so. It seems there is a separate variable for each user keeping track of the last date they marked all as read. One horrible hack would be to update that value whenever the regular query shows no unread posts or comments.

      TINSTAAFK
      • nevermoor Sep 19,2012 10:51 am || Up

        Correct.

        And that’s true, I could do that. It’s actually a pretty good idea.

        "There's never enough time to do all the nothing you want"
      • nevermoor Sep 19,2012 3:50 pm || Up

        This is implemented now. I’m going to keep an eye on the table, but it’s not really a horrible hack, and I can use it at the same time to delete some unreadcomments rows. Which should significantly reduce that table (though make things slow as people transition into it. Especially the first load for FKers who never marked all read.

        "There's never enough time to do all the nothing you want"
        • MikeV Sep 19,2012 4:12 pm || Up

          Appears like unread comments are showing up on the main page now.

          And I have to say: mikev is one of my favorite people on here -slusser.

          Thanks, and go As.

  11. Glorious Mundy Sep 19,2012 4:08 pm

    I’m getting database connection errors.

    • dmoas Sep 19,2012 4:11 pm || Up

      Errors galore. And the unread deal is going whackadoodle and pulling a lot of crap as unread.

  12. dmoas Sep 19,2012 4:14 pm

    WordPress database error: [Can’t find file: ‘./freekrau_fkdb/wp_unreadcomments.frm’ (errno: 13)]
    SELECT ID, post_date, lastvisit, is_done, comment_count, post_title, count(ID) as newcomments from wp_posts LEFT OUTER JOIN (select * from wp_unreadcomments WHERE userid = 31) as filterTable ON ID = postid LEFT OUTER JOIN (select comment_date, comment_post_ID from wp_comments WHERE wp_comments.comment_date > ‘2009-05-13 05:59:28’ AND user_id <> 31) as filterTableTwo ON filterTableTwo.comment_post_ID = wp_posts.ID AND filterTableTwo.comment_date > COALESCE(lastvisit,’2009-05-13 05:59:28′) WHERE post_type = ‘post’ and post_status = ‘publish’ and (is_done IS NULL OR is_done = 0) AND filterTableTwo.comment_date > COALESCE(lastvisit,’2009-05-13 05:59:28′) GROUP BY ID UNION SELECT DISTINCT ID, post_date, lastvisit, is_done, comment_count, post_title, 0 as newcomments from wp_posts LEFT OUTER JOIN (select * from wp_unreadcomments WHERE userid = 31) as filterTable ON ID = postid WHERE lastvisit IS NULL and post_date > ‘2009-05-13 05:59:28’ AND post_type = ‘post’ and post_status = ‘publish’and comment_count=0
    insert into wp_unreadcomments (userid, postid, lastvisit) values (31, -1, ‘2012-09-19 16:14:01’) on duplicate key update lastvisit = ‘2012-09-19 16:14:01’
    DELETE FROM wp_unreadcomments WHERE userid = 31 AND lastvisit < '2012-09-19 16:14:01' There are no unread comments, only unwritten ones insert into wp_unreadcomments (userid, postid, lastvisit) values (31, 2254, '2012-09-19 16:14:01') on duplicate key update lastvisit = '2012-09-19 16:14:01'

    • Jennifer Sep 19,2012 4:19 pm || Up

      WordPress database error: [Lost connection to MySQL server during query]
      SET SQL_BIG_SELECTS=1;
      WordPress database error: [MySQL server has gone away]
      SELECT ID, post_date, lastvisit, is_done, comment_count, post_title, count(ID) as newcomments from wp_posts LEFT OUTER JOIN (select * from wp_unreadcomments WHERE userid = 15) as filterTable ON ID = postid LEFT OUTER JOIN (select comment_date, comment_post_ID from wp_comments WHERE wp_comments.comment_date > ‘2009-05-13 01:31:52’ AND user_id <> 15) as filterTableTwo ON filterTableTwo.comment_post_ID = wp_posts.ID AND filterTableTwo.comment_date > COALESCE(lastvisit,’2009-05-13 01:31:52′) WHERE post_type = ‘post’ and post_status = ‘publish’ and (is_done IS NULL OR is_done = 0) AND filterTableTwo.comment_date > COALESCE(lastvisit,’2009-05-13 01:31:52′) GROUP BY IDUNIONSELECT DISTINCT ID, post_date, lastvisit, is_done, comment_count, post_title, 0 as newcomments from wp_posts LEFT OUTER JOIN (select * from wp_unreadcomments WHERE userid = 15) as filterTable ON ID = postid WHERE lastvisit IS NULL and post_date > ‘2009-05-13 01:31:52’ AND post_type = ‘post’ and post_status = ‘publish’and comment_count=0

      WordPress database error: [MySQL server has gone away]
      SET SQL_BIG_SELECTS=0;
      [Can’t find file: ‘./freekrau_fkdb/wp_unreadcomments.frm’ (errno: 13)]
      SELECT ID, post_date, lastvisit, is_done, comment_count, post_title, count(ID) as newcomments from wp_posts LEFT OUTER JOIN (select * from wp_unreadcomments WHERE userid = 15) as filterTable ON ID = postid LEFT OUTER JOIN (select comment_date, comment_post_ID from wp_comments WHERE wp_comments.comment_date > ‘2009-05-13 01:31:52’ AND user_id <> 15) as filterTableTwo ON filterTableTwo.comment_post_ID = wp_posts.ID AND filterTableTwo.comment_date > COALESCE(lastvisit,’2009-05-13 01:31:52′) WHERE post_type = ‘post’ and post_status = ‘publish’ and (is_done IS NULL OR is_done = 0) AND filterTableTwo.comment_date > COALESCE(lastvisit,’2009-05-13 01:31:52′) GROUP BY ID UNION SELECT DISTINCT ID, post_date, lastvisit, is_done, comment_count, post_title, 0 as newcomments from wp_posts LEFT OUTER JOIN (select * from wp_unreadcomments WHERE userid = 15) as filterTable ON ID = postid WHERE lastvisit IS NULL and post_date > ‘2009-05-13 01:31:52’ AND post_type = ‘post’ and post_status = ‘publish’and comment_count=0
      insert into wp_unreadcomments (userid, postid, lastvisit) values (15, -1, ‘2012-09-19 16:13:32’) on duplicate key update lastvisit = ‘2012-09-19 16:13:32’
      DELETE FROM wp_unreadcomments WHERE userid = 15 AND lastvisit < '2012-09-19 16:13:32' • There are no unread comments, only unwritten ones insert into wp_unreadcomments (userid, postid, lastvisit) values (15, 2254, '2012-09-19 16:13:32') on duplicate key update lastvisit = '2012-09-19 16:13:32' I can't see comments in the threads, just in the dashboard.

      • dmoas Sep 19,2012 4:24 pm || Up

        You just *HAAAAAD* to one up me, didn’t you.

        • Jennifer Sep 19,2012 4:28 pm || Up

          Yes. It’s my job to keep you in check.

      • nevermoor Sep 19,2012 4:55 pm || Up

        Thanks! These were helpful.

        The lesson, as always: I’m an idiot, and you can’t do performance testing on a sandbox system only you use.

        "There's never enough time to do all the nothing you want"
  13. nevermoor Sep 19,2012 4:55 pm

    Just to close the loop: All my fault. I effect up the database interaction and let loose the mother of all queries. Which asploded the site’s functionality.

    It should be back now, minus the unread comment tracking data.

    "There's never enough time to do all the nothing you want"
    • gonealookin Sep 19,2012 4:57 pm || Up

      Thanks. I would have been on a nonstop bender for the last three days.

      What I discovered Blew. My. Mind. -- Pat Boone
  14. SPWC Sep 20,2012 10:19 am

    Got this one a couple of minutes ago… probably nothing, tho

    ERROR

    The requested URL could not be retrieved

    While trying to retrieve the URL: http://freekraut.net/?

    The following error was encountered:

    Connection to 31.22.4.74 Failed
    The system returned:

    (111) Connection refused
    The remote host or network may be down. Please try the request again.

    Your cache administrator is webmaster.
    Generated Thu, 20 Sep 2012 17:18:03 GMT by sv21.byethost21.org (squid/2.7.STABLE9)

    \"Weren\'t you already aware the Kay is already writing everyone\'s story? We\'re all just characters who believe we are real. Things make more sense now, don\'t they. Be honest.\"- DMOAS
    • nevermoor Sep 20,2012 11:14 am || Up

      Odd.

      Probably just a little server hiccup.

      "There's never enough time to do all the nothing you want"
  15. nevermoor Sep 20,2012 12:54 pm

    Ok. Major upgrade to the unread comment code just went live, so as always let me know if you have any problem. Unread comments query should now touch hundreds of database rows instead of millions, but we will have keep an eye on the code to make sure it is accurate.

    "There's never enough time to do all the nothing you want"
    • JamesV Sep 20,2012 1:37 pm || Up

      I ran into something just now where the widget just showed “loading” but when I clicked into a thread it said I had to be logged in to comment. I logged in and the widget seems to be showing the unread comments now, but when I opened this thread the Z-browsing didn’t register.

      • nevermoor Sep 20,2012 1:44 pm || Up

        Hmm. Sounds like something got crossed up where it both marked the comment as read and told you you weren’t logged in.

        Not encouraging, but hopefully not something that is happening to other people.

        "There's never enough time to do all the nothing you want"
        • JamesV Sep 20,2012 1:52 pm || Up

          I’m in Firefox, by the way. This also took a while to load the page and I’m still not getting the Z-function working in here.

          Also, trying to submit this comment timed out on me after over a minute with the following error the first time:

          The website you are trying to access is currently unavailable. Please try again at a later time.
          Click the button below to browse the site from cache. (Cookies and Javascript must be enabled.)

        • JamesV Sep 20,2012 1:53 pm || Up

          Also just went into the Events from 10/2 post and the Z-function is not working for me there either. I stayed logged in.

        • AV Sep 20,2012 2:28 pm || Up

          i got kicked off mid writing a comment earlier, round 11am. i hit post and it said i had to log back in.

          *i’m* AV. alex vause. put this loon in psych before she hurts someone.
          • nevermoor Sep 20,2012 2:31 pm || Up

            Yeah. I changed to (and back from) http://www.frekraut.com. That effed up your cookies.

            "There's never enough time to do all the nothing you want"
            • AV Sep 20,2012 2:39 pm || Up

              [spits out cookies.]

              those aren’t chocolate chips, are they?

              *i’m* AV. alex vause. put this loon in psych before she hurts someone.
              • nevermoor Sep 20,2012 2:42 pm || Up

                "There's never enough time to do all the nothing you want"
    • nevermoor Sep 20,2012 2:47 pm || Up

      Found the bug in the upgrade code and just rolled it out. Hopefully this time it will be faster, more stable, AND functional.

      "There's never enough time to do all the nothing you want"
      • andeux Sep 20,2012 2:48 pm || Up

        FreeSiteUpgrade

        TINSTAAFK
        • nevermoor Sep 20,2012 2:50 pm || Up

          In fairness to me (and for the MySQL curious) the issue is that MySQL doesn’t handle derived tables well AT ALL. Instead it discards their indexes, and makes you look at the whole damn thing every time.

          If, however, you create a view…

          "There's never enough time to do all the nothing you want"
          • vignette17 Sep 20,2012 6:23 pm || Up

            I just want to make sure that the mySQL curious didn’t let their curiosity break FK.

            • nevermoor Sep 20,2012 6:26 pm || Up

              Nope. Not at all. The problem is that a working query just wasn’t fast enough, and I haven’t yet figured out how to pare the whole thing down.

              "There's never enough time to do all the nothing you want"
  16. JamesV Sep 20,2012 2:11 pm

    Josh (emperor) tells me he’s having trouble logging in because the e-mail feature to send a password reset is suspended. Nevermoor, he’d like to know if you can send him a new one.

    • nevermoor Sep 20,2012 2:16 pm || Up

      Sent. Thanks.

      "There's never enough time to do all the nothing you want"
      • JamesV Sep 20,2012 2:18 pm || Up

        Cool. And the Z thing worked for me this time.

    • FreeSeatUpgrade Sep 20,2012 2:33 pm || Up

      Missed opportunity: you could have told him he had to email you first.

      "Kraut will get you through times of no money better than money will get you through times of no kraut."
      • nevermoor Sep 20,2012 2:42 pm || Up

        He knows what he did.

        "There's never enough time to do all the nothing you want"
        • JamesV Sep 20,2012 2:45 pm || Up

          This is true.

          • doctorK Sep 20,2012 6:08 pm || Up

            Best regards, and go A’s!

            • whimsical witch Sep 20,2012 6:10 pm || Up

              heh.

            • JamesV Sep 20,2012 7:13 pm || Up

              YOU ARE BANNED FROM FREE KRAUT.

              You can browse the blog, but you can’t participate.

              • DFA Sep 20,2012 8:36 pm || Up

                We should have showed up to the A’s blog day with signs and anytime tonces tried to go anywhere or do anything jumped up with a sign that said

                YOU ARE BANNED FROM REAL LIFE

                You can browse, but you can’t participate.

                In play, run(s)! Talk dirty to me gamecast, talk dirty. - Nevermoor
                • JamesV Sep 20,2012 9:14 pm || Up

                  That would have been awesome.

                • nevermoor Sep 20,2012 9:16 pm || Up

                  If I were invited, I would definitely refuse to speak to him well past the point of awkwardness.

                  Of course, first we’d probably have to let Google index us.

                  "There's never enough time to do all the nothing you want"
                • JamesV Sep 20,2012 9:23 pm || Up

                  I can’t promise I wouldn’t have had some harsh words for him if I went.

                  That or I’d have just pretended he didn’t even exist.

                • Kylianna Sep 21,2012 12:42 am || Up

                  I’ve gone for the latter whenever I’ve accidentally ended up in the same vicinity of him.

                  Since ending up in Coliseum Jail is probably bad.

                • beebo Sep 21,2012 11:09 am || Up

                  Is that different from real life jail?

                • PDXAthleticsfan Sep 21,2012 11:22 am || Up

                  You have to pay for your own soft drinks.

                  A soliloquy of fresh-sounding ideas which would probably be disastrous.
                • beebo Sep 21,2012 11:56 am || Up

                  Fascists!!!!

                • dmoas Sep 20,2012 9:30 pm || Up

                  But you’d have to sit and stand right next to him. Very closely. Revving up the awkwardness that you’re not even acknowledging his existence.

                • nevermoor Sep 20,2012 9:34 pm || Up

                  Nope. Just act as if he isn’t there.

                  "There's never enough time to do all the nothing you want"

Leave a Reply