MySQL Stored Procedure tip

The other day I was writing a stored procedure, when after making a change it just wouldn't save and kept giving me an error, saying something useful like "There is an error in your SQL syntax...".

After pulling my hair out for a day trying to work it out, I realised that when dealing with MySQL stored procedures you must put all of your declare statements at the start of the sproc.

My new phone - Samsung i5700

I've been looking for a mobile phone for a while now, and after a lot of deliberation I settled on the Samsung i5700. The phone came from Parallel Imported, here in Auckland and after turning on the phone and playing around with it, it was obvious that it originally came from England, most likely from Virgin Mobile.

The first thing to note was that the phone came with Android version 1.5, which I immediately wanted to upgrade to the latest version (2.2) as soon as possible. This lead to my first disappointment. It turns out that the only way to upgrade the phone is by installing the Samsung New PC Studio software. This should've been straight forward enough, except that:

* The software CD which came with the phone had the wrong version of the software, meaning it wouldn't recognise the phone
* Samsung has several different versions of New PC Studio, depending on
- your county
- your provider
* New PC Studio has been replaced by a piece of Samsung software called Kies, which also refuses to recognise the phone
* Once I located the correct version of the software and installed it, it recognised the phone the first time it was plugged, but all consequent attempts to get it to recognise the phone have failed
* The Samsung software is incredibly annoying. It uses too much sound and the only thing I can see myself using it for is to upgrade to the newer version of Android

I swear, Samsung are utter crap at software, and if you had to find someone to write a manual on how not to create useful, bug free software, you should see the guys which created NPS and Kies.

Once I got that out of the way (in the process I had exceeded the download limit on my internet connection) I found that the software was not available to download. i.e. it would not allow me to upgrade the phone. By this time I was furious. The reason that the software wasn't available is that you first needed Samsung to issue the update and then get the mobile provider (probably Virgin Mobile) to push the upgrade to all of the devices. These kinds of problems are exactly what everyone was trying to warn consumers about when they warned of Android fragmentation. In giving too much say to the handset makers and the mobile services providers, Google have created a situation where each middle man tries to "add value" by adding their own proprietary layer. From this consumers point of view, all the middle men do is add shit.

Samsung should stick to making TV's and not try and be a software company. Virgin Mobile should invest money in their infrastructure instead of trying to wring every last dollar out of their clients.

Ubuntu 10.04, impressions after a week

As a part of doing a general clean up of my laptop, last Wednesday/Thursday I carefully backed up all of the data accross all of the 5 different partitions (all of which had different operating systems, including three versions of Ubuntu), before deleting the partitions and installing Ubuntu 10.04 LTS as the only OS on my laptop. The process was quite time consuming, having to log into each and every OS and copy anything useful over to the "Data" partition. Ultimately, the effort did pay off in the end though and I'm left a feeling of great satisfaction at the fact that I didn't lose any data and managed to clean up my system.

As for the OS of choice for this rebirth of my laptop, it had to be Ubuntu 10.04. I had used Unetbootin recently to trial both the latest versions of Ubunut and Kubuntu, but ultimately settled on Ubuntu due to its better support of the wireless chipset in my laptop. The wireless is something I've constantly been struggling with over the years, with it behaving strangely, such as reducing power until the connection dies at which point it tries to reconnect. With 10.04 these problems seem largely solved (cross fingers), although my connection still sometimes drops out when I'm on the other side of the house. The other neat little thing is that this newer version of Ubuntu supports the status LED of the wireless chipset.

The few problems which I ran into after the install were to do with third party software and importing items from the previous installs. Google Gears failed to install, and after tracking the problem down it seems that Google is only providing a 64-bit version of the software, meaning that 32-bit users are out of luck. The fix was simply to install the 'xul-ext-gears' package from the repositories, but this could have been better integrated into the interface. The second problem I faced was to do with trying to import all of my old Tomboy notes. I have yet to find a script/program to do this for me and just pointing the synchronisation to the directory containing the old notes doesn't seem to work. It looks like I'm going to have to write some code for this one, not that I mind too much :)

Overall, 10.04 is a very impressive release. I'm more impressed with the stability improvements than any of the new features (social media anyone?).

Linux Hypervisor in 2.6.32

Just came accross this article on Slashdot, detailing a new feature of the 2.6.32 kernel, dubbed 'Kernel Shared Memory'.

The technology allows you to de-duplicate memory regions in virtual machines running on the hypervisor. What does this mean? It means that you can have a physical server with 10GB of RAM and have 3 times that number of VM's running on it with each having a full 1GB of RAM. In the release notes, there's even a reference to 52 Windows VM's running on a single 16GB server with 1GB memory each!

This of course depends a lot on what kinds of OS's you have running on the server (ideally you want the same OS on all of the VM's to maximise the duplicate memory) and also how similar the memory profiles of those VM's are. But my guess is that in most operating systems, there is a large amount of memory which is common, loaded at boot and then never written to.

From what I can tell, right now only the KVM hypervisor supports KSM, but seeing how general the technology is (it can be used by any process to de-duplicate memory pages) there's no reason why Xen couldn't easily make use of KSM.

Connecting to SQL Server from Perl on Linux

One of the tasks I recently had to do at the office was to get a program written in Perl and running on Linux to connect to and work with Microsoft's SQL Server. I thought that I would document the process here on my blog, in the off chance that it might help someone, and also so that I have a reference I can go back to in the future.

So, I went through the process of installing the latest stable FreeTDS driver. If you get the tarball and install using the 'configure, make, make install' method the files are installed under the '/usr/local' directory.

To create a connection to your SQL Server you have to modify the '/usr/local/etc/freetds.conf' file and add the details of your server. For example here is what the details of a SQL Server 2008:

# SQL Server 2008
[sql2008]
host = 231.321.452.871
port = 1433
tds version = 8.0

In order to test the connection you can use the 'tsql' command found under '/usr/local/bin' by passing it the Server, Username and Password variables:

tsql -S sql2008 -U [username] -P [password]

Note that if the connection is successful, you should get a very simple prompt (something like '1>') from which you can exit using the 'quit' or 'exit' commands.

So, now that we know that we have the FreeTDS driver working, we can set about getting access to this interface programatically, i.e. from our program.

For the next step we're going to install the DBD::Sybase module from CPAN and configure it to work with FreeTDS. When I first tried this I attempted to run the following command:

sudo cpan DBD::Sybase

This however, resulted in the following error:

Please set SYBASE in CONFIG, or set the $SYBASE environment variable at Makefile.PL line 103, <in> line 45.

This was a relatively simple fix which involved going into the '/root/.cpan/build/DBD-Sybase-1.09/CONFIG' file and changing the line:

SYBASE=$ENV{SYBASE}||'/opt/sybase'

To:

SYBASE=/usr/local

After making the change, run the command 'perl Makefile.PL' and just leave all of the values set to the default and when it comes to the part where it asks you for the server, database, username and password in order to test the install, enter the credentials for your server.

The next part is to simply run the 'make' command, which will go and compile the module. Trying this gives the following errors:

dbdimp.c:786: error: ‘BLK_VERSION_150’ undeclared (first use in this function)
dbdimp.c:786: error: (Each undeclared identifier is reported only once
dbdimp.c:786: error: for each function it appears in.)
dbdimp.c:790: error: ‘BLK_VERSION_125’ undeclared (first use in this function)
dbdimp.c:794: error: ‘BLK_VERSION_120’ undeclared (first use in this function)


In order to fix these errors, simply add the following lines after any #include statements in the 'dbdimp.c' file:

#define BLK_VERSION_150 BLK_VERSION_100
#define BLK_VERSION_125 BLK_VERSION_100
#define BLK_VERSION_120 BLK_VERSION_100

Run 'make' again, and this time it should build without any errors (although not without warnings). The next step is to run the 'make test' command to test the compiled code. Again, you should note that I've never gotten the code to pass all of the tests, my theory is that some of the tests are specific to a Sybase database and fail with SQL Server.

Once 'make test' finishes, run 'make install', which simply copies the compiled files accross to the correct place in the filesystem. Now, to test that what we've just done works, take the following code, paste it into a file, update it for you database and see if you get the correct return values:

#!/usr/bin/perl

use DBI;

$dsn = 'DBI:Sybase:server=sql2008';

my $dbh = DBI->connect($dsn, '[username]', '[password]');
die "Unable to connect to server $DBI::errstr" unless $dbh;

$dbh->do('USE [database]');

$query = 'SELECT * FROM [table]';
$sth = $dbh->prepare ($query) or die 'prepare failed\n';
$sth->execute() or die "unable to execute query $query error $DBI::errstr";

$rows = $sth->rows ;
print "$rows rows returned by query\n";

while ( @first = $sth->fetchrow_array ) {
foreach $field (@first) {
print "$field, ";
}
print "\n";
}

That's it! Hopefully you're able to access SQL Server through Perl running on Linux now. For more information please have a look at the links below:
http://www.perlmonks.org/?node_id=392385
http://www.n3ncy.net/UNIX/FreeBSD/FreeTDS.htm
http://lists.ibiblio.org/pipermail/freetds/2006q3/020583.html

Getting QPID running on Ubuntu

After reading Joshua Kramers article in the November 2009 issue of Linux Journal, I decided to try out AMQP and see how easy it was to set up and get going.

Firstly, the article doesn't mention:

* You need dependencies, namely valgrind, libboost-dev, uuid-dev
* the version of g++ that comes with Ubuntu 9.04 (g++-4.3) doesn't work with qpid-0.5, so you need to change the symlink to point to the older g++4.2 version
* the python tests fail due to an obscure line which goes something like:
if isinstance(sock, socket.SSLType):
AttributeError: 'module' object has no attribute 'SSLType'
* When trying to install the python modules I had a probem with the setup.py script and ended up commenting out the 'scripts=["amqp-doc"]' argument to the setup method due to the fact that it complained about not being able to find the script.
* Trying to run the 'Listing1.py' code resulted in it not being able to find the AMPQ spec, even after I had set the environment variable $AMPQ_SPEC to point to the correct file. Eventually I just hardcoded the value by editing the '/usr/local/lib/python2.6/dist-packages/qpid/spec.py' file and setting 'amqp_spec = "/usr/local/share/qpid/specs/amqp.0-10-qpid-errata.xml"' instead of the previous version which was ' amqp_spec = os.environ["AMQP_SPEC"]'

I haven't been able to find anything else on this error, with the exception of other frustrated qpid users.

Processes as trees

Just found this nice little command which displays all running processes as a tree structure. Actually, the command is just good ol' ps, but with the axf arguments. i.e.

# ps axf
...
...
8923 ?        Ss     0:00 /bin/sh /command/svscanboot
8925 ?        S      0:00  \_ svscan /service
10888 ?        S      0:00  |   \_ supervise qmail-send
13089 ?        S      0:00  |   |   \_ qmail-send
13097 ?        S      0:00  |   |       \_ qmail-lspawn ./Maildir/
13099 ?        S      0:00  |   |       \_ qmail-rspawn
13100 ?        S      0:00  |   |       \_ qmail-clean
10889 ?        S      0:00  |   \_ supervise log
13088 ?        S      0:00  |   |   \_ /usr/local/bin/multilog t /var/log/qmail
10890 ?        S      0:00  |   \_ supervise qmail-smtpd
21951 ?        S      0:00  |   |   \_ /usr/local/bin/tcpserver -v -R -l smtp1.smartmailpro.co.nz -x /etc/tcp.smtp.cdb -c 20 -u 502 -g 501 0 25 /var/qmail/bin
10891 ?        S      0:00  |   \_ supervise log
13091 ?        S      0:00  |       \_ /usr/local/bin/multilog t /var/log/qmail/smtpd
8926 ?        S      0:00  \_ readproctitle service errors: .................................................................................................