Randomly select a MySQL record

This is a reminder to myself as I have this problem every now and then. I thought I share this with you.

Ok the problem is to random(ish)ly select a record from a MySQL database in an efficient way without any scripting or programming. There are several more or less simple ways described everywhere on the web, one being

SELECT * FROM table_name ORDER BY RAND() DESC LIMIT 1;

It does the job. Very slowly though, it can take MySQL several seconds or even minutes to complete the query. After fiddling around for some time, I came up with this:

set @randomId=FLOOR(RAND()*(SELECT MAX(pk_field) FROM table));
PREPARE randomStmt FROM "SELECT * FROM table WHERE pk_field > ? LIMIT 1";
EXECUTE randomStmt USING @randomId

… where pk_field is a numeric primary key. Ok the query goes like this:

  1. Get the highest value for pk_field from table_name, multiply with a random number between 0…1, cut off decimals and store in variable randomId.
  2. Prepare a statement where we can insert randomId and retrieve the desired record with.
  3. Take the query, insert randomId and execute.

This runs very fast even with a large dataset. I hear your objections already… :)

“This is stupid. Why didn’t you use COUNT(pk_field) to find the end of the number range? Even then this approach would still be stupid BTW.” Because it is slow. I currently have a table with >1.8 million records and COUNT(pk_field) takes >1 seconds to complete. Not good. MAX(pk_field) is good enough in most cases. YMMV though.

“The query is not truly random and prefers records at the end of a large pk_field range gap!” True. That’s what I meant with “YMMV” :) My solution is not truly random and it is biased, especially when there are large gaps in pk_field. It works for me though and is random enough when there are no large pk_field gaps.

How to access Xing profiles via API and PHP

Note 2018-03-19: This post is deprecated as AFAIK Xing does not provide this API anymore.

Yesterday I have dabbled in accessing Xing profiles via API. For years people were asking Xing to finally provide an API but they were hesitant because of the strict privacy laws in Germany. Xing has lost users in the last months and other social networks already have APIs so they were forced to provide an API as well. Competition *does* work apparently. :)

Since I don’t have time to make a polished product out of it I thought I share my result with you in the hope you will let me know what you did with it. Let’s go.

The goal is to access a Xing profile (your own for now as you would have to use production keys and have your Xing API app reviewed by Xing in order to access other people’s profiles) and display the profile as raw data for further processing. You can go from there, making the profile information look beautiful, store access tokens and user IDs for subsequent requests and so on.

  • Go to dev.xing.com and register as a Xing developer. Once you have, log in and retrieve your testing OAuth consumer key and consumer secret.
  • Get oauth-php library from Google Code, unpack it and push it on a web server.
  • Grab my Xing client example, unpack it and put it in oauth-php’s client example dir. Edit xing.php and enter your OAuth consumer key and consumer secret and the full callback URL to xing.php.
  • Call xing.php in your web browser. You will get redirected to Xing so that you — as a Xing user — can give your Xing API app permission to access your profile. Once you have given permission, Xing will redirect to the xing.php script which will display raw profile data. If everything went well that is…

Now you have a go. :)

The keys to running a successful WordPress blog — technically speaking

Heise online reports WordPress is going to clean up the plugins dir because plugins “suck” and that — despite this fact — WordPress has become a constant in the web because large blogs such as Smashing Magazine are using it.

How do large WordPress blogs like Smashing Magazine accomplish this when plugins suck so much?

In the past years I have responsible for many WordPress installations, including Smashing Magazine‘s WordPress installations. I think I can tell you the keys that make a blog running WordPress successful or unsuccessful, technically speaking.

It’s the plugins:

  • How many of them are installed – the less the better!
  • Which ones are installed — always look how experienced the plugin’s developer is!
  • How they got chosen — make a security audit, either by yourself if you are competent, or hire someone how is!

In fact there are many, many WordPress plugins out there that have been developed by, let’s say, inexperienced developers. There are *tons* of security issues out there. The more plugins you install, the more security issues you install.

When I take over as a WordPress sysadmin, the first thing I do is throw out all unneeded plugins. Then I update the remaining ones. Then I try to further reduce the amount of plugins, either by implementing features myself or by replacing plugins with more capable/secure ones.

Here’s my last tip: If you cannot find a decent, capable, and secure WordPress plugin that suits your needs, hire a good developer with a security background to create it for you. Obviously you have to make sure not to hire one of the inexperienced developers. Please don’t go collecting plugins like “Oh I take this, and this one as well, this one sounds nice too” — this is not going to work in the long run. A successful WordPress blog is *always* run by competent admins and developers, not by “WordPress plugin collectors”.

Of course there are other factors as well, like always having the most recent versions of them installed, or to have interesting contents, but those are the keys IMHO.

Quick tip for Java/Netbeans users on Linux

If you are using Linux (Ubuntu and Debian in my case) and are being haunted by unresponsive, CPU and memory hogging Java applications you might find this piece of information useful:

  1. Check if you are using OpenJDK by issuing “java -version” in a terminal.
  2. If something with “OpenJDK” comes up, follow instructions (in german, but you get the point) on how to switch to the Java JDK from Oracle.

Netbeans now runs like a breeze now. Enjoy :)

Setting fake user agent in Polipo

I use Polipo as a caching web proxy for my VPN and for some simple anonymizing. It’s a great software.

One thing bugs me though: when censoring User-Agent, as intended, it does not send a User-Agent header. But: Sometimes poorly written web scripts break on a missing user agent string or web scripts take a missing user agent string as an indicator for a bot and block the request (Wikipedia does that for example).

I have created a tiny patch that sets “User-Agent: empty” header when censoring “User-Agent” so that web scripts do not break or block the request. There are only four new lines and you can set the fake user agent string in config.h:

diff  -cB  ./polipo/config.h  ./polipo_custom/config.h
***  ./polipo/config.h 2011-07-17  12:48:42.000000000  +0200
---  ./polipo_custom/config.h 2011-07-17  13:22:06.000000000  +0200
***************
***  35,40  ****
---  35,41  ----
    #define  CONFIG_INT_LIST  12
    #define  CONFIG_ATOM_LIST  13
    #define  CONFIG_ATOM_LIST_LOWER  14
+  #define  USER_AGENT_STRING  "empty"
   
    typedef  struct  _ConfigVariable  {
            AtomPtr  name;
diff  -cB  ./polipo/server.c  ./polipo_custom/server.c
***  ./polipo/server.c 2011-07-17  12:48:42.000000000  +0200
---  ./polipo_custom/server.c 2011-07-17  13:25:28.000000000  +0200
***************
***  1678,1683  ****
---  1678,1686  ----
                    goto  fail;
   
            if(request->request  &&  request->request->headers)  {
+   if(strstr(request->request->headers->string,  "User-Agent:")  ==  NULL)  {
+           n  =  snnprintf(connection->reqbuf,  n,  bufsize,  "\r\nUser-Agent:  %s",  USER_AGENT_STRING);
+   }
                    n  =  snnprint_n(connection->reqbuf,  n,  bufsize,
                                                  request->request->headers->string, 
                                                  request->request->headers->length);

You can check your user agent string with http://whatsmyuseragent.com/.

There is no PayPal for me

Every now and then colleagues or customers ask me whether I can do PayPal transactions with them.

Unfortunately, I can not. Let me explain:

Until some 7-8 years ago I had been a happy PayPal customer with a positive balance of x.y EUR. Then, from PayPal’s point of view, I made a “suspicious” transaction of exactly x.y EUR.

For PayPal, this was an attempt of fraud. I cannot remember who PayPal thought was the victim in this case but it does not matter either.

PayPal froze my account and demanded some proof or acknowledgement that the transaction was no fraud. They suggested sending them a utility invoice that should prove that I am real. Unless you do not know, in Germany it is absolutely uncommon that a company asks for a copy of a utility invoice. Maybe in some other less developed country this is considered appropriate, but I do not. This is odd and strange and none of their business.

I saw no need to prove my innocence, because… you certainly remember: In a constitutional state you are innocent until proven guilty.

My account remained frozen for three years until PayPal started inviting me back to use their services again.

Thanks, but no thanks.

Most companies pay good money for customer leads but this company seems to have a Customer Repellent Department.

So, there is a good reason for my PayPal abstinence. I hope you understand I can not risk my balance being frozen by a rampant company.