Moodle
  1. Moodle
  2. MDL-30942

2.x upgrade times out when ALTER large tables, add more upgrade_set_timeout() or better dynamic estimate

    Details

    • Type: Improvement Improvement
    • Status: Open
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: 2.2
    • Fix Version/s: STABLE backlog
    • Component/s: Installation
    • Labels:
    • Environment:
      Windows 2008 R2 Apache/PHP, OS X 10.6 MySQL
    • Database:
      MySQL
    • Testing Instructions:
      Hide

      The installation kept "quitting" (status bar stops loading update/wheel stops spinning with a silent looking page) at the database conversion stage, until an upgrade_set_timeout(60*40) was injected into the problematic section in /lib/db/upgrade.php then the installation continued.

      A log table that's over 2GB size takes over 20 minutes to ALTER the ip column size. The installation gets stuck at this step and times out. The same issue repeats itself and gets stuck everytime since there is no column size checking (mysql_field_len?) in the process.

      Show
      The installation kept "quitting" (status bar stops loading update/wheel stops spinning with a silent looking page) at the database conversion stage, until an upgrade_set_timeout(60*40) was injected into the problematic section in /lib/db/upgrade.php then the installation continued. A log table that's over 2GB size takes over 20 minutes to ALTER the ip column size. The installation gets stuck at this step and times out. The same issue repeats itself and gets stuck everytime since there is no column size checking (mysql_field_len?) in the process.
    • Workaround:
      Hide

      adding upgrade_set_timeout(60*40) or higher before potential timeout situations, such as at around line 1039 of moodle 2.2. /lib/db/upgrade.php, before the ALTER TABLE statement on the log table.

      Show
      adding upgrade_set_timeout(60*40) or higher before potential timeout situations, such as at around line 1039 of moodle 2.2. /lib/db/upgrade.php, before the ALTER TABLE statement on the log table.
    • Affected Branches:
      MOODLE_22_STABLE
    • Rank:
      33965

      Description

      With a default out-of-box or a low php timeout setting and some large 1.x tables, such as log and backup_log, during the 2.x upgrade the ALTER TABLE statements can timeout the script. The installation page then hangs with no hint of progress or status. There isn't always an error, warning, or timeout message when this happens. Reloading the page will repeat the process in most ALTER cases and end up in the same place.

      In /lib/db/upgrade.php, many of the add/change field ALTER TABLE operations have no upgrade_set_timeout().

      Adding upgrade_set_timeout(60*40) or higher lets the alter statements complete. However this number varies on performance and table size. Maybe a better solution is to have a function checking the size of the working table and estimate a generous timeout value accordingly.

        Issue Links

          Activity

          Hide
          Michael de Raadt added a comment -

          Thanks for reporting this and suggesting a solution.

          Show
          Michael de Raadt added a comment - Thanks for reporting this and suggesting a solution.
          Hide
          Aparup Banerjee added a comment -

          hm, perhaps also we should detect what needs to be altered before running every single alter statement again upon reload - if thats even possible.

          Show
          Aparup Banerjee added a comment - hm, perhaps also we should detect what needs to be altered before running every single alter statement again upon reload - if thats even possible.

            People

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

              Dates

              • Created:
                Updated: