[English summary: This discusses a recent legal decision that the domain orango.dk, which was used by one person for an email address since the year 2000, should be transfered to a newly started company with the same name. The decision has been appealed to the courts.]

Som mange andre er jeg bekymret over den afgørelse, som Klagenævnet for domænenavne har foretaget sidste måned. Nævnet afgjorde, at domænet orango.dk, som har været benyttet til email af en privatperson siden 2000, skulle overdrages til et firma startet i 2009.

Som mange andre har jeg registreret et privat domæne primært med det formål at have en email adresse som jeg kan benytte i al fremtid, således at jeg undgår det store besvær ved et skift af email med at oplyse kontakter om den nye adresse samt skifte diverse registreringer bundet til email. Bekymringen går på at denne afgørelse tilsyneladende gør det muligt for enhver at registere en virksomhed med et navn relateret til mit domæne og herefter få Klagenævnet for domænenavne til at fratage mig mit domæne og dermed min email.

Så er det virkelig sådan retstilstanden på området er? Eller er der en fornuftig chance for, at afgørelsen kan blive ændret ved den anke-retsag, som den oprindelige ejer af domænet har anlagt? Jeg nærlæste nævnets afgørelse.

Følgende citater fra afgørelsen er interessante:

Den hjemmeside, der i dag findes ved opslag på domænenavnet "orango.dk", har ingen naturlig tilknytning til domænenavnet "orango.dk".

Det fremgår klart af denne afgørelse, samt af andre afgørelser jeg løb igennem, at nævnet lægger stor vægt på den eventuelle webside, som findes på et domæne. Og som jeg forstår sagen, er det for så vidt korrekt af nævnet at konkludere, at den oprindelige ejer af domænet orango.dk ikke har nogen nævneværdig interesse i at bevare denne webside.

Indklagede findes ikke herudover at have godtgjort nogen væsentlig interesse i at kunne råde over netop domænenavnet "orango.dk" ... indklagedes interesser i det omtvistede domænenavn kan varetages mindst lige så godt gennem registrering og brug af et andet domænenavn.

Det er vel primært denne del af afgørelsen, som virker stødende på mange. Er en email adresse, som har været anvendt gennem 9 år, virkelig ikke af "væsentlig interesse"?

Men nævnet vurderer jo netop, at den oprindelige ejer af domænet ikke har godtgjort en sådan interesse. Og læser man sagsfremstillingen kan man da også se, at der blot er nævnt helt kort at domænet har været benyttet til email. Mens der er fremlagt en statistik for web-trafik på websiden på domænet, er der ikke fremlagt nogen oplysninger for i hvor høj grad domænet rent faktisk har været benyttet til email, om overhovedet?

Der er heller ikke argumenteret fra inklagedes side for at netop domænet orango.dk har særlig betydning for denne anvendelse som email (dette virker jo indlysende for indforståede, idet domænet optræder i diverse adressebøger osv., men jeg tror det er farligt at antage at dette også er indlysende for klagenævnet og at man derfor ikke behøver at argumentere for det).

Nævnet skriver videre om den generelle baggrund for afgørelsen:

... der i den praktiske udmøntning af kravet om god skik vil kunne indgå en række modstående almene hensyn, som må afbalanceres over for hinanden, hvilket efter omstændighederne vil kunne resultere i, at en registrant, som er uden reel interesse i et domænenavn, vil kunne blive pålagt at afstå domænenavnet til en anden, ...

Dette synes at anføre, at en tvungen overdragelse af et domæne alene vil ske hvis den oprindelige registrant er uden reel interesse i domænet. Det virker muligt at argumentere for en reel interesse i at bevare ens primære email igennem 9 år.

Man kunne for eksempel anføre anslået statistik over antal emails modtaget på adressen igennem de 9 år, samt anslået antal kontakter, som har registreret den eksisterende email og som vil skulle oplyses om en ændret adresse. Der er i hvert tilfælde et vist håb for at en sådan argumentation fra inklagedes side ville give nævnet mulighed for en anden afgørelse.

Omvendt har nævnet ikke selv søgt at opklare dette aspekt, så man kunne omvendt frygte, at det udelukkende vil fokusere på websiden trods mere udførlige oplysninger om anvendelsen som email.

Det bliver under alle omstændigheder spændende at følge ankesagen.

Tags: dansk, itpolitik

(2 comments | Leave a comment)

I have now published the Buildbot configuration file that we use for our continuous integration tests in our Buildbot setup. Every push into main and development branches of MariaDB is built and tested on a range of platforms to catch and fix any problems early (and we also test MySQL releases before merging to easily see whether any new problems already existed in MySQL or were introduced by something specific to MariaDB).

The configuration is included in the Tools for MariaDB Launchpad project.

Now, the Buildbot configuration file is not something that most MariaDB users will need or want to care about, of course. But I think it is still very important to have it publicly available, not sitting on some private server of the company Monty Program AB.

The reason is that the whole idea with MariaDB is to make a community branch of MySQL, developed by the community and for the community. We want MariaDB the project to be bigger than Monty Program AB the company. And since the Buildbot testing is so central to the whole MariaDB development process, the Buildbot setup also needs to be available for the community. Want to improve the setup, just see what it is doing, or even set up your own master to show you can do a better job (and yes our Windows setup currently really suck)? Just go ahead! Wondering how the Buildbot setup can be continued if Monty Program AB disappears or turns fascist? Now there is an answer.

Hopefully the configuration can also be useful as an example for people doing fancy things with Buildbot. There is some cool stuff in there. Like creating a source tarball on a linux host, and uploading it to be built on a Windows host (this is how releases are done, so important to check that no files are missing from the source tarball). Another cool thing is the builders that boots op KVM virtual machines on demand to build and test binary packages (.deb, .rpm, and .tar.gz) on all of the 18 Linux platforms we currently release for.

BTW, you do not get the miscellaneous passwords in the published configuration file, sorry! :-)

[The license for the configuration file (which is in fact a sizable Python script, as this is the way Buildbot is configured) is GPL.]

Tags: debugging, developmentprocess, freesoftware, mariadb, mysql, testing

(Leave a comment)

One of the things that I am really happy about in MariaDB is that we have our releases available as apt (and yum for Centos) repositories. This is largely thanks to being able to build this on the OurDelta package build infrastructure (which again builds on things like the Debian packaging scripts for MySQL).

Something like the Debian apt-get package system (which is also used by Ubuntu) is one of the major innovations in the Free Software world in my opinion. Debian has spent many years refining this system to where it is today. Want to run the mysql client, but it isn't installed? Just try to run it on your local Ubuntu host:

    $ mysql
    The program 'mysql' can be found in the following packages:
     * mysql-client-5.0
     * mysql-client-5.1
    Try: sudo apt-get install <selected package>
    -bash: mysql: command not found

Now, MariaDB is not in the distributions yet. However, it is easy to add external repositories like OurDelta into your system, after which packages from the external repositories are available fully integrated with the package system:

    wget -O- http://ourdelta.org/deb/ourdelta.gpg | sudo apt-key add -
    sudo wget http://ourdelta.org/deb/sources/karmic-mariadb-ourdelta.list \
	-O /etc/apt/sources.list.d/ourdelta.list

In order to make all this work, there is a lot of work going on behind the scenes in the scripts that make up the .deb packaging. I think most people underestimate the amount of work and clever engineering that goes into making a well-working .deb package. It is easy to laugh at how behind the latest stable Debian release is on software versions (and I am happy to do this on occasion as well). But Debian is still unique in the sheer amount of software it contains and the level of integration of each package in the whole system. And it is this work through the last more than 15 years that allows something like Ubuntu to exists, with an upgrade system that allows it to do 6-month release cycles to provide up-to-date software to its users.

As an example of the kind of details that needs to be dealt with, I wanted to explain a tricky packaging bug I fixed recently.

The bug was with installing MariaDB 5.1.39 on top of an existing MySQL 5.0 installation in Debian and Ubuntu. This is supposed to automatically run the mysql_upgrade program to upgrade all tables from the 5.0 format to the 5.1 format. The symptoms were that the upgrade was not performed correctly, for example the system tables in the mysql database were missing some of the columns added in the 5.1 version.

What made this tricky was that the bug was quite elusive. It did not happen always, and some platforms were ok while others (eg. Ubuntu Hardy amd64) seemed to have it more or less repeatedly. Even worse, even when it did occur, it went away by itself (this is because the .deb MariaDB and MySQL packages actually check for the need to upgrade the database on every server start, and the upgrade procedure always worked when the server was restarted after installation).

