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

Tu dirección de correo electrónico no será publicada.

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.