DB error: insufficient permissions after relocating a CiviCRM install

[Chris Burgess]

Something I often run into is that copying a CiviCRM DB to a user which doesn't have access to the original (or which is unavailable to the original DB user) triggers one of CiviCRM's vague error messages, DB error: insufficient permissions, when it tries to rebuild the CiviCRM menu.

If you use the same DB user for all sites on a server, or you use the same DB user across multiple servers, this won't come up for you. When there are dev and live sites on a server, I like to keep them firewalled from one another using DB permissions, so that one site can't "endanger" the other. (*depending on the rest of your setup.)

Anyway, the solution to this is to replace the DEFINER in the DB dump before loading it.

Here I am backing up production_example and copying CiviCRM to dev_example here. Production DB user is "prod_example", dev DB user is "dev_example".

Code: [Select]

mysqldump production_example > production_example.sql
perl -pi -e 's#DEFINER=`prod_example`#DEFINER=`dev_example`#g' production_example.sql
cat production_example.sql | mysql dev_example
Dropping this here as much for my future reference as anything. I know this is pretty much a limitation of how MySQL dumps triggers, and that mysql/triggers are here to stay for now.

[Chris Burgess]

Lobo pointed out recently that you can also do this by skipping DB triggers when backing up the DB.

Code: [Select]

mysqldump --skip-triggers
If this is done, CiviCRM will regenerate the triggers as required. And sometimes even when not required!

The instructions above are then only needed if you're working from a DB dump which already has triggers, and getting a new dump is a pain.