Log

Today, I was quite mystified by a very slow running query. There was a table named 'bar' with about 3000 rows. I wanted to list all the rows that had a duplicate value for a certain field ('foo'), and only those rows. The solution was to build a query that selected the rows where the value of the 'foo' field was in the results of a subquery that selected 'foo' for duplicate values of foo. The query finally looked something like this:

SELECT 
foo
FROM bar
WHERE foo IN (
  SELECT 
  foo
  FROM bar  
  GROUP BY foo
  HAVING count(foo) > 1
)

The inner query (explained in this post) was very fast, and returned only two rows. The outer query, when I ran it like this:

SELECT foo FROM bar WHERE foo IN (1, 2);

also ran very fast. However the combination of the two was extremely slow. I thought this was weird, since there were only two results in the inner query. A colleague of mine and me took a look at the EXPLAIN of the query, and found out it was actually doing a full join of 3000×3000 rows. The use of HAVING threw me off because it appeared in the inner join. But HAVING is always applied very late in the execution process, just before the results are sent to the client. This means MySQL doesn't even look at the HAVING to optimize queries. From the manual:

The HAVING clause is applied nearly last, just before items are sent to the client, with no optimization.

Putting an index on the 'foo' column solved the speed problem, though it's still not as fast as it could be because it's still doing a JOIN on 'foo' with itself, only this time only doing 2×3000 rows.

A link related to the previous post: The Aluminum Foil Deflector Beanie.

_{Thanks Aczid.}

Did you know that, after World War II came to an end, a secret organisation called "Gladio", funded by the CIA and NATO, was brought to life in Europe in order to (amongst other things) neutralise communistic influences? The organisation spanned almost the whole of West-Europe, including countries such as Belgium, Germany, The Netherlands, France, the Scandinavian countries and more.

The organisation varied widely between countries and remained active (officially) until 1992 in at least the Netherlands. In some countries, the network deteriorated into terrorist organisations. Evidence has been found that in Italy, the organisation was involved in a bombing on the Italian Military Corps. In 1983 the Dutch government was forced to confirm that weapons found in a stash were related to NATO planning for unorthodox warfare. Earlier this year, investigators in the Netherlands found out that weapons had been illegally supplied to the Gladio network long after it had supposedly been dismantled in 1992. The same thing happened in Norway in 1979. Strong evidence was found in Germany that arms caches revealed by the person responsible for the 1980 Octoberfest bombing where related to the Gladio network.

More information on Wikipedia's English Gladio article. A Dutch article (with more information on Dutch specifics of the network) is also available.

A secret organisation brought to life in order to influence politics in a massive amount of countries… and it goes rogue and commits random acts of terrorism.. Amazing! Who would've thought something like that might happen?!

It's strange how today's 'freedom fighting' groups supplied with arms by western countries always seem to wind up being tomorrow's terrible terrorists. Maybe.. I dunno, but, you know, maybe we should stop funding these kind of things? Just maybe. Oh, and, also stop bringing secret organisations to life. They have a tendency to not work out very well, it appears.

Good news for the tin-foil hat wearers though: They can't call you paranoid now anymore. Big bad evil secret organisations really are everywhere! And to top it off, they're terrorists that are funded by your government, with your money, trying to stop you from exercising your right to democracy! Because, just in case nobody informed you yet: Democracy is great, unless you want to believe in something your government doesn't approve of, such as communism.

Hackers Take Down the Most Wired Country in Europe:

At exactly 11 pm, Estonia was slammed with traffic coming in at more than 4 million packets per second, a 200-fold surge. Globally, nearly 1 million computers suddenly navigated to a multitude of Estonian sites, ranging from the foreign ministry to the major banks. It was a larger-scale version of what had happened to the Postimees, except that the entire country's bandwidth capacity was being squeezed.

The story is a little over the top, there's a lot of speculation and I doubt Estonia is 'the Most Wired Country in Europe', but it's still an interesting read. I wonder if the Europian Union has any plans on how to counter such calamities. Basically all that's needed is a good line of communications via which immediate action can be taken to stop traffic as close to the root as possible, I guess.

The dutch consumer-rights organisation 'Consumentenbond' has recommended Ubuntu as a possible alternative to Vista after it received 4200 complaints about Vista in four weeks. Translation of the dutch Nu.nl article:

Flood of complaints about Windows Vista

AMSTERDAM – Consumer organisation 'Consumentenbond' has collected 4200 complaints about Vista in four weeks. Vista still isn't a viable replacement for XP, according to the organisation.

More home users are confronted with the new operating system, as Windows Vista comes pre-installed on most new computers. In august the Consumentenbond opened an online report form where customers with problems can report their problems.

Especially non-working software and peripherals have led to problems. For 59% of the complainers some computer programs refused to work. 46% experienced non-working peripherals such as printers and videocards. Slow computers and repeated network failures where also a big source of complaints.

The Consumentenbond remarked: "Obviously, Vista isn't a viable replacement for XP yet". In a letter to Microsoft, the consumer organisation has requested that Microsoft allows unsatisfied customers be allowed to downgrade to XP without cost..

Alternatives

The consumentenbond advises consumers to only get Vista if they're sure that their hard- and software works under the operating system. The consumer organisation also recommends consumers to look into alternatives such as Apple's Mac OS X or Ubuntu Linux.

Disappointed

Microsoft is disappointed about the investigation. "Microsoft had hoped to learn more about the user's experience with Vista. The complains however only focus on peripheral compatibility. The conclusion that Windows Vista wouldn't be a viable replacement for Windows XP cannot be determined from the investigation."

A spokesman is surprised that the consumer organisation now sought contact with Microsoft in order to discuss the outcome of the investigation. "The whole summer we've tried to get into contact with the Consumentenbond, but they have not responded".

According to Microsoft, there are now close to a million Vista users in the Netherlands. "The Consumentenbond does not mention how many users are content with Vista". Microsoft points users with problems to their support website.

– NU.nl: Klachtenregen over Windows Vista; 7 september 2007 14:53

I used to never get spam, until I made a little mistake in my mail client and sent mail to a mailinglist under my real email address. My address wound up on the big bad public internet, and a few hours later the first spam emails started to come in. I installed a quite sophisticated spamfilter to get rid of them, but it doesn't work at all.

One of the most frequent spam mails I get is the following:

From: Euro VIP Casino
Subject: ontvang 400 Euro GRATIS als u lid wordt

   Voel de unieke opwinding van het spelen bij Europa's beste on-line
   casino... en ontvang EUR 400 GRATIS als u lid wordt
   ...

(Translation of subject: "Receive 400 Euro's for FREE if you join now")

The sender and the subject are always the same. Always. The body is also almost the same all the time. Only a single word is needed to identify this as spam 100% of the time: Casino. In total, there are five words that would always mark this as spam without any false-positives: Euro, VIP, Casino, 400 and GRATIS.

Yet the spamfilter, which is really quite sophisticated, still lets one through every now and then!

I'm not sure how the spamfilter works exactelly, but I do know it involves at least a bayesian filtering technique and some other clever tricks. But all I basically need is manual control over a blacklist and whitelist of words. I simply want to say: "Mark word Casino as spam" and I'll be done with it.

I guess I'll have to write my own additional filter.

From the Apache2 configuration file for host example.com:

php_admin_value upload_tmp_dir "/var/www/example.com/tmp/"

From the file /var/www/example.com/htdocs/test.php:

var_dump(ini_get("upload_tmp_dir"));

Output:

string(29) "/var/www/example.com/tmp/"

Then, after trying to upload a file, from the /var/www/example.com/logs/error.log:

[Sun Sep 02 18:09:05 2007] [error] [client 88.211.179.104] PHP Warning:  Unknown: open_basedir restriction in effect. File(/tmp) is not within the allowed path(s): (/var/www/example.com/) in Unknown on line 0, referer: http://example.com/test.php
[Sun Sep 02 18:09:05 2007] [error] [client 88.211.179.104] PHP Warning:  File upload error - unable to create a temporary file in Unknown on line 0, referer: http://example.com/test.php

I'll highlight the important part for you: open_basedir restriction in effect. File(/tmp) is not within the allowed path(s):.

The lesson?? PHP needs to STOP silently ignoring errors and stop just using the default value when errors are encountered! The problem was that I specified the wrong upload_tmp_dir. It should have been /var/www/example.com/htdocs/tmp/. PHP should have thrown an error because this directory doesn't exist (it has detected this, because it falls back to the default of /tmp) and not just continue.

PHP's configuration implementation is one of the worst I've ever seen, and whoever's responsible for these kinds of problems should feel ashamed. PHP, like MySQL, is way to lenient when it comes to errors; silently trying to 'recover' from them. And people wonder why they're considered such bad projects?

A tip for PHP's developers: Fail early and fail loudly.