17 April 2012, 9:18

Hello,

I have a problem with the upgrade to mahara 1.5.

I have running a mahara 1.4 installation and I'd tried to upgrade the site to the new 1.5 version. But it doesn't work. During the upgrade process it is allways shown:

A column of your database is using a collation that is not the same as the database default. Please ensure all columns use the same collation as the database.

I am using mysql 5.1.61

17 April 2012, 11:26

Sorry, I have lost the connection to the mahara.org-server.

One addition: the database of mahara has the character set UTF8 and all the tables the collation utf8_general_ci. The type of the database ist InnoDB

Has someone an idea what is wrong? There ar no extra plugins installed, only the iphoneuploud.file.

Thank you for your help!

michael

18 April 2012, 4:56

It was my error! Sorry for bothering you!

The existing tables of the database had the collation utf8_unicode_ci. I made a dump, drobed the database, changed the collation in the dump from utf8_unicode_ci to utf8_general_ci, created a new database and imported the dump into the new database.

After that the upgrade from mahara 1.4.2  to 1.5 ran. ;-)

Thank you anyway! ;-)

michael

18 April 2012, 14:36

Hello Michael,

Great that you figured out the problem yourself and also thank you for posting the solution in case somebody else has the same / a similar question.

Cheers

Kristina

25 April 2012, 16:47

A fix for the collation problem is committed, and will be in the official 1.5.1 tarball.  In the meantime, if you're using MySQL and utf8_unicode_ci, you can upgrade without changing your collation by generating a tarball from the git repository:

https://gitorious.org/mahara/mahara/archive-tarball/1.5_STABLE

18 April 2012, 1:29

Hello

My Upgrading doesn't run.

Here is the message:

Could not execute command: ALTER TABLE mh_group ADD CONSTRAINT mh_grou_ins_fk FOREIGN KEY (institution) REFERENCES mh_institution (name)

Call stack (most recent first):

• execute_sql("ALTER TABLE mh_group ADD CONSTRAINT mh_grou_ins_fk...") at /home/.sites/383/sitexxx/web/portfolio/lib/dml.php:1395
• execute_sql_arr(array(size 1), true, true) at /home/.sites/383/sitexxx/web/portfolio/lib/ddl.php:1051
• add_key(object(XMLDBTable), object(XMLDBKey)) at /home/.sites/383/sitexxx/web/portfolio/lib/db/upgrade.php:2385
• xmldb_core_upgrade("2011061002") at /home/.sites/383/sitexxx/web/portfolio/lib/upgrade.php:301
• upgrade_core(object(stdClass)) at /home/.sites/383/sitexxx/web/portfolio/admin/upgrade.json.php:94

Whats bad, now I can't return back to the 1.4 Version, because that version now doen't run too.
Could someone please give help and support?

I am using

PHP 3.2.16
MySQL 5.3

Thanks in advance and kind regards
Conrad

18 April 2012, 17:05

Hi Conrad,

Often when you get upgrade errors in MySQL your database can end up in a part-upgraded state, which won't work with either the old or the new version of the code.  That's why database backups are part of the upgrade process.  There's usually not much point in trying to resume a failed upgrade, it's better to start again.

It would be helpful if you could you restore your old 1.4 database from the backup, run the upgrade once only, and report the very first error you get.

Also, when you get an SQL error like that, you can usually get more information, including an error number, if you try to rerun the query directly in your terminal client.  That would help us to find out why the command failed.

R.

18 April 2012, 18:45

I'm also running into upgrade errors. Here's the first error I get after I refreshed the database (after a previously failed attempt):

Could not execute command: UPDATE "block_instance", "artefact" SET "block_instance".configdata = CONCAT('a:1:{s:10:"artefactid";i:', CAST("artefact".id AS CHAR), ';}') WHERE "artefact".artefacttype = 'html' AND "artefact".note IS NOT NULL AND "block_instance".blocktype = 'textbox' AND CAST("block_instance".id AS CHAR) = "artefact".note

Here's the error code if I run it directly in MySQL:

#1267 - Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

I created my database as unicode_ci...

18 April 2012, 22:47

Hi Tony,

I don't know enough about collations in MySQL to tell you what's going on there.  I wonder if one of those CAST calls is returning utf8_general_ci (maybe because it's the default?), and this is incompatible with utf8_unicode_ci.  Just a wild guess really, but could you try running this:

UPDATE "block_instance", "artefact" SET "block_instance".configdata = CONCAT('a:1:{s:10:"artefactid";i:', CAST("artefact".id AS CHAR CHARACTER SET utf8) COLLATE utf8_unicode_ci, ';}') WHERE "artefact".artefacttype = 'html' AND "artefact".note IS NOT NULL AND "block_instance".blocktype = 'textbox' AND CAST("block_instance".id AS CHAR CHARACTER SET utf8) COLLATE utf8_unicode_ci = "artefact".note

and see if it succeeds and updates any rows?

19 April 2012, 5:32

Hi Richard, Hi Tony,

I've got exactly the same error message like Tony during the upgrade process at the webinterface.

This was my procedure:
1. mysqldump -u root --create-options -c -e mahara > /tmp/mahara_db_unicode.sql
2. mysql> create database mahara15 character set utf8 collate utf8_unicode_ci;
3. mysql> use mahara15;
4. mysql> \. /tmp/mahara_db_unicode.sql
5. try to upgrade on the website

I've tried the following statements at the mysql prompt and I've got the the following error messages:

mysql> UPDATE "block_instance", "artefact" SET "block_instance".configdata = CONCAT('a:1:{s:10:"artefactid";i:', CAST("artefact".id AS CHAR), ';}') WHERE "artefact".artefacttype = 'html' AND "artefact".note IS NOT NULL AND "block_instance".blocktype = 'textbox' AND CAST("block_instance".id AS CHAR) = "artefact".note;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"block_instance", "artefact" SET "block_instance".configdata = CONCAT('a:1:{s:10' at line 1

mysql> ALTER TABLE `group` ADD CONSTRAINT grou_ins_fk FOREIGN KEY (institution) REFERENCES institution (name);
ERROR 1005 (HY000): Can't create table 'mahara15.#sql-5785_875c4' (errno: 121)

But this procedure works for me:
1. mysqldump -u root --create-options -c -e mahara > /tmp/mahara_db_unicode.sql
2. sed 's/utf8_unicode_ci/utf8_general_ci/g' /tmp/mahara_db_unicode.sql > mahara_db_general.sql
3. mysql> create database mahara character set utf8 collate utf8_general_ci;
4. mysql> use mahara15;
5. mysql> \. /tmp/mahara_db_general.sql &
6. the upgrade on the website worked

Greetings,
michael