Hi all,

Unlike most messages in this forum, this one is to celebrate that the upgrade of our main production server from Moodle 1.9.17+ to 2.2.3+ was a success! I had been fretting about this operation for months. Our main Moodle environment has about 5000 courses, circa 10 000 users, some 250 Gb worth of moodledata and a MySql database that was set up first in Moodle 1.4 upgraded to 1.5 and 1.6 and then recreated in 1.8 and upgraded to 1.9. It was never going to be easy.

That it finally went through smoothly (even though the upgrade process took some 6 hours) is thanks to the tireless Moodlers in these forums and the Moodle developers responding to their bug reports. We ran numerous upgrade tests that revealed numerous problems (mostly database-related), for all of which we tracked down solutions in moodle.org and the Moodle Tracker.

Here is a list of the things we had to do to prepare our database for the upgrade. Perhaps some of you may find these SQL commands useful for your own upgrades:

Our test upgrades found a number of conflicts with missing or erroneous values in certain database tables that caused DDL execution errors. These were corrected in our case with the following commands:

ALTER TABLE mdl_user MODIFY COLUMN lastip VARCHAR(45) NOT NULL DEFAULT '';
ALTER TABLE mdl_post MODIFY COLUMN uniquehash VARCHAR(255) NOT NULL DEFAULT '';
ALTER TABLE mdl_resource MODIFY COLUMN reference VARCHAR(255) NOT NULL DEFAULT '';
UPDATE mdl_user SET lastip = '0.0.0.0' WHERE lastip IS NULL;
UPDATE mdl_user SET city = 'unknown' WHERE city IS NULL;
ALTER TABLE mdl_modules ALTER COLUMN search SET DEFAULT 'default';

Next we ran into the timeout issue that can really mess up your server (So be sure you have a full back up (database AND moodledata folders) of you 1.9 installation before attempting the upgrade!). To deal with that we changed the timeout setting from 300 to 14 400 in the following locations:

In php.ini (in the server)

max_execution_time : 14400
max_input_time : 14400
session.gc_maxlifetime 14400
memory_limit = 2048M

In moodle/lib/file_storage/file_storage.php (in the Moodle 2.2.3 files that will run the upgrade, not in the old 1.9.17 files that were replaced, naturally)

$timeout = isset($options['timeout']) ? $options['timeout'] : 14400;

In moodle/lib/moodlelib.php (in the Moodle 2.2.3 files that will run the upgrade, not in the old 1.9.17 files that were replaced, naturally)

function upgrade_set_timeout($max_execution_time=14400) {
global $CFG;

$max_execution_time = (!empty($max_execution_time) && $max_execution_time < 14400) ? 14400 : $max_execution_time;

if (!isset($CFG->upgraderunning) or $CFG->upgraderunning < time()) { $upgraderunning = get_config(null, 'upgraderunning'); } else {

(The code highlighted in green was added into the file. These settings are temporary for the sake of the upgrade and will be undone in the next upgrade.)

What with that issue out of the way, we discovered that we had tables in our database that had different collations (Most of the tables in our case had collation utf8_general_ci while some had utf8_unicode_ci. We ran the following SQL queries to track down which tables and columns did not have utf8_general_ci as collation:

SELECT table_schema, table_name, table_collation
FROM information_schema.tables
WHERE table_schema = 'your_database'
AND table_collation != 'utf8_general_ci';

and

SELECT table_schema, table_name, column_name, collation_name
FROM information_schema.columns
WHERE table_schema = 'your_database'
AND collation_name != 'utf8_general_ci';

Instead of the green highligted string you would fill in the name of your MySql database. These comands listed the tables and columns that needed to be changed to collation utf8_general_ci. We exported them and created a list of SQL commands along the following template:

ALTER TABLE ‘tablename’ CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

In place of the green highlighted string we would have the table name of a table that contained a collation other than utf8_general_ci.

After this all tables had the same collation, excapt for the PMA tables that have always been utf8_bin. Another problem occurred when it turned out that for some new tables created during the upgrade Moodle chose another collation than utf8_general_ci. The following SQL command instructs the database to create only tables with the collation utf8_general_ci:

ALTER SCHEMA DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

That fixed the error concerning foremost the new wiki tables. After all these interventions the upgrade ran its course (although it took quite a bit longer than we had expected, so be prepared for that).

After the upgrade we expected to come accross some more errors  and we did and so far we fixed two. First of all we discovered there was a problem posting new forum messages. This was due to a missing default value in the mdl_forum_posts table. It was fixed with the follow SQL command (which can be applied before the upgrade too):

ALTER TABLE mdl_forum_posts ALTER attachment SET default 'None';

Next we found that we could not create new wiki pages (editing exiting ones worked though). This bug (The error message contains "Duplicate entry for key 'mdl_wikisubw_wikgrouse_uix") which will be fixed in the upcoming releases was overcome by editing the file mod/wiki/pagelib.php on row 949:

//if (!$this->subwiki = wiki_get_subwiki_by_group($this->wid, $groupid)) {
        //$swid = wiki_add_subwiki($PAGE->activityrecord->id, $groupid, $this->uid);
        //$this->subwiki = wiki_get_subwiki($swid);

        if (empty($this->subwiki)) {

        //If subwiki is not set then try find one and set else create one.
        if (!$this->subwiki = wiki_get_subwiki_by_group($this->wid, $groupid)) {
        $swid = wiki_add_subwiki($PAGE->activityrecord->id, $groupid, $this->uid);
        $this->subwiki = wiki_get_subwiki($swid);
        }

In which the code highligted in red (and now commented out) was replaced by the code highlighted in green.

I would like to underline that most of the SQL commands and all of the code listed above were not invented by us, but contributed by other moodlers in these forums and in the tracker and we want to say kudos to them!

Happy Midsummer from Lapland!

Rgrds,
Paul.