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

MySQL Database not correctly upgraded from Moodle 1.6.4 to 1.8.2.

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 1.8.2
    • Fix Version/s: 1.8.4, 1.9
    • Component/s: Database SQL/XMLDB
    • Labels:
      None
    • Environment:
      PHP 5, MySQL 5
    • Database:
      MySQL
    • Affected Branches:
      MOODLE_18_STABLE
    • Fixed Branches:
      MOODLE_18_STABLE, MOODLE_19_STABLE

      Description

      We upgraded from Moodle 1.6.4 to Moodle 1.8.2 about a month ago and have been having major performance issues since then. Our semester started last week and, with the influx of new students, the load on our Moodle increased exponentially. We had noticed that the database queries seemed somewhat inefficient and eventually the load reached a point where our database became completely deadlocked. Users kept getting database connection errors and were unable to use the system in any way.

      In our attempts to solve the problem we swapped to a new server, installed the latest versions of MySQL 5 and PHP 5 and tested everything running Apache 1.3 and Apache 2.2 without any improvement. We also updated to Moodle 1.8.3+, but did not notice any performance enhancements.

      The major issues, as far as we can tell, are:
      1. The upgrade scripts are severely defective: fields were not correctly initialised, keys were not created. We established this by comparing the database table structure of our Moodle with a fresh, 'vanilla' install of Moodle. The create table statements of the fresh install were markedly different to those used by the upgrade scripts. For example, the order of some table columns in a fresh Moodle install are different to those generated in an existing database by the upgrade scripts.
      2. The entire database became deadlocked due to damaged or missing indices (another side effect of the defective upgrade scripts): Moodle queries were locking tables in the database faster than the MySQL daemon could free them. This also caused data corruption in the database.

      In the attachments (same content, one in Open Office format and one in MS Excel) you will find a comparison between existing tables after an upgrade and the tables after a clean install of Moodle. Items in red are missing, items in green are swapped.

      More information can be found in the moodle.org General Problems Forum:
      http://moodle.org/mod/forum/discuss.php?d=83216

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              stronk7 Eloy Lafuente (stronk7)
              Reporter:
              hgkuper Heinz-Günter Kuper
              Tester:
              Nobody
              Participants:
              Component watchers:
              Andrew Nicols, Dongsheng Cai, Huong Nguyen, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              3 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Fix Release Date:
                11/Jan/08