09 August 2016, 2:33

We've migrated Mahara to a new server, dumped the database, recreated the database on the new server with the same username/password and imported the dumped database. All of the file paths and files on the new server remain the same as the old one, however after login we get the following message:

Site unavailable

A nonrecoverable error occurred. This probably means you have encountered a bug in the system

Looking in the Apache logs I see the following (sanitised):

AH01071: Got error 'PHP message: [WAR] 68 (lib/errors.php:747) Failed to get a recordset: mysqli error: [1449: The user specified as a definer ('DBUSER'@'%') does not exist] in EXECUTE("UPDATE "usr" SET "username" = 'xxx' , "password" = '' , "salt" = 'xxx' , "passwordchange" = '0' , "active" = '1' , "deleted" = '0' , "expiry" = NULL , "expirymailsent" = '0' , "lastlogin" = '2016-08-08 15:14:05' , "lastlastlogin" = '2016-04-22 15:57:47' , "lastaccess" = '2016-08-08 15:14:05' , "inactivemailsent" = '0' , "staff" = '0' , "admin" = '0' , "firstname" = 'xxx' , "lastname" = 'xxx' , "studentid" = 'xxx' , "preferredname" = 'xxx' , "email" = 'xxx' , "profileicon" = NULL , "suspendedctime" = NULL , "suspendedreason" = NULL , "suspendedcusr" = NULL , "quota" = '52428800' , "quotaused" = '0' , "authinstance" = '3' , "ctime" = '2015-11-13 16:08:25' , "showhomeinfo" = '1' , "unread" = '0' , "urlid" = NULL , "probation" = '0' WHERE "id" = '67' ")\nPHP message: [WAR] 68 (lib/errors.php:747) Command was: UPDATE "usr" SET "username" = ? , "password" = ? , "salt" = ? , "passwordchange" = ? , "active" = ? , "deleted" = ? , "expiry" = ? , "expirymailsent" = ? , "lastlogin" = ? , "lastlastlogin" = ? , "lastaccess" = ? , "inactivemailsent" = ? , "staff" = ? , "admin" = ? , "firstname" = ? , "lastname" = ? , "studentid" = ? , "preferredname" = ? , "email" = ? , "profileicon" = ? , "suspendedctime" = ? , "suspendedreason" = ? , "suspendedcusr" = ? , "quota" = ? , "quotaused" = ? , "authinstance" = ? , "ctime" = ? , "showhomeinfo" = ? , "unread" = ? , "urlid" = ? , "probation" = ? WHERE "id" = ? and values was (xxx,,xxx,0,1,0,,0,2016-08-08 15:14:05,2016-04-22 15:57:47,2016-08-08 15:14:05,0,0,0,xxx,xxx,xxx,xxx, xxx,xxx,,,,,52428800,0,3,2015-11-13 16:08:25,1,0,,0,67)\nPHP message: Call stack (most recent first):\nPHP message: * log_message("Failed to get a recordset: mysqli error: [1449: Th...", 8, true, true) at /var/www/vhosts/domain.dir/httpdocs/mahara-15.10.1/lib/errors.php:97\nPHP message: * log_warn("Failed to get a recordset: mysqli error: [1449: Th...") at /var/www/vhosts/domain.dir/httpdocs/mahara-15.10.1/lib/errors.php:747\nPHP message: * SQLException->__construct("Failed to get a recordset: mysqli error: [1449: Th...") at /var/www/vhosts/domain.dir/httpdocs/mahara-15.10.1/lib/dml.php:1301\nPHP message: * update_record("usr", object(stdClass), array(size 1)) at /var/www/vhosts/domain.dir/httpdocs/mahara-15.10.1/auth/user.php:415\nPHP message: * User->commit() at /var/www/vhosts/domain.dir/httpdocs/mahara-15.10.1/auth/user.php:1608\nPHP message: * LiveUser->commit() at /var/www/vhosts/domain.dir/httpdocs/mahara-15.10.1/auth/user.php:1695\nPHP message: * LiveUser->authenticate(object(stdClass), "********") at /var/www/vhosts/domain.dir/httpdocs/mahara-15.10.1/auth/user.php:1507\nPHP message: * LiveUser->login("u0024642", "********") at /var/www/vhosts/domain.dir/httpdocs/mahara-15.10.1/auth/lib.php:1462\nPHP message: * login_submit(object(Pieform), array(size 6)) at Unknown:0\nPHP message: * call_user_func_array("login_submit", array(size 2)) at /var/www/vhosts/domain.dir/httpdocs/mahara-15.10.1/lib/pieforms/pieform.php:537\nPHP message: * Pieform->__construct(array(size 9)) at /var/www/vhosts/domain.dir/httpdocs/mahara-15.10.1/auth/lib.php:505\nPHP message: * auth_setup() at /var/www/vhosts/domain.dir/httpdocs/mahara-15.10.1/init.php:408\nPHP message: * require("/var/www/vhosts/domain.dir/httpdocs/mah...") at /var/www/vhosts/domain.dir/httpdocs/mahara-15.10.1/index.php:16\nPHP message: \nPHP message: [WAR] 68 (lib/dml.php:1301) Failed to get a recordset: mysqli error: [1449: The user specified as a definer ('DBUSER'@'%') does not exist] in EXECUTE("UPDATE "usr" SET "username" = 'xxx' , "password" = '' , "salt" = 'xxx' , "passwordchange" = '0' , "active" = '1' , "deleted" = '0' , "expiry" = NULL , "expirymailsent" = '0' , "lastlogin" = '2016-08-08 15:14:05' , "lastlastlogin" = '2016-04-22 15:57:47' , "lastaccess" = '2016-08-08 15:14:05' , "inactivemailsent" = '0' , "staff" = '0' , "admin" = '0' , "firstname" = 'xxx' , "lastname" = 'xxx' , "studentid" = 'xxx' , "preferredname" = 'xxx' , "email" = 'xxx' , "profileicon" = NULL , "suspendedctime" = NULL , "suspendedreason" = NULL , "suspendedcusr" = NULL , "quota" = '52428800' , "quotaused" = '0' , "authinstance" = '3' , "ctime" = '2015-11-13 16:08:25' , "showhomeinfo" = '1' , "unread" = '0' , "urlid" = NULL , "probation" = '0' WHERE "id" = '67' ")\nPHP message: [WAR] 68 (lib/dml.php:1301) Command was: UPDATE "usr" SET "username" = ? , "password" = ? , "salt" = ? , "passwordchange" = ? , "active" = ? , "deleted" = ? , "expiry" = ? , "expirymailsent" = ? , "lastlogin" = ? , "lastlastlogin" = ? , "lastaccess" = ? , "inactivemailsent" = ? , "staff" = ? , "admin" = ? , "firstname" = ? , "lastname" = ? , "studentid" = ? , "preferredname" = ? , "email" = ? , "profileicon" = ? , "suspendedctime" = ? , "suspendedreason" = ? , "suspendedcusr" = ? , "quota" = ? , "quotaused" = ? , "authinstance" = ? , "ctime" = ? , "showhomeinfo" = ? , "unread" = ? , "urlid" = ? , "probation" = ? WHERE "id" = ? and values was (xxx,,xxx,0,1,0,,0,2016-08-08 15:14:05,2016-04-22 15:57:47,2016-08-08 15:14:05,0,0,0,xxx,xxx,xxx,xxx,xxx,,,,,52428800,0,3,2015-11-13 16:08:25,1,0,,0,67)\nPHP message: Call stack (most recent first):\nPHP message: * update_record("usr", object(stdClass), array(size 1)) at /var/www/vhosts/domain.dir/httpdocs/mahara-15.10.1/auth/user.php:415\nPHP message: * User->commit() at /var/www/vhosts/domain.dir/httpdocs/mahara-15.10.1/auth/user.php:1608\nPHP message: * LiveUser->commit() at /var/www/vhosts/domain.dir/httpdocs/mahara-15.10.1/auth/user.php:1695\nPHP message: * LiveUser->authenticate(object(stdClass), "********") at /var/www/vhosts/domain.dir/httpdocs/mahara-15.10.1/auth/user.php:1507\nPHP message: * LiveUser->login("xxx", "********") at /var/www/vhosts/domain.dir/httpdocs/mahara-15.10.1/auth/lib.php:1462\nPHP message: * login_submit(object(Pieform), array(size 6)) at Unknown:0\nPHP message: * call_user_func_array("login_submit", array(size 2)) at /var/www/vhosts/domain.dir/httpdocs/mahara-15.10.1/lib/pieforms/pieform.php:537\nPHP message: * Pieform->__construct(array(size 9)) at /var/www/vhosts/domain.dir/httpdocs/mahara-15.10.1/auth/lib.php:505\nPHP message: * auth_setup() at /var/www/vhosts/domain.dir/httpdocs/mahara-15.10.1/init.php:408\nPHP message: * require("/var/www/vhosts/domain.dir/httpdocs/mah...") at /var/www/vhosts/domain.dir/httpdocs/mahara-15.10.1/index.php:16\nPHP message: \n', referer: https://domain.dir/portfolio/

