Today I upgraded our Mahara site from version 1.7.2 to 1.8.1 (current version). What it was expected to be an easy procedure turned out to a trouble due to a database (MySQL) collation issue. Below are the details:
After doing all backups as it’s required I downloaded and unzip the new version to perform the upgrade via cli-script.
me@myserver:/var/www/mahara# php ./admin/cli/upgrade.php
[WAR] 77 (lib/mahara.php:183) 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.
Call stack (most recent first):
* ensure_upgrade_sanity() at /var/www/mahara/admin/cli/upgrade.php:46
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.
My old Mahara version (1.7.2) had an utf8_spanish2_ci collation in tables. As I had recently changed to a new server and I had configured MySQL to a utf8_general_ci default collation the upgrade process stopped showing a warning about «.. not the same collation..».
The first thing I did was to change the utf8_spanish2_ci collation in every single mahara tables to utf8_general_ci. This can be done through MySQL console or phpMyAdmin using:
ALTER TABLE tablename COLLATE utf8_general_ci
I prefered to use a short php script (change_collation.php) to do it faster. Copy and paste into a file if you want to use it and change the words in red to reflect your configuration values:
<?php
$database_name=’mahara_database_name‘;
$database_username=’mahara_database_user‘;
$database_password=’mahara_database_password‘;
$connection = mysql_connect(‘localhost‘,$database_username,$database_password);
if(!$connection) {
echo «Cannot connect to the database – incorrect details»;
}
else {
mysql_select_db($database_name);
$result=mysql_query(‘show tables’);
while($tables = mysql_fetch_array($result)) {
foreach ($tables as $key => $value) {
mysql_query(«ALTER TABLE «.$value.» COLLATE utf8_general_ci«);
}
}
echo «Successfull collation change!»;
}
?>
Then I restarted MySQL in a Debian/Ubuntu Linux machine:
me@myserver:/var/www/mahara# service mysql restart
or
me@myserver:/var/www/mahara#/etc/init.d/mysql restart
But when I attempt to perform the upgrade it still showed the same message again:
me@myserver:/var/www/mahara# php ./admin/cli/upgrade.php
[WAR] 77 (lib/mahara.php:183) 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.
Call stack (most recent first):
* ensure_upgrade_sanity() at /var/www/mahara/admin/cli/upgrade.php:46
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.
So I used my database backup file (mahara_172_backup.sql) to search for the string ‘utf8_spanish2‘ and I found many occurrences:
me@myserver:/var/www/mahara# grep ‘utf8_spanish’ mahara_172_backup.sql
….
`name` varchar(20) CHARACTER SET utf8 COLLATE utf8_spanish2_ci NOT NULL,
`displayname` varchar(100) CHARACTER SET utf8 COLLATE utf8_spanish2_ci NOT NULL,
`release` text CHARACTER SET utf8 COLLATE utf8_spanish2_ci NOT NULL,
/*!50003 SET collation_connection = utf8_spanish2_ci */ ;
/*!50003 SET collation_connection = utf8_spanish2_ci */ ;
/*!50003 SET collation_connection = utf8_spanish2_ci */ ;
`plugin` varchar(100) CHARACTER SET utf8 COLLATE utf8_spanish2_ci NOT NULL,
`field` varchar(100) CHARACTER SET utf8 COLLATE utf8_spanish2_ci NOT NULL,
`value` text CHARACTER SET utf8 COLLATE utf8_spanish2_ci NOT NULL,
…..
Next step was to search for all utf8_spanish2_ci occurrences and replace them with utf8_general_ci. This can be done with a text editor but I preferred to do it with sed.
me@myserver:/var/www/mahara# sed ‘s/utf8_spanish2_ci/utf8_general_ci/g’ ./mahara_172_backup.sqll > ./mahara_172_utf8_general_ci_backup.sql
Notice that the name of the output file is different from the source so that we always keep the source a backup safe.
Now we re-check if all utf8_spanish2_ci occurrences were removed successfully:
me@myserver:/var/www/mahara# grep ‘utf8_spanish’ ./mahara_172_utf8_general_ci_backup.sql
.. and no occurrences are displayed this time so we will drop the Mahara database, recreate it again and re-import the data from mahara_172_utf8_general_ci_backup.sql
me@myserver:/var/www/mahara# mysql -umahara_database_user -pmahara_database_password
Enter password:
mysql> DROP database mahara_database_name;
mysql> create database mahara_database_name character set utf8 collate utf8_general_ci;
mysql> exit
… then we use mysqldump to re-import the data:
me@myserver:/var/www/mahara# mysql -u mahara_database_user -pmahara_database_password mahara_database_name < ./mahara_172_utf8_general_ci_backup.sql
Now try running again the cli-script to perform the upgrade and it must be working as expected
me@myserver:/var/www/mahara# php ./admin/cli/upgrade.php
[INF] 99 (admin/cli/upgrade.php:49) Upgrading Mahara
[INF] 99 (lib/mahara.php:217) Upgrading core
[DBG] 99 (lib/db/upgrade.php:2388) Update database for flexible page layouts feature
[DBG] 99 (lib/db/upgrade.php:2389) 1. Create table view_rows_columns
[DBG] 99 (lib/db/upgrade.php:2397) 2. Remake the table view_layout as view_layout_columns
[DBG] 99 (lib/db/upgrade.php:2406) 3. Alter table view_layout
[DBG] 99 (lib/db/upgrade.php:2418) 4. Create table view_layout_rows_columns
[DBG] 99 (lib/db/upgrade.php:2429) 5. Create table usr_custom_layout
[DBG] 99 (lib/db/upgrade.php:2439) 6. Convert existing view_layout records into new-style view_layouts with just one row
[DBG] 99 (lib/db/upgrade.php:2477) 7. Drop the obsolete view_layout.columns and view_layout.widths fields
[DBG] 99 (lib/db/upgrade.php:2484) 8. Update default values for tables view_layout, view_layout_columns and view_layout_rows_columns
[DBG] 99 (lib/db/upgrade.php:2487) 9. Update the table «block_instance»
[DBG] 99 (lib/db/upgrade.php:2515) 10. Add a «numrows» column to the views table.
[DBG] 99 (lib/db/upgrade.php:2523) 11. Update the table «view_rows_columns» for existing pages
[INF] 99 (lib/mahara.php:217) Upgrading artefact.resume
[INF] 99 (lib/mahara.php:217) Upgrading artefact.comment
[INF] 99 (lib/mahara.php:217) Upgrading auth.ldap
Done! 🙂
I hope it helps!