CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site
This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Installing CiviCRM »
  • Drupal Installations (Moderator: Piotr Szotkowski) »
  • development server to Staging server civicrm database insert failing
Pages: [1]

Author Topic: development server to Staging server civicrm database insert failing  (Read 741 times)

cfusch

  • I post occasionally
  • **
  • Posts: 44
  • Karma: 1
  • CiviCRM version: 4.6.6
  • CMS version: Drupal 7.39
  • MySQL version: 5.6.23
  • PHP version: 5.4.38
development server to Staging server civicrm database insert failing
January 09, 2014, 04:23:54 pm
I bet this is a commonly successful, but given I'm new to moving civicrm off the development server and onto a staging server, I'm having a problem.

On my development server the CMS is Drupal 7.  D7 and CiviCRM are latest versions.  I'm at a juncture in development where I need to move to a staging server so my client can start participating.

Development is on my desktop.
Staging is on a Hostgator VPS and the database has a PREFIX_

development root name is MyDev.org
Staging root name is MyDev.net (different from development ... fixed in config files)

Development civicrm user is civiuser
Staging  civicrm user is PREFIX_civiuser  (also fixed in the settings config files)

PROBLEM: When trying to import the civicrm database to my staging server, the import fails with something like this error: "civicrm database import error !50017. I've tried to import form both the command line and from PHPmyAdmin with the same response.  I seems that my development url, civicrm username are embedded in the database and somehow make mysql think they are still the owners, db name, and user.  Which I"m not sure how that happens.

I tried to first make all of the appropriate changes to civicrm.settings.php (and settings.php) first, but obviously that didn't have any effect upon trying again.  I also tried to "ignore" on the mysqldump the cache tables as listed in "Moving an Existing Installation to a New Server or Location" by Lola Slade on the CiviCRM website, but continuing to get the error.

I've missed something in the process because this is, surely, done thousands of times by other developers.

What am I doing wrong??? 

Carl

Hershel

  • Forum Godess / God
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4640
  • Karma: 176
    • CiviHosting
  • CiviCRM version: Latest
  • CMS version: Mostly WordPress and Drupal
Re: development server to Staging server civicrm database insert failing
January 10, 2014, 03:53:04 am
Open the dump in an editor and search for "DEFINER" and you will probably see what the problem is.
CiviHosting and CiviOnline -- The CiviCRM hosting experts, since 2007

See here for the official: What to do if you think you've found a bug.

cfusch

  • I post occasionally
  • **
  • Posts: 44
  • Karma: 1
  • CiviCRM version: 4.6.6
  • CMS version: Drupal 7.39
  • MySQL version: 5.6.23
  • PHP version: 5.4.38
Re: development server to Staging server civicrm database insert failing
January 10, 2014, 07:22:24 am
Hershel, Thanks for your swift response. 

I'm guessing from your comment that I need to modify any string following "DEFINER" to concur with the new location url, user, dbname, etc. AND then try to import.  Right?? 

- Carl
« Last Edit: January 10, 2014, 07:24:50 am by cfusch »

cfusch

  • I post occasionally
  • **
  • Posts: 44
  • Karma: 1
  • CiviCRM version: 4.6.6
  • CMS version: Drupal 7.39
  • MySQL version: 5.6.23
  • PHP version: 5.4.38
Re: development server to Staging server civicrm database insert failing
January 10, 2014, 02:55:16 pm
Hershel, I'm still not getting a successful import ...

I changed all occurrences of the old user name to the new user name which has all permissions except GRANT. And still getting an error on the import of the *.sql file.  Tried both PHPmyadmin and command line.  Was there something other than just the user name???  Here's the error from the command line: (PHPmyadmin error is below this one)

ERROR 1227 (42000) at line 424: Access denied; you need the SUPER privilege for this operation

NOTE: Does this mean that I need to login to my VPS WHM to do an import???  Never had to do that with Drupal or anything else ... what am I missing?

Here's the error coming from the PHPmyadmin:

Error

SQL query:

DELIMITER ; ;

/*!50003 CREATE*//*!50017 DEFINER=`navysail_user`@`localhost`*//*!50003 TRIGGER civicrm_address_after_insert after insert ON civicrm_address FOR EACH ROW BEGIN UPDATE civicrm_contact SET modified_date = CURRENT_TIMESTAMP WHERE id = NEW.contact_id; END */ ; ;

MySQL said: Documentation
#1227 - Access denied; you need the SUPER privilege for this operation

Regards,

Carl

cfusch

  • I post occasionally
  • **
  • Posts: 44
  • Karma: 1
  • CiviCRM version: 4.6.6
  • CMS version: Drupal 7.39
  • MySQL version: 5.6.23
  • PHP version: 5.4.38
SOLVED Re: development server to Staging server civicrm database insert failing
January 10, 2014, 05:06:33 pm
SOLVED!  Thanks to the forum, it was an easy matter to fix.  Make sure that logging is turned OFF in CiviCRM: "Administration > System Settings > MIsc"    AND  then do the export from the original website using --skip-triggers on the mysqldump.  That did it.   ... of course following by making adjustments in the settings files for the new db and user names.

Thanks,

Carl
Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Installing CiviCRM »
  • Drupal Installations (Moderator: Piotr Szotkowski) »
  • development server to Staging server civicrm database insert failing

This forum was archived on 2017-11-26.