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.


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!

Less than one tenth of one percent contribute to Drupal core!

Drupal Ladder

Do you remember when you first discovered Drupal? For some folks, it might have been love at first sight. Or, if you were like me, it could have been one of those relationships you really didn't know where it was going but you liked it so you kept moving forward anyway. And today? You couldn't imagine living without Drupal!

For some, including me, there needs to be some clarification when I talk about "relationship". Google defines a relationship as:

The way in which two or more concepts, objects, or people are connected, or the state of being connected.

And there's the word . . . "connected". If you've been in a personal relationship for any period of time, you've undoubtedly confronted or been confronted with, I just don't feel like we're connecting any more...

So, let me ask you, if Drupal had lips, what would it say to you??

  • We've been dating for two years, where's this going?

  • Why do you spend soooooo much time with your friends?

  • Would you put down that remote/game controller/beer/bike/(insert your own vise) and pay attention to me!

Remember, we all need a little love. And, that includes Drupal!

The scary numbers

The way that we're going, by 2014, about one tenth of one percent will be contributing to core! I know, this is math and it's easy to distort numbers. The fact remains though. With an ever expanding base of Drupal users, site builders, and developers, we should be able to maintain (or even grow) the percent of people that give back to core.

<img class="post-image" src="//

" alt="Drupal Ladder contribution">

Here's where the Drupal Contribution Ladder comes in. The goal is to get 1% of users contributing to core by 2014.

You next best 10 minutes for Drupal

If you were at DrupalCon Denver, you might have attended <a href="

" target="_blank" title="The Drupal Contribution Ladder">The Drupal Contribution Ladder: A Gift and a Challenge to You from Boston. It's very worthwhile to watch the whole thing but you might not have the time right now. So, the folks over at Drupalize.Me have put together a <a href="

" target="_blank" title="Learn Drupal Ladder video series">Learn Drupal Ladder video series and the intro is 10 minutes. Go there and watch it now!

Next steps

Like many of you, I've been absent way too long and haven't contributed to core. And, if you're like me, part or most of your living is based upon Drupal. It only seems fair to give back to the project and technology that enables so much of what we enjoy both professionally and personally. Just call it Drupal Love!

It's pretty easy to get started regardless if you are new to Drupal or are a seasoned veteran. First, go to the <a href="

" target="_blank" title="Learn Drupal website">Learn Drupal website and familiarize yourself with the resources. You can also download a summary of the <a href="

" title="Drupal Contribution Ladder slides">Drupal Contribution Ladder slides. Second, get a feel on where you are at. Are you just getting started? Are you ready to contribute? Or, are you at a point where you can maintain a core component or initiative? Third, read the section on sprints and when you're ready, take action!

I'm now commiting myself . . . are you??

Drupal 7, MediaFront, MPEG-4 moov atoms, and buffering

Drupal 7 MPEG4

In this article, I'll address why some MPEG files buffer/load completely before playing and then give you several solutions to resolve this issue. For some background, it's well documented that MPEG-4 files could buffer completely before playing from a website. If you have a very small video, this might not be an issue. With video times of 15, 30, or even 60 mins+, it can be a real issue for many site builders. Even my 4 min videos were driving me nuts . . .

Btw, I've been using Media and Buffer/Load Time for MP3s is very, very long. In scanning the comments, travist replied with a comment about video metadata needing to be at the beginning of the file.

After a bit of research, I found out this issue is related to moov (movie) atoms which are pieces of metadata stored within the video file. When these atoms are at the end of the file, the entire video file must load before playing. The metadata contains all sorts of information about the file. If the player doesn't get the metadata when the file first loads, it has to wait until it sees the data before it can start to play. To understand all the low-level details, read this excellent article, Understanding the MPEG-4 movie atom.

The solution

The easiest solution is to determine if your encoding software has an option to store the moov atoms at the beginning of the file. For me, I was converting WMV files to MPEG-4 and using Handbrake. During my conversions, I did not use the Web optimized option. But, when I checked this option, the moov atoms were correctly located at the beginning of the file. I'll show you how to determine where your moov atoms are located in a moment.

Handbrake screen shot

That's great but what if you've converted 100's of video files to MPEG-4 already?

How do you like your parsley?

One thing I love about Open Source is how we come up with our names... Just know that AtomicParsley will soon become your best friend when you need to check where your moov atoms are located. And, I know that checking my moov on Friday night is where the action is at!

Download AtomicParsley. There are binaries for Windows, Mac, and Linux as well as source code. And yes, the Linux binaries work on .deb or .rpm distributions. You can also access the man page.

To check the location of your moov atoms,

# In a shell or terminal.

$ AtomicParsley  -T

will display all atoms in your video file. You want to ensure your Atom moov lines are ABOVE your Atom mdat line. Atom mdat represents the video data. The best way to see this in action to view the AtomicParsley home page. Look at the AtomicParsley output.

So, now you're like me . . . your Atom moov lines are at the end of the AtomicParsley output, what's next?

A French lesson from Ecole Nationale Supérieure des Télécommunications

