Comment 0 for bug 985608

Hello,

I've tried to upgrade a small mahara installation.

- operating system: debian squeeze
- mahara-version: 1.4.2 -> 1.5.0
- database: mysql 5.1.61
- character set of the database mahara: utf8 and
- collation of all the tables of the mahara-database: utf8_unicode_ic

My first try was to upgrade mahara via the GUI. For the output of the error-messages please see the discussion in the following forum-thread "problem with upgrade to mahara 1.5": https://mahara.org/interaction/forum/topic.php?id=4474.

The secound try was to upgrade over CLI:
sudo -u www-data /usr/bin/php ~/mahara/admin/cli/upgrade.php

And I've got the following error messages on the terminal client:
[INF] 8f (admin/cli/upgrade.php:61) Upgrading Mahara
[INF] 8f (lib/mahara.php:231) Upgrading core
[DBG] 8f (lib/dml.php:159) mysql error: [1267: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='] in adodb_throw(
[DBG] 8f (lib/dml.php:159) UPDATE "block_instance", "artefact"
[DBG] 8f (lib/dml.php:159) SET "block_instance".configdata = CONCAT('a:1:{s:10:"artefactid";i:', CAST("artefact".id AS CHAR), ';}')
[DBG] 8f (lib/dml.php:159) WHERE
[DBG] 8f (lib/dml.php:159) "artefact".artefacttype = 'html'
[DBG] 8f (lib/dml.php:159) AND "artefact".note IS NOT NULL
[DBG] 8f (lib/dml.php:159) AND "block_instance".blocktype = 'textbox'
[DBG] 8f (lib/dml.php:159) AND CAST("block_instance".id AS CHAR) = "artefact".note, )
[DBG] 8f (lib/dml.php:159) Command was:
[DBG] 8f (lib/dml.php:159) UPDATE "block_instance", "artefact"
[DBG] 8f (lib/dml.php:159) SET "block_instance".configdata = CONCAT('a:1:{s:10:"artefactid";i:', CAST("artefact".id AS CHAR), ';}')
[DBG] 8f (lib/dml.php:159) WHERE
[DBG] 8f (lib/dml.php:159) "artefact".artefacttype = 'html'
[DBG] 8f (lib/dml.php:159) AND "artefact".note IS NOT NULL
[DBG] 8f (lib/dml.php:159) AND "block_instance".blocktype = 'textbox'
[DBG] 8f (lib/dml.php:159) AND CAST("block_instance".id AS CHAR) = "artefact".note
[WAR] 8f (lib/errors.php:749) Could not execute command:
[WAR] 8f (lib/errors.php:749) UPDATE "block_instance", "artefact"
[WAR] 8f (lib/errors.php:749) SET "block_instance".configdata = CONCAT('a:1:{s:10:"artefactid";i:', CAST("artefact".id AS CHAR), ';}')
[WAR] 8f (lib/errors.php:749) WHERE
[WAR] 8f (lib/errors.php:749) "artefact".artefacttype = 'html'
[WAR] 8f (lib/errors.php:749) AND "artefact".note IS NOT NULL
[WAR] 8f (lib/errors.php:749) AND "block_instance".blocktype = 'textbox'
[WAR] 8f (lib/errors.php:749) AND CAST("block_instance".id AS CHAR) = "artefact".note
Call stack (most recent first):
  * log_message("Could not execute command:
                    UP...", 8, true, true) at ~/mahara/lib/errors.php:109
  * log_warn("Could not execute command:
                    UP...") at ~/mahara/lib/errors.php:749
  * SQLException->__construct("Could not execute command:
                    UP...") at ~/mahara/lib/dml.php:161
  * execute_sql("
                    UPDATE {block_instance}, {art...") at ~/mahara/artefact/internal/blocktype/textbox/db/upgrade.php:89
  * xmldb_blocktype_textbox_upgrade("2010061800") at ~/mahara/lib/upgrade.php:374
  * upgrade_plugin(object(stdClass)) at ~/mahara/lib/db/upgrade.php:2576
  * xmldb_core_upgrade("2011061006") at ~/mahara/lib/upgrade.php:301
  * upgrade_core(object(stdClass)) at ~/mahara/lib/mahara.php:263
  * upgrade_mahara(array(size 8)) at ~/mahara/admin/cli/upgrade.php:62

And after trying to contine the uprade, I've got the message on the web-interface:
Could not execute command: ALTER TABLE `group` ADD CONSTRAINT grou_ins_fk FOREIGN KEY (institution) REFERENCES institution (name)
Call stack (most recent first):

    * execute_sql("ALTER TABLE `group` ADD CONSTRAINT grou_ins_fk FOR...") at /var/www/wissensmanagement.beuth-hochschule.de/htdocs/mahara15/lib/dml.php:1395
    * execute_sql_arr(array(size 1), true, true) at /var/www/wissensmanagement.beuth-hochschule.de/htdocs/mahara15/lib/ddl.php:1051
    * add_key(object(XMLDBTable), object(XMLDBKey)) at /var/www/wissensmanagement.beuth-hochschule.de/htdocs/mahara15/lib/db/upgrade.php:2385
    * xmldb_core_upgrade("2011061006") at /var/www/wissensmanagement.beuth-hochschule.de/htdocs/mahara15/lib/upgrade.php:301
    * upgrade_core(object(stdClass)) at /var/www/wissensmanagement.beuth-hochschule.de/htdocs/mahara15/admin/upgrade.json.php:94

The solution for me was to change the collations of all the tables:
1. mysqldump -u root --create-options -c -e mahara > /tmp/mahara_db_unicode.sql
2. sed 's/utf8_unicode_ci/utf8_general_ci/g' mahara_db_unicode.sql > mahara_db_general.sql
3. mysql> create database mahara15 character set utf8 collate utf8_general_ci;
4. mysql> use mahara15;
5. mysql> \. /tmp/mahara_db_general.sql &
6. the upgrade on the website http://localhost/mahara/admin/upgrade.php worked well. ;-)

Thanx,
michael