Moodle
  1. Moodle
  2. MDL-27248

Debug info: Duplicate entry '13-20021' for key 'category_sortorder'

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Critical Critical
    • Resolution: Fixed
    • Affects Version/s: 1.9.13, 2.0.3, 2.1, 2.2
    • Fix Version/s: 1.9.14, 2.0.5, 2.1.2
    • Component/s: Course, Libraries
    • Labels:
    • Environment:
      Ubuntu server 10.04 amd64 bit, RHE 6.1,
    • Database:
      MySQL
    • Testing Instructions:
      Hide

      1/ get some old install from before 2004 or manually add index into the course table
      2/ upgrade
      3/ verify the index was removed using some db admin tool

      Show
      1/ get some old install from before 2004 or manually add index into the course table 2/ upgrade 3/ verify the index was removed using some db admin tool
    • Affected Branches:
      MOODLE_19_STABLE, MOODLE_20_STABLE, MOODLE_21_STABLE, MOODLE_22_STABLE
    • Fixed Branches:
      MOODLE_19_STABLE, MOODLE_20_STABLE, MOODLE_21_STABLE
    • Pull from Repository:
    • Pull Master Branch:
      w33_MDL-27248_m22_catsort
    • Rank:
      16979

      Description

      Debug info: Duplicate entry '13-20021' for key 'category_sortorder'
      UPDATE mdl_course
      SET sortorder = sortorder + 1
      WHERE category = ?
      [array (
      0 => '13',
      )]
      Stack trace:

      • line 394 of /lib/dml/moodle_database.php: dml_write_exception thrown
      • line 703 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
      • line 1095 of /lib/datalib.php: call to mysqli_native_moodle_database->execute()
      • line 3817 of /course/lib.php: call to fix_course_sortorder()
      • line 135 of /course/edit.php: call to update_course()
      1. category.diff
        2 kB
        Rasmus Prentow
      2. datalib.diff
        1 kB
        Julien Boulen

        Issue Links

          Activity

          Hide
          Julien Boulen added a comment -

          Hi,

          We use this patch for the moment.

          There was also something wrong near
          https://github.com/moodle/moodle/blob/MOODLE_20_STABLE/lib/datalib.php#L1091

          But after we have applied this patch, sql query doesn't seem to be call anymore.

          Show
          Julien Boulen added a comment - Hi, We use this patch for the moment. There was also something wrong near https://github.com/moodle/moodle/blob/MOODLE_20_STABLE/lib/datalib.php#L1091 But after we have applied this patch, sql query doesn't seem to be call anymore.
          Hide
          Brent Lee added a comment -

          I have the same issue on one of my big sites:

          LAMP Stack, latest code base. It has to be related to the DB content post upgrade. Some of my other installations have no issues.

          I see this issue when I attempt to sort courses, or when I edit a course settings.

          Debug info: Duplicate entry '92-10001' for key 'category_sortorder'
          UPDATE mdl_course
          SET sortorder = sortorder + 1
          WHERE category = ?
          [array (
          0 => '92',
          )]
          Stack trace:
          • line 394 of /lib/dml/moodle_database.php: dml_write_exception thrown
          • line 707 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
          • line 1095 of /lib/datalib.php: call to mysqli_native_moodle_database->execute()
          • line 3798 of /course/lib.php: call to fix_course_sortorder()
          • line 135 of /course/edit.php: call to update_course()

          Show
          Brent Lee added a comment - I have the same issue on one of my big sites: LAMP Stack, latest code base. It has to be related to the DB content post upgrade. Some of my other installations have no issues. I see this issue when I attempt to sort courses, or when I edit a course settings. Debug info: Duplicate entry '92-10001' for key 'category_sortorder' UPDATE mdl_course SET sortorder = sortorder + 1 WHERE category = ? [array ( 0 => '92', )] Stack trace: • line 394 of /lib/dml/moodle_database.php: dml_write_exception thrown • line 707 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end() • line 1095 of /lib/datalib.php: call to mysqli_native_moodle_database->execute() • line 3798 of /course/lib.php: call to fix_course_sortorder() • line 135 of /course/edit.php: call to update_course()
          Hide
          Brent Lee added a comment -

          The diff file has solved our issues relating to course settings.

          Show
          Brent Lee added a comment - The diff file has solved our issues relating to course settings.
          Hide
          Jeff Ottinger added a comment -

          It seems as though this issue is based on the way the query is performed. Take the following query:

          UPDATE mdl_course SET sortorder = sortorder + 1 WHERE category = 92

          This query fails for me because my installation of MySQL checks the unique key "category_sortorder" as it updates each row. The first sortorder value of 10000 tries to update to 10001, but 10001 still exists, isn't unique and MySQL throws an error.

          For other people the query won't fail because MySQL appears to check the unique keys of the affected rows AFTER the query is executed but before it is applied (? not sure)

          Show
          Jeff Ottinger added a comment - It seems as though this issue is based on the way the query is performed. Take the following query: UPDATE mdl_course SET sortorder = sortorder + 1 WHERE category = 92 This query fails for me because my installation of MySQL checks the unique key "category_sortorder" as it updates each row. The first sortorder value of 10000 tries to update to 10001, but 10001 still exists, isn't unique and MySQL throws an error. For other people the query won't fail because MySQL appears to check the unique keys of the affected rows AFTER the query is executed but before it is applied (? not sure)
          Hide
          Steve added a comment -
          Show
          Steve added a comment - I have had a related (the same?) problem and created a duplicate bug here http://tracker.moodle.org/browse/MDL-28236?page=com.atlassian.jira.plugin.system.issuetabpanels%3Achangehistory-tabpanel
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Opting out from this, raising priority to critical and sending to STABLE backlog.

          Somebody should be on this ASAP. Ping!

          Show
          Eloy Lafuente (stronk7) added a comment - Opting out from this, raising priority to critical and sending to STABLE backlog. Somebody should be on this ASAP. Ping!
          Hide
          Brent Lee added a comment -

          Thanks Eloy. I am happy to help with testing to resolve this one. I have it happening on one of my five instances, and I have a dev copy ready for any direction for testing.

          -Brent.

          Show
          Brent Lee added a comment - Thanks Eloy. I am happy to help with testing to resolve this one. I have it happening on one of my five instances, and I have a dev copy ready for any direction for testing. -Brent.
          Hide
          Henning Bostelmann added a comment - - edited

          I can reproduce the problem here on our installation (with a lot of categories, migrated from 1.9). System environment: Ubuntu 10.04.2, MySQL server version 5.1.41-3ubuntu12.10; occurs with both MyISAM and InnoDB tables. EDIT: I'm running Moodle 2.1+ build 20110708.

          As a related problem, if one identifies the category that causes the problem (id=13 in the above example, id=83 for me), opens the category.php page for that category, and clicks "Re-sort courses by name", the following error is encountered:


          Debug info: Duplicate entry '83-900001' for key 'category_sortorder'
          UPDATE mdl_course SET sortorder = ? WHERE id = ?
          [array (
          0 => 900001,
          1 => '539',
          )]
          Stack trace:

          • line 397 of /lib/dml/moodle_database.php: dml_write_exception thrown
          • line 1056 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
          • line 1465 of /lib/dml/moodle_database.php: call to mysqli_native_moodle_database->set_field_select()
          • line 64 of /course/category.php: call to moodle_database->set_field()


          Possibly the fix_course_sortorder() procedure should best be run during an upgrade script? (This seems to be related to a specific category in the migrated data; in my case, is has sortorder=90000,coursecount=16, and the courses in it have sortorder=90000 to sortorder=90015 in consecutive sequence.)

          Show
          Henning Bostelmann added a comment - - edited I can reproduce the problem here on our installation (with a lot of categories, migrated from 1.9). System environment: Ubuntu 10.04.2, MySQL server version 5.1.41-3ubuntu12.10; occurs with both MyISAM and InnoDB tables. EDIT: I'm running Moodle 2.1+ build 20110708. As a related problem, if one identifies the category that causes the problem (id=13 in the above example, id=83 for me), opens the category.php page for that category, and clicks "Re-sort courses by name", the following error is encountered: — Debug info: Duplicate entry '83-900001' for key 'category_sortorder' UPDATE mdl_course SET sortorder = ? WHERE id = ? [array ( 0 => 900001, 1 => '539', )] Stack trace: line 397 of /lib/dml/moodle_database.php: dml_write_exception thrown line 1056 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end() line 1465 of /lib/dml/moodle_database.php: call to mysqli_native_moodle_database->set_field_select() line 64 of /course/category.php: call to moodle_database->set_field() — Possibly the fix_course_sortorder() procedure should best be run during an upgrade script? (This seems to be related to a specific category in the migrated data; in my case, is has sortorder=90000,coursecount=16, and the courses in it have sortorder=90000 to sortorder=90015 in consecutive sequence.)
          Hide
          Brent Lee added a comment -

          More information:

          When I set the sort order in the DB to something random, and then "sort by name," I receive no error and it sorts fine. After the sort, the data looks good in the DB, all of the sortID's are in the right sequence.

          Now, if I move a course up or down, I receive the error of Duplicate Entry.

          Show
          Brent Lee added a comment - More information: When I set the sort order in the DB to something random, and then "sort by name," I receive no error and it sorts fine. After the sort, the data looks good in the DB, all of the sortID's are in the right sequence. Now, if I move a course up or down, I receive the error of Duplicate Entry.
          Hide
          Brent Lee added a comment -

          Any status on this issue?

          Show
          Brent Lee added a comment - Any status on this issue?
          Hide
          Rasmus Prentow added a comment - - edited

          We use the diff from above, and manually order all sortorders so there is no course with sortorder % 1000. EDIT: 10000
          That solved the first issue.

          In a similar issue when trying to move a course we added a temporary swap location in /course/category.php

          Line 193-149.

          if ($swapcourse and $movecourse) {
          // check course's category
          if ($movecourse->category != $id)

          { print_error('coursedoesnotbelongtocategory'); }

          $DB->set_field('course', 'sortorder', time(), array('id' => $swapcourse->id));
          $DB->set_field('course', 'sortorder', $swapcourse->sortorder, array('id' => $movecourse->id));
          $DB->set_field('course', 'sortorder', $movecourse->sortorder, array('id' => $swapcourse->id));
          }

          We run moodle 2.1 using mysql db.

          We encounter the bug posted by Bostelman aswell when trying to sort by name.

          Show
          Rasmus Prentow added a comment - - edited We use the diff from above, and manually order all sortorders so there is no course with sortorder % 1000. EDIT: 10000 That solved the first issue. In a similar issue when trying to move a course we added a temporary swap location in /course/category.php Line 193-149. if ($swapcourse and $movecourse) { // check course's category if ($movecourse->category != $id) { print_error('coursedoesnotbelongtocategory'); } $DB->set_field('course', 'sortorder', time(), array('id' => $swapcourse->id)); $DB->set_field('course', 'sortorder', $swapcourse->sortorder, array('id' => $movecourse->id)); $DB->set_field('course', 'sortorder', $movecourse->sortorder, array('id' => $swapcourse->id)); } We run moodle 2.1 using mysql db. We encounter the bug posted by Bostelman aswell when trying to sort by name.
          Hide
          Petr Škoda added a comment -

          How many courses do you have in those problematic categories? By default it can not be more than 10000 (I think), there is some way to change that if necessary.

          Show
          Petr Škoda added a comment - How many courses do you have in those problematic categories? By default it can not be more than 10000 (I think), there is some way to change that if necessary.
          Hide
          Rasmus Prentow added a comment -

          The issue posted by Bostelmann can be solved by adding a swap location aswell
          line 60 - 74 of /course/category.php

          if ($resort and confirm_sesskey()) {
          if ($courses = get_courses($category->id, "fullname ASC", 'c.id,c.fullname,c.sortorder')) {
          $i = 1;
          foreach ($courses as $course)

          { $DB->set_field('course', 'sortorder', time()+$i, array('id'=>$course->id)); $i++; }

          $i = 1;
          foreach ($courses as $course)

          { $DB->set_field('course', 'sortorder', $category->sortorder+$i, array('id'=>$course->id)); $i++; }

          fix_course_sortorder(); // should not be needed
          }
          }

          Show
          Rasmus Prentow added a comment - The issue posted by Bostelmann can be solved by adding a swap location aswell line 60 - 74 of /course/category.php if ($resort and confirm_sesskey()) { if ($courses = get_courses($category->id, "fullname ASC", 'c.id,c.fullname,c.sortorder')) { $i = 1; foreach ($courses as $course) { $DB->set_field('course', 'sortorder', time()+$i, array('id'=>$course->id)); $i++; } $i = 1; foreach ($courses as $course) { $DB->set_field('course', 'sortorder', $category->sortorder+$i, array('id'=>$course->id)); $i++; } fix_course_sortorder(); // should not be needed } }
          Hide
          Rasmus Prentow added a comment -

          We currently have 10 categories and max of 30 courses per category.

          Show
          Rasmus Prentow added a comment - We currently have 10 categories and max of 30 courses per category.
          Hide
          Brent Lee added a comment -

          We are 52 categories, and max of 40 per category.

          Show
          Brent Lee added a comment - We are 52 categories, and max of 40 per category.
          Hide
          Rasmus Prentow added a comment -

          A 10000 courses per category limit seems quite reasonable.

          Show
          Rasmus Prentow added a comment - A 10000 courses per category limit seems quite reasonable.
          Hide
          Michael Blake added a comment -

          This issue is causing problems for a MP client. Please give it priority.

          Show
          Michael Blake added a comment - This issue is causing problems for a MP client. Please give it priority.
          Hide
          Henning Bostelmann added a comment -

          As a temporary workaround, I dropped the uniqueness constraint from the affected index. In MySQL, that's the following DB statement:

          ALTER TABLE mdl_course DROP INDEX category_sortorder, ADD INDEX category_sortorder (category, sortorder);

          Show
          Henning Bostelmann added a comment - As a temporary workaround, I dropped the uniqueness constraint from the affected index. In MySQL, that's the following DB statement: ALTER TABLE mdl_course DROP INDEX category_sortorder, ADD INDEX category_sortorder (category, sortorder);
          Hide
          Carlos Lopez added a comment -

          I have the same error. Sometimes to move a course to a different category I need to edit direct in the course. Some times is in the Category seccion, in "move the courses selected to.."
          Why sometimes is different ways?

          Show
          Carlos Lopez added a comment - I have the same error. Sometimes to move a course to a different category I need to edit direct in the course. Some times is in the Category seccion, in "move the courses selected to.." Why sometimes is different ways?
          Hide
          Petr Škoda added a comment -

          The problem is that for some reason the unique index on the course table was not dropped, so far I did not find where it was supposed to happen...

          Show
          Petr Škoda added a comment - The problem is that for some reason the unique index on the course table was not dropped, so far I did not find where it was supposed to happen...
          Hide
          Henning Bostelmann added a comment -

          Sorry, maybe my previous comment was not clear. I dropped the constraint in my local installation, as a temporary workaround.

          Show
          Henning Bostelmann added a comment - Sorry, maybe my previous comment was not clear. I dropped the constraint in my local installation , as a temporary workaround.
          Hide
          Petr Škoda added a comment -

          Sorry, maybe my previous comment was not clear. The dropping of the constraint is the proper solution.

          Show
          Petr Škoda added a comment - Sorry, maybe my previous comment was not clear. The dropping of the constraint is the proper solution.
          Hide
          Petr Škoda added a comment -

          Thanks for the report and cooperation.

          Show
          Petr Škoda added a comment - Thanks for the report and cooperation.
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Integrated, thanks!

          Show
          Eloy Lafuente (stronk7) added a comment - Integrated, thanks!
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Tested under 21_STABLE (mysql and postgresql):

          • Created index manually ('category', 'sortorder').
          • Upgrade to latest integration.
          • Index was dropped.

          Passing!

          Show
          Eloy Lafuente (stronk7) added a comment - Tested under 21_STABLE (mysql and postgresql): Created index manually ('category', 'sortorder'). Upgrade to latest integration. Index was dropped. Passing!
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Many thanks for the hard work, this has been sent upstream and is available in all the git and cvs repositories.

          Show
          Eloy Lafuente (stronk7) added a comment - Many thanks for the hard work, this has been sent upstream and is available in all the git and cvs repositories.

            People

            • Votes:
              9 Vote for this issue
              Watchers:
              12 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: