Thursday 10 September 2009

MySQL Bug #11918

In the software industry, every now and then you come accross some indescribably dense acts of stupidity that cost you hours of debugging time. When it's not your own code that's the problem, you get to rant about it on your blog ;)

Today I spent the last four hours trying to create a MySQL stored procedure to do something which I thought was quite basic. It just had to take an integer as input and return that number of rows from a specific table and at the end delete the rows. Kind of like a stack data structure (the rows 'pop' out), but with a database. So I sat down and wrote this code:

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`testGet`$$
CREATE PROCEDURE `test`.`testGet` (IN numRecords INT)
BEGIN
CREATE TEMPORARY TABLE tmpTable
SELECT * FROM test LIMIT numRecords;

DELETE FROM test WHERE idTest IN (SELECT idTest FROM tmpTable);

SELECT * FROM tmpTable;

DROP TABLE tmpTable;
END$$

DELIMITER ;

So after saving this code and trying to run it I get this cryptic error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'numRecords;

DELETE FROM test WHERE idTest IN (SELECT idTest FROM tmpTable);

' at line 4 (errno: 1064)

I spent the next two hours trying to figure out what MySQL was complaining about, pulling my hair out, trying to see which part of the code was screwing up.... finally after much googling, I came across this bug report on the MySQL bug tracker, the title of which simply reads "SP does not accept variables in LIMIT clause". The bug's been sitting in the bug tracker since the 13th of July 2005, FOUR YEARS this last July. The bug report is full of comments with detailed workarounds, most of which end up being versions of the 'EXECUTE STMT USING' statement. There's a big discussion in the comments as to whether this is a bug fix or a feature request, meanwhile Oracle and SQL Server continue to dominate the marketplace. Nice going guys. I'm stupified that this bug has been hanging around for four years and is still not fixed. I'm tempted to download the source and have a look at how difficult it would be to patch.

Sunday 6 September 2009

Eclipse Opening Tutorial woes

I've been looking around for a good all around development environment for a while now and today I've decided to give Eclipse a go. I've installed the eclipse package from the Ubuntu 9.04 repositories and everything seemed to be working well. I was a little disappointed with the fact that the Ubuntu repositories only had version 3.2 of Eclipse, when the main stable release was at 3.5, but this didn't bother me too much.I opened up Eclipse and decided to have a go at the first two tutorials, hoping to get an idea of what this IDE's all about.

The first tutorial goes through the process of creating a Java project with the ambitious aim of printing out 'Hello World!' to the command line. Going through the tutorial was quite straight forward, teaching you how to create a new jave project and add classes to the project.

The second tutorial was the same as the first, except that instead of printing 'Hello World!' to the command line you would print it to a window. In order to do this you use the SWT by downloading the Eclipse project and making it a part of your projects runtime. The tutorial went fine until it came to running the code and I ran into the following error:

Exception in thread "main" java.lang.UnsatisfiedLinkError: no swt-gtk-3550 or swt-gtk in swt.library.path, java.library.path or the jar file
at org.eclipse.swt.internal.Library.loadLibrary(Unknown Source)
at org.eclipse.swt.internal.Library.loadLibrary(Unknown Source)
at org.eclipse.swt.internal.C.(Unknown Source)
...
This is extremely disappointing. I mean, this is meant to be the second tutorial that the user works through, it should be bullet proof. The entry barrier to getting up and running on Eclipse should not be so high, that you have to go out of your way to learn the intricacies of SWT. So far, not a very good start for Eclipse.

Update: Got the second tutorial working. Instead of adding the SWT project off of the official site, I added the 'swt-gtk.jar' library found under /usr/lib/java on my Ubuntu install. Still not too happy about having to go to these kinds of lengths to get the first couple of examples up and running, but at least I'm learning :)

Saturday 5 September 2009

Don't write a single line of code without a Specification

Today I was updating one of our servers and naturally had a lot of free time while I waited for everything to download and install, so I started reading the 12 Steps to Better Code (also known as the Joel Test) article on the joelonsoftware blog.

The article makes some really good points about software development and how to do it right. From a programmers perspective it also gives you clues as to what to look for in a company you're potentially going to be working for.

After reading through the article I clicked on one of the links and found myself reading the full 4-part series about functional specifications. This article went into much more depth than the original 12 Steps to Better Code. The articles talked about why you need a spec, how to write a spec, who should write one and some general tips. This series of articles was timely for me personally due to our company having hired a new contractor which has taken on a project that's been dragging on for years at the company and as one of the first things he did was to write a functional specification and a technical specification (although the functional spec is actually referred to as the functional requirements, po-ta-to poh-ta-to). The amazing thing is that this first, relatively simple step has impressed the hell out of management. Finally they have a document that they can refer to to see the full functionality of the finished product and can start answering questions by clients as to what new features the program will have etc... They also look at the document and can get some more concrete idea of which stage the project is at, although this isn't the idea behind functional specifications. The other point that really hit home for me was about how by putting the functional and technical specifications in their respective documents, you minimize the interruptions to your own work, because instead of having management, marketing, Q&A people etc... bothering you with questions all day about how each part of the finished product will work, you can just refer them to the document. I would love it if I could answer all of the annoying questions I get throughout the day with "Have you read the spec?".

Overall, the message of "don't write a single line of code until you have a spec" is a powerful one, now if only I had the self-discipline to stick to it :)

Friday 4 September 2009

Installing 64-bit Adobe Flash on Ubuntu

Adobe Labs released their alpha flash player 10 for 64-bit linux in November 2008. In order to install it, simply go to the download site, download the .tar.gz file and extract it. The uncompressed file is just the libflashplayer.so. In the install instructions they say to place this file in ~/.mozilla/plugins and restart Firefox. However, when I tried this I found it didn't work. In order to make the plugin load, I placed it under /usr/lib/mozilla/plugins/ and this did the trick.

While most advice recommends that you install the 32-bit version of flash along with an emulation layer, I've found the 64-bit version of the flash player works better than the 32-bit version and is generally quite stable.