Web Based Portable mysql tool suite

Are you limited in what software you can use at work?  This article details how to have a web based tool suite for mysql.  It currently has the following tools:

• AjaxMyTop — a php implementation of mytop (think top for mysql) which runs in a browser.
• rbdb — a phpmyadmin work-alike in progress.  It’s the result of the 2008 Rails Rumble contest.

So you’ve noticed that both a php and a ruby application are included.  Pretty spiffy, eh?

The magick partly lies in the container — I’m using GlassFish v3 prelude.  Another piece is Quercus, a Java implementation of PHP 5. JRuby is used for ruby.

What follows is a process for creating your own tool suite.  You’ll need the following:

• Java Development Toolkit.  I used jdk1.6.
• git
• Quercus – the 3.2 war.
• Resin — this will become clear later (I’m using 3.2)
• AjaxMyTop
• rbdb
• mysql
• mysqlconnector – this is the jdbc device driver.
• GlassFish
• JRuby

1. Before we do anything else, make sure that the jdk and mysql have been installed.
2. Uncompress GlassFish and start it.  You’ll find the start script in {GLASSFISH_DIR}/glassfish/bin
3. Point your browser to http://localhost:4848 – this is the GlassFish console. If it informs you that there are updates available, please install them.
4. On the plugins page, click on jruby and jruby-gems and install them.
5. Still in the console, select Web Applications and then deploy the Quercus.war file.  Name the deployment ajaxmytop.
6. Uncompress the AjaxMyTop zip file.  Copy the contents to {GLASSFISH_DIR}/glassfish/domains/domain1/applications/ajaxmytop.  
7. Read the …/ajaxmytop/README.txt file and execute the sql code to add permissions for the mysql user.
8. Unfortunately, if you attempt to run it now, the Quercus war file is missing DomDocument and the app will fail.  However, there is a solution for this, based upon information from this Quercus forum post.  In order to fix the issue, do the following:
   1. Uncompress the resin distribution.
   2. Within the lib directory is resin.jar.  Un-jar this to a separate location (we’ll be jar’ing it back up in a moment).
   3. Remove com/caucho/quercus/servlet/ResinQuercusServlet.
   4. Re-Jar the resin jar. (jar cvf resin.jar *)
   5. Copy the modified jar to {GLASSFISH_DIR}/glassfish/domains/domain1/applications/ajaxmytop/WEB-INF/lib.
9. Uncompress the mysql connector.  Copy the jar to {GLASSFISH_DIR}/glassfish/domains/domain1/applications/ajaxmytop/WEB-INF/lib.
10. Take a break.  We’ve got one application completed!
11. Uncompress JRuby.
12. Set the JRUBY_HOME and JAVA_HOME variables to point to their locations on your system.  Under unix-flavoured OS’s you’ll need to export these variables as well.
13. Modify your path to be ${JRUBY_HOME}/bin:${JAVA_HOME}/bin:${PATH} on unicen.  On windows it’d be something like %JRUBY_HOME%\bin;%JAVA_HOME%\bin;%PATH%
14. We will need a couple of gems to do our deployment: jruby -S gem install warbler haml
15. Clone the rbdb distribution: git clone rbdb
16. I ran into a few things attempting to get rbdb to run stand-alone within jruby.  One of these was the haml above.  Also, it wanted the database.yml in rbdb/config.
17. One caveat here — rbdb uses the built-in rails mysql driver.  In an attempt to optimize, it assumes that if your host is localhost that you’ll be using the mysql socket at /tmp/mysql.sock.  Obviously, if it isn’t, this will cause issues.  So, rather than localhost, use 127.0.0.1.  In my case, I actually hardcoded the values into apps/controllers/application.rb — I wanted to only be able to access it on localhost.
18. Invoke warbler via warble.  It’ll create your war for you.  It does add a number of things that you probably don’t need since jruby is already included in the GlassFish server, but it works.
19. Deploy the rbdb.war
20. This step probably isn’t needed but just to be safe, stop and restart the GlassFish instance.
21. AjaxMyTop is available at http://localhost:8080/ajaxmytop.  Likewise, rbdb is available at http://localhost:8080/rbdb.  I did notice that it didn’t handle all the images properly, but it’s working.

Well, I hope you found this interesting and/or useful.  I’ll admit that this was the first time I actively played with GlassFish.  I’m really impressed.  It has a very small memory footprint — RSS as low as 25mb for the admin console.

The reason I didn’t use phpmyadmin has to do with issues with it’s use of encryption for cookies.  Despite using the unlimited crypto (which fixed one issue) and the latest and greatest version of Quercus, I was still getting errors — it said that the key needed to be a multiple of 8 bytes — I had 16 hex digits.  So, I don’t know about that one.