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

Unknown Column 'reviewer.userid' in 'where clause' in h5pactivity

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Unresolved
    • Icon: Minor Minor
    • None
    • 4.4.4
    • H5P
    • MOODLE_404_STABLE
    • MDL-84085-405
    • MDL-84085-500
    • Hide
      1. Login as admin
      2. Navigate to Users > Permissions > Define roles in site administration
      3. Edit the Student role
        • Role archetype: Authenticated user
      4. Navigate to Users > Permissions > User policies in site administration
        • Default role for all users: Student
      5. Create a user
      6. Create a course
      7. Enrol user as student on course
      8. Add H5P activity to course
      9. Navigate to Permissions in the H5P activity
      10. Allow the following capability for the Student role:
        • mod/h5pactivity:reviewattempts
      11. View the H5P activity
      12. Confirm there is no error/exception
      13. Press Attempts report
      14. Confirm there is no error/exception
      Show
      Login as admin Navigate to Users > Permissions > Define roles in site administration Edit the Student role Role archetype: Authenticated user Navigate to Users > Permissions > User policies in site administration Default role for all users: Student Create a user Create a course Enrol user as student on course Add H5P activity to course Navigate to Permissions in the H5P activity Allow the following capability for the Student role: mod/h5pactivity:reviewattempts View the H5P activity Confirm there is no error/exception Press Attempts report Confirm there is no error/exception
    • Hide

      Code verified against automated checks.

      Checked MDL-84085 using repository: https://github.com/paulholden/moodle.git

      More information about this report

      Built on: Tue May 6 11:08:15 UTC 2025

      Show
      Code verified against automated checks. Checked MDL-84085 using repository: https://github.com/paulholden/moodle.git MOODLE_405_STABLE (0 errors / 0 warnings) [branch: MDL-84085-405 | CI Job ] MOODLE_500_STABLE (0 errors / 0 warnings) [branch: MDL-84085-500 | CI Job ] main (0 errors / 0 warnings) [branch: MDL-84085 | CI Job ] More information about this report Built on: Tue May 6 11:08:15 UTC 2025

      Same issue as reported in MDL-75300

       

      OBSERVED BEHAVIOR:

      When accessing any H5P Module we get the following error:

      Debug info: Unknown column 'reviewer.userid' in 'where clause'
      		 
      SELECT COUNT(*)
      		 
      FROM m_user u JOIN m_user_enrolments ej1_ue ON ej1_ue.userid = u.id
      		 
      JOIN m_enrol ej1_e ON (ej1_e.id = ej1_ue.enrolid AND ej1_e.courseid = ?)
      		 
       
      		 
      LEFT
      		 
      JOIN m_h5pactivity_attempts ha ON ha.userid = u.id
      		 
      WHERE 1 = 1 AND u.deleted = 0 AND u.id <> ? AND u.deleted = 0 AND reviewer.userid IS NULL AND ha.h5pactivityid = ? AND ha.completion = ?
      		 
      [array (
      		 
      0 => '2',
      		 
      1 => '1',
      		 
      2 => '1',
      		 
      3 => 1,
      		 
      )]
      		 
      Error code: dmlreadexception Stack trace:
      		 
       
      		 
          line 497 of /lib/dml/moodle_database.php: dml_read_exception thrown
      		 
          line 346 of /lib/dml/moodle_read_replica_trait.php: call to moodle_database->query_end()
      		 
          line 1364 of /lib/dml/mysqli_native_moodle_database.php: call to mysqli_native_moodle_database->query_end()
      		 
          line 1687 of /lib/dml/moodle_database.php: call to mysqli_native_moodle_database->get_records_sql()
      		 
          line 1760 of /lib/dml/moodle_database.php: call to moodle_database->get_record_sql()
      		 
          line 1985 of /lib/dml/moodle_database.php: call to moodle_database->get_field_sql()
      		 
          line 322 of /mod/h5pactivity/classes/local/manager.php: call to moodle_database->count_records_sql()
      		 
          line 97 of /mod/h5pactivity/view.php: call to mod_h5pactivity\local\manager->count_attempts()

      We did find that disabling the 'Enable attempt tracking' on the H5P settings allows the H5P content to load.

      And same as the original bug reported what we could find out is that the core function the H5P activity used to generate the reveiwersjoin does not return a join via the function get_active_users_join:

       // But excluding all reviewattempts users converting a capabilities join into left join.
      $reviewersjoin = get_with_capability_join($context, 'mod/h5pactivity:reviewattempts', 'u.id');

      That missing join leads to the error above. But we could not figure out why the join is missing.

      EXPECTED BEHAVIOR:

      H5P content loads with Enable attempt tracking enabled or disabled.

            pholden Paul Holden
            garrett.fraser Garrett Fraser
            Daniel Ziegenberg Daniel Ziegenberg
            Votes:
            1 Vote for this issue
            Watchers:
            3 Start watching this issue

              Created:
              Updated:

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