Details

    • Database:
      Microsoft SQL
    • Testing Instructions:
      Hide

      Ideally this should be tested on at least MSSQL (to make sure that the reported issue is fixed) and then PostgreSQL and/or MySQL (to make sure there is no regression).

      • Create a workshop and let at least two students submit their work
      • Switch the workshop to the Assessment phase
      • TEST: make sure that the table of students' submissions is displayed and that you can sort the rows by firstname, lastname and submission title (using the arrows in the table header)
      Show
      Ideally this should be tested on at least MSSQL (to make sure that the reported issue is fixed) and then PostgreSQL and/or MySQL (to make sure there is no regression). Create a workshop and let at least two students submit their work Switch the workshop to the Assessment phase TEST: make sure that the table of students' submissions is displayed and that you can sort the rows by firstname, lastname and submission title (using the arrows in the table header)
    • Workaround:
      Hide

      None known

      Show
      None known
    • Affected Branches:
      MOODLE_20_STABLE, MOODLE_21_STABLE, MOODLE_22_STABLE
    • Fixed Branches:
      MOODLE_20_STABLE, MOODLE_21_STABLE
    • Pull from Repository:
    • Pull Master Branch:
      MDL-28015-workshop-sql
    • Rank:
      17698

      Description

      Hi

      Moodle 2.03+ SQL: 10 Windows 2008R2 IIS7.5

      Started workshop for the first time with a class. Used grading by accumlative grades.

      Used random allocations

      It kept on failing on grade calculations (logged in as admin/teacher) and it kept on stating Database error. (I can provide screenshots if necessary)

      thanks

      J.

      Replication steps:

      1. Start Workshop for a course
      2. Set Accumulative Grading
      3. Random assignment of tasks
      4. Students grade tasks
      5. DB error then appears when trying to do Grade calculations

      PS Debug info below

      Debug info: SQLState: 42000<br>Error Code: 169<br>Message: [Microsoft][SQL Server Native Client 10.0][SQL Server]A column has been specified more than once in the order by list. Columns in the order by list must be unique.<br>
      SELECT TOP 10 u.id AS userid,u.firstname,u.lastname,u.picture,u.imagealt,u.email,s.title AS submissiontitle, s.grade AS submissiongrade, ag.gradinggradeFROM mdl_user uLEFT JOIN mdl_workshop_submissions s ON (s.authorid = u.id AND s.workshopid = '1' AND s.example = 0)LEFT JOIN mdl_workshop_aggregations ag ON (ag.userid = u.id AND ag.workshopid = '1')WHERE u.id IN ('335','180','468','251','196','374','113','121','192','153','222','229','201','227','465','193','126','164','224','163','385','2')ORDER BY lastname ASC,u.lastname,u.firstname,u.id[array (0 => '1',1 => '1',2 => 335,3 => 180,4 => 468,5 => 251,6 => 196,7 => 374,8 => 113,9 => 121,10 => 192,11 => 153,12 => 222,13 => 229,14 => 201,15 => 227,16 => 465,17 => 193,18 => 126,19 => 164,20 => 224,21 => 163,22 => 385,23 => 2,)]Stack trace:line 391 of \lib\dml\moodle_database.php: dml_read_exception thrownline 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 1342 of \mod\workshop\locallib.php: call to sqlsrv_native_moodle_database->get_records_sql()line 355 of \mod\workshop\view.php: call to workshop->prepare_grading_report_data()
      

        Activity

        Hide
        Charles Fulton added a comment -

        Eyeballing it lastname is sorted on twice. I don't have a SQL Server instance running Moodle anymore. Try changing line 1345 in mod/workshop/locallib.php to read: "$sqlsort = $sortby . ' ' . $sorthow . ',u.lastname,u.id';"

        Show
        Charles Fulton added a comment - Eyeballing it lastname is sorted on twice. I don't have a SQL Server instance running Moodle anymore. Try changing line 1345 in mod/workshop/locallib.php to read: "$sqlsort = $sortby . ' ' . $sorthow . ',u.lastname,u.id';"
        Hide
        James Cracknell added a comment -

        Hi Charles

        Doing this I get 500 Internal Error.

        Thanks

        James

        Show
        James Cracknell added a comment - Hi Charles Doing this I get 500 Internal Error. Thanks James
        Hide
        Charles Fulton added a comment -

        Ack, I transposed something. Try this: $sqlsort = $sortby . ' ' . $sorthow . ',u.firstname,u.id';

        Also, I'm just assuming that's line 1345 in your instance; it might be a few lines off. Whichever line it is, you're removing ",u.lastname" from it.

        Charles

        Show
        Charles Fulton added a comment - Ack, I transposed something. Try this: $sqlsort = $sortby . ' ' . $sorthow . ',u.firstname,u.id'; Also, I'm just assuming that's line 1345 in your instance; it might be a few lines off. Whichever line it is, you're removing ",u.lastname" from it. Charles
        Hide
        James Cracknell added a comment -

        Hi Charles

        Thanks for this - have made the SQL change and it "works", but......

        Am going to attach a couple of screenshots. The notes in the "Assessment" phase now disappears and it refuses to calculate the grades. I can close the Workshop but the grades don't pass to the gradebook.

        thanks again for your help

        James

        Show
        James Cracknell added a comment - Hi Charles Thanks for this - have made the SQL change and it "works", but...... Am going to attach a couple of screenshots. The notes in the "Assessment" phase now disappears and it refuses to calculate the grades. I can close the Workshop but the grades don't pass to the gradebook. thanks again for your help James
        Hide
        James Cracknell added a comment -

        These 2 attachments show the Assessment Notes/details in the box where you change the phase of the Workshop missing.

        Also you can not click on Calculate Grades. So no grade is transferred to the Gradebook.

        Show
        James Cracknell added a comment - These 2 attachments show the Assessment Notes/details in the box where you change the phase of the Workshop missing. Also you can not click on Calculate Grades. So no grade is transferred to the Gradebook.
        Hide
        James Cracknell added a comment -

        Installed 2.1 today (well upgraded) and issue still a problem. The fix works on new workshops.

        Show
        James Cracknell added a comment - Installed 2.1 today (well upgraded) and issue still a problem. The fix works on new workshops.
        Hide
        David Mudrak added a comment -

        I can confirm there is the issue with MS SQL when sorting by lastname of firstname. I'll fix that.

        Regarding the second problem:
        1. firstly please do not describe new non-MS SQL related problem here but open a new issue for them
        2. please note that the planner tool's fields in the Assessment phase and Close phase can be actually empty - it just means the current user has no explicit task in the given phase.

        Show
        David Mudrak added a comment - I can confirm there is the issue with MS SQL when sorting by lastname of firstname. I'll fix that. Regarding the second problem: 1. firstly please do not describe new non-MS SQL related problem here but open a new issue for them 2. please note that the planner tool's fields in the Assessment phase and Close phase can be actually empty - it just means the current user has no explicit task in the given phase.
        Hide
        James Cracknell added a comment -

        Sorry but I thought they might be related, as the "fix" went on to cause other issues.

        Show
        James Cracknell added a comment - Sorry but I thought they might be related, as the "fix" went on to cause other issues.
        Hide
        David Mudrak added a comment -

        Submitting a patch for integration into 2.0, 2.1 and 2.2dev branches. Tested locally on MSSQL 2008 and PostgreSQL 8.3.

        Show
        David Mudrak added a comment - Submitting a patch for integration into 2.0, 2.1 and 2.2dev branches. Tested locally on MSSQL 2008 and PostgreSQL 8.3.
        Hide
        David Mudrak added a comment -

        James, I was able to reproduce the problem at MSSQL 2008. The patched version submitted for integration seems to solve the problem. I did not spot any other issues - final grades were calculated correctly and then pushed to the Gradebook on the Workshop close.

        Show
        David Mudrak added a comment - James, I was able to reproduce the problem at MSSQL 2008. The patched version submitted for integration seems to solve the problem. I did not spot any other issues - final grades were calculated correctly and then pushed to the Gradebook on the Workshop close.
        Hide
        James Cracknell added a comment -

        just wanted to say Thank You! to everyone who has helped with this.

        Show
        James Cracknell added a comment - just wanted to say Thank You! to everyone who has helped with this.
        Hide
        Eloy Lafuente (stronk7) added a comment -

        Integrated all branches, thanks!

        Offtopic: Curious way to avoid the dupes, for sure I'd have followed a uglier way. Though some comment could help in the future when somebody looks that code, lol.

        Show
        Eloy Lafuente (stronk7) added a comment - Integrated all branches, thanks! Offtopic: Curious way to avoid the dupes, for sure I'd have followed a uglier way. Though some comment could help in the future when somebody looks that code, lol.
        Hide
        Eloy Lafuente (stronk7) added a comment -

        Tested in 20_STABLE under the 4 DBs. All them handle the submissions table orders by any column perfectly. Passed!

        Show
        Eloy Lafuente (stronk7) added a comment - Tested in 20_STABLE under the 4 DBs. All them handle the submissions table orders by any column perfectly. Passed!
        Hide
        David Mudrak added a comment -

        Actually I was thinking of a helper method initially that would take two arrays (implicit and explicit sorting) and optionally a list of allowed fields. It would then return the required string for SQL. That could be part of dmllib as a friend of get_sql_or_equal() etc. (with proper unit tests, of course).

        Show
        David Mudrak added a comment - Actually I was thinking of a helper method initially that would take two arrays (implicit and explicit sorting) and optionally a list of allowed fields. It would then return the required string for SQL. That could be part of dmllib as a friend of get_sql_or_equal() etc. (with proper unit tests, of course).
        Hide
        Eloy Lafuente (stronk7) added a comment -

        Moodle's git/cvs repositories have been updated with this piece of art! Thanks!

        Show
        Eloy Lafuente (stronk7) added a comment - Moodle's git/cvs repositories have been updated with this piece of art! Thanks!

          People

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

            Dates

            • Created:
              Updated:
              Resolved: