Moving CiviCRM to a new location

[SarahG (FountainTribe)]

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?

[Dave Greenberg]

Don't really know BUT ... I assume you DO have SUPER privilege on the DB you're restoring to. If so, maybe just search and replace the old db name w/ new db name in the dump file. If that seems hack-ish or doesn't work - might check for doc on dumping / restoring DB's that have VIEWS in them.

[podfish]

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

[Dave Greenberg]

Did some snooping and it's not clear to me that you can create views w/o a definer. I did find some stackexchange posts on removing definer for mysql dumps (to handle migration problems).

Perhaps you can investigate what's possible and submit a patch.

[e_mason]

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