After poking for this for some hours, I managed to get it reproducibly on a KVM virtual machine containing Ubuntu Hardy. I then traced the problem into the upgrade procedure, which happens in the /etc/mysql/msyql_upgrade script which is run from /etc/init.d/mysql start. Comparing the log from this (in syslog) with the corresponding log from a successful installation showed that the upgrade procedure seemed to be aborted half-way through, but with absolutely no output (like an error message) to indicate any reason for this.

Now the fact that running the upgrade procedure (or just server start) manually did not exhibit any problems made it quite puzzling to figure out what was going on. Looking through the source code of mysql_upgrade and mysqlcheck (which is called from mysql_upgrade) did not reveal anything that would indicate a problem like this. One step would be to start instrumenting the code with printouts, but this would require building a full set of packages and installing them inside a virtual machine for every iteration, which would have been quite time-consuming.

A better approach turned out to be to install the package running under strace -f. This generates a log (45 MByte of it!) of all system calls made by the installation process, including child processes like the mysql_upgrade invocation. Digging through this for a while, I finally discovered that the upgrade process was being terminated because it received a SIGHUP (hangup) signal!

Now why would it be killed with SIGHUP? Turns out it is due to this snippet from the /etc/mysql/debian-start bash script:

    (
      upgrade_system_tables_if_necessary;
      check_root_accounts;
      check_for_crashed_tables;
    ) <&2 &

Now finally the problem is understood, and the fix is a one-liner: just add this before starting the background job:

    trap "" SIGHUP

[Incidentally, if you installed MariaDB 5.1.39 .debs and experienced this problem, there is an easy workaround for this bug: just restart the MariaDB server once after installation (sudo /etc/init.d/mysql restart). This will make the upgrade procedure go through if it did not already. This fix will be included in the upcoming MariaDB 5.1.41 release.]

Tags: debian, debugging, freesoftware, mariadb, mysql, programming, ubuntu

(Leave a comment)

Most of this year I have been working on the MariaDB project. So it is interesting to look back and see what has been achieved.

For those that do not know, MariaDB is a project to create a community-oriented branch of the MySQL code base. We want MariaDB to be developed for the community, by the community, and driven by the needs of the community.

Turns out that a lot has been achieved already:

• We have had three releases (and a fourth is being prepared currently). The code is getting close now to release candidate.
• We have apt-able (and yum-able on Centos/RHEL) repositories for the releases. These are based on the OurDelta infrastructure (scripts, build machines, etc). This means MariaDB installation and upgrade can be done the prefered way using the built-in package management tools of Linux distributions, without having to wait for MariaDB to be included in the next release of ones favorite distribution.
• We have collected a lot of the exiting external patches and storage engines floating around in the community and put them together in a single MariaDB package. Want to test out PBXT and XtraDB against each other in the same server process? This is now just an apt-get install or ./configure away; no need to collect multiple source trees/patches together manually.
• We have the basic infrastructure up for developing the MariaDB project: Web site, mailing list, Launchpad project, Source code repository, IRC channel #maria on FreeNode with archive, Continuous integration testing framework (using Buildbot), development procedures etc. etc.
• We are continually merging the latest changes from MySQL 5.1, so MariaDB can keep track with any bug fixes and changes (the upcoming MariaDB release will include MySQL 5.1.41).
• We have a company Monty Program AB employing a number of the old MySQL core hackers and driving development of MariaDB. We have Open Query engineers working on binary packages of MariaDB, through OurDelta. We have PrimeBase developing the PBXT storage engine and making it work well in MariaDB. We have Percona developing XtraDB and the Percona patches and helping making them work well with MariaDB. We have Patrick Galbraith andAntony Curtis working on getting things like FederatedX and OQGraph working well in MariaDB. And many others.
• We have numerous new features and bug fixes, available in a MariaDB release that is a plug-in replacement for MySQL for those who need it or just want to try out the new stuff.
• And lots more ...

One thing I see we have not been doing much of so far is speading the word that MariaDB is here and ready to try and use for everyone interested. So this is something we need to put more focus on going forward. I guess we have all just been too busy initially getting things running, and then as MariaDB started to get into shape we just continued as before without realising that we have moved into the next phase.

We have already planned that developers from MariaDB and PBXT will be manning a stand and presenting at FOSDEM 2010, so if you are there and want to hear about what we are doing, be sure to connect up with us! We are also planning a presence at the 2010 O'Reilly MySQL Conference & Expo.

If anyone wants to follow the day-to-day activity of MariaDB development, the best place is probably the IRC channel #maria on FreeNode (archive). The mailing list maria-developers@lists.launchpad.net is also a good place to follow development (requires Launchpad and membership approval, but we approve everyone who is interested!)

Tags: freesoftware, mariadb

(5 comments | Leave a comment)

Testing and automation. These two are key to ensuring high quality of software releases.

Ever since I worked briefly in the team at MySQL AB that is responsible for creating the binary (and source) packages of MySQL releases, I have had the vision of a fully automated release procedure. Whenever someone pushes a new commit to the release branch revision control tree, the continuous integration test framework should kick in and do all the steps needed for producing release packages:

• Checkout the new revision.
• Build a source tarball, and save it.
• For each platform, build a binary package from the source tarball. The build should be done in a freshly installed machine without any revision control checkouts, previous build trees, or extra installed software, to ensure that no unwanted dependencies or stray references to other files or packages are introduced.
• For each platform, install the binary packages, this time on freshly installed machines with also no build tools (compilers, development packages, etc.) installed, to check that they install correctly without unexpected dependencies. Run tests of the installed server, including starting the server and running basic queries and test suites.
• Upgrade testing, installing the new packages on machines prepared with earlier installations, and testing that the upgrade procedure works and preserves old data.

To do this efficiently, clearly the use of virtual machines is needed. This weekend I played with KVM and Buildbot, and managed to set up a proof-of-concept of this that I am really pleased with.

KVM

There are lots of options for virtualisation these days, including KVM, Xen, VirtualBox, and Vmware. I use KVM, and I really like it. The integration into the distributions is excellent (sudo apt-get install kvm and you're up and running). The interface is powerful and flexible, and at the same time really simple to learn and use. Just a couple of commands with man pages, like it should be in a Unix system. Basically, it just works!

I started by installing a basic ubuntu Jaunty server in a virtual machine:

  qemu-img create -f qcow2 vm-jaunty-i386-base.qcow2 8G
  kvm -m 2047 -hda vm-jaunty-i386-base.qcow2 -cdrom ubuntu-9.04-server-i386.iso \
    -boot d -smp 2 -cpu qemu32,-nx -net nic,model=virtio -net user -redir tcp:2222::22

Using the virtio network driver greatly improved throughput for me when copying things into and out of the virtual machine. The -cpu qemu32,-nx (disable "No eXecute" support) is needed in this case due to some bug or incompatibility, or the installation hangs upon reboot. As usual Google is your friend in cases like this:

• https://bugs.launchpad.net/ubuntu/+source/kvm/+bug/396219
• http://episteme.arstechnica.com/eve/forums/a/tpc/f/96509133/m/303006642931

Incidentally, I did this using remote X over an SSH connection. This works fine, no need for physical access to the host server. After installation we will run the virtual machine without a graphic console, but it was just easier to use the stock Ubuntu installer than trying to find a way to install over the emulated serial port.

Initial setup

Next I did some basic preparation to make the installed virtual machine work well for command line and script usage. However, the amount of extra packages installed is kept to a minimum to get proper testing against unwanted dependencies.

I Installed ssh server for remote access. I then set it up to use the serial console (as we will be running kvm in -nographic mode). To get a login prompt on serial port 0, create /etc/event.d/ttyS0:

    start on stopped rc2
    start on stopped rc3
    start on stopped rc4
    start on stopped rc5
    stop on runlevel 0
    stop on runlevel 1
    stop on runlevel 6
    respawn
    exec /sbin/getty 115200 ttyS0

    serial --unit=0 --speed=115200 --word=8 --parity=no --stop=1
    terminal --timeout=3 serial console

    sudo adduser --disabled-password buildbot
    sudo adduser buildbot sudo
    sudo visudo
    # uncomment `%sudo ALL=NOPASSWD: ALL'

    ssh-keygen -t dsa

    kvm_pid_2222' ; exec kvm -m 2047 -hda /kvm/vms/vm-jaunty-i386-makedist.qcow2 \
        -redir 'tcp:2222::22' -boot c -smp 2 -cpu qemu32,-nx -nographic \
        -net nic,model=virtio -net user
    # We should get a login prompt in the terminal window
    ssh -p 2222 buildbot@127.0.0.1 'sudo id'
    # We should get root access without login or sudo asking for password.

    qemu-img create -b vm-jaunty-i386-base.qcow2 -f qcow2 vm-jaunty-i386-makedist.qcow2

    sudo apt-get install bzr
    sudo apt-get build-dep mysql-5.1-server

    scp -rp -P 2222 .bzr buildbot@127.0.0.1:

    qemu-img create -b vm-jaunty-i386-base.qcow2 -f qcow2 vm-jaunty-i386-build.qcow2

    qemu-img create -b vm-jaunty-i386-base.qcow2 -f qcow2 vm-jaunty-i386-install.qcow2

     sudo adduser --system --group mysql

    sudo apt-get install buildbot
    sudo addgroup buildbot kvm  # To allow buildbot to run kvm
    sudo -u buildbot buildbot create-slave --usepty=0 /var/lib/buildbot/maria-slave \
        hasky.askmonty.org:9989 knielsen-kvm-x86 <password>

f_kvm_jaunty_x86.addStep(Compile(
        logfiles={"kernel": "kernel_2222.log"},
        command=["sh", "-c", """
kill -9 "$(cat kvm_pid_2222)"
(exec sh -c "echo \$\$ > 'kvm_pid_2222' ; exec kvm -m 2047 \
    -hda /kvm/vms/vm-jaunty-i386-makedist.qcow2 -redir 'tcp:2222::22' -boot c \
    -smp 2 -cpu qemu32,-nx -nographic -net nic,model=virtio -net user" \
    </dev/null >kernel_2222.log 2>&1) &
sleep 15
while : ; do ssh -o ConnectTimeout=4 -p 2222 buildbot@127.0.0.1 true && break; sleep 2; done
ssh -p 2222 buildbot@127.0.0.1 'mkdir -p buildbot && cd buildbot && \
    rm -Rf build && bzr co "lp:~maria-captains/maria/mariadb-5.1-knielsen" build && \
    cd build && BUILD/compile-dist && make dist && \\
    mv "$(make show-dist-name).tar.gz" ..'
"""]))

f_kvm_jaunty_x86.addStep(SetProperty(
        property="distname",
	command=["ssh", "-p", "2222", "buildbot@127.0.0.1", "cd buildbot/build && make show-dist-name"],
        ))

f_kvm_jaunty_x86.addStep(ShellCommand(
	command=["sh", "-c", WithProperties("""
scp -P 2222 buildbot@127.0.0.1:buildbot/%(distname)s.tar.gz .
ssh -p 2222 buildbot@127.0.0.1 'sudo shutdown -h now'
while : ; do sleep 5; kill -0 "$(cat kvm_pid_2222)" || break; done
rm -f kvm_pid_2222
""")],))

f_kvm_jaunty_x86.addStep(Compile(
        command=["sh", "-c", WithProperties("""
qemu-img create -b /kvm/vms/vm-jaunty-i386-build.qcow2 -f qcow2 vm-tmp-2222.qcow2
kill -9 "$(cat kvm_pid_2222)"
(exec sh -c "echo \$\$ > 'kvm_pid_2222' ; exec kvm -m 2047 -hda vm-tmp-2222.qcow2 \
    -redir 'tcp:2222::22' -boot c -smp 2 -cpu qemu32,-nx -nographic -net nic,model=virtio \
    -net user" </dev/null >>kernel_2222.log 2>&1) &
# ...
ssh -p 2222 buildbot@127.0.0.1 'rm -Rf buildbot && mkdir buildbot'
scp -P 2222 %(distname)s.tar.gz buildbot@127.0.0.1:buildbot/
ssh -p 2222 buildbot@127.0.0.1 'cd buildbot && tar zxf %(distname)s.tar.gz && \
    cd %(distname)s && ./configure ...'
# ...
""")],))

f_kvm_jaunty_x86= factory.BuildFactory()
f_kvm_jaunty_x86.addStep(Compile(
        description=["making", "dist"],
        descriptionDone=["make", "dist"],
        logfiles={"kernel": "kernel_2222.log"},
        command=["sh", "-c", """
kill -9 "$(cat kvm_pid_2222)"
(exec sh -c "echo \$\$ > 'kvm_pid_2222' ; exec kvm -m 2047 \
    -hda /kvm/vms/vm-jaunty-i386-makedist.qcow2 -redir 'tcp:2222::22' -boot c -smp 2 \
    -cpu qemu32,-nx -nographic -net nic,model=virtio -net user" \
    </dev/null >kernel_2222.log 2>&1) &
sleep 15
while : ; do ssh -o ConnectTimeout=4 -p 2222 buildbot@127.0.0.1 true && break; sleep 2; done
ssh -p 2222 buildbot@127.0.0.1 'mkdir -p buildbot && cd buildbot && \
    rm -Rf build && bzr co "lp:~maria-captains/maria/mariadb-5.1-knielsen" build && \
    cd build && BUILD/compile-dist && make dist && \
    mv "$(make show-dist-name).tar.gz" ..'
"""
                 ],
        ))
f_kvm_jaunty_x86.addStep(SetProperty(
        property="distname",
	command=["ssh", "-p", "2222", "buildbot@127.0.0.1",
                 "cd buildbot/build && make show-dist-name"],
        ))
f_kvm_jaunty_x86.addStep(ShellCommand(
        description=["copying", "tarball"],
        descriptionDone=["copying", "tarball"],
        logfiles={"kernel": "kernel_2222.log"},
	command=["sh", "-c", WithProperties("""
scp -P 2222 buildbot@127.0.0.1:buildbot/%(distname)s.tar.gz .
ssh -p 2222 buildbot@127.0.0.1 'sudo shutdown -h now'
while : ; do sleep 5; kill -0 "$(cat kvm_pid_2222)" || break; done
rm -f kvm_pid_2222
""")],
        ))
f_kvm_jaunty_x86.addStep(Compile(
        description=["making", "bintar"],
        descriptionDone=["make", "bintar"],
        logfiles={"kernel": "kernel_2222.log"},
        command=["sh", "-c", WithProperties("""
qemu-img create -b /kvm/vms/vm-jaunty-i386-build.qcow2 -f qcow2 vm-tmp-2222.qcow2
kill -9 "$(cat kvm_pid_2222)"
(exec sh -c "echo \$\$ > 'kvm_pid_2222' ; exec kvm -m 2047 -hda vm-tmp-2222.qcow2 \
    -redir 'tcp:2222::22' -boot c -smp 2 -cpu qemu32,-nx -nographic -net nic,model=virtio \
    -net user" </dev/null >>kernel_2222.log 2>&1) &
sleep 15
while : ; do ssh -o ConnectTimeout=4 -p 2222 buildbot@127.0.0.1 true && break; sleep 2; done
ssh -p 2222 buildbot@127.0.0.1 'rm -Rf buildbot && mkdir buildbot'
scp -P 2222 %(distname)s.tar.gz buildbot@127.0.0.1:buildbot/
ssh -p 2222 buildbot@127.0.0.1 'cd buildbot && tar zxf %(distname)s.tar.gz && \
    cd %(distname)s && CC="gcc -static-libgcc" CXX="gcc -static-libgcc" \
    CFLAGS="-O2 -fno-omit-frame-pointer -g" CXXFLAGS="-O2 -fno-omit-frame-pointer -g" \
    ./configure --prefix=/usr/local/mysql --exec-prefix=/usr/local/mysql \
    --libexecdir=/usr/local/mysql/bin --localstatedir=/usr/local/mysql/data \
    --with-server-suffix=1 --with-comment="(MariaDB - http://askmonty.org/)" \
    --with-system-type=linux-gnu --enable-shared --enable-static --enable-thread-safe-client \
    --enable-local-infile --with-big-tables --with-libwrap --with-ssl --without-docs \
    --with-readline --with-extra-charsets=all --with-embedded-server --with-libevent \
    --with-partition --with-zlib-dir=bundled --with-plugins=max-no-ndb && make -j3 && \
     sudo rm -Rf /usr/local/mysql && sudo make install && \
     sudo mv /usr/local/mysql /usr/local/%(distname)s-Linux-x386 && \
    tar zcf ../%(distname)s-Linux-x386.tar.gz -C /usr/local %(distname)s-Linux-x386/'
scp -P 2222 buildbot@127.0.0.1:buildbot/%(distname)s-Linux-x386.tar.gz .
ssh -p 2222 buildbot@127.0.0.1 'sudo shutdown -h now'
while : ; do sleep 5; kill -0 "$(cat kvm_pid_2222)" || break; done
rm -f kvm_pid_2222
""")],
        ))
f_kvm_jaunty_x86.addStep(Test(
        description=["testing", "bintar"],
        descriptionDone=["test", "bintar"],
        logfiles={"kernel": "kernel_2222.log"},
        command=["sh", "-c", WithProperties("""
qemu-img create -b /kvm/vms/vm-jaunty-i386-install.qcow2 -f qcow2 vm-tmp-2222.qcow2
kill -9 "$(cat kvm_pid_2222)"
(exec sh -c "echo \$\$ > 'kvm_pid_2222' ; exec kvm -m 2047 -hda vm-tmp-2222.qcow2 \
    -redir 'tcp:2222::22' -boot c -smp 2 -cpu qemu32,-nx -nographic -net nic,model=virtio \
    -net user" </dev/null >>kernel_2222.log 2>&1) &
sleep 15
while : ; do ssh -o ConnectTimeout=4 -p 2222 buildbot@127.0.0.1 true && break; sleep 2; done
ssh -p 2222 buildbot@127.0.0.1 'rm -Rf buildbot && mkdir buildbot'
scp -P 2222 %(distname)s-Linux-x386.tar.gz buildbot@127.0.0.1:buildbot/
ssh -p 2222 buildbot@127.0.0.1 'cd buildbot && \
    sudo rm -Rf /usr/local/mysql /usr/local/%(distname)s-Linux-x386 && \
    sudo tar zxf %(distname)s-Linux-x386.tar.gz -C /usr/local/ && \
    sudo ln -s %(distname)s-Linux-x386 /usr/local/mysql && cd /usr/local/mysql && \
    sudo sudo chown -R mysql . && sudo chgrp -R mysql . && \
    sudo bin/mysql_install_db --user=mysql && sudo chown -R root . && \
    sudo chown -R mysql data mysql-test && \
    cd mysql-test && sudo su -s /bin/sh -c "perl mysql-test-run.pl alias" mysql'
ssh -p 2222 buildbot@127.0.0.1 'sudo shutdown -h now'
while : ; do sleep 5; kill -0 "$(cat kvm_pid_2222)" || break; done
rm -f kvm_pid_2222
""")],
        ))

bld_kvm_jaunty_x86 = {'name': 'kvm-jaunty-x86',
                      'slavename': 'knielsen-kvm-x86',
                      'builddir': 'kvm-jaunty-x86',
                      'factory': f_kvm_jaunty_x86,
                      }

c['builders'].append(bld_kvm_jaunty_x86)

Tags: developmentprocess, mariadb, mysql, programming, testing

(Leave a comment)

Like so many others, I got interested in the Drizzle project when it started. Some good ideas, lots of enthusiasm, and just pure GPL license, no "yes, we will take your work for free and sell proprietary licenses to it" SCA.

I even started contributing some development, fixing a number of Valgrind-detected bugs in Drizzle. I am proud that we kept the MySQL code 100% free of Valgrind errors, and wanted to help keep the same in Drizzle. So I debugged and fixed quite a few of the Valgrind-detected bugs that had crept in since forking from MySQL.

As I remember, I got down to two or three remaining or so. However, I it did discourage me somewhat to see how quickly these bugs had been allowed to enter the code. I remember one case where there was a Drizzle patch that had tried to simplify some field types. As I remember, the patch tried to simplify the code by eliminating some of multiple variants of string types. All well and good, but then there was one place where this elimination was a bit tricky, and the patch just #ifdef'ed out the offending part of the code, leaving the resulting code completely broken, as detected by Valgrind. And this had been in the source for 4 months! Cleaning up code is good, but not if only the easy 90% is done, and the rest is left undone. [Later the Drizzle people started the nonsense with "Drizzle is GPL, but contributions are considered licensed to Sun under BSD", and I kind of lost interest.]

Anyway, so half a year later I though it would be interesting to see how the state of Valgrind is for Drizzle nowadays. So I branced the latest lp:drizzle (and lp:libdrizzle), built it, and ran the test suite under Valgrind:

    (cd tests && ./mtr --valgrind --force)

Unfortunately, the results are not good: 1900 Valgrind warnings!

The warnings are all kinds: Memory leaks, mismatched free()/delete, uninitialised memory accessed from system calls or conditional jumps, etc. Some are probably benign or even false positives from Valgrind. Some are probably minor bugs, like tiny memory leaks in seldom-used features or garbage in log output. And some are most definitely serious bugs in the code that need to be fixed. With a flood of 1900 errors, it is impossible to tell which is which without days of careful study of the errors and debugging of the code.

I hope Drizzle will fix these issues. I have a lot of experience with Valgrind, and I know how hard it is both to debug and fix the issues reported, and also to get all developers to understand the importance of not allowing code into the tree with Valgrind problems. But I have also learned how many real, serious problems Valgrind can detect, problems that are often impossible to otherwise catch during development. Valgrind warnings can be caused by benign problems, but they are very rarely false alarms. But it is important to fix problems quickly, otherwise the number of problems will pile up until the sheer mass of issues makes it impossible to ever get back to a clean state with zero warnings in the test suite.

(Drizzle has done other good stuff. Like Building with -Werror -pedantic -Wall. This is something I hope we can soon duplicate in MariaDB. We do have a clean Valgrind test run in MariaDB, and make sure we keep it by running every push through Valgrind in Buildbot).

Tags: developmentprocess, drizzle, mariadb, mysql, programming, valgrind

(4 comments | Leave a comment)

Among other things, these past few months I have been working on setting up Buildbot, including adding various enhancements and bug fixes that are needed to properly build and test the MariaDB and MySQL code base.

Since Buildbot is written in Python, this means I have also had to learn Python. I am an old-time Perl hacker, so this exercise feels a bit like living in enemy territory ;-)

