Mahara: How to solve collation issue when upgrading

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!

 

 

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *