Remote MySQL GUI with SSH

Posted by Luke Francl
on Tuesday, August 28

Back in my PHP/MySQL days I used to be quite the MySQL console jockey. I used it for all kinds of stuff. Then I got a new job, moved to DB2 and thankfully forgot as much as I could about MySQL. Now I’m doing Rails and working with MySQL again. But these days I use CocoaMySQL for nosing around the database on my local machines.

On remote servers, I was still using the console, but I recently found this trick which allows you to open up CocoaMySQL on a remote database using an SSH tunnel. The database doesn’t have to be configured to accept connections from outside of localhost.

Here’s how it works.

First, create an SSH tunnel.

ssh -L 8888:example.com:3306 user@example.com

Here I’m connecting the free port 8888 on my local machine to 3306 (the MySQL port) on the remote server, logging in as user.

Then configure CocoaMySQL to use the tunnel. Set the host to 127.0.0.1 and the port to 8888. The user, database, and password will be that of your remote server.

(There’s a section in the config screen to use an SSH tunnel, which I think is supposed to create the tunnel automatically, but I wasn’t able to get that to work.)

I’ve found this tip useful in my work. Hopefully you will too!

Finding a job with Craigslist

Posted by Jon
on Saturday, August 25

Guy Kawasaki has a new article called How to Get a Job on Craigslist. He recently posted a job listing there and got 37 good candidates. This is a great reminder of the power of Craigslist.

At Slantwise, we’ve hired four full-time employees in the last few years, and we found two of those on Craigslist. We’ve also worked with about 6 key contractors over the last three years four of those came through Craigslist.

Guy lists a few job application tips in his article. Here are my tips.

1. Build trust. Since any job hiring is based on limited information – a few conversations, not months of actual work – we’re going to hire the person we trust the most. This is a matter of skill (do we trust that you know what you’re doing, and that you can excel in your role?) and personality (do we trust that you’ll work hard, take your job seriously, and work well with others?).

2. Be specific. “Good communicator” doesn’t mean a thing to me. Everyone says that. “Spoke at three conferences” or “blogged weekly for two years” is meaningful. The same goes for project/development skills. Let us know what projects you’ve worked on and what your role has been. Go deeper if possible – “Built a SOAP adapter for (foo)” is better than “experience with web services.”

3. Avoid jargon. If your email looks like it was taken from a “how to write a cover letter” book, or some sort of Dilbert job-application-generator, I won’t take you as seriously as someone else. The right job application will sound professional, but professional in a one-developer-to-another way. If you were out for a drink with a peer from another company, how would you explain what you do?

4. Apply for the right job. Don’t copy-and-paste. Explain why you’d be good for this job, not just any designer/developer/manager job. When I’ve posted to Craigslist, I’ve typically gotten about one generic job inquiry for every personal one, and not surprisingly, the generic ones don’t get much of my time.

5. Worry about the email, not the resume. Sure, send me your resume too, but it doesn’t matter that much. A good email (what used to be the cover letter) should tell me everything I want to know. Namely: what relevant skills do you have? Where have you used them? Have you worked on any open-source or hobby projects? What do you do to further your skills, apart from work? And what do you do in your spare time? It will also set the tone for your application.

6. That said, don’t worry about your application too much. We are not going to hire someone based on the quality and composition of their cover letter or resume, or based on how smoothly a phone interview goes. We’re going to hire someone because of their skills and personality. If don’t interview very well, but you’re a kick-ass developer, guess which part is more important to us?

Next time we need to hire someone, I’m going to use two resources: Craigslist and the local developer community (RUM and MinneBar, mostly). Whether you’re looking to hire, or looking for a job, I highly recommend using these two tools.

Flash Player adds H.264 video support

Posted by Jon
on Tuesday, August 21

Starting today, with Flash Player 9 Update 3 Beta 2, Flash Player will support H.264 video and AAC audio. This is great news for sites hosting online video. It is the equivalent of Microsoft announcing that IE7 would now render web pages exactly like Firefox. In other words, once this becomes reality, online video sites will really only need to worry about one format.

