Some days ago we moved our Mahara 1.7.2  database (MySQL)  to a new machine to separte both web and database servers. Some days later we performed and upgrade to 1.8.1 and, after sorting out some issues related to collation, everything seemed to run as it was expected but, as I tried to clean my inbox I received a «A nonrecoverable error occurred. This probably means you have encountered a bug in the system» message. I couldn’t delete any message neither through Mahara interface nor PHPMyAdmin as root.

As I could’t find anything interesting in error.log (from Apache and MySQL), I added some extra lines to config.php to report a higher level of debugging:

$cfg->log_dbg_targets     = LOG_TARGET_SCREEN | LOG_TARGET_ERRORLOG;
$cfg->log_info_targets    = LOG_TARGET_SCREEN | LOG_TARGET_ERRORLOG;
$cfg->log_warn_targets    = LOG_TARGET_SCREEN | LOG_TARGET_ERRORLOG;
$cfg->log_environ_targets = LOG_TARGET_SCREEN | LOG_TARGET_ERRORLOG;

This allowed me to get more details about what was happening in my intallation when I tried to delete all my messages. Below and excerpt:

[WAR] a2 (lib/errors.php:736) Failed to get a recordset: mysqli error: [1449: The user specified as a definer (‘webXuser’@’localhost‘) does not exist] in adodb_throw(DELETE FROM «notification_internal_activity» WHERE id IN (15418,15415,15411,15408) AND usr = ’43’, )
Command was: DELETE FROM «notification_internal_activity» WHERE id IN (15418,15415,15411,15408) AND usr = ? and values was (43)
Call stack (most recent first):
log_message(«Failed to get a recordset: mysqli error: [1449: Th…», 8, true, true) at /var/www/mahara/lib/errors.php:93
log_warn(«Failed to get a recordset: mysqli error: [1449: Th…») at /var/www/mahara/lib/errors.php:736
SQLException->__construct(«Failed to get a recordset: mysqli error: [1449: Th…») at /var/www/mahara/lib/dml.php:966
delete_records_sql(«DELETE FROM «notification_internal_activity» WHERE…», array(size 1)) at /var/www/mahara/lib/dml.php:943
delete_records_select(«notification_internal_activity», «id IN (15418,15415,15411,15408) AND usr = ?», array(size 1)) at /var/www/mahara/account/activity/index.json.php:88
[WAR] a2 (lib/dml.php:966) Failed to get a recordset: mysqli error: [1449: The user specified as a definer (‘webXuser’@’localhost‘) does not exist] in adodb_throw(DELETE FROM «notification_internal_activity» WHERE id IN (15418,15415,15411,15408) AND usr = ’43’, )
Command was: DELETE FROM «notification_internal_activity» WHERE id IN (15418,15415,15411,15408) AND usr = ? and values was (43)
Call stack (most recent first):
delete_records_sql(«DELETE FROM «notification_internal_activity» WHERE…», array(size 1)) at /var/www/mahara/lib/dml.php:943
delete_records_select(«notification_internal_activity», «id IN (15418,15415,15411,15408) AND usr = ?», array(size 1)) at /var/www/mahara/account/activity/index.json.php:88
[DBG] 1b (lib/dml.php:152) mysqli error: [1449: The user specified as a definer (‘webXuser’@’localhost‘) does not exist] in adodb_throw(
            DELETE FROM «notification_internal_activity»
            WHERE id IN (
                ‘15405’,’15408′,’15411′,’15415′,’15418′,’15421′,’15425′,’15427′,’15459′
            ), )
Command was:
            DELETE FROM «notification_internal_activity»
            WHERE id IN (
                ‘15405’,’15408′,’15411′,’15415′,’15418′,’15421′,’15425′,’15427′,’15459′
            )
[WAR] 1b (lib/errors.php:736) Could not execute command:
            DELETE FROM «notification_internal_activity»
            WHERE id IN (
                ‘15405’,’15408′,’15411′,’15415′,’15418′,’15421′,’15425′,’15427′,’15459′
            )

The relevant information I could take out from the excerpt was that MySQL didn’t allow to execute the deletion of records (see the pink code) because they (the records) belonged to a non-existing user webXuser@localhost. That was right, because that user (webXuser) was the mahara_database_user that I had in my config.php before changing to the new database. When I moved to the new database I changed not only to a different mahara_database_user (webYuser) but also  mahara_database_password  and mahara_database_name.

I got stuck because it was the first time that I couldn’t delete records from a MySQL database as root. I search for solution and I found this thread which pointed my out the way to solve this issue.

I had to create in my new server a new user called webXuser and grant it properly:

grant all on mahara_database_name.* to 'webXuser'@'%' identified by 'password' with grant
 option;

Change the words in bold to reflect your own configuration and execute the SQL through PHPMyAdmin or MySQL console. Be aware of:

• Backup your database first! If something goes wrong you’ll be able to restore
• mahara_database_name is the name of the database in the new MySQL server
• webXuser is the mahara_database_user in the old MySQL server
• password is the mahara_database_password in the old MySQL server
• The % character is dangerous because it allows external connections to your MySQL installation if someone knows both username and password. Be careful!

Now if you try again to delete your old messages you’ll be able to do it and, I suspect (not confirmed yet) that this tweak could be necessary to perform some extra operations in the database when dealing with non-fresh installations. So I will keep the tweak for a while in my configuration until I could confirm that it’s not needed any more.

I hope it helps! 🙂