↓ Archives ↓

Are you stuck in Debian/MySQL/Charset/Collation hell?

So while Debian still hasn’t changed the MySQL default caracter set and collation to utf8, we all know that the first thing to do on a vanilla Debian MySQL installation is to add the following utf8.cnf file to /etc/mysql/conf.d/:


However, if for some reason you didn’t do that and have used software which hasn’t been consistently explicit about character sets and collations, you end up with a nice mess of character sets and collations.

There is a great post on serverfault which helps you out. It comes down to one command which will take some time based on the size of your database:

mysql -B -N --user=user --password=secret -e "SELECT DISTINCT \
TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;' ) FROM \
information_schema.COLUMNS WHERE TABLE_SCHEMA != 'information_schema';" \
| mysql --user=user --password=secret


And of course you need to alter the defaults for existing databases as well:

mysql -B -N --user=user --password=secret -e "SELECT DISTINCT \
utf8_unicode_ci;' ) FROM information_schema.SCHEMATA where SCHEMA_NAME \
!= 'information_schema';" | mysql --user=user --password=secret

First steps with Chef

Today, Jens and I got to play with Chef which supposedly is the hot sh$&!t when it comes to infrastructure automation and such. Installing your own Chef server seems hard at first but will work in the end.

If you’re using Debian, the APT sources will save you some headaches. Just add

deb http://apt.opscode.com/ squeeze main

to your /etc/apt/sources.list and do something like

wget -qO - http://apt.opscode.com/packages@opscode.com.gpg.key | sudo apt-key add -
sudo apt-get update

to be sure to be getting what you asked for. Then, a little sudo apt-get install chef will do the trick to set up a client and sudo apt-get install chef-server will supercharge your node with a fully blown chef server, including but not limited to CouchDB, Solr, RabbitMQ and other fancy stuff. (You’ll want to do this on two different nodes, so use Virtual Box or something.)

After you’ve set up two nodes like that, try following the rest of the instructions in this tutorial and do the first cookbook example, then you’ll have come as far as we have today.

I will update this post as we dig deeper – hopefully later this week.


Today a release candidate for sparkleshare 0.2 came out. Thanks to @jan for making me notice this great software a few days ago. It’s kind of a dropbox or ubuntu one clone but completely open source. You can use it to store your local data on a public server, your own server, your intranet or wherever you trust to put our data. All you need is a git server, ssh and a certificate to access it. No matter if it’s your local data storage, github or plan.io.

Comparing it to dropbox of course there still are a lot of downsides given its early state. There is no sophisticated encryption, only the data transfer is encrypted using ssl certificates. There is no GUI for easy sharing of data: whoever has access to your git repository can access your data, but no one else. However there are already a few nice features working quite well. Among them the easy GUI-based setup, the automatic syncing and the nice way to access old versions o data: right-click on a file, choose a version and a new file appears with the old date in its file name.

There is the source code for linux and mac versions available, binaries and a windows version are expected at a later point of time. In case you are on Ubuntu or Debian I recommend the following steps for installation after getting and unpacking the source from hbons’ github:

# install dependencies for the build process
sudo apt-get install gtk-sharp2 mono-runtime mono-devel monodevelop \
libndesk-dbus1.0-cil-dev nant libnotify-cil-dev libgtk2.0-cil-dev \
libwebkit-cil-dev intltool libtool python-nautilus libndesk-dbus-glib1.0-cil-dev

# using the prefix you enable the nautilus extension
./configure --prefix=/usr

# compile

# use checkinstall instead of make install to install it as a clean debian package
# that can be easily uninstalled using your favorite package manager
sudo checkinstall

# start the service
sparkleshare start

# You possibly have to restart nautilus to enable the plugin
nautilus -q

So go try it out!

Bazaar over chrooted sftp

How to set up bzr for chrooted sftp users

How to restrict a user’s access to sftp://.../var/bzr

For the prototyping editor itself and for a lot of our clients’ projects we are heavy Bazaar users at pidoco° to manage our distributed workflow. When we started some years ago we just installed bzr on one of the test servers where all of the developers had ssh access anyway. We put the repositories in /var/bzr and used sftp to checkout/push/pull source changes. This was handy as a sftp server comes with openssh installed.

As the team grew over the years we got to a point where we wanted to give new developers access to the bzr repositories without giving them full ssh access. However we did not want to have to change all the urls for existing repositories. Luckily this can be achieved easily since Debian Lenny.

Per Server Settings