Our friends at Telecom ParisTech have developed a wonderful Open Source solution! GPAC, more specifically MP4Box, is the project you are looking to use.

You'll need to download/install GPAC to gain access to MP4Box. For Ubuntu, a simple

# In a shell or terminal.

AtomicParsley  -T

works just fine. On .rpm distributions, download the latest rpmfusion-free-release-stable.

# In a shell or terminal.

rpm -Uvh rpmfusion-free-release-stable.rpm

yum install gpac

An alternative for Mac and Windows users is QT Index Swapper which is an Adobe Air application. Unfortunately, Adobe decided not to support Air for Linux desktop environments anymore.

Last, ffmpeg also includes a utility called qt-faststart that you could use as well. Read the comments at the beginning of the file for more information.

Lets move some moovs

There are two MP4Box options when preparing your file for web-based video streaming. Both of these commands will overwrite your original file. Make a backup! Also, you can use the -new option to create a new file once your conversion is complete. And, you can batch process so regardless of how many files you have, it's a simple process.

# In a shell or terminal.

MP4Box -inter 

The -inter option is used to interleave media data in chunks of the desired duration and places the moov atoms at the beginning of the file. The -isma option is used to convert the video file to ISMA 1.0 specification. I tried both options above and both moved the metadata to the beginning of the file. During the conversion process, I noticed that the -isma option also performed the same processing as the -inter option. So, I decided to use the -isma option for my needs. If in doubt, read the MP4Box documentation and see which option makes sense for you.

Once you're done, MEPG videos streamed from your website will play in a few seconds after loading!

Comments, questions, errors, typos? Do you use something different? Let me know!

Drupal 7, AdaptiveTheme, and iPad meta viewport Bug?

Drupal 7 AdaptiveTheme

This week, I've been working on a microsite for one of my clients. Since the target platform is an iPad, I decided to try AdaptiveTheme (base theme) and Sky (sub-theme) for this particular project. There's a lot going on with responsive/adaptive design so it's good to try out different base themes and get an idea of what's possible today.

As I started testing the microsite with the iPad and mobile Safari, I noticed an error when rotating to landscape. It was odd since everything was loading correctly in portrait or landscape without rotation. But, once I changed the orientation to or back to landscape, the iPad automatically zoomed in a bit. Think of automatic pinch-to-zoom. Immediately, I checked the theme settings but everything looked good.

Then I thought, oh, it's a bug with AdaptiveTheme. So, I searched the issue queue . . . nothing. I searched Google with AdaptiveTheme in the keyword phrases . . . nothing. As it turns out, it isn't an AdaptiveTheme issue at all. It's a viewport mobile Safari/iOS bug.

A solution?

