David's Technical Musings

In part 1 of this post, I talked about installing MySQL v4 and PHP v4 and was just about to get to phpMyAdmin.  Let’s start there.

phpMyAdmin

Managing databases can be pretty challenging and complex.  Some folks felt the same way and decided to leverage PHP to develop a web-based admin to for MySQL.  Since both PHP and MySQL are free, they followed the same pattern.  Unlike PHP and MySQL, however, phpMyAdmin is simply a bunch of .php files which get copied to a location accessible to a web site.  For example, if your web site is www.joe.com located at C:\Inetpub\wwwroot, you could simply copy the phpMyAdmin files to C:\Inetpub\wwwroot\phpMyAdmin, which would allow you to access phpMyAdmin by navigating to www.joe.com/phpMyAdmin.  Of course, it wasn’t exactly that simple.  There are some PHP extensions that phpMyAdmin requires in order to work correctly, specifically:

• MySQL
• MySQLi (for v5)
• Multi-Byte String
• Mcrypt

It was at this point that I ask, since both PHP and MySQL were running so well, why couldn’t WordPress, a modern web design environment used by boatloads of people, work with the later versions of these two tools?  I did some research and found that people are in fact using the latest versions.  What clinched it for me was a note on the PHP web site announcing that PHP 4 was being end-of-lifed (meaning it would no longer be supported).  I probably simply glossed over the note when I started since it’s been there since 7/13.

Okay, time to switch.  Fortunately I had installed the software in version-specific directories.  First I installed MySQL 5.  It turned out to be as simple as MySQL 4.  The only thing I ran into was that v5 wanted to listen on the same TCP port as v4, so I shut down the v4 service (which I had called MySQL4) and set it to manual start.  Getting MySQL v5 up and running was pretty straight-forward since I had spent a little bit of time with v4.

PHP v5 seemed like it was quite a bit easier to install than v4 (although it’s been several days now, so the memory could have faded).  There is an MSI installer which takes care of setting up the php.ini file.  I selected the MySQL and MySQLi extensions and it only copied those files that were needed.  Since I was familiar with setting v4, making the few changes to the php.ini file was pretty straight-forward, although I still decided I needed to read the online instructions for PHP on IIS.  Once nice thing is that the installer made all the required changes in IIS for me.  Very nice.

I did have to fix one thing.  In some places it set the path to the ISAPI file using the short file name.  For some reason IIS or PHP didn’t like this, so I had to go in and fix that up.

Now back to phpMyAdmin.  There is a Documentation.html file provided with the installation, so I pulled that up to discover what to do next.  The first thing that needs to be done is to create a config.inc.php file.  Fortunately they provide a web interface for generating one – you just create a config folder in the phpMyAdmin directory and then navigate to scripts/setup.php in your browser.

The first thing the script told me was that I wasn’t connected over a secure connection.  Easy enough to solve since I bought an SSL certificate from GoDaddy.com last year.  Switching to https solved that problem Next it told me I was missing some PHP extensions:

• Multi-Byte String
• Mcrypt

Once I got those installed, I encountered my next problem.  The config folder wasn’t writable for the account the web server was running under.  After modifying the security settings of the folder I was off and running with the setup script.  Hmm, not a very intuitive page.  I found this script to be very confusing.  I finally figured out that I was supposed to Add a Server.  Of course, the settings there required further reading in the documentation (which is conveniently referred to from the setup web page).  I settled on the following settings:

• Connection type = “socket” since both the web server and MySQL are running on the same machine.
• PHP extension = “mysqli” since I’m using PHP v5.
• Authentication type = “cookie” since it is the recommended setting for IIS.  Note that “config” is not good as it writes credentials in a file on the site itself.
• I had to specify a phpMyAdmin control user and password.  More on that below.
• I specified a phpMyAdmin database for advanced features.  More on that below.

Hmm, interesting.  As I was writing this up, I looked at the documentation a little more closely.  There is a note saying that phpMyAdmin needs a controluser that has only SELECT privilege on several mysql tables (where mysql is the system database for MySQL).  It turns out this is only needed with very old versions of phpMyAdmin and MySQL.  Oh well.  I created a user (although as I looked at it more closely, I didn’t give it all the privileges needed) and specified that above.

It turns out that there is also one extra step that needs to be done to make sure the controluser has access to the phpMyAdmin database.  You need to grant the account all privileges on all tables in that database.  I was getting an error message in phpMyAdmin until I did that.

The database specified above is related to a the phpMyAdmin linked-tables infrastructure.  There are two different scripts in the scripts folder that will create the database and the tables – one for older versions of MySQL and one for later versions.  My version was old enough, so I used the latest version: scripts/create_tables_mysql_4_1_2+.sql.  I wasn’t completely familiar with the MySQL syntax for reading scripts, so I had to consult the documentation.  You do it using the source command, e.g.

source scripts/create_tables_mysql_4_1_2+.sql

Note that there is an FAQ referenced in this section that points out that on Win32 machines table names are converted to lowercase.  The solution is to modify the my.ini file in the MySQL installation directory to add a variable, like this:

set-variable = lower_case_table_names=0

I can’t count the number of times I thought I set it up only to have it tell me it wasn’t set up.  I finally read the fine print (well okay, it’s not that fine) saying that all the setup.php page does is generate a config file that must then be copied to the root phpMyAdmin folder.  The other thing that I didn’t get right away is that once you’ve created the file and copied it, you need to not run setup.php anymore.  Just navigate to the phpMyAdmin URL on your site.  It would have been nice if the setup script had displayed instructions for doing these two things.  Even better would have been a button to copy the file and a link to go to the home page.

At this point, my phpMyAdmin page now works.  I’ve used it quite extensively and it definitely makes administering MySQL databases much simpler.  There are still some things that aren’t as easy as they could be, though I won’t get into those right now.

So why did I install phpMyAdmin?  Initially it was because the WordPress installation instructions referred me to it.  As you’ll see in my next installment, however, it may not have been necessary .