Nice, I did something similar to work this problem around on my backup routine.
But, instead of removing the definer statement I replaced it with DEFINER=CURRENT_USER 2013/10/2 Indra Pramana <in...@sg.or.id> > Hi Rafael, > > I managed to resolve the problem by removing all the DEFINER statements > from the dump .sql file. > > sed '/^\/\*\!50013 DEFINER/d' cloudstack-backup-2013100201-fix.sql > > cloudstack-backup-2013100201-fix-without-definer.sql > > I then restored the cloudstack-backup-2013100201-fix-without-definer.sql > into the database and the upgrade works. > > Thank you for your help. > > Cheers. > > > > On Wed, Oct 2, 2013 at 9:38 PM, Rafael Weingartner < > rafaelweingart...@gmail.com> wrote: > > > Could you log in to the database with the user you are using to execute > the > > upgrade and use the command show grants; > > and post me the results? > > > > > > 2013/10/2 Indra Pramana <in...@sg.or.id> > > > > > Hi Rafael, > > > > > > I believe the upgrade script is run by 'cloud' user, correct? By > default > > > full permission to the 'cloud' user has been given, and I even added > > > 'super' privilege to the cloud user but the problem still persists. > > > > > > May I know how to change the DEFINER statement? Which statement you are > > > referring to? > > > > > > Looking forward to your reply, thank you. > > > > > > Cheers. > > > > > > > > > On Wed, Oct 2, 2013 at 9:13 PM, Rafael Weingartner < > > > rafaelweingart...@gmail.com> wrote: > > > > > > > Well, I was getting this error because I was using an Amazon RDS > > > database, > > > > and there the users, even the master do not have super privileges. > So, > > I > > > > could not create a table logged with a user (e.g. root) and using > > > > DEFINER=cloud@'%'. > > > > > > > > But, since you have full access to the database, I do believe that > you > > > just > > > > need to give grant permission to the user you are using to run the > > > queries, > > > > or simple try to change the DEFINER statement to something like: > > > > DEFINER=CURRENT_USER > > > > > > > > > > > > 2013/10/2 Indra Pramana <in...@sg.or.id> > > > > > > > > > Hi Rafael, > > > > > > > > > > May I know how did you resolve the problem? Do we need to manually > > > grant > > > > > super privilege to an account, e.g. 'cloud'@'%'? > > > > > > > > > > Looking forward to your reply, thank you. > > > > > > > > > > Cheers. > > > > > > > > > > > > > > > > > > > > On Wed, Oct 2, 2013 at 8:56 PM, Rafael Weingartner < > > > > > rafaelweingart...@gmail.com> wrote: > > > > > > > > > > > I had the exactly same problem when I was backing up my CS > database > > > on > > > > a > > > > > > Amazon RDS. > > > > > > There was a problem with some DEFINER command generated by the > > > > mysqldump. > > > > > > > > > > > > > > > > > > 2013/10/2 Indra Pramana <in...@sg.or.id> > > > > > > > > > > > > > Hi Rafael, > > > > > > > > > > > > > > What I did is just to setup a new CloudStack 4.1.1 on a test > VM, > > > > > restore > > > > > > > the dump backup of our production database into the test > > > environment, > > > > > and > > > > > > > try to upgrade to 4.2.0. > > > > > > > > > > > > > > Could it be because the database was taken from a different > > > > > environment? > > > > > > > However, I tested accessing the GUI of the test environment > after > > > the > > > > > > dump > > > > > > > restore and I can access it, prior to the upgrade. > > > > > > > > > > > > > > Looking forward to your reply, thank you. > > > > > > > > > > > > > > Cheers. > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > On Wed, Oct 2, 2013 at 8:42 PM, Rafael Weingartner < > > > > > > > rafaelweingart...@gmail.com> wrote: > > > > > > > > > > > > > > > Interesting errors, just by any change are you using the > > database > > > > on > > > > > > > Amazon > > > > > > > > RDS? > > > > > > > > > > > > > > > > > > > > > > > > 2013/10/2 Indra Pramana <in...@sg.or.id> > > > > > > > > > > > > > > > > > Dear all, > > > > > > > > > > > > > > > > > > I tried to simulate upgrading the database from Cloudstack > > > 4.1.1 > > > > to > > > > > > > 4.2.0 > > > > > > > > > on my test environment and getting below error message: > > > > > > > > > > > > > > > > > > MySQLSyntaxErrorException: Access denied; you need (at > least > > > one > > > > > of) > > > > > > > the > > > > > > > > > SUPER privilege(s) for this operation > > > > > > > > > > > > > > > > > > Anyone can advise what super privilege is required? The > > > database > > > > > > worked > > > > > > > > > fine on 4.1.1 prior to the upgrade. > > > > > > > > > > > > > > > > > > Below is more logs from the management-server.log: > > > > > > > > > > > > > > > > > > ==== > > > > > > > > > 2013-10-02 20:33:05,876 DEBUG [utils.db.ScriptRunner] > > > > > (Timer-1:null) > > > > > > > > UPDATE > > > > > > > > > `cloud`.`account` SET `cloud`.`account`.`default`=1 WHERE > id > > IN > > > > > (1,2) > > > > > > > > > 2013-10-02 20:33:05,913 DEBUG [utils.db.ScriptRunner] > > > > > (Timer-1:null) > > > > > > > > UPDATE > > > > > > > > > `cloud_usage`.`account` SET `default`=1 WHERE id IN (1,2) > > > > > > > > > 2013-10-02 20:33:05,914 DEBUG [utils.db.ScriptRunner] > > > > > (Timer-1:null) > > > > > > > > UPDATE > > > > > > > > > `cloud`.`user` SET `cloud`.`user`.`default`=1 WHERE id IN > > (1,2) > > > > > > > > > 2013-10-02 20:33:05,915 DEBUG [utils.db.ScriptRunner] > > > > > (Timer-1:null) > > > > > > > > ALTER > > > > > > > > > VIEW `cloud`.`user_view` AS select user.id, > > > > > > > > > user.uuid, user.username, user.password, > > > > > > > > > user.firstname, user.lastname, user.email, > > > > > > > > > user.state, user.api_key, user.secret_key, > > > > > > > > > user.created, user.removed, user.timezone, > > > > > > > > > user.registration_token, user.is_registered, > > > > > > > > > user.incorrect_login_attempts, user.default, > > > > > > > > > account.idaccount_id, account.uuid account_uuid, > > > > > > > > > account.account_name > > > > > > > > > account_name, account.type account_type, > > > > > > > > > domain.iddomain_id, domain.uuid domain_uuid, > > > > > > > > > domain.name domain_name, domain.path domain_path, > > > > > > > > > async_job.id job_id, async_job.uuid job_uuid, > > > > > > > > > async_job.job_status job_status, > async_job.account_id > > > > > > > > > job_account_id from `cloud`.`user` > > > inner > > > > > > > > > join `cloud`.`account` ON user.account_id = > > account.id > > > > > > > > > inner join `cloud`.`domain` ON account.domain_id = > > > > > domain.id > > > > > > > > > left join `cloud`.`async_job` ON > > async_job.instance_id > > > = > > > > > > > > > user.id > > > > > > > > > and async_job.instance_type = 'User' and > > > > > > > > async_job.job_status = > > > > > > > > > 0 > > > > > > > > > 2013-10-02 20:33:06,007 ERROR [utils.db.ScriptRunner] > > > > > (Timer-1:null) > > > > > > > > Error > > > > > > > > > executing: ALTER VIEW `cloud`.`user_view` AS select > > > > > > > > > user.id, > > > > > > > > > user.uuid, user.username, user.password, > > > > > > > > > user.firstname, user.lastname, user.email, > > > > > > > > > user.state, user.api_key, user.secret_key, > > > > > > > > > user.created, user.removed, user.timezone, > > > > > > > > > user.registration_token, user.is_registered, > > > > > > > > > user.incorrect_login_attempts, user.default, > > > > > > > > > account.idaccount_id, account.uuid account_uuid, > > > > > > > > > account.account_name > > > > > > > > > account_name, account.type account_type, > > > > > > > > > domain.iddomain_id, domain.uuid domain_uuid, > > > > > > > > > domain.name domain_name, domain.path domain_path, > > > > > > > > > async_job.id job_id, async_job.uuid job_uuid, > > > > > > > > > async_job.job_status job_status, > async_job.account_id > > > > > > > > > job_account_id from `cloud`.`user` > > > inner > > > > > > > > > join `cloud`.`account` ON user.account_id = > > account.id > > > > > > > > > inner join `cloud`.`domain` ON account.domain_id = > > > > > domain.id > > > > > > > > > left join `cloud`.`async_job` ON > > async_job.instance_id > > > = > > > > > > > > > user.id > > > > > > > > > and async_job.instance_type = 'User' and > > > > > > > > async_job.job_status = > > > > > > > > > 0 > > > > > > > > > 2013-10-02 20:33:06,007 ERROR [utils.db.ScriptRunner] > > > > > (Timer-1:null) > > > > > > > > > com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: > > > Access > > > > > > > denied; > > > > > > > > > you need (at least one of) the SUPER privilege(s) for this > > > > > operation > > > > > > > > > 2013-10-02 20:33:06,009 ERROR > > > > > [cloud.upgrade.DatabaseUpgradeChecker] > > > > > > > > > (Timer-1:null) Unable to execute upgrade script: > > > > > > > > > > /usr/share/cloudstack-management/setup/db/schema-410to420.sql > > > > > > > > > com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: > > > Access > > > > > > > denied; > > > > > > > > > you need (at least one of) the SUPER privilege(s) for this > > > > > operation > > > > > > > > > at > > > > > > > > > > com.cloud.utils.db.ScriptRunner.runScript(ScriptRunner.java:193) > > > > > > > > > at > > > > > > > > > com.cloud.utils.db.ScriptRunner.runScript(ScriptRunner.java:87) > > > > > > > > > at > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > com.cloud.upgrade.DatabaseUpgradeChecker.runScript(DatabaseUpgradeChecker.java:201) > > > > > > > > > at > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > com.cloud.upgrade.DatabaseUpgradeChecker.upgrade(DatabaseUpgradeChecker.java:262) > > > > > > > > > at > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > com.cloud.upgrade.DatabaseUpgradeChecker.check(DatabaseUpgradeChecker.java:391) > > > > > > > > > at > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > com.cloud.utils.component.ComponentContext.initComponentsLifeCycle(ComponentContext.java:90) > > > > > > > > > at > > > > > > > > > > > > > > > > > > > > > > > > > com.cloud.servlet.CloudStartupServlet$1.run(CloudStartupServlet.java:54) > > > > > > > > > at java.util.TimerThread.mainLoop(Timer.java:534) > > > > > > > > > at java.util.TimerThread.run(Timer.java:484) > > > > > > > > > 2013-10-02 20:33:06,011 ERROR > > > > > [cloud.upgrade.DatabaseUpgradeChecker] > > > > > > > > > (Timer-1:null) Unable to upgrade the database > > > > > > > > > com.cloud.utils.exception.CloudRuntimeException: Unable to > > > > execute > > > > > > > > upgrade > > > > > > > > > script: > > > > > /usr/share/cloudstack-management/setup/db/schema-410to420.sql > > > > > > > > > at > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > com.cloud.upgrade.DatabaseUpgradeChecker.runScript(DatabaseUpgradeChecker.java:210) > > > > > > > > > at > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > com.cloud.upgrade.DatabaseUpgradeChecker.upgrade(DatabaseUpgradeChecker.java:262) > > > > > > > > > at > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > com.cloud.upgrade.DatabaseUpgradeChecker.check(DatabaseUpgradeChecker.java:391) > > > > > > > > > at > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > com.cloud.utils.component.ComponentContext.initComponentsLifeCycle(ComponentContext.java:90) > > > > > > > > > at > > > > > > > > > > > > > > > > > > > > > > > > > com.cloud.servlet.CloudStartupServlet$1.run(CloudStartupServlet.java:54) > > > > > > > > > at java.util.TimerThread.mainLoop(Timer.java:534) > > > > > > > > > at java.util.TimerThread.run(Timer.java:484) > > > > > > > > > Caused by: > > > > > com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: > > > > > > > > > Access denied; you need (at least one of) the SUPER > > > privilege(s) > > > > > for > > > > > > > this > > > > > > > > > operation > > > > > > > > > at > > > > > > > > > > com.cloud.utils.db.ScriptRunner.runScript(ScriptRunner.java:193) > > > > > > > > > at > > > > > > > > > com.cloud.utils.db.ScriptRunner.runScript(ScriptRunner.java:87) > > > > > > > > > at > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > com.cloud.upgrade.DatabaseUpgradeChecker.runScript(DatabaseUpgradeChecker.java:201) > > > > > > > > > ... 6 more > > > > > > > > > === > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > -- > > > > > > > > Rafael Weingartner > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > -- > > > > > > Rafael Weingartner > > > > > > > > > > > > > > > > > > > > > > > > > > > -- > > > > Rafael Weingartner > > > > > > > > > > > > > > > -- > > Rafael Weingartner > > > -- Rafael Weingartner