Anyway, Python is often touted as a more "pretty" language. And in many ways it is. Still, it is not without its own gotchas. Think "scope rules". Obviously someone haven't been reading up on the subject before implementing things in Python, causing the language to behave stupidly (and certainly different from what one expects) in the following three cases that I hit during my Buildbot work.

First assignment is implicit scope declaration

    def foo():
	s = 0
	def inc():
	    s = s + 1
	print s
	inc()
	print s

UnboundLocalError: local variable 's' referenced before assignment

This is the work-around:

    def foo():
	s = { 'blarg': 0 }
	def inc():
	    s['blarg'] = s['blarg'] + 1
	print s['blarg']
	inc()
	print s['blarg']

Class vs. instance members

    class Bar:
	s = 0
	def foo(self, x):
	    self.s += x
	    print self.s

    a = Bar()
    a.foo(5)
    b = Bar()
    b.foo(8)

Ah, the answer is another variant of assignment creating a new scope. Look at self.s += x. This statement first reads s.self, which provides the value of the class member. It then assigns the new value to s.self, but since this is assignment, it now refers to an instance member, so it creates a new instance member! I don't know what those Python guys where thinking when they made self.s refer to two different variables in a single statement ...

So this means that while the above example works as expected, this very similar one does not:

    class Bar:
	s = []
	def foo(self, x):
	    self.s.append(x)
	    print self.s

    a = Bar()
    a.foo(5)
    b = Bar()
    b.foo(8)

The work-around here is to initialise member variables in the constructor __init__(), not in the class declaration.

    class Bar:
	def __init__(self):
	    self.s = []
	def foo(self, x):
	    self.s.append(x)
	    print self.s

    a = Bar()
    a.foo(5)
    b = Bar()
    b.foo(8)

Late-binding closure construction

    b = []
    for i in range(10):
	b.append(lambda x: i)

    b[0](42)
    b[3](42)

The reason is apparently that the closure created by (lambda ...) does late binding of captured outer variables, meaning that it refers to the name, not to the value at the time of closure construction. This is unlike any other language I have ever seen that has lexical scoping, so quite confusing.

I know of two work-arounds in this case, neither of them pretty.

One is to use a dummy extra parameter with a default value:

    b = []
    for i in range(10):
	b.append(lambda x, dummy=i: dummy)

    b[0](42)
    b[3](42)

The other work-around is to build and call an extra closure to force binding to the correct value:

    b = []
    for i in range(10):
	b.append( (lambda j: (lambda x: j)) (i) )

    b[0](42)
    b[3](42)

Something to be aware of for an old-time Perl hacker like me, used to using functional style when programming...

Tags: mariadb, mysql, programming, python

(6 comments | Leave a comment)

This is a followup to part 1 of the story, where I found that a hanging ftp transfer was caused by one of my network components not being able to transmit certain bit patterns.

After getting on-site, I had the chance to move around cables to test each component in isolation.

I was quite surprised to learn that the problem seems to be with my old Linksys WRT54GS router. This router has one WAN Ethernet port, four LAN Ethernet port that work as a 4-port switch, and a wireless WLAN "port"/antenna; and the box performs routing/NAT among the three parts.

When I use my test server to send my magic bytes between two hosts both connected to the 4-port LAN switch on the Linksys, there is no problem, nor between two hosts both connected to the WLAN. When I send the data in through the WLAN, and out through the LAN, there is also no problem.

