Wednesday, 29 April 2009

Beware the COLLATE clause

Not sure what the details of this are exactly but I run a test system at work which is basically copies of live database running on my test server for when I test code changes. I usually copy the live backup and restore it to my machine.
Anyway, I notice a particular view was running really slowly on my test server compared to live (even though it should be identical!?) and we are talking 38 seconds instead of 2 here!
I split the view and tried the various parts of it and found a sub view taking all the time. I then opened that and found a particular other view was fine as a standalone select:
SELECT * from ProblemView WHERE...

and the rest of the view was OK but when joined, it took ages. I couldn't really see why but then I noticed the join had a collate clause. Collate is related to the way in which text fields are ordered and depends on languages, sometimes you have to tell it how to collate otherwise the order might be wrong.
Anyway, I got rid of the collate clause and the view became 1 second again instead of 38. Oooh, I thought, a bug in the view, I went into the live site and tried to make the same change thinking it might massively speed it up and got the error:
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS"

Obviously the collation somewhere was different and obviously getting it wrong made the view MASSIVELY slow even though the data is quite small. Hopefully you might find this helpful.

Monday, 27 April 2009

plasma-widget-network-manager NO

Had a right ball ache tonight with my mums computer. I installed Kubuntu 8.10 a while back and she is happily using it despite being a Windows user. This was great. If mum could get on with it (albeit with occasional support calls) then anyone could.
The problem was I had the unsupported updates enabled so I could get a more stable version of KDE 4 since 4.1 was pretty rough. I was also annoyed that I couldn't get knetworkmanager to run automatically like it did on my laptop running kde3. Small annoyance but anyway I upgraded to the latest KDE 4 stuff through the unsupported repo and then when 9.04 (jaunty jackelope) came out, I thought this was my chance to solidify the remaining dodgy interface issues and leave my mum with something that will just be cool. Problem was, after upgrading to 9.04, the wireless stopped working. It is one of those things that is unforgivable for software which is supposed to be release ready that something that worked fine stops working. Of course, because it is the wireless (isn't it always) you can't download any updates or search forums for answers - grrr.
My first guess was to backup all mums files to an external hard disk and then do a clean install rather than the possibly dodgy upgrade. This of course took a while but to no avail, didn't work in exactly the same way, the wireless card driver was fine, lshw showed the card with a kernel driver and iwconfig showed a single wireless interface so no double-driver issue. The clue was the fact that I could see the networks in the list but when I clicked onto them, I would get a connect dialog, fill in all the details but then nothing!
Went to the forums and found out that sometimes network-manager required the password in hex instead of ascii so used the conversion code but to no avail. Also then read it doesn't support wpa properly and double-checked but my router was using wep. In the end, I ground my teeth and decided to uninstall plasma-widget-network-manager and go back to knetworkmanager which had worked before. Next mistake, ran the uninstall and install in the same session and of course as soon as the plasma stuff had uninstalled, I couldn't connect to the net to install knetworkmanager, for some reason I couldn't get it off the CD.
Reinstall AGAIN, another 30 minutes.
This time, I installed knetworkmanager and simply right-clicked the plasma network icon and told it to go whence it had came. I rebooted just to make sure it didn't come back up and it didn't (although knetworkmanager still doesn't always come up).
This left me fuming, this is the reason why people go with Windows. It is inferior, bloated, slow, expensive and insecure but for some reason (the details of which are irrelevant to most people) it just seems to work. The fact that the release has regressed is a big pity and I hope some people's backsides are being kicked for it!!

Wednesday, 22 April 2009

Why another security breach?

Just reading on the news that several UK and US government computers have been compromised by a botnet worm that can be used remotely to carry out various other IT attacks. However many of computers were actually compromised and how many were significant and not just some rubbish desktop in a constituency office I do not know but what I do know is that security can be achieved but people are simply either unable or willing to do so.
For instance, any things like flash drives etc can be used outside of work and can end up with viruses on them. Simple, do not permit any important PC in high risk situations to accept USB flash drives - period. If people need to work on stuff on another PC, they need to log in remotely or find some other way to avoid insecure transport.
Dodgy browser? Simple. Remote Internet Explorer and use something else. Setup the security settings and lock them down (although to be fair, sometimes new ways are found to circumvent what were previously acceptable measures). Better still, run an operating system like Linux, Unix or possibly Macs that have a decent security model. Not only will most viruses not even run on Linux but if they do, they cannot do anything to the system without 'root' access and likewise, they cannot spread across the network to other Linux machines for the same reason. The risk is massive and yet these bods are still using lorry loads of Windows Machines.
Dodgy web sites? Here is a simple solution, use a web site blocking tool like WebSense that will probably block 99% of all malware sites automatically so the chances of stumbling onto one are much reduced.
Better still, use a whole range of measures, everyone of them reducing the attack surface and putting these hacker people out of business.
There aren't many people with the power to carry out such security work so if you are one of them, please do it. I don't want to spend millions via the banks and insurance companies to finance some criminal somewhere in the world.

Monday, 20 April 2009

Microsoft take heed

When I see things that are very wrong in MS products, i wonder how this can be. Much smaller companies produce very high quality software (maybe because they are small?) whereas it's the basics that get me.
If you accidentally hover over the print button in an Outlook email, the system stalls for about 2 or 3 seconds, presumably while it is having a look for printers or something. Why? Why stop me working because of a hover? Is it their way of trying to buy seconds before the button press rather than afterwards?
Why is your software model so flawed that updates and fixes are large and take ages to install? I installed Access 2007 (only) and Microsoft Update then offered me something like 150Mb of fixes!! (how big is office anyway?) they took longer to download than Linux would do for 20 times as many fixes. Also Linux installations after updates can be done usually in less than 30 seconds whereas Windows ones go on for minutes.
Another horror story, I reinstalled a copy of Windows XP that I bought about a year ago and went to Microsoft update and there was 500Mb of updates for it. 500Mb!! It took 2 hours to install after it spent several hours downloading.
Come on MS, these are basics. You had a chance with Vista to right many wrongs and you mucked up big time. Are you surprised that loads of people have started using Linux?
IE8. A step forward in web standards apparently. It scores a paltry 18 out of a 100 in the acid test web site compare to the next worst 'other' browser which is about 75 and Opera 2 for Windows can manage 100. Please Microsoft, get good people and write good software otherwise you will die!!!

Wednesday, 8 April 2009

Problem with Windows Path setting

I installed some software yesterday in XP and needed to run a utility from anywhere on the PC. I put the path to it into the PATH environment variable (Control Panel, System, Advanced, Environment Settings) but yet when I tried to run it, I got, "'nmake' is not recognized as an internal or external command, operable program or batch file.". They could simply have said "nmake not found" but good old bloatmasters Windows!
Anyway, it didn't make any sense, I had tried all the usual, definitely got the correct path, definitely runs OK in the actual program directory and I finally found out what was wrong.
When I pasted the URL into the Path environment variable, I had somehow copied a space before the C: so it looked like: "Something Else; C:\Program Files..." The space doesn't bother Windows but it also breaks the path lookup for the directory after the space. Removed it and it was fine. Great!! Only about 30 year old code still has bugs in it.