Does this have anything to do with: The user specified as a definer ('DBUSER'@'%') does not exist?

The DBUSER does exist and it's the user specified in the config.php, it's all good and working.

09 August 2016, 2:50

Solved the problem after digging a bit deeper, on the source server the DB user was allowed to connect from any host so it was 'DBUSER'@'%', on the new server it was created and only allowed to connect from localhost so it was 'DBUSER'@'localhost'.

Changing the DB users config to allow access from any host (even though the server doesn't actually allow this) fixed the problem.

I know it doesn't make any real difference, but is there a way to update this so that on the new server it's using 'DBUSER'@'localhost' and not 'DBUSER'@'%'?

Rich

09 August 2016, 18:07

Hi Richard,

That's really more of a MySQL question rather than a Mahara-specific question, so you may have more luck if you ask on a MySQL advice forum.

That said, I think whether the connection counts as DBUSER@localhost or DBUSER@%, is going to depend on what value you set for $cfg->dbhost in your Mahara site's config.php file. If you set $cfg->dbhost = 'localhost'; or maybe $cfg->dbhost = '127.0.0.1'; then it should be seen as DBUSER@localhost.

Cheers,

Aaron

09 August 2016, 20:47

The dbhost value wasn't making a difference, but I did find the solution.

The easy solution was to just grant the user access from all hosts (because the previous user had been created that way), even with the server only accepting connections from localhost.

The proper solution (and I did this via phpMyAdmin) was to update the Definer for each trigger in the "Triggers" section of phpMyAdmin. I'm sure you can also do it from the mysql command line too.

Either way will work :D