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

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

XMLWordPrintable

    • Icon: Improvement Improvement
    • Resolution: Deferred
    • Icon: Minor Minor
    • None
    • 2.2
    • Installation
    • Windows 2008 R2 Apache/PHP, OS X 10.6 MySQL
    • MySQL
    • MOODLE_22_STABLE
    • 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.
    • 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.

      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.

            dobedobedoh Andrew Lyons
            davidtang David Tang (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Created:
              Updated:
              Resolved:

                Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.