But when I send the data in through the LAN and out through the WLAN, the transfer hangs due to repeated checksum errors on the data packet. Same if I send in through the LAN and out through the WAN. And same if I send in through the WAN and out through either LAN or WLAN.

So, in summary, the problem happens whenever the data is routed inside the Linksys from any Ethernet port to any other port. But only when it is routed, not when it is merely switched among the 4 LAN ports. And only when going into an Ethernet port.

I of course tried re-cycling the power on the Linksys, switching cables, and switching among the LAN ports. No difference. So I am forced to put the blame on the little blue Linksys box, which is a pity as it has served me well for more than 3 years and I had gotten rather fond of it.

So problem solved! Replacing the Linksys, I should be up and running without problems again. Still, I am left wondering about two questions:

1. This is not the kind of problem I would expect to turn up after 3 years of flawless service; on the other hand I also find it hard to believe that I would have not discovered this problem for 3 years if it was there from the start.
2. I was very surprised to find this problem connected with the routing. I would have expected some hardware problem, like a bad cable or marginal Ethernet port signal processing. But the Ethernet connection works in switch mode and not in router mode. This sounds like a software problem with the driver or IP stack inside the Linksys. A bug that depend on a certain bit string pattern I would not have thought a software problem (Ie. a memcpy() but that depends on the bit pattern copied sounds quite unlikely).

Tags: debugging, network

(Leave a comment)

As this story shows, the cause of a network problem is not always where you first suspect...

So I just set up an ftp server on my home network for easy file transfer with some family members. Everything was working fine, except ... occasionally, file transfers would just hang, for no apparent reasons. Logs did not say anything.

So I of course first thought that I made some error in the setup of the ftp server. The server is behind two NAT routers, and ftp is of course tricky with NAT due to the use of multiple associated connections. I did try during the setup to properly configure masquerading on the server and correct port forwarding in both NAT boxes (ports 20 and 21 and some range of ports for passive ftp connections), but clearly there are potential for errors here.

So I started by double-checking port configuration. It was fine. So I go read up on the details of the ftp protocol and the issues with NAT and firewalls here. But I still have no ideas. And while my initial guess was a configuration problem on my part, I start to wonder... I see a file transfer start, and then after some part of the file has transfered correctly, the transfer hangs. It is hard to imagine how a port misconfiguration could cause this. Failing to initiate the transfer yes, but hang in the middle no.

So finally I managed to find a way to reproduce the problem myself, and I obtained two tcpdumps (each 40Mb...) at each end of the connection. And saw something quite interesting. When the error occurs, the client receives a data packet with TCP checksum error. The packet is re-transmitted, but each retransmission arrives with TCP checksum errors. So this of course explains the hang. But why the checksum error?

It turns out the problem is with the particular data! So extracting the offending packet from the tcpdump, I now have a 1448 byte file which cannot pass through my network uncorrupted :-(:

00000000: 0000 0000 0000 0000 0000 0000 0000 0000  ................
00000010: 0000 0000 0000 0000 0000 0000 0000 0000  ................
00000020: 0000 0000 0000 0000 0000 0000 0000 0000  ................
00000030: 0000 0000 0000 0000 0000 0000 0000 0000  ................
00000040: 0000 0000 0000 0000 0000 0000 0000 0000  ................
00000050: 0000 0000 0000 0000 0000 0000 0000 0000  ................
00000060: 0000 7d00 0000 cfbf 0c00 e1e3 8e00 403e  ..}...........@>
00000070: 7500 ad8b c500 ccaa 0000 8175 5600 d061  u..........uV..a
00000080: 3000 6ff0 2000 0565 ea00 707d 1b00 de3c  0.o. ..e..p}...<
00000090: d800 123a d800 4676 4000 8175 5600 d061  ...:..Fv@..uV..a
000000a0: 3000 6ff0 2000 0565 ea00 707d 1b00 de3c  0.o. ..e..p}...<
000000b0: d800 123a d800 4676 4000 a3db 0000 fe60  ...:..Fv@......`
000000c0: 0000 a3db 0000 fe60 0000 4000 0000 7eaf  .......`..@...~.
000000d0: 0000 4000 0000 7eaf 0000 7f1b 7f1b 7f1b  ..@...~.........
000000e0: 7f1b 0000 0000 0000 0000 2d70 2d70 0000  ..........-p-p..
000000f0: 0000 0000 0000 5b30 9370 52be 8a8a 0205  ......[0.pR.....
00000100: 3c30 16cd eebe 6f05 9e30 d9cd f0be f9b6  <0....o..0......
00000110: 7969 a769 f788 2fea 360c 0000 0070 6405  yi.i../.6....pd.
00000120: 8130 3505 6d30 7856 d1d7 ae8a 9120 a069  .05.m0xV..... .i
00000130: c688 03ea 4c81 0000 0000 9205 3930 3b05  ....L.......90;.
00000140: e230 3a04 4d81 be88 8e69 8a20 02b6 5856  .0:.M....i. ..XV
00000150: d80c 0000 0000 0000 0000 4d05 5b30 b256  ..........M.[0.V
00000160: c1d7 dd30 4170 0000 0000 2a04 2a04 0000  ...0Ap....*.*...
00000170: 0000 0000 0000 0000 0000 0000 0000 0000  ................
00000180: 0000 0000 0000 0000 0000 0000 0000 0000  ................
00000190: 0000 0000 0000 0000 0000 0000 0000 0000  ................
000001a0: 0000 ffff ffff 0000 6c00 0004 56cd 70cd  ........l...V.p.
000001b0: 05e7 5e40 7d05 055c 205e e700 e7ea 07be  ..^@}..\ ^......
000001c0: 56d8 0000 7d8a ea00 4000 0000 0000 0008  V...}...@.......
000001d0: 0000 0000 0000 0000 0000 0000 0000 0000  ................
000001e0: 0000 0000 0000 0000 0000 0000 0000 0000  ................
000001f0: 0000 0000 0000 0000 0000 0000 0000 0000  ................
...

