Archive for 2008

generate random timestamps in mysql

I’m now try to prepare a mysql performance comparison between hdd and flash. So to create a lot of test data I needed a function to create random timestamps in a certain range.

After trying google I gave up and started with the mysql documentation and came up with the following statement:

select from_unixtime(
       unix_timestamp('2008-01-01 01:00:00')+floor(rand()*31536000)
);

So lets break it it down a bit:

first I set a start date (minimum for the random)

unix_timestamp(’2008-01-01 01:00:00′)

now you have the timestamp in Unix timestamp format. This means you can add any given seconds interval to it.

rand()*31536000

For me I wanted a value between 2008 and 2009 (one year: 60 seconds * 60 minutes * 24 hours * 365 days = 31536000). Because the Unix timestamp doesn’t support fractions your need to round the value to an int. (floor or round the value).

After the addition you just convert it back from Unix timestamp to a mysql timestamp.

so here the generic formular:

select from_unixtime(
      unix_timestamp( 'start timestamp')
     +floor(rand()* (max interval in seconds) )
);

,

1 Comment

tunnel your imap over ssh

I recently had the situation that I needed access to my private email accounts at work. Lucky me, my employee prohibits imap connections to the outer world (security risk). So I had to find a way around that proxy to the mail server.

I stumbled upon an article how to use a persistant ssh connection as tunnel through different networks. So starting at that point and checking the ssh man page I came to the following statement (works only with public key authentication):

ssh -N -L 4510:localhost:25 -L 4500:localhost:143 user@host.net

This statement establishes a connection between the local and the remote host and hereby connects the local port 4510 with the remote port 25 (same with 4500). The next step was easy,I just configured my mail client to read from the imap server localhost:4500 and send mails via localhost:4510.

Now that I had the basics running I needed a way to make this connection persistant.

This could be done via a simple bash script which is called by the cron repeatedly.

#!/bin/sh
COMMAND="ssh -N -L 4510:localhost:25 -L 4500:localhost:143 user@host.net"
pgrep -f -x "$COMMAND" > /dev/null 2>&1 || $COMMAND

This script basicly runs a ps and greps the output for the search string. When the string is not found it runs the string as command. If the string is found nothing would we done.

(I’m aware that clients like evolution have built-in support for ssh tunnelling but I couldn’t get it running smoothly. It had always Problems with disconnects which never appeared with this solution)

, , ,

1 Comment

Hello World!

So, this is my my first entry. So lets start with some facts about me.

I work for a major german company as a Software Engineer (according to me – according to my employer my job title changes every 6month or so and I don’t keep track of it). At work I’m part of a team which develops an ESB for the whole company. Our base technology is TIBCO BusinessWorks.

This site underdog-projects.net is only about my private projects. So far only 3 projects exists but I think there will come more. So here a short Review of the existing projects:

cdmfs is a project of a friend of mine. Goal of this projects is to build an client/server architecture for an distributed filesystem which can handle meta information for every file and folder.

pidgin-ocs is the beginning of implementing an Office Communication Server protcol for pidgin. I got stuck at the ntln authentication so far. The pidgin ntlm doesn’t work on my companies server, so I’m trying now get the ntlm from heimdal running. Let’s see how this goes.

shell-tools.net is a projects of mine of a new webpage. The page should consist of the essential shell tools so you can user them online with an web-interface.

That’s it so far from me.

No Comments