Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-63120

Badges cron task fails because of a join over +61 tables

XMLWordPrintable

    • MySQL
    • MOODLE_35_STABLE, MOODLE_400_STABLE, MOODLE_401_STABLE, MOODLE_402_STABLE
    • MOODLE_401_STABLE, MOODLE_402_STABLE
    • MDL-63120_Badges_cron_task_fails-MOODLE_401_STABLE
    • MDL-63120_Badges_cron_task_fails-MOODLE_402_STABLE
    • MDL-63120_Badges_cron_task_fails-master
    • Hide

      Covered by PHPUnit test.

       

      1. Use MySQL DB for unit testing
      2. To verify that the problem exists use the unpatched version of badges/criteria/award_criteria_activity.php and run the unit test with
        vendor/bin/phpunit --filter=test_badge_activity_criteria_with_a_huge_number_of_coursemodules
      3. The unit test creates a course, a user, a badge, enrols the user to the course and creates 75 (>61) coursemodules. Also it adds an activity criteria with BADGE_CRITERIA_AGGREGATION_ANY to a badge. After that it marks all course modules as completed for the user. Finally it runs the scheduled task core\task\badges_cron_task
      4. Use the patched version of badges/criteria/award_criteria_activity.php and run the unit test again. => The problem has disappeared.
      Show
      Covered by PHPUnit test.   Use MySQL DB for unit testing To verify that the problem exists use the unpatched version of badges/criteria/award_criteria_activity.php and run the unit test with vendor/bin/phpunit --filter=test_badge_activity_criteria_with_a_huge_number_of_coursemodules The unit test creates a course, a user, a badge, enrols the user to the course and creates 75 (>61) coursemodules. Also it adds an activity criteria with BADGE_CRITERIA_AGGREGATION_ANY to a badge. After that it marks all course modules as completed for the user. Finally it runs the scheduled task core\task\badges_cron_task Use the patched version of badges/criteria/award_criteria_activity.php and run the unit test again. => The problem has disappeared.

      In our moodle-environment the cronjob task \core\task\badges_cron_task fails regularly with:

      {{
      Scheduled task failed: Auszeichnungen vergeben (core\task\badges_cron_task),Fehler beim Lesen der Datenbank
      Debug info:
      Too many tables; MariaDB can only use 61 tables in a join
      SELECT DISTINCT u.id
                              FROM mdl_user u
                               LEFT JOIN mdl_course_modules_completion cmc38152 ON
                                cmc38152.userid = u.id AND
                                cmc38152.coursemoduleid = ? AND
                                ( cmc38152.completionstate = ? OR
                                  cmc38152.completionstate = ? ) LEFT JOIN mdl_course_modules_completion cmc37442 ON
                                cmc37442.userid = u.id AND
                                cmc37442.coursemoduleid = ? AND
                                ( cmc37442.completionstate = ? OR
                                  cmc37442.completionstate = ? ) LEFT JOIN mdl_course_modules_completion cmc37444 ON
                                cmc37444.userid = u.id AND
                                cmc37444.coursemoduleid = ? AND
                                ( cmc37444.completionstate = ? OR
                                  cmc37444.completionstate = ? ) LEFT JOIN mdl_course_modules_completion cmc37443 ON
                                cmc37443.userid = u.id AND
                                cmc37443.coursemoduleid = ? AND
                                ( cmc37443.completionstate = ? OR
                                  cmc37443.completionstate = ? ) LEFT JOIN mdl_course_modules_completion cmc37477 ON
                                cmc37477.userid = u.id AND
                                cmc37477.coursemoduleid = ? AND
                                ( cmc37477.completionstate = ? OR
                                  cmc37477.completionstate = ? ) LEFT JOIN mdl_course_modules_completion cmc38138 ON
                                cmc38138.userid = u.id AND
                                cmc38138.coursemoduleid = ? AND
                                ( cmc38138.completionstate = ? OR
                                  cmc38138.completionstate = ? ) LEFT JOIN mdl_course_modules_completion cmc38139 ON
                                cmc38139.userid = u.id AND
                                cmc38139.coursemoduleid = ? AND
                                ( cmc38139.completionstate = ? OR
                                  cmc38139.completionstate = ? ) LEFT JOIN mdl_course_modules_completion cmc37647 ON
                                cmc37647.userid = u.id AND
                                cmc37647.coursemoduleid = ? AND
                                ( cmc37647.completionstate = ? OR
                                  cmc37647.completionstate = ? )
      [...]
      Backtrace:

      • line 1277 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
      • line 503 of /lib/badgeslib.php: call to mysqli_native_moodle_database->get_fieldset_sql()
      • line 78 of /badges/cron.php: call to badge->review_all_criteria()
      • line 34 of /badges/cron.php: call to badge_review_cron()
      • line 48 of /lib/classes/task/badges_cron_task.php: call to badge_cron()
      • line 105 of /lib/cronlib.php: call to core\task\badges_cron_task->execute()
      • line 67 of /lib/cronlib.php: call to cron_run_inner_scheduled_task()
      • line 61 of /admin/cli/cron.php: call to cron_run()
        }}
         

      I believe that this problem only occurs in production environments, since the join is probably only then performed over +61 tables.

       

       

            MPe Peter Mayer
            derhelge Helge Wiethoff
            Jordi Pujol-Ahulló Jordi Pujol-Ahulló
            Sara Arjona (@sarjona) Sara Arjona (@sarjona)
            CiBoT CiBoT
            Votes:
            9 Vote for this issue
            Watchers:
            17 Start watching this issue

              Created:
              Updated:
              Resolved:

                Estimated:
                Original Estimate - 0 minutes
                0m
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 45 minutes
                45m

                  Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.