How I Set Up PHPMyAdmin: A Tale of Unnecessary Difficulty

I did it. I won my battle with PHPMyAdmin. And I learned a few things in the process. I finally decided to bite the bullet and rebuild a Microsoft Access database application I had built for the school to track volunteers and volunteer time using PHP and MySQL.

MS Access is a dandy if you want to run a small standalone application. You can build a MS Access application on any computer that has MS Access installed. Copying the entire application to another computer is a snap. But the target computer also has to have MS Access installed if you want to use your application there. If the other computer doesn’t have MS Access and its owners don’t want to spring for it, you can tweak the app to work with OpenOffice Base, which is available for free.

But MS Access really isn’t adequate for a multi-user environment. (This also goes for OpenOffice Base.) It’s not designed to handle the kind of locking and security needed for such an environment. Yes, I know that there are many instances where MS Access applications are run from a server, but it’s not a good way to go.

The freely available MySQL, on the other hand, is designed function well in multi-user environments. But you need more than just a database. You need a front-end application to allow users to work with the database. That’s where PHP comes in. And, of course, you need an application server application that hosts any PHP/MySQL application you build. Proper installation on a central server allows access via a web browser.

My idea was to rebuild the standalone MS Access database application using PHP/MySQL, and then install the new application on the school’s server so that any computer inside of the school’s firewall could work with the database. But first, I wanted to build and run everything on my home network to work out all of the bugs.

I started by downloading the WAMP package that includes PHP, MySQL, PHPMyAdmin, and the Apache HTTP server from WAMPServer. This is for a Windows environment. Other packages are available for other environments. Initial installation was quick and easy. All of the services started.

The root password
My problems started when I opened PHPMyAdmin and saw a message telling me that I should remedy the fact that MySQL had no password for the root user account. Unfortunately, there was no information about how to do that. I began searching through the documentation that installed with PHPMyAdmin and MySQL, only to become quickly frustrated.

A Google search revealed a broad variety of information. It took me a while to realize that most of it was obsolete. Reading through forums that turned up in the Google search was a tedious and mostly fruitless exercise. It was like digging through mounds of manure in hopes of finding a tiny gem.

I finally discovered that MySQL passwords can be set by running MySQL from the command line and using an arcane chain of commands, or by going to the Privileges tab in PHPMyAdmin, clicking on the Edit Privileges icon to the right of the root account, and then entering (and re-typing) the password in the resulting screen. Unfortunately, this immediately breaks PHPMyAdmin. I started getting “Error #1045 - Access denied for user 'root'@'localhost'….”

I searched around and found that I needed to go to my wamp\apps\phpmyadmin directory and edit the config.inc.php file. There is an entry there that reads “$cfg['Servers'][$i]['password'] = '';” All I needed to do was to enter the password I had selected for root between the single quotes on that entry. (This is fine for a limited environment, but you will want to use the cookie method in a broader environment. That’s outside the scope of this post.)

This fixed everything, right? Nope. The # 1045 error persisted. Nothing I did changed that. After much frustration, I completely uninstalled and re-installed WAMP. The process began over again. Again I ended up at the persistent # 1045 error.

Clear the session files
After much perusal of the Internet, I read an entry that mentioned permissions on the tmp directory. I found the wamp\tmp directory and noticed a couple of files that began with “sess” followed by a series of hexadecimal numbers. Suddenly I had the odd idea that I should delete these files. I did so, and then PHPMyAdmin started up just fine.

Apparently PHPMyAdmin always starts by going to the most recent session file in the tmp directory. If the session ended with an error, the new session will start with the same error, even if you fixed the cause of the error.

Just a couple of notes to the PHPMyAdmin developers: 1) Why can’t the config.inc.php file be automatically updated with the root user password when it is changed via the PHPMyAdmin application? 2) You really ought to do something about fixing the problem with PHPMyAdmin retaining an error upon starting when the cause of the error has already been corrected.

Setting up link table capabilities
Now everything was hunky-dory, right? Wrong. PHPMyAdmin displayed a message in a red box stating that the mechanism for linking tables was not properly set up. Clicking on the link for more info led to the documentation. Although there was a lot of technical stuff in there, there was no information on how to remedy this problem.

I once again resorted to Google and found myself sifting through tons of worthless information in search of something useful. I found an entry that explained that the following entries were needed in the confic.inc.php file:

$cfg['Servers'][$i]['controluser'] = 'pma';
$cfg['Servers'][$i]['controlpass'] = 'password for pma user';
$cfg['Servers'][$i]['AllowNoPassword'] = true;
$cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';
$cfg['Servers'][$i]['table_info'] = 'pma_table_info';
$cfg['Servers'][$i]['pdf_pages'] = 'pma_pdf_pages';
$cfg['Servers'][$i]['history'] = 'pma_history';
$cfg['Servers'][$i]['column_info'] = 'pma_column_info';
$cfg['Servers'][$i]['table_coords'] = 'pma_table_coords';
$cfg['Servers'][$i]['designer_coords'] = 'pma_designer_coords';
$cfg['Servers'][$i]['relation'] = 'pma_relation';
$cfg['Servers'][$i]['bookmarktable'] = 'pma_bookmark';

But this simply caused me more questions. What was the pma user? It seemed that these entries implied a database named phpmyadmin with at least eight tables in it. (I’m a data guy. I recognize database structures.) Where was that supposed to come from?

Fortunately, I found another link that provided the answer to this conundrum. You can create the pma user account in the Privileges tab of PHPMyAdmin and assign a password. Then the pma user needs rights to the mysql database. In the SQL tab of the mysql database, I ran the following commands:

GRANT SELECT (Host, User, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv) ON mysql.user TO 'pma'@'localhost';
GRANT SELECT ON mysql.db TO 'pma'@'localhost';
GRANT SELECT ON mysql.host TO 'pma'@'localhost';
GRANT SELECT (Host, Db, User, Table_name, Table_priv, Column_priv) ON mysql.tables_priv TO 'pma'@'localhost';

In the wamp\apps\phpmyadmin\scripts folder is a create_tables.sql file that can be used to create the phpmyadmin database with the eight tables referenced above and to grant rights to the pma user. Even after doing all that, the PHPMyAdmin error didn’t go away until I closed PHPMyAdmin, deleted the session files from the wamp\tmp directory, and restarted PHPMyAdmin.

Finally ready to start developing
Now everything works great. I re-created my tables in MySQL using PHPMyAdmin. I exported my MS Access data to comma delimited files and imported it into the MySQL tables. Voila!, I am now finally ready to start building the application interface using PHP.

I have to seriously question the PHPMyAdmin developers as to why they haven’t streamlined the link table setup. I mean, the clugey hack job you have to go through to set this up and get rid of the error message is simply bizarre, not to mention very poorly documented.