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 »
  • Using CiviCRM »
  • Post-installation Setup and Configuration (Moderator: Dave Greenberg) »
  • Cannot delete DB user and have CiviCRM function
Pages: [1]

Author Topic: Cannot delete DB user and have CiviCRM function  (Read 397 times)

groston

  • I post occasionally
  • **
  • Posts: 61
  • Karma: 0
  • CiviCRM version: 4.5.5
  • CMS version: Drupal 7.34
  • MySQL version: 5.5.34
  • PHP version: 5.3.27
Cannot delete DB user and have CiviCRM function
February 04, 2015, 07:24:32 pm
When I initially installed CiviCRM, I created a DB user associated with the application (call it civiUser). What I would like to do is to eliminate said DB user and use the same DB user as I use for Drupal (call it drupUser). I changed the DB credentials in civicrm.settings.php and deleted civiUser from the DB; and thought that by doing so, I would achieve my objective.

Sadly not.

When a user attempts to create a new account or pay for membership, the action fails (shows the user a 'DB Error: unknown error' message) and the following error message is logged to the database: The user specified as a definer ('civiUser'@'localhost') does not exist].

Based on some research, there are two ways to resolve this: 1) recreate the user and 2) log into the database with root privilege and run the following query:

UPDATE `mysql`.`proc` p SET definer = 'drupUser@localhost' WHERE definer='civiUser@localhost' AND db='myDB';

I recreated civiUser and the problem went away. I then asked the admin of my shared host to run the query, and they did. I then deleted civiUser, but I received the same error.

Question: What else must I do to remove all vestiges of civiUser from my system? Note that the user being called out in civicrm.settings.php is drupUser, so I am not sure what else to do.

JonGold

  • Ask me questions
  • ****
  • Posts: 638
  • Karma: 81
    • Palante Technology
  • CiviCRM version: 4.1 to the latest
  • CMS version: Drupal 6-7, Wordpress 4.0+
  • PHP version: PHP 5.3-5.5
Re: Cannot delete DB user and have CiviCRM function
February 05, 2015, 09:24:32 am
Yeah - the MySQL triggers are created by the current database user, which is why you're seeing that problem.  I think that if you solve this, that's the only vestige you have to remove from the database.  As someone who frequently moves Civi databases between servers that have different user names, I see this a lot!

I have a line I use in vim that does a search/replace on a database dump which removes the trigger definer:
Code: [Select]
%s:/\*\!50017.*localhost`\*/::
However, in your case, it's probably worth trying to switch to drupUser and try running a trigger rebuild:

civicrm/menu/rebuild&reset=1&triggerRebuild=1

Could you please try that approach and let us know if it fixes your problem?
Sign up to StackExchange and get free expert CiviCRM advice: https://civicrm.org/blogs/colemanw/get-exclusive-access-free-expert-help
Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Post-installation Setup and Configuration (Moderator: Dave Greenberg) »
  • Cannot delete DB user and have CiviCRM function

This forum was archived on 2017-11-26.