So what was first assumed an ftp server configuration now turns out to be a nasty network issue :-(. There seems to be four possible sources of the problem:

1. The server network card or driver.
2. My own Linksys router.
3. The Zyxel router provided by the ISP (Fullrate).
4. The ISP network and switch infrastructure.

So now the next step is to test each component in isolation to pinpoint the offender (unfortunately I cannot isolate the Zyxel router from the ISP without obtaining an ADSL simulator, which my guess is I could not afford easily...). But the others can be tested in isolation once I get the change to get on-site and move around cables.

I put together a small Perl snippet to test this without the complexity of a full-blown ftp server getting in the way and confusing the ISP tech support. Running this on the server, I can just run on the client this:

    nc HOST 5376 > /dev/null

To be continued!

#! /usr/bin/perl

use strict;
use warnings;

use Socket;

# Get the data with the problematic bitstream.
my $data;
open(IN, '<', 'trouble_data.raw')
    or die "Failed to read trouble data: $!";
{ local $/= undef;
  $data= <IN>;
}

my $listen_port= 5376;
my $proto = getprotobyname('tcp');

socket(SERVER, PF_INET, SOCK_STREAM, $proto)
    or die "socket() failed: $!\n";
setsockopt(SERVER, SOL_SOCKET, SO_REUSEADDR, pack("l", 1))
    or die "setsockopt() failed: $!";
bind(SERVER, sockaddr_in($listen_port, INADDR_ANY))
    or die "bind() failed: $!";
listen(SERVER,SOMAXCONN)
    or die "listen() failed: $!";

print "Server started successfully.\n";

my $paddr;
while ($paddr= accept(CLIENT,SERVER))
{
  my ($port, $iaddr)= sockaddr_in($paddr);
  my $name= gethostbyaddr($iaddr, AF_INET);

  print "Got connection from '$name' [", inet_ntoa($iaddr), "], sending data...\n";
  print CLIENT $data;
  print "Data sent, closing connection.\n";
  close CLIENT;
}

Tags: debugging, network

(2 comments | Leave a comment)

Actually, what I really wanted to blog about before getting carried away with irony yesterday was an old idea on how to force my developers to use placeholders exclusively for SQL queries in applications. As should be apparent from yesterdays blog entry, I am strongly in favour of using placeholders for interpolating values into SQL queries, due to the great reduction in potential bugs (including, but not limited to, SQL injections).

Basically, wrap the database API so that all database access passes through the wrapper. This can usually be achieved, for example by subclassing DBI (for Perl) and returning such subclasses from the application connection pool, or other similar methods. Probably many large web applications already have such wrappers or use APIs that can be patched or extended appropriately.

Now add code that basically bombs out with a big error message if any SQL query contains a quote character. Something like "Always use placeholders for interpolating values into SQL queries! If in disagreement, go see your development lead for your regular spanking!" or words to the same effect.

Sometimes, the wrapper may sit below some code in the database API that emulates placeholders (for example, DBD::mysql used to emulate placeholders in the client using mysql_real_quote_string() or equivalent, since real server-side placeholders are only available with the newer version of the MySQL protocol for prepared statements). But even in this case, the wrapper can still force the use of placeholders by exploiting the fact that MySQL supports both single (')and double (") quotes. Basically, the wrapper would set some private global variable at random to either a single or a double quote, make placeholder emulation use one, and bomb out if the other is detected in query strings. Then any developer trying to sneak manual quoting into the application would quickly be caught, and subsequently taught.

The technique is not perfect. It does not catch completely unquoted number interpolation (shudder). It will also be somewhat of an annoyance to have to specify all string constants as placeholders (there is nothing wrong with "SELECT value FROM t WHERE id = ? AND color = 'red'"). In the end, I never got to implement it, also because my team was small enough and clue-full enough that normal face-to-face talk was sufficient to make placeholders be used throughout.

But if I ever find myself as lead or architect for a web application team, I will be sorely tempted to implement it, as an educational means for the developers and just to see what reactions it will cause.

Tags: developmentprocess, mysql, perl, security

(3 comments | Leave a comment)

It is sad to see how 9X% (or should that be 99.X%?) of SQL applications are riddled with SQL injection bugs.

There really is no excuse for this. Nobody writes code like this:

sub stupid_sum {
    my ($list) = @_;
    my $string = shift @$list;
    for (@$list) {
      $string .= " + " . $_;
    }
    my $sum = eval($string);
    return $sum;
}

So when we use functions to group our code into logical units, we have this fancy syntax for something called parameters. And we can write clever stuff like this:

int foo(int a) {
    return a + 1;
}

In fact, in the old days, people were using something called embedded SQL, which tries to keep this distinction for using SQL with another language. Though I have to admit, having used Oracle ProC, that this was quite horrible.

But there is no need for embedding the SQL into the syntax of every language, because these brilliant people invented the placeholder! So now we can also have parameters for SQL, an SQL string holds the code to be executed, and placeholders supply the values to be used from other parts of the program.

And it is so easy. No need for mysql_real_quote_string() and other horrors. Just do like this, using Perl and DBI as example:

sub mark_items {
    my ($dbh, $mark, @keys_list) = @_;
    my $placeholders = join(",", map("?", @keys_list));
    $dbh->do("UPDATE t SET flag = ? WHERE id IN (" . $placeholders . ")",
             undef,
             $mark, @keys_list);
}

Just because modern dynamic languages make string concatenation easy, does not mean that confusing code and data is a good thing. Von Neumann architecture is good for CPUs, but at higher levels of abstraction we have moved on.

As Bjarne Stroustrup often says, just as plumbers need an education to be allowed to mess up pipes, why doesn't a programmer need an education that makes him or her understand these things before being allowed to release software?

Tags: mysql, perl, security

(7 comments | Leave a comment)

Der er gang i diskussionen vedrørende EU's mulige krav til at lade folk vælge browser når de køber Windows. Har Microsoft misbrugt sit monopol, er det en god ide at EU blander sig, er det for megen indblanding, osv.

Når man installerer en Linux-distribution skal man typisk ikke vælge browser... men man har jo også allerede valgt en Linux-distribution. Der er fri konkurrence på området, så hvis der er efterspørgsel på Linux med andet end Firefox som default skal det nok også blive muligt at få det.

Men når man vælger en Windows-distribution - nej, man kan ikke vælge Windows-distribution, for den har Microsoft monopol på at lave. Nøjagtigt ligesom alle andre, der har udviklet software, og ikke eksplicit fraskrevet sig den eneret. Det kaldes ophavsret.

Det er jo det, som er problemet. Det er nøjagtigt det samme som med teleinfrastrukturen og TDC. TDC ejer telenettet ("det rå kobber"), og har derfor (qua ejendomsretten) som udgangspunkt monopol på at sælge fastnettelefoni og internetforbindelse. Alle er enige om, at det er uholdbart, så derfor er der indført regulering på området. Det betyder, at den del af TDC som ejer kobberet, er forpligtet til at videresælge adgang til andre udbydere på samme vilkår, som del del af TDC der leverer forbindelserne får. Og det virker stort set.

Løsningen på software burde være det samme. Andre firmaer får ret til samme vilkår til at sammensætte og distribuere Windows (andre softwarepakker) som den del af Microsoft (andre softwarefirmaer) der står for at sælge slutbruger-licenser. Der skal naturligvis betales den samme per-bruger/licens pris til udviklingsdelen af Microsoft, samt til eventuelle ophavsrethavere af andet programmel som inkluderes. Og der skal være fri adgang til at sammensætte og modificere til det markedet efterspørger.

Men det er jo "tvangslicens", kan man det? Ja selvfølgelig. Staten stiller hele den udøvende og dømmende magts resourcer til rådighed for Microsoft og alle andre til at beskytte deres monopol på at distribuere deres software. Det er jo et kollosalt privilegie. Derfor kan man naturligvis også forlange modydelser fra statens side som afbalancerer dette privilegie til bedst mulig gavn for forbrugerne og samfundet.

Man skal huske, at ophavsretten er et tilbud fra statens side, ikke en pligt. Det står en ophavsrethaver frit for ikke at benytte sin ophavsret, jeg mener ikke der er nogen ubetingede pligter for ophavsrethaver i lov om ophavsret, ej heller nogen mulighed for andre end ophavsrethaver til at håndhæve ophavsretten.

Desværre er det en udbredt forestilling, at de eneretter som lov om ophavsret giver er en eller anden form for indlysende gud-given ret, ikke et privilegie som der kan forlanges modydelser for. Så det har nok lange udsigter til at få lavet ændringer i den retning.

Tags: dansk, it-politik, ophavsret

(Leave a comment)

Selecting rows holding group-wise maximum is a favorite problem of mine, but one which only rarely pops up. But for some reason, after my last blog post on the subject, it seems to be mentioned almost daily around here.

Something that I forgot to mention in the previous post is that most of the examples there assume suitable indexing is available to get decent performance. Basically a composite index on both the column(s) in the GROUP BY and the column over which MAX is computed is needed. In the example I gave, such an index is available throught the primary key.

However, such an index may not be available in all cases. Maybe maintaining it would be too expensive, or maybe the data the max is computed over is itself the result of a (sub-)query, and no indexing is available. So it is worth it also to understand this case, as the performance of the different possible queries differ greatly from the indexed case.

So let us modify the original example to not have any useful indexes:

CREATE TABLE object_versions (
  id INT PRIMARY KEY AUTO_INCREMENT,
  object_id INT NOT NULL,
  version INT NOT NULL,
  data VARCHAR(1000)
) ENGINE=InnoDB;

This time, I will use a data set of size only 1% of the previous example, as without indexes some of the queries get ridiculously poor performance. So let us take 10,000 rows, 1000 object each with 10 versions. I use this Perl long^H^H^H^Hone-liner to load the data:

perl -MDBI -le '$vers=10; $groups=1000; $dbh=DBI->connect("dbi:mysql:", "test",
"pass", {RaiseError => 1}); $dbh->do("USE test"); foreach $o (1..$groups)
{ $dbh->do("INSERT INTO object_versions VALUES " . join(", ", map("(null, ?,?,?)",
1..$vers)), undef, map( ($o, $_, "data_${o}_$_"), 1..$vers)); }'

Here are the results:

mysql> SELECT data FROM object_versions o1
WHERE version = (SELECT MAX(version) FROM object_versions o2 WHERE o1.object_id = o2.object_id);
1000 rows in set (25.55 sec)

mysql> SELECT o1.data FROM object_versions o1
INNER JOIN (SELECT object_id, MAX(version) AS version FROM object_versions GROUP BY object_id) o2
ON (o1.object_id = o2.object_id AND o1.version = o2.version);
1000 rows in set (0.72 sec)

mysql> SELECT o1.data FROM object_versions o1
LEFT JOIN object_versions o2 ON o1.object_id = o2.object_id AND o1.version < o2.version
WHERE o2.object_id IS NULL;
1000 rows in set (15.44 sec)

mysql> SELECT MAX(CONCAT(version, ":", data)) FROM object_versions GROUP BY object_id;
1000 rows in set (0.52 sec)

mysql> SELECT data FROM
(SELECT * FROM object_versions ORDER BY object_id DESC, version DESC) t GROUP BY object_id;
1000 rows in set (0.04 sec)

The only query that has any kind of decent performance here is last one using the "evil trick" of abusing the MySQL GROUP BY extensions in a way that is explicitly documented to not produce well-defined results. Which is really sad, since it is the only way I know of of getting the database to make the obvious execution plan in this case, which is to simply sort the data on the GROUP BY expression, and then loop over the rows picking the max row in each group on the way.

In fact, in many cases I think a decent alternative is to just select all rows into the client using ORDER BY, and do the aggregation there.

Now I just need someone to implement my SELECT MAX(object_id, version) ...

Tags: mysql, sql

(Leave a comment)

En entreprenør får bestilling på et hus. Arkitekttegningerne kommer på plads, byggeriet går igang, der er rejsegilde, og man bliver klar til aflevering. Bygherren får nøglerne og flytter straks ind, flyttebilen er der allerede. Og da flyttelæsset er på plads, kommer det fra bygherren: "Hov forresten, jeg kommer i tanke om noget: Kan I ikke lave det med kælder?"

Dette er næppe hverdagskost i byggebranchen. Jeg ved ikke, om det er teknisk muligt at tilføje en kælder til et hus uden væsentlig forstyrrelse af beboerne, men det er i hvert tilfælde ikke cost-effektivt.

Men i software-udvikling er det analoge scenarie hverdagskost. Grundlæggende ændringer i design og funktionalitet i systemer, som er i brug. Det er naturligvis en stor styrke, at vi er i stand til det, men kan man nogen gange savne forståelse hos ikke-teknikere for, hvor meget mere omkostningsfuldt det bliver, når kælderen skal bygges, efter at beboerne er flyttet ind. Og det er trist når resultatet, som det ofte sker, bliver en omgang byggesjusk.

Tags: dansk, developmentprocess

(Leave a comment)

Today there was a question on the Freenode MySQL channel about a classical problem: Rows holding group-wise maximum of a column. This is a problem that I keep encountering every so often, so I thought I would write up something about it.

A good example of the problem is a table like the following holding versioned objects:

CREATE TABLE object_versions (
  object_id INT NOT NULL,
  version INT NOT NULL,
  data VARCHAR(1000),
  PRIMARY KEY(object_id, version)
) ENGINE=InnoDB

SELECT data FROM object_versions WHERE object_id = ? ORDER BY version DESC LIMIT 1

mysql> EXPLAIN SELECT data FROM object_versions
WHERE object_id = 42 ORDER BY version DESC LIMIT 1;
+----+-------------+-----------------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table           | type | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-----------------+------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | object_versions | ref  | PRIMARY       | PRIMARY | 4       | const |    3 | Using where | 
+----+-------------+-----------------+------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)

