Author Topic: Moving DB tables to Drupal DB causing error on mysqldump (Read 3068 times)

emilyf · July 09, 2009, 05:23:25 pm

This is a bit of a mysql question I think, but I wasn't sure where else to find the answer.

I currently have a Drupal 6 installation in 1 DB and my CiviCRM installation in a separate DB. I need to move the CiviCRM DB tables into the Drupal DB so that I can get Drupal events/CiviEvent working properly. I am using phpmyadmin and copied over all the CiviCRM tables to my Drupal DB. I have a backup script that I run which just performs a mysql dump on the main database. However, it now errors out with:

Code: [Select]

mysqldump: Couldn't execute 'show create table `civicrm_event_udate`': SHOW VIEW command denied to user 'nyscasa_dtest'@'localhost' for table 'civicrm_event_udate' (1142)

When I run 'show create table `civicrm_event_udate`' query directly in phpmyadmin, I get a response that looks like:

Code: [Select]

CREATE ALGORITHM=UNDEFINED DEFINER=`nyscasa`@`loca...

I don't really know what this means, but it certainly looks like it's an issue with a db user value being stored in the mysql view. Is there some way I can reset this?

Much appreciated,
emily

emilyf · July 13, 2009, 04:32:58 am

This issue is still keeping me from being able to migrate my tables to a new database. I dumped just this view from my old civicrm DB, manually went into the file and changed the structure as it was forcing the mysql user into the query itself like:

Code: [Select]

CREATE ALGORITHM=UNDEFINED DEFINER=`nyscasa`@`localhost` SQL SECURITY DEFINER VIEW `nyscasa`.`civicrm_event_udate` AS select `nyscasa`.`civicrm_event`.

so i changed everything to reflect my new db:

Code: [Select]

CREATE ALGORITHM=UNDEFINED DEFINER=`nyscasa_dtest`@`localhost` SQL SECURITY DEFINER VIEW `nyscasa_dtest`.`civicrm_event_udate` AS select `nyscasa_dtest`.`civicrm_event`.

but when i go to import the file, phpmyadmin tells me:
#1227 - Access denied; you need the SUPER privilege for this operation
and shell tells me:
ERROR 1142 (42000) at line 28: CREATE VIEW command denied to user 'nyscasa_dtest'@'localhost' for table 'civicrm_event_udate'

However, i can log in to mysql just fine with this user. what is going on with this view? how can i get it into my db properly so that it also exports properly?

emilyf · July 13, 2009, 04:44:41 am

hmmmm, maybe this is my webhost with a create view permissions issue. i'll check on that and post back.

emilyf · July 13, 2009, 05:20:59 am

yes, indeed. it appears that the civicrm db was originally created somewhere else, then moved to a shared hosting where this priv is not available.

can someone tell me exactly what View: civicrm_event_udate does? because i can't use it at all without getting a permissions error, and i am surprised that this client has been operating civicrm so long without seeing an error. input much appreciated,
emily

Donald Lobo · July 13, 2009, 08:42:22 am

civicrm does not use db views

(we do use it for multi-lingual, but i suspect your site is not a multi-lingual site)

so the previous developers on that site might have introduced this view to simplify some of their code?

lobo

emilyf · July 13, 2009, 09:01:40 am

thanks

CiviCRM Community Forums (archive)

News:

Author Topic: Moving DB tables to Drupal DB causing error on mysqldump (Read 3068 times)

emilyf

Moving DB tables to Drupal DB causing error on mysqldump

emilyf

Re: Moving DB tables to Drupal DB causing error on mysqldump

emilyf

Re: Moving DB tables to Drupal DB causing error on mysqldump

emilyf

Re: Moving DB tables to Drupal DB causing error on mysqldump

Donald Lobo

Re: Moving DB tables to Drupal DB causing error on mysqldump

emilyf

Re: Moving DB tables to Drupal DB causing error on mysqldump