Database User Privileges

Posted by jacobson on April 28, 2010 at 6:59pm

I am running 0.4-alpha7. I am trying to migrate a site from D6.14 to D6.16 and have encountered two problems:

The mysqldump fails because the database user does not have LOCK TABLES privilege. I am not aware that this privilege is required by Drupal, so I'm assuming that the privilege is needed by the mysqldump script created by Aegir during a migrate. Is this correct? Is the requirement for this privilege documented?
An error occurs during the migrate as a result of Aegir's conclusion that the "schema" for lightbox2 on the D6.14 site (6100) is newer than the schema on the destination D6.16 site (3). The destination site has the newest lightbox2 in the sites/all/modules directory, as does the D6.14 source site. The update.php has been run on the source site but obviously not on the destination site, since it has not been installed yet. The "Compare Platforms" output from the Migrate destination select dialog shows that both the source and destination site have the (3) lightbox2 schema. I cannot figure out why Aegir insists that the schemas do not match or why Aegir believes that the source site has schema 6100.

Thanks.

Comments

DB Privileges discussed here

Posted by timwood on April 29, 2010 at 12:47am

http://groups.drupal.org/node/24992 in sections titled "Transfer the Database". To quote:

Then create the mysql user that your site accesses the database as, and grant it all permissions on that database except 'GRANT'.

Not sure about #2.

I Skipped This Step

Posted by jacobson on April 29, 2010 at 2:18am

When I installed Aegir, I was installing on the same server on which my existing drupal sites were running. So, I had no need to "transfer the database." As a result, I skipped the step to which you refer.

Thanks.

HAJ

It happens with some sites

Posted by omega8cc on April 29, 2010 at 9:06am

It happens with some sites (and it is not Aegir issue, imho), I have seen this on d5 import and since dbuser with required grants is created on the fly, there is no chance to add required (by this site, not by Aegir) privileges before the import/clone/migrate and you have to patch (temporarily) provision to add privileges on the fly.

The error I have seen was:

Importing database using command: mysql --defaults-file=/dev/fd/3 site_951_0
Database import failed: ERROR 1227 (42000) at line 2244: Access denied; you need the SUPER privilege for this operation
An error occurred at function : drush_provision_mysql_provision_deploy
Dropping database site_951_0
Revoking privileges of site_951_0@% from site_951_0

So I had to use this patch:

--- a/provision/db_server/provision_mysql.drush.inc     2010-04-26 05:03:52.000000000 +0100
+++ b/provision/db_server/provision_mysql.drush.inc     2010-04-29 09:57:21.000000000 +0100
@@ -130,6 +130,7 @@ function _provision_mysql_can_create_dat

 function _provision_mysql_grant($name, $username, $password, $host = '') {
   $host = ($host) ? $host : '%';
+  provision_db_query("GRANT SUPER ON *.* TO %s@%s IDENTIFIED BY '%s'", $username, $host, $password);
   return provision_db_query("GRANT ALL PRIVILEGES ON %s.* TO %s@%s IDENTIFIED BY '%s'", $name, $username, $host, $password);
 }

Just replace GRANT SUPER with GRANT LOCK TABLES and it should work.

Remember to revert the patch when it is done.

Regarding the second issue -

Posted by omega8cc on April 29, 2010 at 9:11am

Regarding the second issue - I would suggest to post it in the Aegir issue queue, since I have also seen that and had to manually change schema numbers in the Aegir db, however don't remember enough details now.

I had this same problem but

Posted by pcambra on June 14, 2010 at 5:24pm

I had this same problem but migrating a site of platform, applying the patch suggested (provision_db_query("GRANT SUPER ON . TO %s@%s IDENTIFIED BY '%s'", $username, $host, $password);) all worked fine.

The sites are D6, the old platform running on D6.16 y the new in 6.17

Should I revert the patch anyway?

Having sites/users with GRANT

Posted by omega8cc on June 19, 2010 at 10:15pm

Having sites/users with GRANT SUPER privileges is probably a bad idea, so yes, revert it if you no longer experience this problem on import/migration.

Just to weigh in

Posted by zkrebs on September 9, 2010 at 4:15pm

I had this problem, and the patch doesn't seem to work with Aegir Alpha 12.

The core of the issue was my Ubercart_views module. Read this thread: http://www.ubercart.org/forum/support/15169/importing_db - after I removed the module, and uninstalled it, I was able to migrate / clone my site successfully.