UPDATE: One of the engineers on Flash Player has posted a detailed account of the new Flash Player. The implementation looks well conceived. As expected (below), you will still need to worry about MPEG licensing issues with H.264.

H.264 is a mpeg-4 video codec that provides the best video compression widely available today. That means that H.264 allows better quality video that other codecs, when comparing files of the same size. AAC is a mpeg-4 audio codec (not an Apple codec!) that is a bit better than mp3 and has better licensing terms. (You have to pay royalties when distributing mp3-encoded content, but you don’t with AAC.) See my earlier post on formats and codecs for more info.

This move is great for online video, and bad for On2. Until now, On2’s VP6 codec was by far the best codec available in the Flash Player. VP6 and H.264 are both good codecs, though H.264 has the edge in my experience. The bigger issue is cost. On2’s Flix Engine software is commercial and isn’t cheap, while free H.264 encoders are available (x264). Expect to see less VP6 content over the next year.

A few caveats, though.

First, H.264 encoders may be free, but H.264 is not (strictly speaking). If you make money from H.264 in one way or another, you’ll need to pay royalties. This is true whether you sell a H.264-encoded content, a H.264 encoder or decoder, or make money through other means (subscriptions or advertising). Fortunately, there are minimums – for instance, if you have less than 100,000 subscribers, you don’t need to pay royalties. See the MPEG-LA FAQ for details. This alone may make On2’s one-time cost an attractive option for some businesses.

There is an outside chance that Adobe may have a licensing arrangement that takes care of this, which would be great for content creators, but don’t count on it.

Second, this Flash 9 update will take time to proliferate. It took 9-12 months for Flash 9 to reach 90% market penetration. So unless you’re willing to force your users to upgrade, don’t drop VP6 or H.263 support today.

Third, H.264-encoded video is more compressed than other video, and so it takes more processor power to watch. Most computers these days are plenty fast for H.264, but some users may see their CPUs spike while watching H.264 video. The good news is that early reports say that the new Flash Player will make use of multiple cores on multi-core processors, though this may be a Zompire Dracularius.

Fourth, H.264 has five levels plus sub-levels. Each level allows for better quality and better compression. What level will Flash Player support? The lower levels (1-1.3) are still better than most competitors, but they don’t make use of the codec’s full potential. The Quicktime format, for instance, only supports level 1.3 (if I remember correctly) – not too shabby, but not as good as the MP4 format. It would be great to see Flash Player support at least level 2 H.264. (More info on levels)

Caveats aside, this sounds like great news for video content creators. Keep watching the wires as more details unfold. And watch for updates to RVideo and Spinoza (our forthcoming video transcoding service), which will support Flash/H.264.

Subversion hooks in Ruby

Posted by Luke Francl
on Sunday, August 19

Your source control system is where the knowledge of your team is consolidated and requirements are turned into working code. That process is recorded in the change history and commit comments of the SCM. Hook scripts help you integrate that knowledge into the rest of your development process. I’ll write about Subversion because it’s what I use, but every SCM worth its salt has similar facilities.

Let’s say I’d like to integrate my commit messages with my bug tracker. Systems like CVSTrac and Trac have made this popular, and it’s really useful. At my last job, I wrote a Python script that submitted our commit messages to Bugzilla, which was what we used.

Just for fun, I decided to re-implement it in Ruby using ActiveRecord and the latest version of Bugzilla. Ruby is a nice language for writing Subversion hooks because it has a lot of useful libraries, and it’s easy to run other executables from ruby with ``. Plus, you can still read the code six months later!

Here’s how it works:

When a commit is submitted to Subversion, the post-commit hook runs svn2bugzilla.rb. This script uses svnlook to extract the commit information and searches for strings like “bug #123”, then creates a new comment in Bugzilla including the commit message, the revision, and the files changed for each bug found.

