mahara-contributors team mailing list archive

[Mahara Bot <985608@xxxxxxxxxxxxxxxxxx>]

Reviewed:  https://reviews.mahara.org/1165
Committed: http://gitorious.org/mahara/mahara/commit/2030d3834d6fc84223edf0a8556773df79d4c86a
Submitter: Hugh Davenport (hugh@xxxxxxxxxxxxxxx)
Branch:    1.5_STABLE

commit 2030d3834d6fc84223edf0a8556773df79d4c86a
Author: Richard Mansfield <richard.mansfield@xxxxxxxxxxxxxxx>
Date:   Fri Apr 20 13:54:30 2012 +1200

    Use MySQL database collation for string literals (bug #985608)
    
    In MySQL, the collation for string literals in SQL expressions is
    defined by the connection collation, which can be different from the
    column collations inside the database.  When comparing string literals
    to values selected from the database, this can result in an "Illegal
    mix of collations" error, even if both the connection and the database
    use the same character set.
    
    Mahara already requires the column and connection character sets to be
    utf8, but doesn't care about the collations, so we can fix this with
    the MySQL "SET CHARACTER SET" statement, which sets the connection
    collation to match the database collation.
    
    Change-Id: Ied6fcf7062fae5aa315a43ec9ce80883e6ef5b2e
    Signed-off-by: Richard Mansfield <richard.mansfield@xxxxxxxxxxxxxxx>

-- 
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