On our scm server we have a user group called bzr that grants read/write access to most of the repositories (except of some personal or release branches) to all users with bzr access. And now we added the group sftponly. All users in this group will be restricted to sftp access only instead of a full shell.

sudo addgroup sftponly
sudo addgroup bzr

You probably have to add ‘/usr/lib/sftp-server’ to /etc/shells to make it a valid shell, eg. like this:

root@host # echo '/usr/lib/sftp-server' >> /etc/shells

The following settings in /etc/ssh/sshd_config force the internal sftp server to be used by openssh and change the root directory for all users in the group sftponly to /var/chroot. Make sure to restart sshd afterwards.

Subsystem sftp internal-sftp
Match Group sftponly
    ChrootDirectory /var/chroot
    AllowTCPForwarding no
    X11Forwarding no
    ForceCommand internal-sftp

Up to now our repositories have been in /var/bzr. These need to be moved to /var/chroot/var/bzr to let the sftponly users access them. /var/chroot needs to have root:root as owner for openssh to work correctly. For the existing ssh users we add a symbolic link to keep the old paths working:

sudo mkdir /var/chroot
sudo chown root:root /var/chroot
sudo mkdir /var/chroot/var
sudo mv /var/bzr /var/chroot/var
sudo ln -s /var/chroot/var/bzr /var/bzr

Per User Settings

giving the user username sftp access, but nor ssh access:

USERNAME=username                                  # give the user a name
sudo adduser ${USERNAME}                           # add user and data to system
sudo usermod -s /usr/lib/sftp-server ${USERNAME}   # disallow ssh/bash, allow ssh/ftp (sftp)
sudo adduser ${USERNAME} bzr                       # allow group access to most bzr folders
sudo adduser ${USERNAME} sftponly                  # disallow access to /, allow access to /var/bzr

This changes user’s shell to sftp-server.


As a result of these settings both normal ssh users as well as restricted users in the sftponly group can use the same url for their shared repositories
bzr checkout sftp://my.domain/var/bzr/my_repository. By using chroot however users in the group sftponly are restricted to using sftp and can only access the folders in the bzr subdirectory.


In the Debian Administration Weblog you can find information on how to setup an OpenSSH SFTP chroot() with ChrootDirectory and on how to restrict users to SFTP only instead of SSH.

iPad Safari Bug: Touching iFrames

I didn’t want to buy an iPad. But in a recent project for one of our clients we had to optimize a page for the iPad. And that is why I just bought one to fix some bugs we had with that page. After some minutes of searching I came up with a nasty browser bug.

The page we developed contained a slider and an image that you can drag around. Both were implemented using the typical touch events that you know from mobile Safari: touchstart, touchmove, touchend, touchcancel. The page worked on my iPhone and on my iPad as well. But our client put the page into an iFrame in order to include it into his website. And that didn’t work.

After some investigation I discovered that the touch events worked in some situations, but not everywhere on the dom element. It turned out that the position of the iFrame has an impact on the area where the touch events work. Let’s say the iFrame is located 100 pixel below the documents top, then the touch events work anywhere on the image except for the lower 100 pixel. From what I know about browsers my guess is that the ‘cursor’ position is not calculated correctly when passing a touch event into an iFrame.

To get a better understanding, please try this little bug demo on your iPad or iPhone. The sources of the iFrame content look like this:

    <div ontouchstart="alert('touched');"
    <div ontouchstart="alert('won't be touched');"
    <div ontouchstart="alert('touched only in upper half');"

And the iFrame sources:

    <iframe style="position:absolute;top:200px;"
        border="0" src="iframecontent.html" width="400" frameborder="0"
        height="400" scrolling="auto"></iframe>

I reported the bug to Apple, but since their bug tracking is not very motivating, I might add the bug to webkit as well. (I didn’t even get an email telling me that the bug was reported.)

JavaScript Testing and Continuous Integration Part II

In my previous post I described how to use the YUI Test Framework to write JavaScript tests for your web application. This part continues with how to run the tests in different browsers and how this can be used with a continuous integration server.

Executing your tests in different browsers

A framework that is very famous when it comes to browser testing is Selenium. It automates the execution of click streams in different browsers using two major components: the selenium IDE and the selenium remote control. The IDE is a Firefox plugin for recording the click streams. Originally, the click streams are written as plain HTML tables, but over the time selenium added drivers for the most popular programming languages. This way, you may record a click stream and have it transformed into e.g. Java / JUnit, where you then can add several assertions or high level programming constructs. A simple selenium JUnit test may then look like this:

public class MyTest extends SeleneseTestCase {
	protected Selenium selenium;

	public void setUp() {
		selenium = new DefaultSelenium("", 4444,
                                       "*firefox", "http://you.web.server/");

	public void testSomething() {
		// wait for...

Having the test, the selenium remote control come into play. It is used to open a browser, inject some JavaScript code that triggers all the desired events, and click through your app. In our case we use selenium to open a firefox, load the HTML page containing our YUI tests, and wait for them to be finished. (The wait is not shown in here, you have to write it yourself.) When our tests finish, we write an invisible <div> containing the string ‘unittestsFinished’ which indicates selenium that the browser may be closed.

Iterate over your browsers

Now that we have selenium to trigger our JavaScript tests we still need to iterate over all browsers. We also need to trigger selenium somehow. Since the continuous integration server at pidoco is a hudson server, and hudson can execute an ant build script, we use ant to do the job:

<property name="browsers" value="firefox,safari,iexplore"/>
<for list="${browsers}" param="browser" delimiter=",">
		<junit haltonfailure="false" dir="antbuild" fork="yes"
			<sysproperty key="browser" value="@{browser}"/>
			<formatter type="xml" />
			<test name="tests.selenium.SeleniumYUITestsLauncher"
		<get src="http://your.test.server/path/to/junitxml"
		<replace file="tests/TEST-@{browser}-yui-result.xml"
                         token="testsuite name=&quot;"
                         value="testsuite name=&quot;@{browser}."/>

Ant iterates over a list of browsers that we want the tests to be executed in. It then triggers the selenium test via JUnit. The test result in the *selenium-result.xml is not very interesting since it is only one test which opens the yui tests. Using a system property ant tells selenium which browser it should open.

Next, we download the reported YUI test result from our test server to the integration server. You may also use selenium to read the result from the HTML page directly and save it to a file within the JUnit test. However, we had a test report server from the beginning, so we used that to get the results from the browser to our integration server. Once we have the xml file we use a regular expression to add a fake package to the test suite such that the integration server can tell us in which browser a test may be failing.

Continuous Integration

The only thing that is left for the continuous integration server is to call the ant target and read all the xml files in the ‘tests’ folder as JUnit test results. This is pretty easy but allows you to analyse your JavaScript tests, which were executed automatically in different browsers, with all the power of the continuous integration server of your choice.

JavaScript Testing and Continuous Integration Part I

Adding your Tests into a continuous integration system for test automation is quite common among professional software development. However, when it comes to testing JavaScript code, many people lack the experience and best practices to set up a productive infrastructure. We at pidoco have invested quite some time to solve this problem and came up with the setup I describe in this blog post.

The JavaScript Testing Framework

There are countless frameworks out there to use for testing JavaScript code. And I have to admit it is quite confusing to find the best for one’s project. From what I saw up to now, many frameworks look very similar to each other. They do differ in some details, which make people like one or the other. To us, it was important to have a comprehensive documentation of the frameworks capabilities. Therefore, we chose the Yahoo UI test framework. It comes with an extensive documentation, just as you know from the Yahoo UI framework. You can transform a JavaScript Object into a test case, which makes it looking quite similar to JUnit tests:

var testCase = new Y.Test.Case({

    name: "TestCase Name",

    //traditional test names
    testSomething : function () {

    testSomethingElse : function () {

Within your test functions you have a broad variety of assertions that you can use. In addition, you can use the wait() and resume() functions to interrupt a test and wait for asynchronous events to happen. This is very useful if you need to wait for some UI components to be rendered or some server calls to return. Next, you can also trigger dom events using the YUI Event object:


Once you have written your first test case you want to execute the test and review the results. YUI Test comes with a nice test result viewer that you may include into your test site. On the other hand, the tests should be executed in several browsers and you may want to review the results on just one page. Therefore, the framework offers a function to send the result to a URL where a server may collect and show the results. In our first attempt we sent the result as a JSON string to the server, who transformed the collected results in a simple table with the tests in rows and the browsers in columns.

var reporter = new Y.Test.Reporter("http://www.yourserver.com/path/to/target",

Since many continuous integration servers like to read test results in an xml file, YUI allows for several formats to get your results:

  • Y.Test.Format.XML (default)
  • Y.Test.Format.JSON
  • Y.Test.Format.JUnitXML
  • Y.Test.Format.TAP

Oh, and if you asked yourself ‘where the heck is this Y coming from?”, here is the solution:

var Y = YUI();

Being able to write a JavaScript unit or integration test the next question is coming up:

How to test your web app

Following a test driven development a lot of JavaScript has to be written just for the tests. When deploying the web app into the productive environment, we don’t want to include the testing code. But how to separate testing code from productive code? We found two solutions to be possible. First, we use a plugin that loads, triggers, and reports the tests, but is only included into the system during development and test. This way, we simply disable the plugin and have a productive system without any testing code. However, not every web app has a plugin system that supports this solution. Therefore, we have thought of a second way to test your app: deploy a second app that contains a simple HTML page which loads the testing code plus an iFrame containing the web app to be tested.

Please have in mind the same origin policy for the HTML page containing the iFrame and the content of the iFrame. If both are delivered from the same domain, the outer page may access the JavaScript code of the iFrame content. This enables us to call the productive code from the testing code. In case your code either of the application or the test checks for e.g. instances of Array, it fails if the array was created inside the iFrame and checked outside or vice versa. You may create an array using new window.frames[0].Array() and test for an array with a instanceof window.frames[0].Array.

Using the iFrame approach you may enhance the HTML page such that it loads you app and the test code, automatically runs the tests, and listening to the Y.Test.Runner.COMPLETE_EVENT to upload the test result to your reporting server.

How the tests are triggered in different browsers and how this goes with continuous integration is subject of the second part of this blog post.

Where am I?

Recently I have been working for several clients where I had to test programs on different servers. The Unix prompt alone doesn’t always tell you which operating system you are logged on and – it gets worse – there seems to be no convention at all on where to store that information. Arun Singh from Novell has written a nice script to determine a Linux distro. However that script does not help you with other Unices and it isn’t installed on any server anyway. So as a note to myself here is a little collection of commands that help to find out the OS your shell is running on. A few further ideas are listed in this German linux forum.

cat /etc/issue*;
cat /etc/*-release;
cat /etc/*version;
lsb-release -a;
cat /proc/version;

Detecting Safari 5.0 with browscap.ini

There are many ways to identify browsers on the server side. Gary Keith offers a quite complete mapping of user agent strings to browser/os versions enhanced with information about their capabilities. I guess it’s impressive how fast Gary was for all these years in adding new browsers to his browscap.ini and how reliable in keeping it up-to-date. Unfortunately, according to his website, he is currently facing health issues and cannot maintain the project for a while. So if you are using php_browscap.ini but missing Safari 5 or Chrome 6 support you can apply the patch listed below. Until then: Get well soon, Gary!

Gary has uploaded a newer version of browscap.ini which now includes Safari 5 and Chrome 6. You don’t need to apply the patch below anymore.

--- browscap.ini_old    2010-06-22 21:50:30.000000000 +0200
+++ browscap.ini_new    2010-06-22 22:31:55.000000000 +0200
@@ -5504,6 +5504,57 @@

+;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; Chrome 6.0
+[Chrome 6.0]
+[Mozilla/5.0 (Macintosh; U; Intel Mac OS X*; *) AppleWebKit/* (KHTML, like Gecko) Chrome/6.* Safari/*]
+Parent=Chrome 6.0
+[Mozilla/5.0 (Windows; U; Windows NT 5.1; *) AppleWebKit/* (KHTML, like Gecko) Chrome/6.* Safari/*]
+Parent=Chrome 6.0
+[Mozilla/5.0 (Windows; U; Windows NT 5.2; *) AppleWebKit/* (KHTML, like Gecko) Chrome/6.* Safari/*]
+Parent=Chrome 6.0
+[Mozilla/5.0 (Windows; U; Windows NT 6.0; *) AppleWebKit/* (KHTML, like Gecko) Chrome/6.* Safari/*]
+Parent=Chrome 6.0
+[Mozilla/5.0 (Windows; U; Windows NT 6.1; *) AppleWebKit/* (KHTML, like Gecko) Chrome/6.* Safari/*]
+Parent=Chrome 6.0
+[Mozilla/5.0 (Windows; U; Windows NT 7.0; *) AppleWebKit/* (KHTML, like Gecko) Chrome/6.* Safari/*]
+Parent=Chrome 6.0
+[Mozilla/5.0 (X11; U; Linux i686*; *) AppleWebKit/* (KHTML, like Gecko) Chrome/6.* Safari/*]
+Parent=Chrome 6.0
+[Mozilla/5.0 (X11; U; Linux x86_64; *) AppleWebKit/* (KHTML, like Gecko) Chrome/6.* Safari/*]
+Parent=Chrome 6.0
 ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; Google Code

 [Google Code]
@@ -9604,6 +9655,47 @@
 Parent=Safari 4.0

+;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; Safari 5.0
+[Safari 5.0]
+[Mozilla/5.0 (Macintosh; ?; *Mac OS X*; *) AppleWebKit/* (KHTML, like Gecko) Version/5.0* Safari/*]
+Parent=Safari 5.0
+[Mozilla/5.0 (Windows; ?; Windows NT 5.1; *) AppleWebKit/* (*) Version/5.0* Safari/*]
+Parent=Safari 5.0
+[Mozilla/5.0 (Windows; ?; Windows NT 5.2; *) AppleWebKit/* (*) Version/5.0* Safari/*]
+Parent=Safari 5.0
+[Mozilla/5.0 (Windows; ?; Windows NT 6.0; *) AppleWebKit/* (*) Version/5.0* Safari/*]
+Parent=Safari 5.0
+[Mozilla/5.0 (Windows; ?; Windows NT 6.1; *) AppleWebKit/* (*) Version/5.0* Safari/*]
+Parent=Safari 5.0
+[Mozilla/5.0 (Windows; ?; Windows NT 7.0; *) AppleWebKit/* (*) Version/5.0* Safari/*]
+Parent=Safari 5.0
 ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; Lunascape 5.0

 [Lunascape 5.0]

SEO-friendly Affiliate Cookies powered by mod_rewrite

So you want to run an affiliate or partner program, like for example the Planio Partner Program. Good idea. Happy customers who recommend your service to their friends are the best marketing you can get. Why not reward your customers and make them even happier?

From a technical point of view, an affiliate program is nothing fancy at first glance:

  1. give your customer a link with a unique token
  2. once a visitor signs up, check if a token is present and look up the respective customer
  3. reward them!

However! There’s some technical pitfalls.

Keep track of the token

This is a rather easy one: of course, you have to remember the token throughout entire visits. You can’t expect a visitor to turn into a paying customer right on the first page. They will check out your site, visit a couple of pages, and maybe even come back another day to buy your product. You still want to reward your affiliate, so cookies will be your single option.

Don’t mess with Google

We’ve learned this the hard way with Magpie and it took us quite some time to recover our page rank, so be sure to read this! Google does not like duplicate content. If you’re copying what others write on the Web or if you have a lot of pages with similar or even identical content, Google’s algorithms will classify your site as spam. What does this have to do with your affiliate program? Well, all those referral links are different because of the token, yet they will most certainly render the same content.

So what can you do? Redirect. Don’t let your app render a page if the request URI contains an affiliate token. Redirect to the actual page using status code 301 (moved permanently). This way, Google will know that the link is still valid (and thus you will get most of the link juice from referring sites), but that its location has changed.

How to implement?

For a long time, we did this within in our application. Rails makes it really easy using before_filter, so it’s no big deal. However, your setup may be more complex. Maybe you have multiple apps on subdomains or sub-URIs and maybe they run on different frameworks. Just think of your corporate blog, most of the time it’s a WordPress. But you’d still want to reward your affiliates if they send you traffic via a link to a great blog post you’ve written, right?

For Planio, we moved the redirection and cookie part to the Web server. Below is a short and sweet Apache config snippet which works really well for us:

# affiliate cookie
RewriteCond %{QUERY_STRING} (.*)ref=([a-zA-Z0-9]{6})(&(.*))?
RewriteRule ^(.*)$ $1?%1%4 [CO=affiliate:%2:.plan.io:43200,R=301,L]

It does everything for us, so our apps don’t have to worry:

  • detect a token in a request URI (we use a ref= query param with a 6 character token)
  • set a cookie named affiliate using the token value which is valid for all our subdomains and for 30 days
  • redirect to the same page using 301, removing the ref parameter and keeping all other query parameters intact (this is great for other tracking stuff, like the params you can generate for Google Analytics)

In the end, we just need a one-liner in our signup code that reads the cookie, finds the affiliate and associates the affiliate with the newly created account.

Update: Thomas points out that you could tell Google to ignore certain query params and avoid 301 redirects using canonicals. He also claims that Google would be my friend. Not so sure about the last one, though 😉

I hope this was useful to you. Do you run affiliate programs for your products? What are your experiences? How did you implement them? I look forward to your thoughts in the comments!