I am trying to use drush to move my live database down to my dev database. I am doing

drush sql-sync @live @devel --sanitize

However I get the error:

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

I think it is due to the views in the db from uc_views module, is there any way around this?

I also tried to do

drush sql-sync --skip-tables-key uc_views @live @devel --sanitize

where uc_views is an option declared in drushrc.php with an array of the db views from uc_views, but to no avail. Here is my drushrc.php

$options['skip-tables']['uc_views'] = array('uc_order_products_pair_vw', 'uc_order_products_qty_vw', 'uc_order_products_user_vw');

I would actually prefer to copy the database views from uc_views rather than exclude them if that is possible.

  • This case has occured to me as well by uninstalling CiviCRM in dev server and then syncing first time to new production server. Something about CiviCRM requirements. – Wtower Feb 16 '15 at 10:05

The user defined in settings.php on one or both sides of the sync lacks the SUPER privilege on the database.

Log in as the mysql root user and do

GRANT SUPER ON [db_name] TO '[username]'@'localhost' IDENTIFIED BY '[password]';

If you are security-conscious (as you should be), it's worth taking the following into account:

Do not grant the PROCESS or SUPER privilege to nonadministrative users. The output of mysqladmin processlist and SHOW PROCESSLIST shows the text of any statements currently being executed, so any user who is permitted to see the server process list might be able to see statements issued by other users such as UPDATE user SET password=PASSWORD('not_secure').

  • It was actually the user in the database I was trying to dump into that lacked the SUPER privilege. Thanks so much! – Craig Bertrand Apr 20 '12 at 14:42
  • Ah, didn't consider than it could happen on both ends. I edited my answer to reflect this. – Letharion Apr 20 '12 at 15:45
  • I wonder, are there security implications to give super (with no connections limit and stuff) to an app db user? – Wtower Feb 16 '15 at 10:00
  • 1
    @Wtower Well by definition you're adding privileges, so yes. On the other hand, it's quite likely that exploiting that somehow will require SQL-injection, in which case you're pretty much screwed anyway. Read up on the privileges SUPER gives you, and decide for yourself. :) – Letharion Feb 16 '15 at 10:10
  • 1
    @Wtower Interesting enough that I've added the relevant bit to the answer. In fact, reading in detail what privileges you get from SUPER, I can't see why drush really needs it. Maybe someone should check the issue queue, and file an issue about it if non exist. – Letharion Feb 16 '15 at 11:39

We ran into the same problem with aegir, the problem isn't the export but the export contains a DEFINER statement containing the user that originally created the view. It's solved in by using sed to remove those lines, see http://drupal.org/node/881496#comment-4516064

For a more permanent solution, you'll have to file an issue for drush or have a look at http://drupal.org/node/518184

  • Yes I had read a bit about DEFINER issues with database views. It seems that backup and migrate also has issues with this. Do you think by giving the user the SUPER privileges I am asking for some other sort of trouble? – Craig Bertrand Apr 20 '12 at 14:45
  • 1
    I'm not even sure that giving the user SUPER permissions is going to solve this, the view will be created with a definer that does not exist on the server, but it might be that mysql creates the user on the fly, you can test this quite easily by running the import as mysql root. On my servers I didn't grant the aegir user SUPER priveleges, but I assume if you're the only user on the user you can do it (on your own risk). – Attiks Apr 20 '12 at 18:51
  • I am not actually using Aegir yet. And it did work so I suppose it is good enough for me. I am the only one on the server anyhow. – Craig Bertrand Apr 20 '12 at 19:10

use same username in both the DB servers. take the dump using the same user privileges. this will fix your issue. use mysql -h option while taking dump.

  • Hello and welcome. Could you please explain how this things should help OP? There is an accepted answer from a year ago. Accepted == it worked, and it tells things totally different than your post. Now, I'm not saying your answer is invalid, but when it's different from already accepted one, it deserves some explanation and description. More of why to do things and how should they help, instead of solution "by the rote". – Mołot Oct 9 '13 at 12:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.