There’s two things I needed to do to get this working:

  1. First, I had to create ActiveRecord classes for the Bugzilla tables representing a bug (bugs, a comment (longdescs), and a user (profiles). These classes don’t use the ActiveRecord conventions, so I had to work around that. The longdescs class has a type column, which ActiveRecord does not like (this strikes me as a major problem for using AR with legacy databases).
  2. Second, I had to use svnlook to get the information I need.

The power of svnlook

Subversion post-commit hooks work by executing a script called hooks/post-commit (note: this script must be executable. Change the file permissions if it’s not working!). By convention, hooks/post-commit should call off to other programs to perform the work. To that end, it provides you with two variables: the repository location, and the commit number.

Using svnlook you can then extract some very useful information from the repository given the revision number. Here’s just a few of the things svnlook can tell you: author, cat (show the files changed), changed (list the files changed), date, diff, and log.

For my script, I needed to know: author, changed, and log. Using them, I can create a message like this:

Bug #1: Re-org for configuration; add comments for clarity.

Revision: 8

Changes:

U   svn2bugzilla.rb

svn2bugzilla.rb

Here’s the code. All the configurable options are at the top of the script. If your subversion user names are not the same as your bugzilla usernames, you can map them in USER_MAP. Then, configure your svnlook location and database connection information and you’re done.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
#!/usr/local/bin/ruby
require 'rubygems'
require 'active_record'
require 'set'

# If your Subversion usernames are not the same as your 
# Bugzilla usernames, map them here.
USER_MAP = {"luke" => "luke@slantwisedesign.com"}

# Location of svnlook binary. Change as necessary.
SVNLOOK = "/usr/local/bin/svnlook"

# Configure your AR connection here. 
# Bugzilla supports both MySQL and PostgreSQL.
AR_CONFIG = {:adapter => 'mysql', 
             :database => 'YOUR_BUGZILLA_DB', 
             :username => 'YOUR_BUGZILLA_DB_USER', 
             :password => 'YOUR_BUGZILLA_DB_PASS' }

# You should not have to change anything below this line.

if ARGV[0].nil? || ARGV[1].nil?
  puts "Usage: svn2bugzilla.rb repos_path revision"
  puts "To be used as a subversion post-commit hook."
  exit
end

REPOS_PATH = ARGV[0]
REVISION = ARGV[1]

ActiveRecord::Base.establish_connection(AR_CONFIG)

# These are the three Bugzilla tables we'll be dealing with.
# It'd probably be less code just to query the database directly, 
# bug using ActiveRecord is more fun!

class Bug < ActiveRecord::Base
  set_primary_key "bug_id"
  # longdescs has a column named 'type' which doesn't play well with AR.
  # select the columns we need manually.
  has_many :longdescs, :select => "comment_id, bug_id, who, bug_when, thetext"
end

# longdescs is the comments table.
class Longdesc < ActiveRecord::Base
  set_primary_key "comment_id"
  belongs_to :bug
  belongs_to :profile, :foreign_key => "who"
end

# profiles is the user table
class Profile < ActiveRecord::Base
  set_primary_key "userid"
end

class Commit
  def initialize(repository_path, revision_number)
    @revision_number = revision_number
    @log_message = `#{SVNLOOK} log #{repository_path} -r #{revision_number}`.strip
    @files_changed = `#{SVNLOOK} changed #{repository_path} -r #{revision_number}`
    @author = `#{SVNLOOK} author #{repository_path} -r #{revision_number}`.strip
  end
  
  def message
    <<MESSAGE
#{@log_message}


Revision: #{@revision_number}

Changes:

#{@files_changed}
MESSAGE
  end

  def author
    if USER_MAP[@author].nil? 
      return @author
    end
    
    USER_MAP[@author]
  end
  
  # return a Set of unique bug numbers in the commit message
  def bug_numbers
    bugs = Set.new
    @log_message.scan(/bug\D{1,3}(\d+)/i).each do |match|
      bugs << match[0]
    end
    
    bugs
  end
end

# Do the actual work of submitting the comment to the database

commit = Commit.new(REPOS_PATH, REVISION)
commit.bug_numbers.each do |bug|
  bug = Bug.find_by_bug_id(bug)
  
  next if bug.nil?
  
  user = Profile.find_by_login_name(commit.author)
  
  next if user.nil?
  
  bug.longdescs.create(:who => user.id, 
                       :thetext => commit.message, 
                       :bug_when => Time.now)
end

Configuring hooks in post-commit

By default, there is no hooks/post-commit file for a Subversion repository. You need to copy the template file named post-commit.tmpl to post-commit and chmod it so it’s executable.

Then, remove any examples from the post-commit script, and add svn2bugzilla.rb:

1
2
3
4
REPOS="$1"
REV="$2"

/usr/local/bin/ruby /path/to/script/svn2bugzilla.rb $1 $2

You can read more about Subversion hooks in the manual.

Testing the script

Testing glue code like this is a bit of a pain because it doesn’t exist on its own. If you install it as a hook and it’s not working, you won’t get any feedback. Since it’s a post-commit hook, the commits will succeed just fine even if the script’s not working.

To test it, you can run the script by hand with svn2bugzilla.rb /path/to/repos rev_number and see what happens.

E-mail Sucks.

Posted by Bruno
on Thursday, August 16

Are you afraid to talk on the phone? Do you have a mysterious, gnawing instinct to avoid face-to-face contact? Do you catch yourself making rationalizations like “I bet he won’t be there if I call, I’ll just e-mail and wait for his response.”

These are signs your brains has vomited on itself and become reliant on your keyboard as its sole communications outlet. Beware.

This happened to me the other day: I had an e-mail exchange going with someone, and their latest request didn’t make sense. He was asking me to do something, but I couldn’t figure out what. My first instinct was to a fire off a quick “Hey, I don’t get this” e-mail and sit happily awaiting his reply.

Then, in an act of self-defiance, I picked up the phone to call him. I hadn’t ever spoken with him before, but hey, the number was right there on the e-mail signature. How hard could it be?

Not very. In all of 45 seconds I had my question figured out and was moving on with my work. E-mail would have taken 10 times as long and would’ve had a much higher chance of miscommunication.

Which brings me to an idea my friend Dan exposed me to: the higher the fidelity of your communication, the better chance you have of making yourself understood. Makes sense, right? A phone with a good connection is better than a walkie-talkie with white noise and static. An MP3 with a high bit rate transmits communicates more than one with a low bit rate.

In other words, the higher the fidelity, the clearer the message. E-mail, with its asynchronism and ability to attach rich documents, is great for certain kinds of messages. But when it comes to quick conversation and explanations, it has a low bit rate. And I think many of us have become so e-mail-saturated that we get lazy and abuse it in simple situations.

Interesting links

Posted by Luke Francl
on Friday, August 10
  • Kristian Köhntopp writes about common MySQL performance problems with Rails. He shows some ignorance of Rails, but most of the issues he raises are important. Every database has its gotchas so at some level, database abstractions like ActiveRecord fall over. Fortunately, as long as you’re aware of the issues Kristian raises, you can work around most of them. Calling attention to Rails’ default of using large varchars and select * by default is especially important.
  • Patrick Reagan’s caches_constants plugin looks like a nice implementation of the common Java pattern of type-safe enumerations backed by the database. That means you can use constants in your code and foreign keys in your database to refer to a set of objects. With Patrick’s plug in, these objects are only queried for once when your Rails app starts up.
  • I thought the Ruby documentation for Object#instance_variable_set was pretty funny:

    Sets the instance variable names by symbol to object, thereby frustrating the efforts of the class‘s author to attempt to provide proper encapsulation. The variable did not have to exist prior to this call.

  • If you’re wondering why you can’t get Bugzilla working with Apache 2.2, the answer is that they’ve changed the default permissions. Raditha Dissanayake has information on how to fix this.
  • Finding the intersection of two date ranges is annoying, but it has a simple solution if you’re clever about it. Ryan Farley visualizes the problem, but “Dithermaster” realized it’s a lot simpler to find out if two ranges don’t overlap, and negate that. His solution is nicely usable in SQL.
  • During Ostrava on Rails I had the pleasure of meeting (and drinking a pivo or three) with Robert Cigán, developer for Czech Rails development shop Skvělý.CZ. He sent in a link to Skvělý.CZ’s latest application, sMoney.eu an easy to use personal accounting app for EU users. Check it out if you’re in the EU!