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

Error when viewing participant's posts page

    XMLWordPrintable

    Details

    • Database:
      Microsoft SQL
    • Testing Instructions:
      Hide

      Repeat the following for all supported databases, especially Oracle and Mssql!

      1. Log in as admin/teacher
      2. Navigate to a course with students who have posted to the forums
      3. Navigate to Navigation > CourseXXX > Participants
      4. Select a participant
      5. Navigate to Navigation > CourseXXX > Participants > ParticipantXXX > Forum posts > Posts
      6. VERIFY: the results are accurate and no errrors are returned
      7. Navigate to Navigation > CourseXXX > Participants > ParticipantXXX > Forum posts > Discussions
      8. VERIFY: the results are accurate and no errrors are returned
      Show
      Repeat the following for all supported databases, especially Oracle and Mssql! Log in as admin/teacher Navigate to a course with students who have posted to the forums Navigate to Navigation > CourseXXX > Participants Select a participant Navigate to Navigation > CourseXXX > Participants > ParticipantXXX > Forum posts > Posts VERIFY: the results are accurate and no errrors are returned Navigate to Navigation > CourseXXX > Participants > ParticipantXXX > Forum posts > Discussions VERIFY: the results are accurate and no errrors are returned
    • Affected Branches:
      MOODLE_22_STABLE
    • Fixed Branches:
      MOODLE_22_STABLE, MOODLE_23_STABLE
    • Pull from Repository:
    • Pull Master Branch:
      wip-MDL-34527-m24
    • Pull Master Diff URL:

      Description

      There is an error when you view the Posts and Discussions pages related to a course participant. This seems to happen in MS SQL and Oracle but not MySQL or PostgreSQL.

      The problem is that we are using the DISTINCT qualifier on all fields from the forum table, which includes a long text field. We need to investigate what fields are actually needed from this query. I started tracing this back, but it will take some time to determine which fields are actually needed.

      Default exception handler: Error reading from database Debug: The ntext data type cannot be selected as DISTINCT because it is not comparable.
      		 
      SELECT DISTINCT f.*, cm.id AS cmid
      		 
                  FROM mdl_forum f
      		 
                  JOIN mdl_course_modules cm ON cm.instance = f.id
      		 
                  JOIN mdl_modules m ON m.id = cm.module
      		 
                  JOIN mdl_forum_discussions fd ON fd.forum = f.id
      		 
                          JOIN mdl_forum_posts fp ON fp.discussion = fd.id
      		 
                  WHERE m.name = 'forum' AND f.course = ? AND fp.userid = ?
      		 
      [array (
      		 
        0 => 2203,
      		 
        1 => '33713',
      		 
      )]
      		 
      * line 394 of \lib\dml\moodle_database.php: dml_read_exception thrown
      		 
      * line 255 of \lib\dml\mssql_native_moodle_database.php: call to moodle_database->query_end()
      		 
      * line 711 of \lib\dml\mssql_native_moodle_database.php: call to mssql_native_moodle_database->query_end()
      		 
      * line 740 of \lib\dml\mssql_native_moodle_database.php: call to mssql_native_moodle_database->get_recordset_sql()
      		 
      * line 7925 of \mod\forum\lib.php: call to mssql_native_moodle_database->get_records_sql()
      		 
      * line 8078 of \mod\forum\lib.php: call to forum_get_forums_user_posted_in()
      		 
      * line 121 of \mod\forum\user.php: call to forum_get_posts_by_user()

      Replication steps:

      1. Log in as admin/teacher
      2. Navigate to a course with students who have posted to the forums
      3. Navigate to Navigation > CourseXXX > Participants
      4. Select a participant
      5. Navigate to Navigation > CourseXXX > Participants > ParticipantXXX > Forum posts > Posts
      6. Navigate to Navigation > CourseXXX > Participants > ParticipantXXX > Forum posts > Discussions

      Expected result: The student's posts are reported

      Actual result: With MS SQL and Oracle, an error is shown

      The offending query is at around 8046 in /mod/forum/lib

        Attachments

          Issue Links

          has been marked as being related by

          Bug - A problem which impairs or prevents Moodle from functioning correctly. MDL-35513 Attempt to go to Forum > Posts under My Profile and error generated.

          • Major - Major loss of function, incorrect output
          • Closed

          Bug - A problem which impairs or prevents Moodle from functioning correctly. MDL-35936 Reports -> Security overview doese not work on Moodle 2.2+, MS-SQL 2008R2

          • Minor - Minor loss of function where workaround is possible
          • Closed
          is duplicated by

          Bug - A problem which impairs or prevents Moodle from functioning correctly. MDL-30118 Error when viewing users´ forum posts from the Navigation block (forum_get_courses_user_posted_in)

          • Minor - Minor loss of function where workaround is possible
          • Closed

          Bug - A problem which impairs or prevents Moodle from functioning correctly. MDL-35505 The ntext data type cannot be selected as DISTINCT because it is not comparable

          • Minor - Minor loss of function where workaround is possible
          • Closed

          Bug - A problem which impairs or prevents Moodle from functioning correctly. MDL-39788 Oracle - Error when viewing forum posts page from "My profile"

          • Minor - Minor loss of function where workaround is possible
          • Closed
          will help resolve

          Bug - A problem which impairs or prevents Moodle from functioning correctly. MDL-36759 MS SQL DB Read fail (ntext with a DISTINCT SQL Query)

          • Minor - Minor loss of function where workaround is possible
          • Closed

            Activity

              People

              Assignee:
              samhemelryk Sam Hemelryk
              Reporter:
              fweiss Fred Weiss
              Integrator:
              Dan Poltawski
              Tester:
              Michael de Raadt
              Participants:
              Component watchers:
              Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Fix Release Date:
                12/Nov/12