Moodle
  1. Moodle
  2. MDL-32758

Advanced assignment creation throws "The service is unavailable" error on IIS/MS-SQL.

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Blocker Blocker
    • Resolution: Cannot Reproduce
    • Affects Version/s: 2.2.2, 2.3
    • Fix Version/s: None
    • Component/s: Assignment (2.2)
    • Labels:
    • Testing Instructions:
      Hide
      1. Test using MS-SQL
      2. Add an activity (select assignment, advanced uploading of files
      3. enter assignment name and description and then save
      4. Attempt to complete a submission to the advanced upload

      Expected Outcome:

      • Everything works nicely
      Show
      Test using MS-SQL Add an activity (select assignment, advanced uploading of files enter assignment name and description and then save Attempt to complete a submission to the advanced upload Expected Outcome: Everything works nicely
    • Workaround:
      Hide

      See the fix below.

      Show
      See the fix below.
    • Affected Branches:
      MOODLE_22_STABLE, MOODLE_23_STABLE
    • Rank:
      39750

      Description

      I have upgraded to the latest release of Moodle but our teachers' are now reporting The service is unavailable. when they try to access or create advanced assignments.

      We are running Windows 2008 R2 and MS SQL

        Activity

        Hide
        Michael de Raadt added a comment -

        Hi, Paul.

        It would be good if we could get more details from you about that.

        Do you mean the teachers are attempting to use advanced grading methods in an assignment?

        Could you provide a list of steps we could follow to replicate your problem?

        Show
        Michael de Raadt added a comment - Hi, Paul. It would be good if we could get more details from you about that. Do you mean the teachers are attempting to use advanced grading methods in an assignment? Could you provide a list of steps we could follow to replicate your problem?
        Hide
        Paul Rolfe added a comment -

        Hi Michael,

        Thanks for the response. The issue is the following:

        At a course, enable editing, Add an activity (select assignment, advanced uploading of files, it loads the configuartion page fine (enter assignment name and description and then save). when you then try to view or edit this activity you get service not available.

        Paul

        Show
        Paul Rolfe added a comment - Hi Michael, Thanks for the response. The issue is the following: At a course, enable editing, Add an activity (select assignment, advanced uploading of files, it loads the configuartion page fine (enter assignment name and description and then save). when you then try to view or edit this activity you get service not available. Paul
        Hide
        Paul Rolfe added a comment -

        Basically its this page /mod/assignment/view.php?id=2808

        Show
        Paul Rolfe added a comment - Basically its this page /mod/assignment/view.php?id=2808
        Hide
        Paul Rolfe added a comment -

        Not sure if its relevant but we are also running MSSQL would that be an issue as we had the same sort of error when we tried to use a custom module and it did not like the MSSQL server. I have tried turning on logging but it doesnt show anything.

        Paul

        Show
        Paul Rolfe added a comment - Not sure if its relevant but we are also running MSSQL would that be an issue as we had the same sort of error when we tried to use a custom module and it did not like the MSSQL server. I have tried turning on logging but it doesnt show anything. Paul
        Hide
        Petr Škoda added a comment -

        Please try to configure you IIS to show the real error pages instead of general IIS pages and enable maximum debug in your moodle installation temporarily.

        Show
        Petr Škoda added a comment - Please try to configure you IIS to show the real error pages instead of general IIS pages and enable maximum debug in your moodle installation temporarily.
        Hide
        Paul Rolfe added a comment -

        Hi, here is the full error (I am using MS SQL)

        Error reading from database Debug info: SQLState: 42000<br>
        Error Code: 402<br>
        Message: [Microsoft][SQL Server Native Client 10.0][SQL Server]The data types ntext and varchar are incompatible in the equal to operator.<br>

        SELECT COUNT('x')
        FROM mdl_assignment_submissions s
        LEFT JOIN mdl_assignment a ON a.id = s.assignment
        INNER JOIN (SELECT DISTINCT eu1_u.id
        FROM mdl_user eu1_u
        JOIN mdl_role_assignments eu1_ra3 ON (eu1_ra3.userid = eu1_u.id AND eu1_ra3.roleid IN (6,5,4,3,1) AND eu1_ra3.contextid IN (1,21,110,2866,3631))
        JOIN mdl_user_enrolments eu1_ue ON eu1_ue.userid = eu1_u.id
        JOIN mdl_enrol eu1_e ON (eu1_e.id = eu1_ue.enrolid AND eu1_e.courseid = '61')
        WHERE eu1_u.deleted = 0 AND eu1_u.id <> '1') u ON u.id = s.userid
        WHERE s.assignment = '148' AND
        s.data2 = 'submitted'
        [array (
        0 => '61',
        1 => '1',
        2 => '148',
        )]
        Stack trace:
        •line 394 of \lib\dml\moodle_database.php: dml_read_exception thrown
        •line 252 of \lib\dml\sqlsrv_native_moodle_database.php: call to moodle_database->query_end()
        •line 368 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->query_end()
        •line 773 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->do_query()
        •line 807 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->get_recordset_sql()
        •line 1297 of \lib\dml\moodle_database.php: call to sqlsrv_native_moodle_database->get_records_sql()
        •line 1372 of \lib\dml\moodle_database.php: call to moodle_database->get_record_sql()
        •line 1543 of \lib\dml\moodle_database.php: call to moodle_database->get_field_sql()
        •line 420 of \mod\assignment\type\upload\assignment.class.php: call to moodle_database->count_records_sql()
        •line 3942 of \mod\assignment\lib.php: call to assignment_upload->count_real_submissions()
        •line 3501 of \lib\navigationlib.php: call to assignment_extend_settings_navigation()
        •line 2910 of \lib\navigationlib.php: call to settings_navigation->load_module_settings()
        •line 601 of \lib\pagelib.php: call to settings_navigation->initialise()
        •line 617 of \lib\pagelib.php: call to moodle_page->magic_get_settingsnav()
        •line 133 of \blocks\settings\block_settings.php: call to moodle_page->__get()
        •line 280 of \blocks\moodleblock.class.php: call to block_settings->get_content()
        •line 232 of \blocks\moodleblock.class.php: call to block_base->formatted_contents()
        •line 926 of \lib\blocklib.php: call to block_base->get_content_for_output()
        •line 978 of \lib\blocklib.php: call to block_manager->create_block_contents()
        •line 349 of \lib\blocklib.php: call to block_manager->ensure_content_created()
        •line 6 of \theme\aardvark\layout\general.php: call to block_manager->region_has_content()
        •line 685 of \lib\outputrenderers.php: call to include()
        •line 637 of \lib\outputrenderers.php: call to core_renderer->render_page_layout()
        •line ? of unknownfile: call to core_renderer->header()
        •line 1363 of \lib\setuplib.php: call to call_user_func_array()
        •line 194 of \mod\assignment\lib.php: call to bootstrap_renderer->__call()
        •line 194 of \mod\assignment\lib.php: call to bootstrap_renderer->header()
        •line 51 of \mod\assignment\type\upload\assignment.class.php: call to assignment_base->view_header()
        •line 51 of \mod\assignment\view.php: call to assignment_upload->view()

        Show
        Paul Rolfe added a comment - Hi, here is the full error (I am using MS SQL) Error reading from database Debug info: SQLState: 42000<br> Error Code: 402<br> Message: [Microsoft] [SQL Server Native Client 10.0] [SQL Server] The data types ntext and varchar are incompatible in the equal to operator.<br> SELECT COUNT('x') FROM mdl_assignment_submissions s LEFT JOIN mdl_assignment a ON a.id = s.assignment INNER JOIN (SELECT DISTINCT eu1_u.id FROM mdl_user eu1_u JOIN mdl_role_assignments eu1_ra3 ON (eu1_ra3.userid = eu1_u.id AND eu1_ra3.roleid IN (6,5,4,3,1) AND eu1_ra3.contextid IN (1,21,110,2866,3631)) JOIN mdl_user_enrolments eu1_ue ON eu1_ue.userid = eu1_u.id JOIN mdl_enrol eu1_e ON (eu1_e.id = eu1_ue.enrolid AND eu1_e.courseid = '61') WHERE eu1_u.deleted = 0 AND eu1_u.id <> '1') u ON u.id = s.userid WHERE s.assignment = '148' AND s.data2 = 'submitted' [array ( 0 => '61', 1 => '1', 2 => '148', )] Stack trace: •line 394 of \lib\dml\moodle_database.php: dml_read_exception thrown •line 252 of \lib\dml\sqlsrv_native_moodle_database.php: call to moodle_database->query_end() •line 368 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->query_end() •line 773 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->do_query() •line 807 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->get_recordset_sql() •line 1297 of \lib\dml\moodle_database.php: call to sqlsrv_native_moodle_database->get_records_sql() •line 1372 of \lib\dml\moodle_database.php: call to moodle_database->get_record_sql() •line 1543 of \lib\dml\moodle_database.php: call to moodle_database->get_field_sql() •line 420 of \mod\assignment\type\upload\assignment.class.php: call to moodle_database->count_records_sql() •line 3942 of \mod\assignment\lib.php: call to assignment_upload->count_real_submissions() •line 3501 of \lib\navigationlib.php: call to assignment_extend_settings_navigation() •line 2910 of \lib\navigationlib.php: call to settings_navigation->load_module_settings() •line 601 of \lib\pagelib.php: call to settings_navigation->initialise() •line 617 of \lib\pagelib.php: call to moodle_page->magic_get_settingsnav() •line 133 of \blocks\settings\block_settings.php: call to moodle_page->__get() •line 280 of \blocks\moodleblock.class.php: call to block_settings->get_content() •line 232 of \blocks\moodleblock.class.php: call to block_base->formatted_contents() •line 926 of \lib\blocklib.php: call to block_base->get_content_for_output() •line 978 of \lib\blocklib.php: call to block_manager->create_block_contents() •line 349 of \lib\blocklib.php: call to block_manager->ensure_content_created() •line 6 of \theme\aardvark\layout\general.php: call to block_manager->region_has_content() •line 685 of \lib\outputrenderers.php: call to include() •line 637 of \lib\outputrenderers.php: call to core_renderer->render_page_layout() •line ? of unknownfile: call to core_renderer->header() •line 1363 of \lib\setuplib.php: call to call_user_func_array() •line 194 of \mod\assignment\lib.php: call to bootstrap_renderer->__call() •line 194 of \mod\assignment\lib.php: call to bootstrap_renderer->header() •line 51 of \mod\assignment\type\upload\assignment.class.php: call to assignment_base->view_header() •line 51 of \mod\assignment\view.php: call to assignment_upload->view()
        Hide
        Paul Rolfe added a comment -

        Can anyone help this is issue as some of lectures cannot retrieve student work that has been submitted. I understand that some 'casting' needs to be done but I have no idea how to go about this.

        Paul

        Show
        Paul Rolfe added a comment - Can anyone help this is issue as some of lectures cannot retrieve student work that has been submitted. I understand that some 'casting' needs to be done but I have no idea how to go about this. Paul
        Hide
        Michael de Raadt added a comment - - edited

        Hi, Paul.

        Yes, I was able to reproduce that in MS SQL.

        The error is in the assignment module. The fix is...

        modassignmenttypeuploadassignment.class.php, line 420
        {$DB->sql_compare_text('s.data2')} = '".ASSIGNMENT_STATUS_SUBMITTED."'", $params);
        
        Show
        Michael de Raadt added a comment - - edited Hi, Paul. Yes, I was able to reproduce that in MS SQL. The error is in the assignment module. The fix is... modassignmenttypeuploadassignment.class.php, line 420 {$DB->sql_compare_text('s.data2')} = ' ".ASSIGNMENT_STATUS_SUBMITTED." '", $params);
        Hide
        moodle.com added a comment -

        The summary of this issue needs to be updated.

        Show
        moodle.com added a comment - The summary of this issue needs to be updated.
        Hide
        Jason Fowler added a comment -

        Estimated time to fix (including setting up MS-SQL version for 2.3) 3 hours

        Show
        Jason Fowler added a comment - Estimated time to fix (including setting up MS-SQL version for 2.3) 3 hours
        Hide
        Jason Fowler added a comment -

        it would seem this has been fixed already - I've looked at the code, and the fix seems to be in place already, will be testing it ASAP to ensure this is the case

        Show
        Jason Fowler added a comment - it would seem this has been fixed already - I've looked at the code, and the fix seems to be in place already, will be testing it ASAP to ensure this is the case
        Hide
        Jason Fowler added a comment -

        It would seem that this issue has been fixed already, probably by a related issue that I have been unable to locate.

        Show
        Jason Fowler added a comment - It would seem that this issue has been fixed already, probably by a related issue that I have been unable to locate.

          People

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

            Dates

            • Created:
              Updated:
              Resolved: