If only your database would just tell you that replication had failed or that the disk was full…
Ok some database servers do but MySQL doesn’t (yet). Another excuse to write a script

In my pursuit for total database visibility I have been searching for a tool that would tell me when something went wrong, that would simulate regular usage, and let me know if anything failed. After all SNMP can only probe so far and if your database is secure the last thing you want to do is open up another port on your server.

Don’t get me wrong there are tools out there that do a decent job of monitoring MySQL. MySQL Entperprise Monitor is one such tool. But if you want to look under the hood or add some functionality specific to your environment things start to get tricky.

So what’s this dbAlerter?

dbAlerter is the name of a project (Script) that I started developing to provide notification of key server events. It’s written in Python; Why?, I wanted something lightweight. Java would have been my first choice but the JVM eats too much ram and felt too heavy for such simple functionality.
Python on the other hand feels lighter and when running a script as a daemon it seems to take up less resources allowing the DB to use more memory and continue doing it’s job. Plus it’s been a while since I wrote anything with Python, variety is the spice of life!

Speaking of Java, if you have ever used tools like JConsole and jmap (and if not why not!) you may not have realised that since JDK 6 Update 7 there’s now one tool to rule them all - VisualVM.

VisualVM pulls together several commandline tools like JConsole, jstat, jinfo, jstack, and jmap to make monitoring and profiling a more visual experience.

Now that the changes from BSD Java have been merged into the OpenJDK and the FreeBSD Foundation have updated the JDK to 1.6.0-7 I’m pretty sure the original concept of write once run anywhere is closer than ever.

Databases are used by many different disciplines, from your DBA to your Developer, right down to the End User. They can also be viewed from many different perspectives…

DBA’s are usually interested in the server configuration, index usage etc. Developers however are often concerned with retrieval methods, datatypes and occasionally stored procedures. One thing is for sure, your database if used effectively will be changing frequently, and with so many people accessing your database do you know what the last change was? Would you know if someone added an index to a table? (would you care… )

Revision Control is often used to track changes to source code and other documents, so why not your database?.

When I first started to think about change control for my database servers one tool sprung to mind, Rancid. If you manage more than one network device you may already be familiar with the Really Awesome New Cisco confIg Differ.

Using a combination of scripts Rancid will connect to your network devices, check for changes by comparing the config with a VCS managed copy and notify you with details of the changes. Why not do the same for your database? Enter dbRecorder.

Rather than extend Rancid I decided to prove the concept by writing a quick Java application that utilised Open Source libraries like SVNKit and jYAML. The features continued to creep and before I knew it I was recording more than just table definitions. Server configuration and grant tables all found their way into my Subversion repository coupled with e-mail notification whenever anything changed - nice huh!.

The aptly named dbRecorder is currently targeted at my database of choice, MySQL. It’s far from a finished product (then again so are most of my ideas) but it’s working to a degree and can only improve over time….

One thing I never got around to was making this site work well with IE6 (or even just splash the screen with some silly message when someone using IE6 tries to connect) and with Firefox 3 soon to be released I am hoping I won’t have to. If you have not tried Firefox 3 already you can visit the site today and pledge your download to help break a world record as soon as it is released!.

It’s been a while since I had chance to work on Informeer as my itch was one of multi-user web based password management (AuthStor). Oh and moving house.

Now that things are settling down again (Servers back up and running) I decided to take a break from AuthStor and focus on something new - Informeer.

The concept is simple, Centralised Notification.

I am forever configuring notifications from several sources, be it backup alerts, host monitoring notification and even simple applications that send mail via SMTP. When living in a world of change, both software and business, having to visit every application to change an e-mail address or add a new user to a notification schedule can be quite time consuming. Add to that the effort of having to modify firewalls, SMTP servers and XMPP settings etc etc. The idea of a single web interface where all your notifications can be configured is quite appealing. Informeer aims to make that a reality (one day).

