Moodle
  1. Moodle
  2. MDL-26280

DDL sql execution error when upgrading from 1.9.10+ to 2.0.1+

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Blocker Blocker
    • Resolution: Won't Fix
    • Affects Version/s: 2.0.1
    • Fix Version/s: None
    • Component/s: Installation
    • Labels:
    • Environment:
      upgrade from moodle 1.9.10+ to 2.0.1+
      php: 5.3.0 (entropy.ch package)
      mysql: 5.1.40
      server: MacOS 10.5.8

      moodle 1.9 size:
      100+ courses
      1500+ users
      MySQL database = 1.5MB
      moodledata directory = 30 GB
    • Rank:
      15886

      Description

      Before upgrading my production moodle site to version 2, I am practising the upgrade process on another server from 1.9.10+ to 2.0.1+

      Here's what I have done:

      1) made a duplicate of the 1.9 moodledata directory
      2) made a duplicate of the 1.9 MySQL database, and made a new user with appropriate privileges
      3) downloaded mooodle version 2 via CVS, and did NOT copy over the original config.php file, nor did I copy over any non-standard mods, blocks or themes or anything else.
      4) accessed the top page of this install via Firefox
      5) as expected, the install window loads, and I type in the relevant details (paths to Moodledata, db name, user name etc.)
      5) then, as expected the "Your Moodle files have been changed..." warning appears in browser window (.../admin/index.php)
      6) clicked on Continue, and the "Server checks" reports no issues (Your server environment meets all minimum requirements) other than recommendation to install php Intl extension. Note: I have successfully carried out completely fresh installs of version 2 without the intl extension.
      8) clicked on Continue, and the "Plugins check" informs me that I have various Non-standard blocks and Modules (i.e. the tables for these non-standard items exist in the database). However, according to this doc
      http://docs.moodle.org/en/Upgrading_to_Moodle_2.0#A_word_about_optional_plugins_and_themes
      these non-standard mods and blocks should not be a problem. Apparently their corresponding tables will be left dormant until I install updated versions of the mods and blocks.
      9) clicked on Upgrade, course files and blog attachments are migrated, and then I get the following error message (see also attached GIF):

      DDL sql execution error

      Debug info: MySQL server has gone away
      ALTER TABLE mdl_log MODIFY COLUMN ip VARCHAR(45) NOT NULL DEFAULT '' after userid
      Stack trace:

      • line 397 of /lib/dml/moodle_database.php: ddl_change_structure_exception thrown
      • line 636 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
      • line 88 of /lib/ddl/database_manager.php: call to mysqli_native_moodle_database->change_database_structure()
      • line 75 of /lib/ddl/database_manager.php: call to database_manager->execute_sql()
      • line 636 of /lib/ddl/database_manager.php: call to database_manager->execute_sql_arr()
      • line 648 of /lib/ddl/database_manager.php: call to database_manager->change_field_type()
      • line 1042 of /lib/db/upgrade.php: call to database_manager->change_field_precision()
      • line 1360 of /lib/upgradelib.php: call to xmldb_main_upgrade()
      • line 252 of /admin/index.php: call to upgrade_core()

      Fatal error: Exception thrown without a stack frame in Unknown on line 0

      10) there is a Continue button, which when I click takes me back to the "Your Moodle files have been changed, and you are about to automatically upgrade your server to this version:". On trying to re-run the process I am taken back to the same error.

      Hope this information helps to fix this problem.

      I see that "DDL sql execution error upon 1.9 to 2.0 migration" has been reported: MDL-22503.
      I have also posted a question re this error: http://moodle.org/mod/forum/discuss.php?d=167997

        Activity

        Hide
        Eloy Lafuente (stronk7) added a comment - - edited

        Hi Peter,

        the "MySQL server has gone away" isn't related with Moodle at all IMO.

        It uses to happen when the connection with the MySQL server is lost, due to some timeout happening at some place (TCP / Apache / PHP / MySQL). If the timeout is because of MySQL you can try changing some setting, see for more information:

        http://dev.mysql.com/doc/refman/5.0/en/gone-away.html

        The reason for this happening in that exact point of the Moodle 1.9 => 2.0 upgrade is that the log (mdl_log) table uses to be the biggest (in terms of number of records), specially if you've configured Moodle to keep all the log records, never deleting them. You can have there "zillions" of records. And changing the length of such a big table can take a lot of time, causing the timeout commented above.

        So, from a Moodle perspective, if you cannot tune your TCP / Apache / PHP / MySQL to allow longer timers... you can try one of these alternatives, both aimed to reduce the number of records in the logs table in order to allow upgrade to continue:

        1) Enable the deletion of old records. Look for the "loglifetime" admin setting (somewhere under Admin -> Server) and decide how long you want to keep records. That will cause, in next Moodle cron execution, the deletion of a lot of old records, making easier the upgrade. Note this must be done in 1.9 before the upgrade and you will lost any log before the cut point configured.

        2) If you don't want to lose any record, you can do something like this:

        • Dump your logs table (complete).
        • Empty the table (it will have 0 records after this).
        • Run the upgrade.
        • Load the logs after the upgrade
          This way, the upgrade operation will be instantaneous (the table is 100% empty). At the end, you will retain all the log records.

        Aimed to avoid the webserver / browser timeouts, also these could be useful alternatives:

        3) Change the column specs from terminal:

        • Connect to your new MySQL database via terminal (not phpMyAdmin and friends)
        • Perform manually the: ALTER TABLE mdl_log MODIFY COLUMN ip VARCHAR(45) NOT NULL DEFAULT '' after userid;
        • Run the upgrade, it should detect the column is already of length 45 and do nothing, hence continuing.

        4) Try the upgrade using the CLI tool (instead of doing it over the web), that will avoid some components (browsers, web servers) to timeout.

        In any case, before trying any of the 1), 2), 3), 4) above, I'd review all the "timeout" settings in your environment (from the webserver to the mysql database).

        And that's all I can say about the "MySQL server has gone away" error. Hope it helps.

        Closing this as won't fix as far as there is nothing to fix/solve within Moodle... ciao

        Show
        Eloy Lafuente (stronk7) added a comment - - edited Hi Peter, the "MySQL server has gone away" isn't related with Moodle at all IMO. It uses to happen when the connection with the MySQL server is lost, due to some timeout happening at some place (TCP / Apache / PHP / MySQL). If the timeout is because of MySQL you can try changing some setting, see for more information: http://dev.mysql.com/doc/refman/5.0/en/gone-away.html The reason for this happening in that exact point of the Moodle 1.9 => 2.0 upgrade is that the log (mdl_log) table uses to be the biggest (in terms of number of records), specially if you've configured Moodle to keep all the log records, never deleting them. You can have there "zillions" of records. And changing the length of such a big table can take a lot of time, causing the timeout commented above. So, from a Moodle perspective, if you cannot tune your TCP / Apache / PHP / MySQL to allow longer timers... you can try one of these alternatives, both aimed to reduce the number of records in the logs table in order to allow upgrade to continue: 1) Enable the deletion of old records. Look for the "loglifetime" admin setting (somewhere under Admin -> Server) and decide how long you want to keep records. That will cause, in next Moodle cron execution, the deletion of a lot of old records, making easier the upgrade. Note this must be done in 1.9 before the upgrade and you will lost any log before the cut point configured. 2) If you don't want to lose any record, you can do something like this: Dump your logs table (complete). Empty the table (it will have 0 records after this). Run the upgrade. Load the logs after the upgrade This way, the upgrade operation will be instantaneous (the table is 100% empty). At the end, you will retain all the log records. Aimed to avoid the webserver / browser timeouts, also these could be useful alternatives: 3) Change the column specs from terminal: Connect to your new MySQL database via terminal (not phpMyAdmin and friends) Perform manually the: ALTER TABLE mdl_log MODIFY COLUMN ip VARCHAR(45) NOT NULL DEFAULT '' after userid; Run the upgrade, it should detect the column is already of length 45 and do nothing, hence continuing. 4) Try the upgrade using the CLI tool (instead of doing it over the web), that will avoid some components (browsers, web servers) to timeout. In any case, before trying any of the 1), 2), 3), 4) above, I'd review all the "timeout" settings in your environment (from the webserver to the mysql database). And that's all I can say about the "MySQL server has gone away" error. Hope it helps. Closing this as won't fix as far as there is nothing to fix/solve within Moodle... ciao
        Hide
        Peter Ruthven-Stuart added a comment - - edited

        Thank you Eloy for your quick response to my report. Much appreciated.

        I followed your suggestion 2: dumping the mdl_log table.

        This did the trick; I was able to successfully upgrade 1.9.10+ to 2.0.1+. There were two small problems on the way but no show-stoppers.
        http://tel.c.fun.ac.jp/FUNmoodleTWO
        I suppose I should really learn how to change the MySQL timeout - something to do with a my.cnf file - but for the time being dumping the mdl_log table seems to be a good and safe solution.

        Incidentally, the mdl_log table was about 700 MB almost half the size of the entire database.

        Now to try with the production site.

        Show
        Peter Ruthven-Stuart added a comment - - edited Thank you Eloy for your quick response to my report. Much appreciated. I followed your suggestion 2: dumping the mdl_log table. This did the trick; I was able to successfully upgrade 1.9.10+ to 2.0.1+. There were two small problems on the way but no show-stoppers. http://tel.c.fun.ac.jp/FUNmoodleTWO I suppose I should really learn how to change the MySQL timeout - something to do with a my.cnf file - but for the time being dumping the mdl_log table seems to be a good and safe solution. Incidentally, the mdl_log table was about 700 MB almost half the size of the entire database. Now to try with the production site.

          People

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

            Dates

            • Created:
              Updated:
              Resolved: