Hacker's ramblings

Gillian from Oracle informed me that my query is not valid SQL and the 5.5 version worked just because I was lucky.

The correct way of using aggregate function count() is something like this:

SELECT mlh.changedate, mlh_latest.counts, mlh.level
FROM memberlevelhistory mlh
INNER JOIN (
    SELECT member, MAX(changedate) as maxdate, COUNT(changedate) as counts
    FROM memberlevelhistory
    WHERE member = 5
    AND approved <> 'N'
) AS mlh_latest ON mlh.member = mlh_latest.member AND mlh.changedate = mlh_latest.maxdate
WHERE mlh.member = 5
AND mlh.approved <> 'N';

Now the result is equally correct on both tested versions.

MySQL 5.6.10 handles INNER JOIN / subquery -pair differently than 5.5.29. I found out this by accident when working code ceased to return proper results.

Example setup, a very simple table and couple of rows:

CREATE TABLE `memberlevelhistory` (
  `member` tinyint(3) unsigned NOT NULL,
  `changedate` date NOT NULL,
  `level` int(10) unsigned NOT NULL,
  `approved` char(1) NOT NULL,
  PRIMARY KEY (`changedate`,`member`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `memberlevelhistory`
(`member`, `changedate`, `level`, `approved`)
VALUES
(5, '2009-08-01', 1, 'Y'),
(5, '2009-08-27', 2, 'Y'),
(5, '2009-10-01', 4, 'Y'),
(5, '2010-01-01', 5, 'Y'),
(5, '2010-02-01', 8, 'Y'),
(5, '2010-03-15', 9, 'Y'),
(5, '2011-02-01', 11, 'Y'),
(5, '2011-05-01', 12, 'Y'),
(5, '2012-02-01', 13, 'Y'),
(5, '2012-03-01', 14, 'Y'),
(5, '2012-04-01', 15, 'Y');

Description of columns:

• member: user ID
• changedate: when member lever was changed
• level: user level
• approved: level change approved by administration
  

The idea of the table is that most recent approved level is user's current level.

Example query to get user's current approved level with total number of approved user levels:

SELECT mlh.changedate, count(*), mlh.level
FROM `memberlevelhistory` mlh
INNER JOIN (
    SELECT member, changedate, level
    FROM `memberlevelhistory`
    WHERE member = 5
    AND approved <> 'N'
    ORDER BY `changedate` DESC
) AS `mlh2` ON mlh.member = mlh2.member AND mlh.changedate = mlh2.changedate
WHERE mlh.member = 5
AND mlh.approved <> 'N'

MySQL 5.5 result, current level as expected:

+------------+----------+-------+
| changedate | count(*) | level |
+------------+----------+-------+
| 2012-04-01 |       11 |    15 |
+------------+----------+-------+
1 row in set (0.00 sec)

MySQL 5.6 result, a surprise here:

+------------+----------+-------+
| changedate | count(*) | level |
+------------+----------+-------+
| 2009-08-01 |       11 |     1 |
+------------+----------+-------+
1 row in set (0.00 sec)

The query behaviour has changed. The subquery ORDER BY -clause has no effect. I did solve the problem of latest level with LIMIT 1 in the subquery, but it ruins the COUNT(*). I'm still working to replicate the 5.5 result in a single query, if a solution can be found, I'll blog about it.

I got bunch of automated comments to this blog. The comments were very generic about "how great this blog is" and "how fast the site loads", blah. blah. I typically check the moderation box for my blog entries, so they were just hoping to get automated publicity. In my case I just deleted the crap.

The idea of this spam-campaign was to distribute links to freemasonrysecrets.com

WTF?! Who cares about that?

SQLite has very little support for typical arithmetic functions. In the SQLite contrib-section there is an extension for that by Liam Healy. The description goes:

Provide mathematical and string extension functions for SQL queries using the loadable extensions mechanism.

• Math: acos, asin, atan, atn2, atan2, acosh, asinh, atanh, difference, degrees, radians, cos, sin, tan, cot, cosh, sinh, tanh, coth, exp, log, log10, power, sign, sqrt, square, ceil, floor, pi
• String: replicate, charindex, leftstr, rightstr, ltrim, rtrim, trim, replace, reverse, proper, padl, padr, padc, strfilter
• Aggregate: stdev, variance, mode, median, lower_quartile, upper_quartile

To ease the installation, I packaged into into a RPM for CentOS 6:

• sqlite-extension-functions-2010.01.06-1.x86_64.rpm
• sqlite-extension-functions-2010.01.06-1.src.rpm

The source-RPM will build quite easily on any RPM-disto. There are no weird dependecies or anything.

Example usage:

# sqlite3
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .load libsqlitefunctions.so
sqlite> select floor(1.9);
1
sqlite> select ceil(2.1);
3
sqlite> select reverse("reverse");
esrever
sqlite> .quit

Now developing apps with SQLite back-end is much easier.

I've been having Sonera (or TeliaSonera) Internet connection for ages. Occasionally I refer to my own IP with the DNS-name and now they chose to change them. It looks like they chose to change the subscriber identifier part of FQDN to indicate IPv4 address instead of some sort of internal identifier.

Typical Sonera broadband dynamic IP-address' reverse-DNS FQDN has format:

• Connection type: (fixed: dsl or cable)
• City identifier: always 3 characters
  
• Gateway identifier: (example: brasgw1)
• Subscriber identifier: hex-decimal -combo
  
• Dynamic broadband pool identifier: (fixed: dhcp.inet.fi)

A regexp would be:
^(dsl|cable)-([a-z]{3})([a-z0-9]+)-([0-9a-f]{6,}-\d{1,3})\.dhcp\.inet\.fi$

The old subscriber identifier had 8 hex digits, a dash and 1-3 decimal digits. For example: fe82eb00-56

The new subscriber identifier has 6 hex digits to represent the first 3 bytes of IPv4 address, a dash and 1-3 decimal digits for the last byte of IPv4 address. For example IPv4 address of 21.32.43.54 would be: 15202b-54

Wishful thinking: Are they finally preparing to offer IPv6?

On my Google AdSense payment history: Jan 28 2013: Invalid Traffic - AdSense for Content

They reduced my monthly payment with about 1% of the total sum. No explanations, no nothing. They're just claiming that I'm not following the mutual agreement made about AdSense usage policy and provide no proof of that. Quite literally they're saying is that I'm abusing the system to gain extra payments out of Google and if not abusing the system, I'm a liar because I don't admit doing that. Nice!

There is a discussion thread Help to identify Invalid Traffic Source in the Google AdSense in English support forums. Lot of people having this issue since December 2012, and no possibility of tracking who clicked what to cause the alleged invalid clicks.

This is not about the 1% of lost revenue, it's about me being called a liar and not introducing any kind of evidence what I did. The AdSense payments fluctuate a lot anyway, they somewhat correlate with visitor flow, but sometimes Google issues more expensive ads which yield more cost-per-click. Then again they issue cheaper ads with lower CPC. Since they get to control who gets what, in the end it is a zero-sum-game and on a monthly payment differences equal out resulting something that correlates the site visitor flow. But this 100% control is not enough for them, they don't want to pay out what they're agreed to do and get 101% of control.

It is needless to say this, but I'm doing it anyway: This sucks like a Kirby vacuum cleaner scam!

My production box crashed with:

** glibc detected ** nginx: worker process: malloc(): memory corruption: 0x00000000012f9d20 ***
** glibc detected ** nginx: worker process: malloc(): memory corruption: 0x00000000012f9d20 ***
2013/02/21 21:34:30 [alert] 20048#0: worker process 7258 exited on signal 9

Which was bad.

I like ETags to reduce page loads, and Nginx does not support them. To get it support ETags, there is a module in GitHub, which I am using.

After a nice couple of hours of debugging it turned out that all other places in Nginx-code call ngx_memzero() before calling ngx_open_cached_file(). It turned out to be the crucial mistake in the module. I filed an issue to the original author to notify everybody else.

My production boxes did actually return weird errors now and then, which I didn't think much of. Weird things happen sometimes. However, this fix seems to help, there are no worker processes dying and page load erros seem to gone away. Hopefully the box does not crash again.

Adblock Plus is a great add-on for Fire-/Waterfox, Chrome and Opera. It really keeps the unwanted commercial crap out of your browsing experience. There are so many badly designed and implemented ads in the world, that they triple or quadruple page loading time, look ugly and sometimes when programmer has really been a total idiot, a failure to load the stupid ad crashes the JavaScript functionality which would be otherwise needed. Programmers: Test your site with ads blocked, please.

My point is, that if ad programmers are idiots, equally idiotic programmer in Adblock Plus project decided that I cannot type. Sure, now and then my fingers do not hit they keys they should have been, but I most definitely don't need a machine to fix my typos. That's for sure. Now the braniac that made the decision that I cannot type, enables this useless helper as default.

The entire feature is fucking useless! It never works when I mis-type, but it always triggers when I enter the address correctly. Looks like somebody else has the same issue and wrote instructions on how to disable the I-think-you-wrote-it-wrong-I'll-make-it-much-worse-for-you -addon.

Can you show me a single working example, where machine would 100% detect user's mistake and 100% of the time correct it properly? No you cannot. Nobody can.

Earlier I wrote about elaborate Chinese scam to shake down money from unsuspecting corporate domain owners.

This time the e-mail really didn't specify any URLs to fake companies, nor really specify any fake company names. The e-mail was sent by clark.yang@picweb.net. There is a web site http://www.picweb.net/, which is located in Los Angeles, USA. The content has lot of references to China. Un-surprisingly, most of the links are either non-existent or actually are not links. Especially the top menu for "Products and Services" is not a link, so they actually don't sell anything. The web site looked like this:

The e-mail for picweb.net is handled by mx168.cn4e.com which is in CHINANET Fujian province network. There actually is a mail server in the address, but I don't know if they handle any e-mail for picweb.net. In the e-mail headers they tried to fake SMTP-route and point finger to a most likely innocent Chinese IP-address. They are not very good in forging headers and the attempt is rather childish.

Also un-surprisingly, the e-mail arrived to Google via IP-address 117.27.141.168, which is in same network as the previous domain scam e-mail. Also the above mail server is in the same ISP's block. They still don't care what kind of crybercrime is going on on their wire.

I'll post the contents of the e-mail here:

(Mail to the brand holder, thanks)

Dear Brand Holder,
We are the department of Asian Domain Registration Service in China. I have something to confirm with you. We formally received an application on February 20, 2013 that a company which self-styled "HongDa International Co.,Ltd" were applying to register "hqcodeshop" as their Net Brand and some domain names through our firm.

Now we are handling this registration, and after our initial checking, we found the name were similar to your company's, so we need to check with you whether your company has authorized that company to register these names. If you authorized this, we will finish the registration at once. If you did not authorize, please let us know within 7 workdays, so that we will handle this issue better. Out of the time limit we will unconditionally finish the registration for "HongDa International Co.,Ltd". Looking forward to your prompt reply.

Best Regards,
Clark Yang
Regional Manager

AnHui Office:
Phone: +86-551 6512 0117      
    Fax: +86-551 6512 3308
Postal Code:230022

Address:AnGao World Cities,No. 99,WangJiang West Road,HeFei,AnHui Province,China

ShangHai Headquarters:
Postal Code:201315
Address:No.11,Lane 788,Xiupu Road,Nanhui District,ShangHai,China

The e-mail headers are here:

Delivered-To: jatu@hqcodeshop.fi
Received: by 10.64.148.67 with SMTP id tq3csp180927ieb;
        Wed, 20 Feb 2013 00:58:50 -0800 (PST)
X-Received: by 10.66.243.169 with SMTP id wz9mr52609194pac.34.1361350730222;
        Wed, 20 Feb 2013 00:58:50 -0800 (PST)
Return-Path: <clark.yang@picweb.net>
Received: from mail.umail168.cn4e.com (mail.umail168.cn4e.com. [117.27.141.168])
        by mx.google.com with ESMTP id o4si27012995paw.72.2013.02.20.00.58.48;
        Wed, 20 Feb 2013 00:58:50 -0800 (PST)
Received-SPF: neutral (google.com: 117.27.141.168 is neither permitted nor denied by best guess record for domain of clark.yang@picweb.net) client-ip=117.27.141.168;
Authentication-Results: mx.google.com;
       spf=neutral (google.com: 117.27.141.168 is neither permitted nor denied by best guess record for domain of clark.yang@picweb.net) smtp.mail=clark.yang@picweb.net
Received: from clarkyangpc (localhost.localdomain [127.0.0.1])
    by mail.umail168.cn4e.com (Postfix) with SMTP id 9B02BA28004;
    Wed, 20 Feb 2013 16:58:46 +0800 (CST)
Received: from clarkyangpc (unknown [124.73.90.238])
    by mail.umail168.cn4e.com (Postfix) with ESMTPA;
    Wed, 20 Feb 2013 16:58:46 +0800 (CST)
From: "Clark Yang"<clark.yang@picweb.net>
To:
Subject: "hqcodeshop" Net Brand and domain name registration
Date: Wed, 20 Feb 2013 17:00:53 +0800
Message-Id: <DM__130220165401_37568426463@mail.picweb.net>
MIME-Version: 1.0
Content-Type: multipart/related;
    boundary="----=_NextPart_13022017005237571425618_001"
X-Priority: 1
X-Mailer: DreamMail 4.6.9.2
Disposition-Notification-To: clark.yang@picweb.net

Yet again I did report this scam to Google. Looks like they are powerless with these ones.

What happened to Symantec / Norton?

They used to be the backup company, but they have not release anything after Norton Ghost 15.0 (yes, I am an user). That is from Nov 2009, making newest version over 3 years old! On top of that they announced that Windows 8 will not be supported. WTF!? That is an excellent indication of their commitment to the product. Those who cannot understand sarcasm: they abandoned the product years ago.

Screw you Symantec! I'm going home.... erhm... to Acronis True Image. They release updates, they release new versions, they support Windows 8. Their level of commitment is from a totally different planet than Symantec.

I ended up gotting a new laptop. Since my weapon of choice has been Thinkpad for ages, I got me a new Lenovo. The T-series has existed for ages. Even IBM made them during 90s. The T-series laptops are a bit pricey, but they are meant for working. Couple of years ago a colleague said "and they look like tools, too!".

Here is the link to Lenovo-page.

Since this is an ultrabook (whatever that actually means). It is quite light and only 22 mm thick, as you would expect there is no DVD-drive and only 2 USB-ports. They are USB3, but still, only two of them. Everything in this reminds me of Apple laptops, with the difference that there is a latch at the bottom of the machine. You can open the bottom very easily and all of the parts are easily accessible. It is easy to notice the philosophical difference in design and the ease of manufacturing with Lenovo and Apple. This one was designed by the same people, who actually build and fix them.

The really exciting part is that the state-of-the-art Ivy Bridge CPU/chipset/graphics -combo runs really, really fast. The difference to my previous laptop is a huge. To speed up things even more, I changed the 128 MiB Samsung SSD into 240 MiB Intel SpeedDaemon 520-series. I'm getting Windows Performance index of 4,9 with the weakest link being GPU and specifically desktop graphics. i5 CPU gets 6,9, memory gets 5,9 and SSD gets maximum reading of 7,9. All of these are really good numbers for a 1,7 GHz machine!

Typical usage scenario for a business laptop is to use it in docking station at the office. I got the Lenovo USB3-dock, but still I wouldn't call this a traditional docking station. To "dock" the laptop, you just connect USB3-cable and charger. The dock has quite a many USB3-ports, two DV-i ports and a 1Gbit/s Ethernet connector, all of which I require when docked.

But I'll stamp this with my seal-of-approval. T430u is definitely one to get!

The puzzle at Mystery Hunt site. A PDF-version of it, to solve it with paper and pencil.

I wrote the regexp-puzzle into a HTML / JavaScript -page, go see it at http://opensource.hqcodeshop.com/grid/. It will display the regexp with red colour if it does not match.

This one is really tough to solve. Good luck!

I got my hands on Huawei B593, punched in a SIM-card and turned the thing on. The admin is at http://192.168.1.1/ and admin password is admin like on many other devices, no surprises there.

Since my SIM is a pre-paid one which I use for testing and temporary Internet access, I simply wanted to see how much credit I had there. But wait! There is no Send SMS -functionality. I got the PDF-manual which said that there would be one. I think every 3G USB-stick software has that, why a 4G router wouldn't have the simple thing?

Little bit of Googling revealed that some telcos actually don't put it there. I did get my box from an independent reseller, but it looked like it was a Sonera (Telia) branded hardware. Little bit of more Googling ended me to one of their competitors, Saunalahti. From their customer support page, I got their firmware, uploaded it and noticed a number of drastic changes. Login screen was changed in to a generic Huawei one. After login my precious SMS-menu was there!

Yet another shame on you Sonera! Why did you leave the SMS-sending out. Idiots!

Update:
The login screen for the Saunalahti firmware version V100R001C260SP055 looks like this.

Update 2:
Most non-Finnish readers cannot read the telco's support-page. Download link for the firmware is http://www.elisa.fi/elisa/docimages/attachment/tuki/elisa_r+m+h+s.tar.bz2

Update 3:
There is an article about firmware from 3 Denmark.