While I take my time with the implementation I thought I would post a basic intro to RPC-XML using Perl. The RPC-XML Perl module makes writing your own client/server application a piece of cake, and while not quite point and click, it will give you enough flexibility to centralise your own notifications (or anything else for that matter). It’s not rocket science, but it works!

If you have been looking for a flexible quick and easy fix to the centralised notification problem, or even if you are just looking to push some data from a firewalled site to one of your servers This Tutorial should help get you started.

In between moving house and playing with notifications I found time to upgrade to Wordpress 2.5 and MoinMoin 1.6, and wow what an improvement in both camps.
The Wordpress dashboard is the most visible improvement - it’s amazingly clean! If you have been waiting for 2.5 to settle down or just cautious about upgrading, I would say take the plunge, you won’t regret it. As for MoinMoin 1.6, there really is no better Wiki engine out there (my opinion) and it just keeps getting better with every release!.

That just leaves me to post a quick MySQL tip that may well be obvious, but happens to catch me out from time to time.

MySQL Tip of the month

If you ever get the urge to convert a 1Gig MyISAM table (with 34 million rows) to InnoDB within MySQL, try to avoid the simple ALTER TABLE tablename ENGINE=INNODB; method.
I made the mistake of running that command on a fairly decent server with bags of space and memory only to find the command running over 5 hours later with an InnoDB tablespace at around 4Gig (ok a bit of tuning might have helped)

Your best bet is to dump the table using mysqldump, drop the existing table and re-import the table after modifying the definition to be InnoDB. That worked for me in less than 10 minutes. Alternatively you can create a new InnoDB table and insert directly from the existing table as per MySQL documentation e.g INSERT INTO newtable SELECT * FROM oldtable.

I’m sure I am not the first to make this mistake, and may not be the last…..

AuthStor came to life back in late 2007 after the Password Safe I had been using became corrupt. Not through any failure in Password Safe’s functionality but really to the way it was being used - Multi-User.

I had been seeking a decent Web Based Password Manager (Open Source of course) for ages but had not found anything that I liked. All the semi-decent software out there seemed to be Closed Source with license fees attached and to be honest I would feel more comfortable if I understood the inner workings, especially if this software is storing the key to my empire (mwuhahahaha).

So after a long history of Spreadsheets, Password Safe, Keychain and various other methods of storing passwords I finally decided to scratch the itch and roll my own password manager.

Now AuthStor is not my first attempt at a password manager, in fact I originally started to write an extension to MoinMoin that provided a method of storing passwords directly within the Wiki. This worked great for a while but was not the most secure method of managing passwords, especially as the secret key was embedded within the Python code!. I learned a lot from mmPasswords and hope to improve upon that greatly through the development of AuthStor.

So what is in store for AuthStor? (Get it? ) Well key features will include Multi-User capability, Full auditing, GnuPG encryption, REST like interface and that is just the start. Future plans include interfaces like SMS, VoIP and anything else I can think up.

Here is a sneak peak of the progress made so far:

The application itself is written in Perl and runs on top of the Catalyst Framework. Why Catalyst? Flexibility, Performance, Scalability, the list goes on… Catalyst’s plugin architecture provides multiple views (among many other things) so I can focus on the functionality and not worry about the presentation. Speaking of presentation, AuthStor makes extensive use of the Yahoo! User Interface Library to provide a rich interface with dynamic elements such as DataTable’s, Toolbars and Treeviews and all wrapped in a BSD License!!. Nice work folks!.

Expect a commit in the next few weeks…

It’s been a few weeks since I last posted here or even found time to develop any of the ideas that continually spring to mind. In fact this is the first weekend in several where my head is not stuck in a book about Perl, Solaris or UML. Why you may ask? (or not).

Well after finally getting round to taking the Solaris Network Administrator exam, I thought it only right that I complete the set and get to know Solaris Security a bit better by taking the SCSECA.

After achieving the SCSECA, I was just about getting ready to start writing code again when I stumbled upon the UML Certification Program.
I have been a fan of UML for a long time but never found a tool that I felt comfortable using, or more to the point a tool worth the money!. That was until on the very same day I decided to trial Visual Paradigm, could this be the UML tool I have been seeking?

Visual Paradigm for UML is not overly expensive (many price levels to cater for all requirements), it runs on Windows / Linux / OSX and it’s easy to use - I like it!.

Armed with the UML tool I have been looking for and a copy of the UML 2 Certification Guide I spent another week brushing up on my UML skills while studying for the first of 3 exams offered by the OMG, UML Certified Professional - Fundamental.
I passed the exam last week and learned a lot more UML along the way!
So finally, with all that studying out of the way I can finally get back to the fun stuff - Code.

But where does Perl fall into this jacked-in period? Well I have been waiting for Perl 6 for a long time now and plan to use Catalyst as the platform for BinaryStor and AuthStor. So what better way to get up to speed than a Perl readathon of as many Perl books as I can get my hands on. From Learning to Mastering and eventually Best Practices all over again, you never know I might just finish reading before the release of Perl 6.

As for the Code I cannot make my mind up as to where to focus my energy and so opted for a working NetUnit (I did think of porting it to Perl ). It’s far from complete of course and suffers from the constraint of root privileges, but I’m hoping a future Java release will fix that….

I had one of those lightbulb moments the other day while pondering a problem I recently encountered relating to firewall changes.
The problem came to light the morning after some firewall rules had been modified, nothing new there I hear you say.
Of course at the time everything looked to be working, that is from the perspective of the few people that were actually working that late in the night. It was only when the morning came and the rest of the business started to come online that the problems were apparent. It didn’t take long to fix the problem but it got me thinking, how could we prevent this in future?

I have been spending some time looking at Unit testing recently and began to wonder if there were any testing tools or frameworks that could be used to prevent this kind of problem in future. Unit testing for software is pretty much standard practice nowadays and works well during the development phase, but once the application is in situ how can you test the operation from a networking perspective?. Web testing tools are great for validating web application functionality and I’m pretty sure there are loads of other test frameworks out there, but are they easy to use? I wanted a way to describe and validate the problem before and after the work was undertaken, and so NetUnit was born…

NetUnit aims to be simple to use, something I could give to a non-techie and have them run a Test Suite to validate connectivity or application functionality. NetUnit mimics Apache Ant in its operation and design, in fact I was going to write some Ant tasks at first but kept thinking of new ways to extend the tool in future.

The Test Suite used to describe the Tests to perform is a simple XML file. NetUnit parses this file and outputs the result of each test along the way - simple huh!.

Another benefit of this tool that immediately sprung to mind was the ability to take a Test Suite file and generate documentation for your applications external network interface or firewall policies. In fact the ideas don’t stop there and I have quite a list of features that could be implemented in future. Time permitting as always….

While working on MySQLDump.java this weekend I decided to look into the new features that are heading our way in MySQL 5.1. It cannot be long before we see a general release and I needed to start thinking of ways to backup items like Events. This also made a good opportunity to start afresh with my dev server and try out the latest Solaris 10 8/07 release with GlassFish v2, the releases just keep coming! (SXDE 9/07 was just released).

The Solaris 10 install was as smooth as previous releases (Using Text Based) and once started took at least an hour or so to finish, I cannot remember the exact time as after checking the status a couple of times I forgot all about it. The general feel was much improved however, and the overall speed increase made the upgrade worthwhile.

GlassFish installed and ran as expected - easy. I would be very interested to see the difference in http performance when compared to Apache. Something for another day maybe?

Next on my list was MySQL 5.1.21-beta.
I originally downloaded the pkgadd version but failed to start the server due to a an [ERROR] Can’t find messagefile. It was only after receiving the same message with the Tar distribution that I headed over to the MySQL Bug System where it seemed the issue was a known one - BugID 30678.

Not a major issue as the solution was to simply add --language=./share/mysql/english/ to server install / startup commands. Something that I am sure would have been fixed in the release candidate. I would like to see a more complete installer for Solaris, something similar to the FreeBSD port. The pkgadd install does not really offer much customisation and both the Tar file / pkgadd lack any sample service manifest files.

Once installed everything worked as expected and my first task was to install the Sakila test database. I intend to use this database to validate the operation of the MySQLDump.java app. It may not be the biggest database to work with but it does support a wide variety of data types, views, triggers etc. I am currently using the MySQL Toolkit to verify the backup and restore. If you have not used the Toolkit before (it’s written in Perl) it includes a nice utility that can test if 2 tables are identical - handy for validating a backup.

I envisage using unit tests for this purpose in future, but need to find a good book on TestNG first. A quick dump of the film_text table worked as expected and the data was identical to that supplied by MySQL, I must be on the right track! - If only I had more time…

I did however find time to attend the MySQL Backup and Recovery webinar this morning and was impressed with the new Backup API planned for MySQL 5.2 onward. I just booked my place at the European MySQL Conference and hope to get some more insight into future developments, or at least a copy of the MySQL 5.1 Cluster Certification Study Guide.

It’s been quite a while since I last looked at Google Co-op, but after moving my site recently I realised my Subscribed Links were no longer up to date, let alone working!. I then discovered the Python script I wrote a while back was no longer functioning either, then again it’s no wonder seeing as I had not looked at it for over a year!. Time for an upgrade…

I have been on a Java kick recently, and when not suffering from Vertigo I found myself getting back up to speed with JDK 1.6 and the now final GlassFish V2 release. The difference in speed between JDK 1.5 and 1.6 is certainly noticeable, and as my Google Analytics stats displayed 99% of recent clients having Java support (not necessarily 1.6) I decided a re-write of DeliciousSubscribed was in order.

I started out on the del.icio.us site where there was not much in the way of Java utilities, there was however a well built library - del.icio.us Java API. I would have used that library if it were not for the dependencies on Apache Commons, not that there is anything wrong with Apache Commons I might add!, I just wanted to keep the dependencies to a minimum for size and simplicities sake. So back to drawing board it was, my goal was to simply reproduce the Python script in Java and create an executable jar that can be run from the command line or embedded in another Java app if needed.

The JRE/JDK contains enough libraries to make connecting to the del.icio.us HTTP API and parsing the resultant XML a breeze. I did select one dependency however and that is args4j. Args4j provides simplified command line parsing, comes in under 36k and happens to be licensed under the MIT License so no viral licensing there. It did not take long to get a valid Subscribed Links XML file.

There is still a lot more to do before the code is stable of course and I am sure there are many more features I can add, in fact while re-writing the app I noticed a few cool additions to the Subscribed Links service that I may make use of in a future release:

• Include images in your Subscribed Links.
• Include Google Gadgets in your Subscribed Links.

You never know I might even whip up a JNLP deployed GUI when I finally get around to reading Filthy Rich Clients.

Speaking of features MySQL 5.1 must be getting close to Release Candidate and includes some cool new features such as Partitioning, Row based replication, Table logs and my personal fave Events.
If you are looking for a easy read intro i’d suggest you wander over to the MySQL site where you can find a brief intro into the major new features and bug fixes found in 5.1.

Right I must get back to Warhawk on the PS3. It’s been a while since I got hooked on an online game but this one seems to be stealing every hour I have free!

Whenever I decide to take a holiday I can usually guarantee that something cool will occur in the IT industry in my absence.

Now this holiday has been slightly different in many ways. I had to cancel my flight due to a sudden onset of Vertigo 1 day before I was supposed to fly. If like me you have never experienced Vertigo before, thank yourself lucky!. Having no sense of balance and intense room spin whenever you open your eyes is no fun at all, it took at least 2 days for me to stop being sick.

