Add-ons
  1. Add-ons
  2. CONTRIB-2342

ForumNG: Error in cron when trying to mark messages as sent (again)

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.9.9
    • Fix Version/s: None
    • Component/s: None
    • Labels:
    • Environment:
      Linux (RHEL), PHP5.1.6, MySQL5.0.77 (using mysqli)
    • Database:
      MySQL
    • Affected Branches:
      MOODLE_19_STABLE
    • Rank:
      22110

      Description

      As a previous tracker cron job fails to send emails :
      (tested on both current CVS version of forumng and stable version 1.6 - stable version 1.8 does not seem to work at all on our server)

      Processing module function forumng_cron ...
      Email processing:
      Initial query: Marking processed posts: A forum exception occurred and forum cron was aborted: Failed to execute SQL
      UPDATE
      mdl_forumng_posts
      SET
      mailstate = 1
      WHERE
      id IN (SELECT fp.id
      FROM
      mdl_forumng_posts fp
      INNER JOIN mdl_user u ON fp.userid=u.id
      LEFT JOIN mdl_user eu ON fp.edituserid=eu.id
      LEFT JOIN mdl_forumng_posts reply ON fp.parentpostid = reply.id
      LEFT JOIN mdl_user replyu ON reply.userid = replyu.id
      LEFT JOIN mdl_user replyeu ON reply.edituserid = replyeu.id
      INNER JOIN mdl_forumng_discussions fd ON fp.discussionid = fd.id
      INNER JOIN mdl_forumng_posts discussionpost ON fd.postid = discussionpost.id
      INNER JOIN mdl_forumng f ON fd.forumid = f.id
      INNER JOIN mdl_course_modules cm ON f.id = cm.instance
      INNER JOIN mdl_context x ON x.instanceid = cm.id
      INNER JOIN mdl_course c ON c.id = f.course
      INNER JOIN mdl_forumng clonef
      ON (clonef.originalcmid = cm.id OR (f.shared=1 AND clonef.id = f.id))
      INNER JOIN mdl_course_modules clonecm ON clonef.id = clonecm.instance
      INNER JOIN mdl_modules m ON m.id = cm.module AND m.id = clonecm.module
      WHERE
      – Skip future posts (this is more relevant when using the set state
      – version of the query)...
      fp.created < 1283885701

      – Post must not have been mailed yet, also wait for editing delay if
      – not set to mailnow
      AND ((fp.mailstate = 0
      AND fp.created < 1283883901)
      OR fp.mailstate = 4)

      – Don't mail out really old posts (unless they were previously hidden)
      AND (fp.created > 1283712901 OR fd.timestart > 1283712901)

      – Discussion must meet time requirements
      AND fd.timestart < 1283885701
      AND (fd.timeend = 0 OR fd.timeend > 1283885701)

      – Post and discussion must not have been deleted and we're only looking
      – at original posts not edited old ones
      AND fp.deleted = 0
      AND fd.deleted = 0
      AND fp.oldversion = 0

      – Course-module and context limitations
      AND m.name='forumng'
      AND x.contextlevel = 70)

      #0 /home/moodle/moodle/mod/forumng/forum_utils.php(603): forum_utils::execute_sql('?UPDATE? mdl...')
      #1 /home/moodle/moodle/mod/forumng/forum_mail_list.php(252): forum_utils::update_with_subquery_grrr_mysql('?UPDATE? mdl...', 'SELECT fp.id ?F...')
      #2 /home/moodle/moodle/mod/forumng/forum_mail_list.php(56): forum_mail_list->mark_mailed('1283885701')
      #3 /home/moodle/moodle/mod/forumng/forum_cron.php(199): forum_mail_list->__construct(true)
      #4 /home/moodle/moodle/mod/forumng/forum_cron.php(160): forum_cron::email_normal()
      #5 /home/moodle/moodle/mod/forumng/forum_cron.php(24): forum_cron::email()
      #6 /home/moodle/moodle/mod/forumng/lib.php(110): forum_cron::cron()
      #7 /home/moodle/moodle/admin/cron.php(117): forumng_cron()
      #8

      {main}

        Activity

        Martyn Colliver created issue -
        Hide
        Sam Marshall added a comment -

        Sorry about this - I guess it is broken again in MySQL for some reason. It used to work, we must have somehow broken the query... I am really busy and about to go away on holiday again (sigh) but will try to check on Friday.

        By the way as you have obviously noticed we do rely on community for MySQL testing as nobody here uses it and I'm probably the only one with a test setup, which I don't have time to actually use on a general basis, only when problems are reported or occasionally for quick checks of a new major feature (which won't cover everything we might have broken). So thank you for helping in this way.

        Show
        Sam Marshall added a comment - Sorry about this - I guess it is broken again in MySQL for some reason. It used to work, we must have somehow broken the query... I am really busy and about to go away on holiday again (sigh) but will try to check on Friday. By the way as you have obviously noticed we do rely on community for MySQL testing as nobody here uses it and I'm probably the only one with a test setup, which I don't have time to actually use on a general basis, only when problems are reported or occasionally for quick checks of a new major feature (which won't cover everything we might have broken). So thank you for helping in this way.
        Hide
        Sam Marshall added a comment -

        Grr - out of time. Sorry, I wasn't able to get a MySQL-based system up and running in time to look at this today. Will look at it when I get back (end of the month).

        Show
        Sam Marshall added a comment - Grr - out of time. Sorry, I wasn't able to get a MySQL-based system up and running in time to look at this today. Will look at it when I get back (end of the month).
        Hide
        Sam Marshall added a comment -

        OK it took an inordinate amount of effort to get my MySQL test system working (with hindsight there would have been an easier way) but I managed it. And found... WHAT THE @*$! ... well, the problem is documented in the MySQL 5.1 manual:

        'Currently, you cannot update a table and select from the same table in a subquery.'

        Well why not!

        I sort of think at this point the best solution is to install an orbital death laser which will be automatically targeted at any server that ever runs MySQL, however I will try to come out with a plan B just in case that one doesn't work out.

        Show
        Sam Marshall added a comment - OK it took an inordinate amount of effort to get my MySQL test system working (with hindsight there would have been an easier way) but I managed it. And found... WHAT THE @*$! ... well, the problem is documented in the MySQL 5.1 manual: 'Currently, you cannot update a table and select from the same table in a subquery.' Well why not! I sort of think at this point the best solution is to install an orbital death laser which will be automatically targeted at any server that ever runs MySQL, however I will try to come out with a plan B just in case that one doesn't work out.
        Hide
        Martyn Colliver added a comment -

        Many thanks Sam for looking into this.

        Sorry that MySQL is proving to be a pain.....
        Am interested in changing from the dark side and presume you would encourage postgresql !!

        Think that lots of us are still using MySQL and thanks for the support you are giving.

        Show
        Martyn Colliver added a comment - Many thanks Sam for looking into this. Sorry that MySQL is proving to be a pain..... Am interested in changing from the dark side and presume you would encourage postgresql !! Think that lots of us are still using MySQL and thanks for the support you are giving.
        Hide
        Sam Marshall added a comment -

        Yes I would encourage postgres, although not to the point where you get to blame me if you have problems with it

        OK, done - thankfully it looks like I already dealt with this issue but the existing fix is not working for 'mysqli' because I hard-coded the dbtype 'mysql' - at that point I didn't know there was a 'mysqli' type... I have changed the condition so that it now uses the butchered query for any dbtype that starts with mysql. Testing on mysqli, it seems to work.

        Fixed in stable R1.11.

        btw I note you said newer versions than R1.6 don't work for you at all - if this is still the case then please file another bug!

        Show
        Sam Marshall added a comment - Yes I would encourage postgres, although not to the point where you get to blame me if you have problems with it OK, done - thankfully it looks like I already dealt with this issue but the existing fix is not working for 'mysqli' because I hard-coded the dbtype 'mysql' - at that point I didn't know there was a 'mysqli' type... I have changed the condition so that it now uses the butchered query for any dbtype that starts with mysql. Testing on mysqli, it seems to work. Fixed in stable R1.11. btw I note you said newer versions than R1.6 don't work for you at all - if this is still the case then please file another bug!
        Sam Marshall made changes -
        Field Original Value New Value
        Status Open [ 1 ] Resolved [ 5 ]
        Resolution Fixed [ 1 ]
        Helen Foster made changes -
        Labels ForumNG
        Helen Foster made changes -
        Component/s Module: Forumng [ 10572 ]

          People

          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development