Unexpected Error of general user after server reinstall and DB restore

Submitted by komoon on 2014-05-01

Dear,

After server reinstall and db restore from dump, general permission user report "Unexpected error" when log-in.
But admin account or system permission user logged in, everything is good. All of issue article and attached files, reply of project works normally.

Somebody knows how to solve this problem? Helps will be appreciated!

debug log (default.log)
---------------------------------
[2014-05-01 08:12:04, ] //user login

Running script: /index.php
SQL Query: SELECT server_name, server_uuid, db_version FROM server
Query execution time: 0.0 ms
Returned rows: 1
SQL Query: SELECT set_key, set_value FROM settings
Query execution time: 0.0 ms
Returned rows: 37
Executing page: Index
Begin Transaction
SQL Query: SELECT user_id, user_name, user_passwd, user_access, passwd_temp FROM users WHERE user_login = ? AND user_access > ?
Query execution time: 0.0 ms
Returned rows: 1
Commit Transaction
SQL Query: SELECT s.session_id, s.session_data, s.last_access, u.user_id, u.user_name, u.user_access FROM sessions AS s JOIN users AS u ON u.user_id = s.user_id WHERE s.session_id = ?
Query execution time: 0.0 ms
Returned rows: 0
SQL Execute: INSERT INTO log_events ( event_type, event_severity, event_message, event_time, user_id, host_name ) VALUES ( ?, ?, ?, ?, ?, ? )
Query execution time: 31.2 ms
Affected rows: 1
SQL Execute: INSERT INTO sessions ( session_id, user_id, session_data, last_access ) VALUES ( ?, ?, ?, ? )
Query execution time: 62.5 ms
Affected rows: 1
Total execution time: 171.9 ms

[2014-05-01 08:12:04, ] //logged in but unexpected error

Running script: /client/index.php
SQL Query: SELECT server_name, server_uuid, db_version FROM server
Query execution time: 0.0 ms
Returned rows: 1
SQL Query: SELECT set_key, set_value FROM settings
Query execution time: 0.0 ms
Returned rows: 37
SQL Query: SELECT s.session_id, s.session_data, s.last_access, u.user_id, u.user_name, u.user_access FROM sessions AS s JOIN users AS u ON u.user_id = s.user_id WHERE s.session_id = ?
Query execution time: 15.6 ms
Returned rows: 1
SQL Query: SELECT pref_key, pref_value FROM preferences WHERE user_id = ?
Query execution time: 0.0 ms
Returned rows: 1
Executing page: Client_Index
SQL Query: SELECT COUNT(*) FROM projects AS p JOIN effective_rights AS r ON r.project_id = p.project_id AND r.user_id = ?
*** Unhandled System_Db_Exception: in C:\xampp\htdocs\webissues\system\db\mysqli\engine.inc.php on line 287
Stack trace:
#0 C:\xampp\htdocs\webissues\system\db\mysqli\engine.inc.php(110): System_Db_Mysqli_Engine->handleError(Object(mysqli))
#1 C:\xampp\htdocs\webissues\system\db\mysqli\engine.inc.php(90): System_Db_Mysqli_Engine->executeStatement('SELECT COUNT(*)...', Array)
#2 C:\xampp\htdocs\webissues\system\db\connection.inc.php(256): System_Db_Mysqli_Engine->query('SELECT COUNT(*)...', Array)
#3 C:\xampp\htdocs\webissues\system\db\connection.inc.php(356): System_Db_Connection->queryArgs('SELECT COUNT(*)...', Array)
#4 C:\xampp\htdocs\webissues\system\db\connection.inc.php(343): System_Db_Connection->queryScalarArgs('SELECT COUNT(*)...', Array)
#5 C:\xampp\htdocs\webissues\system\api\projectmanager.inc.php(632): System_Db_Connection->queryScalar('SELECT COUNT(*)...', 4, 2)
#6 C:\xampp\htdocs\webissues\client\projectstree.inc.php(63): System_Api_ProjectManager->getProjectsCount()
#7 C:\xampp\htdocs\webissues\system\web\component.inc.php(100): Client_ProjectsTree->execute()
#8 C:\xampp\htdocs\webissues\system\web\view.inc.php(247): System_Web_Component->run()
#9 C:\xampp\htdocs\webissues\client\index.html.php(22): System_Web_View->insertComponent('Client_Projects...')
#10 C:\xampp\htdocs\webissues\system\web\view.inc.php(284): include('C:\xampp\htdocs...')
#11 C:\xampp\htdocs\webissues\system\web\view.inc.php(217): System_Web_View->executeTemplate()
#12 C:\xampp\htdocs\webissues\system\web\component.inc.php(102): System_Web_View->render()
#13 C:\xampp\htdocs\webissues\system\web\application.inc.php(77): System_Web_Component->run()
#14 C:\xampp\htdocs\webissues\system\core\application.inc.php(210): System_Web_Application->execute()
#15 C:\xampp\htdocs\webissues\system\bootstrap.inc.php(130): System_Core_Application->run()
#16 C:\xampp\htdocs\webissues\client\index.php(61): System_Bootstrap::run('Common_Applicat...', 'Client_Index')
#17 {main}
SQL Execute: INSERT INTO log_events ( event_type, event_severity, event_message, event_time, user_id, host_name ) VALUES ( ?, ?, ?, ?, ?, ? )
Query execution time: 46.9 ms
Affected rows: 1
Total execution time: 109.4 ms

[2014-05-01 08:12:14,] //user log out

Solved!

If you are on same situation, please refer to

webissues.mimec.org/node/656

The problem comes from "effective_rights" view. when DB has restored, that view got problem.
To fix it, delete view "effective_rights" first,
then create manually using SQL.

In my case, I migrated data from host 1 to host 2 with a different $config['db_user'] name, DEFINER for effective_rights keeps the value from host 1 and which doesn't exist in host 2:

/*!50001 DROP TABLE IF EXISTS `effective_rights`*/;
/*!50001 DROP VIEW IF EXISTS `effective_rights`*/;
....
/*!50013 DEFINER=`[db_user_name_here]`@`localhost` SQL SECURITY DEFINER */
....

So at least make sure [db_user_name_here] is $config['db_user'] value in host 2 before importing. effective_rights is not checked for admin who has visibility on all projects/issues, therefore Unexpected error happens only to Regular users.

Edit: alternatively, you can change the above line to:
/*!50013 DEFINER=CURRENT_USER SQL SECURITY DEFINER */
before importing.