CiviCRM Community Forums (archive)

I am trying to move a CiviCRM version 4.1.1 environment to a new location.  I have CiviCRM running in a separate DB than Drupal.   I used PhpMyAdmin to create an SQLDUMP file of the CiviCRM database.

However, I noticed 2 statements in the dump file that are failing when I try to restore it:
--
-- Structure for view `civicrm_view_case_activity_recent`
--
DROP TABLE IF EXISTS `civicrm_view_case_activity_recent`;

CREATE ALGORITHM=UNDEFINED DEFINER=`aleph_crm`@`localhost` SQL SECURITY DEFINER VIEW `civicrm_view_case_activity_recent` AS select `ca`.`case_id` AS `case_id`,`a`.`id` AS `id`,`a`.`activity_date_time` AS `activity_date_time`,`a`.`status_id` AS `status_id`,`a`.`activity_type_id` AS `activity_type_id` from (`civicrm_case_activity` `ca` join `civicrm_activity` `a` on((`ca`.`activity_id` = `a`.`id`))) where ((`a`.`activity_date_time` <= now()) and (`a`.`activity_date_time` >= (now() - interval 14 day)) and (`a`.`is_current_revision` = 1) and (`a`.`is_deleted` = 0) and (`a`.`status_id` <> 1));

-- --------------------------------------------------------

--
-- Structure for view `civicrm_view_case_activity_upcoming`
--
DROP TABLE IF EXISTS `civicrm_view_case_activity_upcoming`;

CREATE ALGORITHM=UNDEFINED DEFINER=`aleph_crm`@`localhost` SQL SECURITY DEFINER VIEW `civicrm_view_case_activity_upcoming` AS select `ca`.`case_id` AS `case_id`,`a`.`id` AS `id`,`a`.`activity_date_time` AS `activity_date_time`,`a`.`status_id` AS `status_id`,`a`.`activity_type_id` AS `activity_type_id` from (`civicrm_case_activity` `ca` join `civicrm_activity` `a` on((`ca`.`activity_id` = `a`.`id`))) where ((`a`.`activity_date_time` <= (now() + interval 14 day)) and (`a`.`is_current_revision` = 1) and (`a`.`is_deleted` = 0) and (`a`.`status_id` = 1));

--
-- Constraints for dumped tables



----------------------------
It seems that "DEFINER=`aleph_crm`" is refering to the db user name on the old server.


The error message I get on restore is:
#1227 - Access denied; you need the SUPER privilege for this operation


Any ideas?

FYI, folks, this is still necessary.  Would it make sense to not create the view with the definer to begin with? It's problematic, especially when the view definition itself is a little obscure--if the definer is incorrect, you can't even run a little query to determine what the view is, because the user doesn't exist at all.

It makes migration a lot more painful than it needs to be for less-technical users.

Nice to meet you at CiviCon SF, Dave!  I do work for a vets nonprofit up here in Tacoma, Washington.

Cheers,

SK

I think the solution varies by the environment that you're going into, but also just outright removing the DEFINER= statement altogether should work.