Cannot delete DB user and have CiviCRM function

[groston]

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]

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?