KB282329: "The user specified as a definer ('XXX') does not exist" error appears when configuring MicroStrategy Intelligence Server 10.1 to MySQL metadata

• Strategy

"The user specified as a definer ('XXX') does not exist" error appears when configuring MicroStrategy Intelligence Server 10.1 to MySQL metadata

SUMMARY:
This document shows one issue that "The user specified as a definer ('XXX') does not exist" error appears when configuring Intelligence Server to MySQL metadata which was migrated to new DB server with MySQL dump. This is because the trigger definer in source system doesn't exist in target. Recreating the trigger in target should resolve the issue.

SYMPTOM:
"The user specified as a definer ('XXX') does not exist" error appears when configuring Strategy Intelligence Server 10.1 to MySQL metadata which was migrated to new DB server with MySQL dump. The DSSErrors.log is as below:


2016-02-15 06:05:28.075 [HOST:MGALLEGOS101][PID:3556][THR:4684][Database Classes][Error] Execute Query failed. 
Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [HY000:1449: on SQLHANDLE] [MySQL][ODBC 5.3(w) Driver][mysqld-5.6.10-enterprise-commercial-advanced]The user specified as a definer ('testuser'@'localhost') does not exist Connection String: DSN=TestDSN;UID=TestDBuser;. SQL Statement: DELETE FROM DSSMDOBJINFO WHERE OBJECT_ID='0154FB3F43BAB095882A628DE63CFFG3' AND PROJECT_ID='7F76FF9143BJTEB70819608FF56KJ08A'.

CAUSE:
When migrating or deploying metadata using mysql dump, the user (the definer) that created the VIEW, TRIGGER or PROCEDURE may not be the same on the target system. Recreating the VIEW, TRIGGER or PROCEDURE with a valid user on the target system should resolve the issue. In this case, the user appearing in error message created one trigger in MD in source system while this user doesn't exist in target system.

ACTION:
Recreate trigger in target system by following steps below:
1. Backup metadata.
2. Connect to metadata in Strategy DB Query Tool in Intelligence Server machine.
3. Run the following SQL statements to recreate trigger:


DROP TRIGGER DSSMDOBJTRIG;
CREATE TRIGGER DSSMDOBJTRIG BEFORE DELETE ON DSSMDOBJINFO FOR EACH ROW
BEGIN
 DELETE FROM DSSMDOBJDEFN WHERE
 DSSMDOBJDEFN.PROJECT_ID = OLD.PROJECT_ID AND
 DSSMDOBJDEFN.OBJECT_ID = OLD.OBJECT_ID;
 DELETE FROM DSSMDOBJDEF2 WHERE
 DSSMDOBJDEF2.PROJECT_ID = OLD.PROJECT_ID AND
 DSSMDOBJDEF2.OBJECT_ID = OLD.OBJECT_ID;
 DELETE FROM DSSMDOBJBLOB WHERE
 DSSMDOBJBLOB.PROJECT_ID = OLD.PROJECT_ID AND
 DSSMDOBJBLOB.OBJECT_ID = OLD.OBJECT_ID;
 DELETE FROM DSSMDOBJDEPN WHERE
 DSSMDOBJDEPN.PROJECT_ID = OLD.PROJECT_ID AND
 DSSMDOBJDEPN.OBJECT_ID = OLD.OBJECT_ID;
 DELETE FROM DSSMDOBJPROP WHERE
 DSSMDOBJPROP.PROJECT_ID = OLD.PROJECT_ID AND
 DSSMDOBJPROP.OBJECT_ID = OLD.OBJECT_ID;
 DELETE FROM DSSMDOBJCMNT WHERE
 DSSMDOBJCMNT.PROJECT_ID = OLD.PROJECT_ID AND
 DSSMDOBJCMNT.OBJECT_ID = OLD.OBJECT_ID;
 DELETE FROM DSSMDOBJSECU WHERE
 DSSMDOBJSECU.PROJECT_ID = OLD.PROJECT_ID AND
 DSSMDOBJSECU.OBJECT_ID = OLD.OBJECT_ID;
 DELETE FROM DSSMDUSRACCT WHERE
 DSSMDUSRACCT.PROJECT_ID = OLD.PROJECT_ID AND
 DSSMDUSRACCT.OBJECT_ID = OLD.OBJECT_ID;
 DELETE FROM DSSMDOBJTRNS WHERE
 DSSMDOBJTRNS.PROJECT_ID = OLD.PROJECT_ID AND
 DSSMDOBJTRNS.OBJECT_ID = OLD.OBJECT_ID;
 DELETE FROM DSSMDOBJLOCK WHERE
 DSSMDOBJLOCK.PROJECT_ID = OLD.PROJECT_ID AND
 DSSMDOBJLOCK.OBJECT_ID = OLD.OBJECT_ID; 
