mahara-contributors team mailing list archive

[Richard Mansfield <985608@xxxxxxxxxxxxxxxxxx>]

** Summary changed:

- upgrade from mahara version 1.4.2 to 1.5.0 fails
+ upgrade to 1.5.0 fails with utf8_unicode_ci collation in mysql

** Changed in: mahara
       Status: New => In Progress

** Changed in: mahara
   Importance: Undecided => High

** Changed in: mahara
     Assignee: (unassigned) => Richard Mansfield (richard-mansfield)

** Changed in: mahara
    Milestone: None => 1.5.1

-- 
You received this bug notification because you are a member of Mahara
Contributors, which is subscribed to Mahara.
https://bugs.launchpad.net/bugs/985608

Title:
  upgrade to 1.5.0 fails with utf8_unicode_ci collation in mysql

Status in Mahara ePortfolio:
  In Progress

Bug description:
  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 ~/mahara/lib/dml.php:1395
      * execute_sql_arr(array(size 1), true, true) at ~/mahara/lib/ddl.php:1051
      * add_key(object(XMLDBTable), object(XMLDBKey)) at ~/mahara/lib/db/upgrade.php:2385
      * xmldb_core_upgrade("2011061006") at ~/mahara/lib/upgrade.php:301
      * upgrade_core(object(stdClass)) at ~/mahara/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

To manage notifications about this bug go to:
https://bugs.launchpad.net/mahara/+bug/985608/+subscriptions