But what if we want to select the latest version of all (or some range of) objects? This is a problem that I think standard SQL (or any SQL dialect that I know of, including MySQL) has no satisfactory answer to.

Intuitively, the problem should be simple for the database engine to solve. Just traverse the BTree structure of the primary key (assume InnoDB clustered index storage here), and for each value of the first part of the primary key (object_id), pick the highest value of the second part (version) and return the corresponding row (this is similar to what I believe is sometimes called index skip scan). However, this idea is surprisingly difficult to express in SQL.

The first method suggested in the above link to the MySQL manual works in this case, but it is not all that great in my opinion. For example, it does not work well if the column that MAX is computed over is not unique per group (as it is in this example with versions); it will return all of the maximal rows which may or may not be what you wanted. And the query plan is not all that great either:

mysql> EXPLAIN SELECT data FROM object_versions o1 WHERE version =
(SELECT MAX(version) FROM object_versions o2 WHERE o1.object_id = o2.object_id);
+----+--------------------+-------+------+---------------+---------+---------+-----------------------+------+-------------+
| id | select_type        | table | type | possible_keys | key     | key_len | ref                   | rows | Extra       |
+----+--------------------+-------+------+---------------+---------+---------+-----------------------+------+-------------+
|  1 | PRIMARY            | o1    | ALL  | NULL          | NULL    | NULL    | NULL                  |    6 | Using where | 
|  2 | DEPENDENT SUBQUERY | o2    | ref  | PRIMARY       | PRIMARY | 4       | einstein.o1.object_id |    1 | Using index | 
+----+--------------------+-------+------+---------------+---------+---------+-----------------------+------+-------------+
2 rows in set (0.00 sec)

The two other suggestions from the MySQL manual are not perfect either (though the first one is blazingly fast, see benchmarks below). One is to use an uncorrelated subquery with a join:

mysql> EXPLAIN SELECT o1.data FROM object_versions o1
INNER JOIN (SELECT object_id, MAX(version) AS version FROM object_versions GROUP BY object_id) o2
ON (o1.object_id = o2.object_id AND o1.version = o2.version);
+----+-------------+-----------------+--------+---------------+---------+---------+-------------------------+------+-------------+
| id | select_type | table           | type   | possible_keys | key     | key_len | ref                     | rows | Extra       |
+----+-------------+-----------------+--------+---------------+---------+---------+-------------------------+------+-------------+
|  1 | PRIMARY     | <derived2>      | ALL    | NULL          | NULL    | NULL    | NULL                    |    3 |             | 
|  1 | PRIMARY     | o1              | eq_ref | PRIMARY       | PRIMARY | 8       | o2.object_id,o2.version |    1 |             | 
|  2 | DERIVED     | object_versions | index  | NULL          | PRIMARY | 8       | NULL                    |    6 | Using index | 
+----+-------------+-----------------+--------+---------------+---------+---------+-------------------------+------+-------------+

The other suggestion uses an outer self-join:

mysql> EXPLAIN SELECT o1.data FROM object_versions o1
LEFT JOIN object_versions o2 ON o1.object_id = o2.object_id AND o1.version < o2.version
WHERE o2.object_id IS NULL;
+----+-------------+-------+------+---------------+---------+---------+-----------------------+------+--------------------------------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref                   | rows | Extra                                |
+----+-------------+-------+------+---------------+---------+---------+-----------------------+------+--------------------------------------+
|  1 | SIMPLE      | o1    | ALL  | NULL          | NULL    | NULL    | NULL                  |    6 |                                      | 
|  1 | SIMPLE      | o2    | ref  | PRIMARY       | PRIMARY | 4       | einstein.o1.object_id |    1 | Using where; Using index; Not exists | 
+----+-------------+-------+------+---------------+---------+---------+-----------------------+------+--------------------------------------+
2 rows in set (0.00 sec)

And in fact, there is a nice way to express this in SQL, except that it does not work (at least not in MySQL):

SELECT MAX(version, data) FROM object_versions GROUP BY object_id;

In fact, one can almost use this technique by an old trick-of-the-trade:

mysql> SELECT MAX(CONCAT(version, ":", data)) FROM object_versions GROUP BY object_id;
+---------------------------------+
| max(concat(version, ":", data)) |
+---------------------------------+
| 2:foo2                          | 
| 1:bar                           | 
| 3:baz2                          | 
+---------------------------------+
3 rows in set (0.00 sec)

mysql> EXPLAIN SELECT MAX(CONCAT(version, ":", data)) FROM object_versions GROUP BY object_id;
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table           | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------+
|  1 | SIMPLE      | object_versions | index | NULL          | PRIMARY | 8       | NULL |    6 |       | 
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------+
1 row in set (0.00 sec)

And speaking of hacks, there is actually another way to solve the problem, one which I learned about recently at a customer:

mysql> EXPLAIN SELECT data FROM
(SELECT * FROM object_versions ORDER BY object_id DESC, version DESC) t GROUP BY object_id;
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+---------------------------------+
| id | select_type | table           | type  | possible_keys | key     | key_len | ref  | rows | Extra                           |
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+---------------------------------+
|  1 | PRIMARY     | <derived2>      | ALL   | NULL          | NULL    | NULL    | NULL |    6 | Using temporary; Using filesort | 
|  2 | DERIVED     | object_versions | index | NULL          | PRIMARY | 8       | NULL |    6 |                                 | 
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+---------------------------------+

Bonus benchmark

As a free bonus, I decided to run some quick benchmarks. As it turns out, the results are quite surprising!

So I filled the table above with 1,000,000 rows, 1000 objects each with 1000 versions. Total table size is about 50Mb or so. I then ran each of the five above queries:

mysql> SELECT data FROM object_versions o1
WHERE version = (SELECT MAX(version) FROM object_versions o2 WHERE o1.object_id = o2.object_id);
1000 rows in set (4 min 22.86 sec)

mysql> SELECT o1.data FROM object_versions o1
INNER JOIN (SELECT object_id, MAX(version) AS version FROM object_versions GROUP BY object_id) o2
ON (o1.object_id = o2.object_id AND o1.version = o2.version);
1000 rows in set (0.01 sec)

mysql> SELECT o1.data FROM object_versions o1
LEFT JOIN object_versions o2 ON o1.object_id = o2.object_id AND o1.version < o2.version
WHERE o2.object_id IS NULL;
1000 rows in set (2 min 42.72 sec)

mysql> SELECT MAX(CONCAT(version, ":", data)) FROM object_versions GROUP BY object_id;
1000 rows in set (0.63 sec)

mysql> SELECT data FROM
(SELECT * FROM object_versions ORDER BY object_id DESC, version DESC) t GROUP BY object_id;
1000 rows in set (15.61 sec)

The differences are huge!

The clear winner is query 2, the uncorrelated subquery. Apparently it can do an index skip scan for the inner MAX/GROUP BY query, followed with a primary key join, so it only ever has to touch 1000 rows. An almost optimal plan.

Query 1 and 3 (correlated subquery and outer join) are spectacularly bad. It looks as if they are doing something like for each 1,000,000 rows in the full table scan, do a 1000 row index range scan in the subquery/join, for a total of 1 billion rows examined. Or something. Not good.

Query 4 and 5, the trick queries, are doing so-so, probably they get away with a sort of a full table scan of 1,000,000 rows.

Conclusions: Uncorrelated subquery is the undisputed winner!

Tags: mysql, sql

(11 comments | Leave a comment)

In case anyone is interested in a copy of my slides for the two lightning talks I gave at the Open Source Days 2008 conference, I have made them available here:

• "Optimizing Large Databases Using InnoDB Clustered Indexes:" HTML and PDF.
• "Profiling with OProfile and Intel Core 2 performance counters:" HTML and PDF.

I waqs quite pleased with the benchmark that I prepared for the InnoDB mini-talk, where I measure the performance difference between clustered and auto_increment primary key, both with data that fits in memory and with data that does not. I have wanted to do this benchmark for quite some time, as I have not really seen real results for this before, though the technique of using clustered primary keys for performance is well-known.

The results are quite interesting, with clustered indexes being faster for I/O bound load with more than an order of magnitude. It is also interesting to see how dead-cheap hardware can do 23k queries/second and read 1000000 rows/second with a properly tuned database.

Tags: innodb, linux, mysql, oprofile, talk

(Leave a comment)

Previously I noticed that StartSSL is offering free SSL certificates, ie. proper certificates with a CA chain that is accepted by browsers. Now while setting up my own web-server I had the opportunity to try it out.

It seems to work well. The certificate seems to work, Firefox 3 does not spit out any errors or warnings as it is otherwise very keen to do on self-signed certificates. The certificate is obtained without too much hassle, and fairly quickly, surely much easier than any CA with paper trail requirements.

I did have an issue with the site requiring the browser to send Referer headers, which I have disabled. Using referer headers for functionality is usually a poor idea, requiring it goes against the HTTP spec, and there is no added security since it is trivial to forge it. Anyway, I switched to using the RefControl Firefox extension, which is much more flexible that what is built into about:config. The Block (3rd party) option suits me well.

Other than that, it is just a matter of registering an account (with the usual email confirmation mail exchange). Then validate your domain, which is the site's way of making it difficult for someone to make a certificate claiming ownership of a domain that belongs to another person. Basically you have to be able to read mail sent to some address associated with the DNS of the domain, like the one in the SOA record or postmaster@your.domain, etc.

Now generate the openssl certificate signing request as described in the Apache/mod_ssl documentation, and upload it in the appropriate web form. In a matter of seconds the properly signed server.crt is returned, and can be installed in Apache using the SSLEngine on, SSLCertificateFile, SSLCertificateKeyFile, and SSLCertificateChainFile directives. Ah, and also remember to remove the passphrase on the server key, or the Apache startup will hang your boot process on the next reboot, waiting for someone to type the passphrase.

I still find it a hassle to have to go through this just to be able to encrypt web traffic, something that really should have been standard since long. The real problem is that the only available standard using https:// URLs mixes two completely different issues: that of encrypting the data channel, and that of validating that the server is who the client think it is. Due to this mixup, implementations are left with the dilemma of either compromising the latter or making the former a hassle, and mainstream browsers generally choose the hassle option.

At least with StartSSL, encryption is possible both free of cost and (relatively) free of administrative hassle. But there is still a number of technical problems and hassle. Like the fact that it is not possible to run multiple domains on the same IP address without browsers spitting out warnings en masse. Well, at least my StartSSL certificate seems to work both for versions of the associated domain both with and without the www. prefix.

Tags: apache, linux

(3 comments | Leave a comment)

After setting up Hobbit monitoring on my home network, I discovered a curious issue with the Zyxel P-2602HW router that Fullrate delivers with their ADSL products: The Telnet administration port occasionally times-out connection attemps:

I really like the Hobbit monitoring tool, I have used it ever since I heard about it when I met the author, Henrik Storner, at a local LUG meeting. It really obeys my favorite software principle, "simple things should be simple, complex things should be possible" (and it only helps that it works similarly to BigBrother, which I learned on a previous job).

Setting it up is litterally as simple as this:

    sudo apt-get install hobbit hobbit-plugins

Hobbit comes preconfigured with a lot of nice stuff. It's not just the basic stuff, either. It automatically sets up monitoring of configured apt repositories so that alerts appear when new security patches are available. And when I set up https/SSL on my local Apache (using StartSSL to obtain a free, valid certificate!), just adding a https://1.2.3.5/ entry in /etc/hobbit/bb-hosts provides not only monitoring of the service running, but also of when the certificate is about to expire, which in almost a year's time I would be bound to forget otherwise. Custom monitoring is both easy and flexible to add ass well (haven't needed that on the home network yet, though).

And why do I need to monitor my home network? Well, I believe any network where one cares at all about it working should be monitored (and if one does not care, why not turn the power off, save some CO2 pollution?)

Well, I don't really use the Telnet administration port on the router that often, so it is not a problem for me. Except that of course I want to avoid alarms about this, as there is probably nothing I can do about it, complaining to Fullrate does not really seem either worthwhile or likely to help.

Hobbit has a simple way to do this, just add badtelnet:<ignore>:<warn>:<error> to the entry in /etc/hobbit/bb-hosts (numbers are levels for ignore, warning, and error):

    1.2.3.4     zyxel               # telnet badtelnet:1:3:4

Tags: linux

(Leave a comment)

Så er træerne plantet!

 

Algoritmen til højre er et spindeltræ (sødkirsebær af sorten Stella). Alle grene er bundet ned til vandret med snore, og toppen er klippet for at frembringe en ny etage sidegrene til foråret.

Der er også sørget for redundans, for det tilfælde at et af træerne ikke skulle vokse ordentligt til. Uden de ekstra træer ville "time to recovery" være minimum et år.

Så er det bare med at vente på foråret, der sker ingenting de næste 6 måneder.

Tags: dansk, gardening

(Leave a comment)

So, with automatic Garbage Collection, memory leaks are a thing of the past, right?

Well, not quite, of course. Automatic Garbage Collection, like most other automated programming techniques, necessarily needs to approximate (ie. guess), and while the guesses of garbage collectors are generally very good, they cannot magically predict the future.

The classic way that a program fools the garbage collector is by maintaining a global list of objects created. This is a common technique for memory management in non-garbage-collected programming environments, where such lists will be used to ensure eventual free() of all objects in some longer-lived manager object.

Unfortunately, if such techniques carry over to garbage collected environment, the effect is to effectively disable the automatic Garbage Collection algorithm! Every object allocated will have an outstanding reference in the global list, causing garbage collection to consider all of it live data, which cannot be deallocated.

I got hit by this while working on an Adobe Flash application using Papervision 3D. It was leaking tons of memory (on the order of 20Mb for every user interaction!).

Turns out that this was a case of the above-mentioned problem. It turns out that every Papervision 3D MaterialObject3D object registers itself in the constructor with the MaterialManager singleton, which is nothing but a global list of allocated material objects. The result is that no material objects (which all inherit from the MaterialObject3D class) will get deallocated ever, unless explicitly done by the application (using MaterialObject3D::destroy() for example). So much for automatic Garbage Collection...

A simple Google search reveals that I am not the first one to be hit by this.

The problem here is a poor design in the Papervision 3D API in this respect, made even worse by the fact that the documentation does not seem to mention this aspect at all (at least I could not find it). A constructor that automagically registers the object in a global list is generally a really poor idea in garbage collected programming environments.

I am not sure why Papervision 3D does this. Maybe it could just be removed. Or alternatively, a better approach would be for the API to make it explicit that such global registration is taking place. For example by forcing the application to explicitly call some MaterialObject3D::register(), documenting the need for unregister/destroy, or by not providing a constructor at all, and instead creating objects in some factory class, again making clear mention of the need to unregister/destroy.

Tags: adobeflash, gc, programming

(Leave a comment)