END;

Comment

0 comments

Details

Knowledge Article

Published:

April 13, 2017

Last Updated:

April 13, 2017

2016-02-15 06:05:28.075 [HOST:MGALLEGOS101][PID:3556][THR:4684][Database Classes][Error] Execute Query failed. Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [HY000:1449: on SQLHANDLE] [MySQL][ODBC 5.3(w) Driver][mysqld-5.6.10-enterprise-commercial-advanced]The user specified as a definer ('testuser'@'localhost') does not exist Connection String: DSN=TestDSN;UID=TestDBuser;. SQL Statement: DELETE FROM DSSMDOBJINFO WHERE OBJECT_ID='0154FB3F43BAB095882A628DE63CFFG3' AND PROJECT_ID='7F76FF9143BJTEB70819608FF56KJ08A'.

DROP TRIGGER DSSMDOBJTRIG; CREATE TRIGGER DSSMDOBJTRIG BEFORE DELETE ON DSSMDOBJINFO FOR EACH ROW BEGIN DELETE FROM DSSMDOBJDEFN WHERE DSSMDOBJDEFN.PROJECT_ID = OLD.PROJECT_ID AND DSSMDOBJDEFN.OBJECT_ID = OLD.OBJECT_ID; DELETE FROM DSSMDOBJDEF2 WHERE DSSMDOBJDEF2.PROJECT_ID = OLD.PROJECT_ID AND DSSMDOBJDEF2.OBJECT_ID = OLD.OBJECT_ID; DELETE FROM DSSMDOBJBLOB WHERE DSSMDOBJBLOB.PROJECT_ID = OLD.PROJECT_ID AND DSSMDOBJBLOB.OBJECT_ID = OLD.OBJECT_ID; DELETE FROM DSSMDOBJDEPN WHERE DSSMDOBJDEPN.PROJECT_ID = OLD.PROJECT_ID AND DSSMDOBJDEPN.OBJECT_ID = OLD.OBJECT_ID; DELETE FROM DSSMDOBJPROP WHERE DSSMDOBJPROP.PROJECT_ID = OLD.PROJECT_ID AND DSSMDOBJPROP.OBJECT_ID = OLD.OBJECT_ID; DELETE FROM DSSMDOBJCMNT WHERE DSSMDOBJCMNT.PROJECT_ID = OLD.PROJECT_ID AND DSSMDOBJCMNT.OBJECT_ID = OLD.OBJECT_ID; DELETE FROM DSSMDOBJSECU WHERE DSSMDOBJSECU.PROJECT_ID = OLD.PROJECT_ID AND DSSMDOBJSECU.OBJECT_ID = OLD.OBJECT_ID; DELETE FROM DSSMDUSRACCT WHERE DSSMDUSRACCT.PROJECT_ID = OLD.PROJECT_ID AND DSSMDUSRACCT.OBJECT_ID = OLD.OBJECT_ID; DELETE FROM DSSMDOBJTRNS WHERE DSSMDOBJTRNS.PROJECT_ID = OLD.PROJECT_ID AND DSSMDOBJTRNS.OBJECT_ID = OLD.OBJECT_ID; DELETE FROM DSSMDOBJLOCK WHERE DSSMDOBJLOCK.PROJECT_ID = OLD.PROJECT_ID AND DSSMDOBJLOCK.OBJECT_ID = OLD.OBJECT_ID; END;