A nightmare developer scenario this month.  I have two customers asking for 4 projects with a rush deadline of the 28th of this month.  A single ‘rush’ project is okay, a few extra liters of Midnight Oil (which really should be the name of a coffee shop) and everyone is happy.  But with 4 projects, I’m really in a mental boundary condition.

So, what is a developer to do with so many things going on at the same time?  He gets a little help from project management software.  You know, to get it all laid out in a clean format with discreet steps to keep from being overwhelmed, crawling into a corner, and whimpering softly.

But project management software hasn’t really moved anywhere in the last 3 years.  All of the solutions in the market now are cheap knock-offs of expensive solutions or just crap.

RT was my first try.  Because it has a Perl background, I figured it would be more to my liking, it has a long history, and it is used by a lot of other developers.  The installation process was clear (if you are a Perl programmer) but took almost an hour.  Their was also a lot of work needed for the mail server setup.  In all, I spent about 5 hours on the installation (because setting up mail servers is painful for me).  But I just didn’t like how it worked.  The logic wasn’t intuitive to me.

I moved on and tried several different solutions, so many that I can’t provide links because I don’t remember them.

I settled on FogBugz from Fog Creek Software.  My goal here is to use FogBugz to get me through this dark and busy time.  If it is as awesome as advertised, I’ll keep using it (the hosted solution, I have no desire to go through another long installation process).  I’ll try to come back in here later and give a more thorough thumbs up or down in 45 days.

I am a bad human being.  I confess that 7 years ago, when I was first putting together the Online Talent Manager personality testing system, I (I’m so ashamed to say this)  used ISO-8859-15 encoding in the MySQL database!  The first 6 months were fantastic, the application grew by leaps and bounds and the amount of data collected was impressive.

If you are a system’s administrator, you know exactly what has happened to me in the last 7 years.  A series of ‘little’ encoding problems with half-hearted fixes and a series of failed conversion schemes to ‘fix’ the problem.  Every new table, every new feature, every new anything became a two faze process:

1. Build it.
2. Play whack-a-mole with badly encoded characters.

And it wasn’t that the characters were badly encoded, it was just that I had to fit those characters into the bizarro scheme that had crudded up my applications over the years.  I thought many times about fixing it, really fixing it, and moving everything to UTF-8, but if you have seen any other posts on the internet about this topic, you know that there are horror stories enough to make a grown administrator quake in his sneakers.

But now, I HAVE DONE IT.  And it wasn’t really that painful or weird.  I’m assuming you are using MySQL and Apache on a flavor of Linux.  Here are the steps.

1)  Create a dump of your current database:

mysqldump -u youruser -pyourpass yourdatabasename > yourdatabase.sql

2) Shut down your web application and go through it removing ISO-8859 or any other encoding schemes from your pages and replacing everything with UTF-8.  If you are using some sort of templating system or CMS, this will be a breeze for you.

3) Now let’s clean up that .sql file with the iconv application.  Make sure you have it installed ( man iconv ).  Here is the formula to take your original dump and clean out all of the latin and replace it with gorgeous clean UTF8:

iconv –from-code LATIN1 –to-code UTF-8 yourdatabase.sql > utfclean_yourdatabase.sql

4) Open up your utfclean_yourdatabase.sql and replace all instances of “latin1″ with “utf8″.  I used vim on my 350 mb sql file and it took about 3 minutes:

vim utfclean_yourdatabase.sql

:%s/latin1/utf8/

:wq

5) Edit the mysql configuration file, in my Ubuntu installation, it was located at /etc/mysql/my.cnf.  Add the following line:

character-set-server=utf8

6) Make sure apache is talking utf8 by default.  Again, for me that was located in the /etc/apache2/conf.d/charset file.  I simply removed the comment from the following line:

AddDefaultCharset UTF-8

7) Restart Apache   /etc/init.d/apache2 restart

8) Restart MySQL /etc/init.d/mysql restart

9) Import your cleaned data back into the database:

mysql -u yourname -pyourpassword yourdatabase < utfclean_yourdatabase.sql

10) Your connections are all talking utf8 now, your database is utf8 now, and now it is just a case of cleaning up your own code to remove any hacks you might have installed.  That took about 2 hours for me and mostly it was just commenting out calls to encoding routines.

11) Enjoy your freedom.