Basically, you want to constrain the viewport scaling to a min and max of 1.0 until the user explicitly requests interaction. Meaning, an event is fired (e.g., touchstart, gesturestart, etc.). This way, a rotation will always "scale" within the min and max parameters. The solution is well documented on StackOverflow and Adactio so they get all the credit for identifying and solving the initial issue. Btw, notice my small modification in their code below. Adding the event to the body did not work for me in mobile Safari/iOS 5.1.

 (function ($) {

  if (navigator.userAgent.match(/iPhone/i) || navigator.userAgent.match(/iPad/i)) {

    var viewportmeta = document.querySelector('meta[name="viewport"]');

    if (viewportmeta) {

      viewportmeta.content = 'width=device-width, minimum-scale=1.0, maximum-scale=1.0, initial-scale=1.0';

      // I changed this from document.body.addEventListener to document.addEventListener

      document.addEventListener('gesturestart', function () {

          viewportmeta.content = 'width=device-width, minimum-scale=0.25, maximum-scale=1.6';

      }, false);




Since you want to load this code for every Drupal page, put it in a javascript file loaded by your theme .info file. I am assuming you'll be wrapping other functions within this file that need jQuery. If not, you can remove the $ and jQuery parameters. You could also add inline javascript code by using drupal_add_js and setting every_page to TRUE.

While this solution isn't perfect as it can lead to some hesitation or a double gesture, it still allows the user to zoom vs. the alternative of using maximum-scale-1.0 (no user scaling).

But, there were still issues with usability.

Another solution?

In the prior example, once the gesturestart event fires, the viewport was set so you can zoom the page. Well, this is great until you rotate the page to portrait then go back to landscape. Guess what? The problem comes back. What I needed was a way to reset the viewport each time the iPad was rotated . . . think orientationchange event. Again, we always want to constrain the rotation by the min and max values and allow the user to initiate the zoom.

In thinking about this a bit more and paying attention to how I use my iPad, my first interaction with the screen is typically a touch not a gesture. So, if I extrapolate my statistically insignificant sample and make even more assumptions, it makes sense to allow the viewport to scale when the touchstart event fires. This is a cleaver way to hide the hesitation in the above example. And, if the user starts with a gesture, well, we haven't made the hesitation problem any worse.

(function ($) {

  if (navigator.userAgent.match(/iPhone/i) || navigator.userAgent.match(/iPad/i)) {

    var viewportmeta = document.querySelector('meta[name="viewport"]');

    if (viewportmeta) {

      viewportmeta.content = 'width=device-width, minimum-scale=1.0, maximum-scale=1.0, initial-scale=1.0';

      document.addEventListener('touchstart', function () {

          viewportmeta.content = 'viewportmeta.content = width=device-width, minimum-scale=0.25, maximum-scale=1.6';

      }, false);

      document.addEventListener('orientationchange', function () {

          viewportmeta.content = 'viewportmeta.content = width=device-width, minimum-scale=1.0, maximum-scale=1.0, initial-scale=1.0';

      }, false);




I have no idea if my usage is typical or atypical of a tablet user. As far as usability, I think it's a better solution for my client. We'll see once the testing feedback starts rolling in!

More solutions?

If above ideas do not work for you, some other "solutions" are . . .

To disable scaling all together, in the html.tpl.php template or hook_preprocess_html function, set the meta tag viewport to:

<meta name="viewport" content="width=device-width, minimum-scale=1.0, maximum-scale=1.0, initial-scale=1.0">

Or, remove the viewport line all together and allow the iPad to scale the page to fit the screen. I know, what's the point of responsive design then . . .

While these are less than ideal, you might be forced to use them depending upon your needs.

The Latest Solution: Listen to the Accelerometer

Added 8/6/2012: This solution works by listening to the device's accelerometer and tries to predict when an orientation change is about to occur. The full details of this fix can be found at iOS orientationchange zoom bug on Github.

Other solutions? Comments? Typos? Let me know!

Drupal 7 syntax highlighting using Wysiwyg, CKEditor, and Syntax Highlighter

Drupal 7 Syntax Highlighting

The goal of this article is to show you how to configure Wysiwyg and CKEditor to embed syntactically highlighted (pretty) code into your Drupal page. Something similar to the Monokai theme as seen in the image.

Module and software downloads

To get started, you will need the following software and modules:

I am going to assume you have a working Drupal site with Wysiwyg and CKEditor configured and running. If not, check out the screencast at DrupalTherapy, Wysiwyg + CKEditor.

Next, download and install Syntax Highlighter, Syntax Highlighter Insert, and the Alex Gorbatchev Syntax Highlighter javascript library. The README.txt file in the Syntax Highlighter module will explain where to put the javascript files. Finally, enable the modules.

# Using drush from a command prompt

drush en -y syntaxhighlighter syntaxhighlighter_insert syntaxhighlighter_insert_wysiwyg

Module settings and configuration

Before you get started, there are a few configuration options you need to set.

Step one

Go to Home > Administration > Configuration > Content authoring > Wysiwyg profiles (admin/config/content/wysiwyg)

  1. Find the INPUT FORMAT used with CKEditor. Click on Edit under OPERATIONS.

  2. Click on BUTTONS AND PLUGINS to expand the section.

  3. Select the Insert syntaxhighlighter tag checkbox.Drupal 7 Syntax Wysiwyg

  4. Click on Save to store the settings.

Step two

Go to Home > Administration > Configuration > Content authoring > Text formats (admin/config/content/formats)

  1. Find the Text Format used with CKEditor in the prior step. Click on configure under OPERATIONS.

  2. Under Enabled filters, select the Syntax highlighter checkbox. Drupal 7 Formats Filters

  3. Make sure Syntax highlighter is positioned above Convert line breaks into HTML in the Filter processing order. If not, the break tag might be displayed within the code on your page. Drupal 7 Formats Order

  4. In the Filter settings, add the <pre> tag to the Limit allowed HTML tags section. Drupal 7 Formats Tags

  5. Click Save configuration.

Step three

Go to Home > Administration > Configuration > Content authoring > Syntax highlighter (admin/config/content/syntaxhighlighter)

  1. Under Enabled Languages, select one or more languages. Only the selected languages will be enabled and the corresponding javascript brush files loaded.

  2. Under Theme, select your desired theme.

  3. Under Tag name, notice the Syntax highlighter defaults to the <pre> tag.

  4. Click Save configuration.

Using Syntax Highlighter

Create a new piece of content or edit an existing page. Next, go to the body section (textarea) of the page. Make sure you have the correct Text format selected. If you don't see the Text format you modified in the prior steps, check your assigned roles and Permissions. On the CKEditor toolbar, you should notice a new button.

Drupal 7 Toolbar

Click on the Syntax highlighter button and an overlay will be displayed. Fill in an optional Title and select your Brush. A Brush is the type of code language you want to highlight. There are also a variety of other options for you to select. Once you are finished, click Insert sytaxhighlighter tag. You should see a placeholder within the textarea field.

Drupal 7 Button1

I've typed some CSS code into the placeholder. I find it's much easy to copy and paste the code into the placeholder vs. entering it freeform.

Drupal 7 Button2

The final result looks like the below. The title Sample CSS Code was entered in the overlay . . . after I clicked on the Syntax Highlighter button.

body {

  font-family: Helvetica, Arial, sans-serif;

  margin: 0;

  padding: 0;


If everything is configured correctly, you should see the same result above.

Not working for you? See something incorrect or a typo? Just let me know!