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

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

by Peter Ruthven-Stuart -
Number of replies: 7
Picture of Plugin developers

Hello,

Before upgrading my production moodle site to version 2, I am practicing the upgrade process on another server.

The upgrade runs, course files and blog attachments are migrated, and then I get a "DDL sql execution error" - see attached screenshot.

The text following this error is:

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

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.

I am trying to upgrade from moodle 1.9.10+ to 2.0.1+

my environment is:

  • php: 5.3.0
  • mysql: 5.1.40
  • server: MacOS 10.5.8

Any suggestions will be gratefully received.

I should add that I have successfully installed a fresh install of version 2.0.1+on this same server.

Attachment upgrading to version 2 DDL sql execution error.gif
Average of ratings: -
In reply to Peter Ruthven-Stuart

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

by Colin Fraser -
Picture of Documentation writers Picture of Testers

This is probably because the database has changed. I am suggesting that it is better to have a complete new installation of Moodle 2.0.x than try to upgrade a v1.9.x You may want to look here.

Average of ratings: -
In reply to Colin Fraser

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

by Ricardo Groiso -
I did so: Each time you get this message Moodle to say that the module or block is not compatible. I started to delete the folder containing the block or module that he meant and asked him to reread the moodle. Moodle jumps incompatibility and follow the installation.
Average of ratings: -
In reply to Colin Fraser

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

by Peter Ruthven-Stuart -
Picture of Plugin developers

Colin,

Thank your for your quick reply and lin to the "Beginning Moodle 2.0 Administration" doc.

Things are looking a little pessimistic. In order to bring my colleagues on board with an upgrade to version 2, I am going to have make sure that they don't need to recreate courses from scratch. Given that importing 1.9 course into version 2 is (almost) a non-starter at the moment (here & here), I was hoping that upgrading would work. I wonder how many people have in fact successfully upgraded to version 2 from 1.9?

My moodle site is not all that big: 100+ courses, 1500+ users, MySQL database 1.5MB, moodledata 30 GB. Surely it must be possible? There are so many good things in version 2 that I'd like my colleagues to be able to use.

Finally, I should add that when I attempted the upgrade, it was with a fresh 2.0.1 download of the moodle directory, and when I ran the install I referenced the 1.9 moodledata directory and corresponding database. i.e. I did not attempt to install the the new moodle directory over the older one, so there were no non-standard mods or blocks in the moodle directory. However, because I was attempting to upgade the 1.9 MySQL database, there are tables for non-standard mods and blocks in the database. But, according to this doc, this should not be a problem. Apparently these tables will be left dormant until I install updated versions of the mods and blocks.

Average of ratings: -
In reply to Peter Ruthven-Stuart

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

by Helen Foster -
Picture of Core developers Picture of Documentation writers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers Picture of Translators

Hi Peter,

Please report your upgrade problem in the Moodle Tracker giving full details (as in your initial post) so that developers can investigate and fix it. There was a DDL sql execution error problem reported and fixed some months ago (MDL-22503) but your problem seems different.

I don't know why Colin is suggesting a new install rather than upgrading because, as you mention, there are lots of reasons to upgrade, and Moodle is designed to upgrade smoothly from one version to another. We just need upgrade problems to be reported in the tracker though, as developers don't often have time to browse these forums.

Average of ratings: -
In reply to Helen Foster

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

by Peter Ruthven-Stuart -
Picture of Plugin developers

Helen,

Thank you for reply.

I have reported this bug: MDL-26280

Let's hope there's a solution.

Average of ratings: -
In reply to Peter Ruthven-Stuart

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

by Eloy Lafuente (stronk7) -
Picture of Core developers Picture of Documentation writers Picture of Moodle HQ Picture of Peer reviewers Picture of Plugin developers Picture of Testers

Hi Peter,

just pasting here my comments from the Tracker (MDL-26280), for reference:

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 lose 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, ciao smile

Average of ratings: Useful (3)
In reply to Eloy Lafuente (stronk7)

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

by Peter Ruthven-Stuart -
Picture of Plugin developers

Hello Eloy,

Thank you very much for your quick response. Thanks to your help I have successfully upgraded moodle 1.9.10+ to 2.0.1+ cool

I followed your suggestion 2, and dumped the mdl_log table. It was indeed a large table (700+ MB), almost half the size of the entire database.

I guess I need to learn more about the MySQL settings, in particular how to adjust 'timeout'. Though during the last 5 years of administering a moodle server I have never had this problem before, so perhaps it's just an issue for this particular upgrade and migration process.

Thanks again for your help.

Average of ratings: Useful (1)