upgrade to 1.5.0 fails with utf8_unicode_ci collation in mysql

Bug #985608 reported by Michael Wuttke on 2012-04-19
10
This bug affects 2 people
Affects Status Importance Assigned to Milestone
Mahara
High
Richard Mansfield

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

description: updated
summary: - 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
importance: Undecided → High
assignee: nobody → Richard Mansfield (richard-mansfield)
milestone: none → 1.5.1

Reviewed: https://reviews.mahara.org/1166
Committed: http://gitorious.org/mahara/mahara/commit/ddc69cb0f949cb7d957808d224615cc630eeb2c3
Submitter: Hugh Davenport (<email address hidden>)
Branch: master

commit ddc69cb0f949cb7d957808d224615cc630eeb2c3
Author: Richard Mansfield <email address hidden>
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 <email address hidden>

Mahara Bot (dev-mahara) wrote :

Reviewed: https://reviews.mahara.org/1165
Committed: http://gitorious.org/mahara/mahara/commit/2030d3834d6fc84223edf0a8556773df79d4c86a
Submitter: Hugh Davenport (<email address hidden>)
Branch: 1.5_STABLE

commit 2030d3834d6fc84223edf0a8556773df79d4c86a
Author: Richard Mansfield <email address hidden>
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 <email address hidden>

Changed in mahara:
status: In Progress → Fix Committed
Melissa Draper (melissa) on 2012-05-07
Changed in mahara:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers