Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-21730

Course "sortorder" values in the database get too large for PHP

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Duplicate
    • Affects Version/s: 1.9.7
    • Fix Version/s: 1.9.10
    • Component/s: Course, Database SQL/XMLDB
    • Labels:
      None
    • Environment:
      64-bit PHP 5, MySQL 5
    • Database:
      MySQL
    • Affected Branches:
      MOODLE_19_STABLE
    • Fixed Branches:
      MOODLE_19_STABLE

      Description

      I originally discovered this problem when using the "Re-sort courses by name" button on the "Add/edit courses" page. When I would click the button, the page would quickly reload and the courses would be in same order as before (not sorted by name). There would be no error messages, not even with developer debugging turned on.

      When looking at the "sortorder" column in the "mdl_course" table, I noticed that most of the values were ridiculously large. And dozens of the values were the same number, 13806016346361000000. While this is still below the max value for that column (unsigned bigint has max of 18446744073709551615), it is above the max value for a floating point number in PHP (even 64-bit). You can test this with the PHP CLI like this:

      [user@host ~]$ php -r 'var_dump(13806016346361000000 + 123456);'
      float(1.3806016346361E+19)
      [user@host ~]$ 

      Notice how it rounded it to the nearest precision it could handle (this was with 64-bit PHP). As you may know, PHP does not keep track of overflows. So it's doing this without throwing any errors. See http://www.php.net/manual/en/language.types.float.php for more information on this.

      So it looks like every time you sort courses, these values in the database get larger and larger until the value overflows and PHP just rounds it. I've been told that this only happens with MySQL but I have not been able to test it with anything else. I was able to fix this on the site that was having the problem by putting the site in Maintenance Mode (just in case) and running the following script:

      <?php
       
      require_once('moodle/config.php');
       
      $rs = get_recordset('course', '', '', 'sortorder', 'id');
      for ($i = 1; $rec = rs_fetch_next_record($rs); ++$i) {
          set_field('course', 'sortorder', $i, 'id', $rec->id);
      }
      rs_close($rs);
       
      ?>

      I'm not sure if this is the best way to fix it (it's technically only temporary), but it worked just fine and now the "Re-sort courses by name" button on the "Add/edit courses" page works as expected.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                andyjdavis Andrew Davis
                Reporter:
                jnrbsn Jonathan Robson
                Tester:
                Nobody
                Participants:
                Component watchers:
                Amaia Anabitarte, Carlos Escobedo, Ferran Recio, Sara Arjona (@sarjona), Víctor Déniz Falcón, Jake Dallimore, Jun Pataleta
              • Votes:
                7 Vote for this issue
                Watchers:
                7 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  25/Oct/10