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!