Having been able to actually use my laptop in the last couple of days I notice that the “cool thing” I usually miss on holiday turns out to be big trouble all over OpenSourceVille, the majority of which seems to revolve around licensing and intellectual property.

Who could miss the BSD vs GPL debate that I’m sure will rage for a long time after the ath5k driver had the BSD license removed in favour of the GPL. Stealing code was suddenly the headline for this action and the rift between the “Linux” developers and “BSD” developers increased once more.
Personally I feel the license should be as “Open” as possible and always apply the BSD license where I can. I want to be recognised as the author of any work I produce and protected against any misuse of such work (liability). With OpenSource development taking place in clear view nowadays, such visibility can only enforce your ideas and intellectual property. Why complicate things with over restrictive licenses…

While still on the topic of licensing it seems John Birrell is having to start all over again with a new port of DTrace on FreeBSD. The CDDL this time preventing kernel hooks from being added to the FreeBSD kernel. This seems such a shame as DTrace technology provides a massive benefit for software developers and will starting afresh really change the situation?, If not through licensing I am sure there must be patents on the intellectual property that Sun could enforce on any DTrace port? Just look at what is going on between NetApp and Sun at the moment.

So from licensing issues to patent infringement where it seems ZFS may not be as revolutionary as Sun claim and in fact NetApp actually came up with some of the functionality back in 1998. Dave’s Blog outlines all the details in what I can only describe as an excellent explanation as to how NetApp have had to file a lawsuit against Sun. Strangely enough I originally heard about this from the MySQL Planet! and not from any of the Solaris / OpenSolaris Planets each of which have around 45 Sun employee feeds?

We develop software to improve our way of life. Licensing and Patents although often necessary seem only to be hindering progress… Still it keeps us busy re-inventing the wheel.

On a good note, I was around to see the launch of the iPod Touch - if you can call that good…I might just wait until the drive space increases before I buy one, 16Gig seems tiny these days but then again the Touch would come in handy for on the go web browsing.
I also noticed Oracle 11g had been released (I obviously missed that one), although only for Linux at the moment?. I’m no Oracle expert but seeing as Oracle provide Java based development tools and my previous installs had a lot of Java involved surely Solaris would make a better fit?

Image combined using artistry from Project TIMEMACHINE - http://www.timemachine.ne.jp/

I started work on a Java class that mimics the functionality of the mysqldump command today.
My goal is to create a standalone application to begin with, but design the class such that it can be plugged into any other Java application requiring MySQL backup capability.

After completing the initial prototype of the dump_table method (minus blob support), I began to wonder what the performance would be like compared to the bundled mysqldump application (written in C).

As this was a prototype with no optimisations, the results were not that bad. The following figures were obtained from running both the C-based mysqldump and the MySQLDump.java app against a fairly large innodb attachments table (about 260mb).

Now this was in no way a strict test environment. I ran the test on my laptop running OSX 10.4.10 connecting to an OpenSolaris server running MySQL 4.0.24 (out of the box) over Wifi. I had to increase the max_allowed_packet and import the large table, but apart from that the server was a clean install.

The mysqldump client was from the MySQL 5.0 packaged install for OSX and the Java VM was version 1.5.0. I did try running the test against the beta JVM 1.6.0 for OSX, but that was 30 seconds slower. I’m sure that will improve when the final version is released and debugging code removed.

I tried a final test by running both commands directly on the OpenSolaris server still connecting via IP.

As you can see there is a slight improvement, probably due to the mysqldump version being older out of the box on Solaris and Java apps running faster on that platform. Of course the final version will check if it is run locally on the server and issue a “SELECT /*!40001 SQL_NO_CACHE */ * INTO OUTFILE”. I just wanted to confirm my view of Java running faster under Solaris.

I wonder how fast this would run under Perl…OK, enough fun with timings, I best get back to writing the code before I head off on Holiday….