Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Critical
-
Resolution: Fixed
-
Affects Version/s: 2.2.4
-
Fix Version/s: 2.2.6, 2.3.3, STABLE backlog
-
Component/s: Forum
-
Labels:
-
Database:Microsoft SQL
-
Testing Instructions:
-
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:
- 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
- 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
-
MDL-35513 Attempt to go to Forum > Posts under My Profile and error generated.
-
- Closed
-
-
MDL-35936 Reports -> Security overview doese not work on Moodle 2.2+, MS-SQL 2008R2
-
- Closed
-
- is duplicated by
-
MDL-30118 Error when viewing users´ forum posts from the Navigation block (forum_get_courses_user_posted_in)
-
- Closed
-
-
MDL-35505 The ntext data type cannot be selected as DISTINCT because it is not comparable
-
- Closed
-
-
MDL-39788 Oracle - Error when viewing forum posts page from "My profile"
-
- Closed
-
- will help resolve
-
MDL-36759 MS SQL DB Read fail (ntext with a DISTINCT SQL Query)
-
- Closed
-