Moodle
  1. Moodle
  2. MDL-30646

Sorting Question Bank Questions by name using link causes Error reading from database

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: 2.1.3
    • Fix Version/s: 2.0.7, 2.1.4, 2.2.1
    • Component/s: Questions
    • Labels:
    • Environment:
      Windows server 2008 R2, SQL Server 2005, IIS 7.5, Zend PHP 5.3.5
    • Database:
      Microsoft SQL
    • Testing Instructions:
      Hide

      Needs to be tested on MS SQL.

      1. Go to the Edit quiz page for any quiz that has at least one question in the question bank.
      2. Make sure the question bank is visible. If not, click show.
      3. Click on the 'Question' column heading to sort by that column.
      4. Verify there is no fatal error.
      5. Try sorting by all the different columns, and make sure you cannot trigger an error.
      6. Go to the question bank, and try sorting on all the different columns there too.

      Show
      Needs to be tested on MS SQL. 1. Go to the Edit quiz page for any quiz that has at least one question in the question bank. 2. Make sure the question bank is visible. If not, click show. 3. Click on the 'Question' column heading to sort by that column. 4. Verify there is no fatal error. 5. Try sorting by all the different columns, and make sure you cannot trigger an error. 6. Go to the question bank, and try sorting on all the different columns there too.
    • Workaround:
      Hide

      Edit the URL as follows:

      Generated URL:
      http://online.mandtsystem.com/mod/quiz/edit.php?cmid=19&cat=17%2C15&qpage=0&qbs1=questionnametext&qbs2=qtype&qbs3=questionname

      Edited URL that works:
      http://online.mandtsystem.com/mod/quiz/edit.php?cmid=19&cat=17%2C15&qpage=0&qbs1=questionnametext&qbs2=qtype

      Once edited, the sort columns work correctly. Generated URL's work as expected.

      Secondary fix-- changing
      question/editlib.php
      828: const MAX_SORTS = 3;

      to

      828: const MAX_SORTS = 2;

      generates URLs that work for me.

      Show
      Edit the URL as follows: Generated URL: http://online.mandtsystem.com/mod/quiz/edit.php?cmid=19&cat=17%2C15&qpage=0&qbs1=questionnametext&qbs2=qtype&qbs3=questionname Edited URL that works: http://online.mandtsystem.com/mod/quiz/edit.php?cmid=19&cat=17%2C15&qpage=0&qbs1=questionnametext&qbs2=qtype Once edited, the sort columns work correctly. Generated URL's work as expected. Secondary fix-- changing question/editlib.php 828: const MAX_SORTS = 3; to 828: const MAX_SORTS = 2; generates URLs that work for me.
    • Affected Branches:
      MOODLE_21_STABLE
    • Fixed Branches:
      MOODLE_20_STABLE, MOODLE_21_STABLE, MOODLE_22_STABLE
    • Pull from Repository:
    • Pull Master Branch:
    • Rank:
      33461

      Description

      Clicking the "Question" sort header in the list of questions while adding to a quiz causes Error reading from database.

      using DB module sqlserv, debug message:

      Question bank contents [Hide]
      Category: Relational
      Select a category:

      Error reading from database

      More information about this error
      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 20 q.hidden, q.category, q.id, q.qtype, q.name, q.questiontext, q.questiontextformat FROM mdl_question q WHERE parent = 0 AND hidden = 0 AND q.category = '17' ORDER BY q.name ASC, q.qtype ASC, q.name ASC
      [array (
      0 => '17',
      )]

      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 1135 of \question\editlib.php: call to sqlsrv_native_moodle_database->get_recordset_sql()
      line 1330 of \question\editlib.php: call to question_bank_view->load_page_questions()
      line 1156 of \mod\quiz\editlib.php: call to question_bank_view->display_question_list()
      line 458 of \mod\quiz\edit.php: call to quiz_question_bank_view->display()

        Issue Links

          Activity

          Hide
          Tim Hunt added a comment -

          The real problem was that quiz_question_bank_view did not change the default sort, even though it changed the set of columns used.

          Show
          Tim Hunt added a comment - The real problem was that quiz_question_bank_view did not change the default sort, even though it changed the set of columns used.
          Hide
          Ken Strickland added a comment -

          Thanks Tim. I applied the patches manually to my current build and all is good.

          Show
          Ken Strickland added a comment - Thanks Tim. I applied the patches manually to my current build and all is good.
          Hide
          Sam Hemelryk added a comment -

          Thanks Tim - this has been integrated now

          Show
          Sam Hemelryk added a comment - Thanks Tim - this has been integrated now
          Hide
          Aparup Banerjee added a comment -

          grabbing this

          Show
          Aparup Banerjee added a comment - grabbing this
          Hide
          Aparup Banerjee added a comment -

          yup works fine for me
          also noting that the fix has been verified to work, thanks Ken

          Show
          Aparup Banerjee added a comment - yup works fine for me also noting that the fix has been verified to work, thanks Ken
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Whoever decided one week was worth 14 days had really one bad idea. Anyway, the nightmare is over, so thanks for your, once again, amazing contributions. Many, many thanks!

          Now... disconnect, relax and enjoy the next days, yay!

          Closing...ciao

          Show
          Eloy Lafuente (stronk7) added a comment - Whoever decided one week was worth 14 days had really one bad idea. Anyway, the nightmare is over, so thanks for your, once again, amazing contributions. Many, many thanks! Now... disconnect, relax and enjoy the next days, yay! Closing...ciao

            People

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

              Dates

              • Created:
                Updated:
                Resolved: