Showing all posts tagged "Drush"

Watch out for the underscore! Setting privileges using phpMyAdmin and MySQL

phpMyAdmin and MySQL

I use a combination of phpMyAdmin and the MySQL command line when administering my sites. I also like to use underscores within my database and table names. Yesterday, I came across a particularly interesting situation setting privileges on a database using both phpMyAdmin and MySQL.

Lets make sure we're all on the same page when it comes to using underscores in schema object names. As you will see, the underscore is perfectly valid so this usage comes down to personal preference.

So, what's the issue?

I was setting up a Drupal site to use the Drush sql-sync command and need to add the Lock Tables privilege to a MySQL user. Initially I created the privileges using phpMyAdmin. I am using version 3.4.10 and MySQL 5.5.24. Notice the underscores in both graphics.

phpMyAdmin graphic 1

phpMyAdmin graphic 2

Since I didn't have the Lock Tables privilege listed, I needed to add it. I shelled into my VPS and used the MySQL command line. After issuing the GRANT command on red_d7,



-- Using the MySQL command line.

mysql> GRANT ... LOCK TABLES ON `red\_d7`.* TO...

I didn't determine this until I did a bunch of troubleshooting . . . In general, this isn't very intuitive. While I realize _ and % are MySQL wildcard characters, I think phpMyAdmin shouldn't expose this level of detail to the end user.

Given the above graphic, there are two privileges for the database red_d7. If you run a command that requires Lock Tables, you get the infamous ERROR 1044 (42000) at line 40: Access denied for user . . . to database 'red_d7'. My assumption is MySQL finds the first privilege line and ignores the second; privileges don't appear to aggregate.

The solution

My workaround was to Revoke (delete) the first line with the escape character leaving the second line with just the underscore.

phpMyAdmin graphic 3

I chose to Revoke the first line since since the database name red_d7 works with both phpMyAdmin and the MySQL command line. If you use phpMyAdmin to revise the privileges, it doesn't add the escape backslash back to the database name.

Comments, questions, corrections?? Let me know!

Drush 5 core-rsync file ownership Issues

Drush 5

Drush is a very powerful tool that can be used for a variety of site building and maintenance tasks. Today, I want to look at the Drush 5 command core-rsync and some gotchas I discovered when moving my site files around.

To be clear, I'm not talking about using core-rsync to manage Drupal code. In my opinion, it's best to use Git and the git-flow process to do that. The focus of this article is only on the files directory under your sites/sitename folder.

Configuration background

With each new site or project, I like to use the Development to Testing to Production workflow. Even if you've rolled out the smallest Drupal site, you'll immediately recognize the problem once you start making changes or begin your next iteration. Like many of you, I use my local machine as the Development server for small projects. My Testing and Production servers are typically hosted on a Virtual Private Server (VPS) somewhere in the cloud.

So, what's the problem?

After using core-rsync to sync files to and from your Development server, file ownership can change leaving your Drupal installation in a bit of a mess. So, it's important to pay close attention to the core-rsync --mode option. This states:

The unary flags to pass to rsync; --mode=rultz implies rsync -rultz. Default is -akz.

The rsync referred to here is the *nix command called rsync -- a fast, versatile, remote (and local) file-copying tool. Notice the bolded default above. We'll get to that in a minute.

Development to testing or production to development?

Typically, in a three server workflow environment, data moves from Production backwards to Development. Although, the first time you bring up a site, you'll probably need to get the initial files onto Testing. Using Drush, this can be accomplished via the following command:



# At a command prompt.

drush core-rsync @dev:%files/ @test:%files

If you are pulling files from Production back to Development, you reverse the source and destination:



# At a command prompt.

drush core-rsync @prod:%files/ @dev:%files

If you haven't setup Drush Aliases before, there is a wealth of information to get you started:

After running the first command, what happens? First, you'll get a message saying all files in the destination folder will be destroyed and replaced. Second, the file transfer will occur. And third, the file ownership for all files on the Testing server will mirror your Development machine. This is probably not what you want. For me, my Development machine had the file owner as robert and the group ownership as staff. I'm running Ubuntu and Apache on my VPSes so, on my Testing server, the owner needs to be my remote login id and the group ownership needs to be www-data.

The solution

You'll need to change the core-sync --mode options as well as activate the setgid bit on each sites/sitename/files folder.

rsync

The default core-rsync options for rsync are --mode=akz. If you look at the rsync man page, you will notice that the -a option really means archive mode or all of the following: -rlptgoD. The -g and -o options are to preserve owner and group. We don't want that. So, the new core-rsync commands would look like:



# At a command prompt.

drush core-rsync @dev:%files/ @test:%files --mode=zkrlptD

drush core-rsync @prod:%files/ @dev:%files --mode=zkrlptD

We are almost there.

Change the setgid bit

There is another small problem though. Since we are not preserving the owner and group, the files will come across as the Drush Alias remote-user with it's corresponding group. Again, not quite what we want. What I need is for the the group ownership on my Development machine to be staff and the Testing server to be www-data.

Before using the core-rsync command, find the "files" folder on each server (Development, Testing, and Production) and change the setgid bit. This will allow the group ownership to be inherited by new files and folders created in the files directory. As a side note, most standard Drupal installations have the files folder located at <drupal_root_dir>/sites/default/files. I am also assuming you have already setup the correct group ownership on the files folder. To change the setgid bit, use the following command. You need to be in your <drupal_root_dir>/sites/default directory when you run the command. This will find all directories under files and change the setgid bit.



# At a command prompt.

find ./files -type d -exec chmod g+s '{}' \;

Now, when you run the core-rsync command, you'll get the right files with the right ownership and Drupal should be happy!

Comments, questions, or corrections?? Let me know!