Fetching the most recent entry from a log-table

Sometimes there’s a need to keep a simple log in a database. A common format could be a table with a layout like this:

- area (CHAR)
- lognotice (CHAR OR text)
- logtime (TIMESTAMP WHEN the event was logged).


Fetching all log entries from a certain area is a simple matter of fetching by the area field, but when building a dashboard with the most recent entry from each area is slightly more complicated – the Query to fetch the data could typically look like this:

SELECT * FROM log log1
WHERE logtime = (
	SELECT MAX(logtime) 
	FROM log log2 
	WHERE log2.area = log.area)

Cleaning up

To keep things clean and tidy, I only sorte data from the past month, week or day (depending on the “log intensity”). To achieve this I usually do something like this:

DELETE FROM log WHERE logtime < ###time###

Resizing vmdk harddisks for VirtualBox

VirtualBox is a free tool, which allows you to run virtual machines on a host computer. Using VirtualBox you can install Linux, Windows and other operating systems to play and experiment with.

Start the process by closing virtualbox (I usually also dettach the virtual harddisk due to be resized).
Go on to a shell and find the directory where the virual machines are located.

If your initial harddisk was cloened from another virtualbox, you may get an error like this:

VBoxManage: error: Cannot register the hard disk 'disk01.vmdk' {eaed1256-a34f-4c2f-873b-62cc78c4be80} because a hard disk 'disk01.vmdk' with UUID {eeed1256-a32f-4c1f-813a-62dd78c4be80} already exists
VBoxManage: error: Details: code NS_ERROR_INVALID_ARG (0x80070057), component VirtualBox, interface IVirtualBox, callee nsISupports
VBoxManage: error: Context: "OpenMedium(Bstr(pszFilenameOrUuid).raw(), enmDevType, enmAccessMode, fForceNewUuidOnOpen, pMedium.asOutParam())" at line 178 of file VBoxManageDisk.cpp

To fix this issue pop into a terminal and enter this command:

vboxmanage internalcommands sethduuid ./disk01.vmdk

This command changes the HD uuid from the disk and ought to fix the issue.

Ready to resize

Next challenge is that the disk is in vmdk format, so to resize we need to convert the disk to VDI format, resize and convert back to vmdk format:

vboxmanage clonehd ./disk01.vmdk clone.vdi --format vdi
VBoxManage modifyhd ./clone.vdi --resize 30720
vboxmanage clonehd ./clone.vdi ./disk01.vmdk --format vmdk

The “virtual physical drive” is now resized, but the partion on the harddisk also need to be resized.

Boot up the virutal box (and make sure the disk is attached if you’ve dettached it during the conversion and resizing).

  • In Linux you can do this through using Gparted.
  • In Windows go to Computer Management (by right clicking “My Computer” and find “Storeage” -> “Disk Manangement”.

Change the partition on the drive need to be expaned and save the changes. The drive should now be resized and ready to be used.

ftp on OSX Lion

While it really isn’t secure at any measure, ftp is a very useful way of moving files around. Apple’s OSX have a build-in basic ftp server, but in Lion (version 10.7) the user interface seems to have disappeared from the User interface. The servers is still available under the hood if you need it.

To enable the ftp-server (the availability) enter this command in a terminal window:

sudo launchctl load -w /System/Library/LaunchDaemons/ftp.plist

From then on use this command to enable the ftp-server:

sudo launchctl start com.apple.ftpd

and youse this command to stop the ftp-server:

sudo launchctl stop com.apple.ftpd

To remove (the availability) of the ftp-server issue this command:

sudo launchctl unload /System/Library/LaunchDaemons/ftp.plist
  • If you need the ftp-server from time to time, you should probably not remove it, but just stop it, when it’s not being used.
  • If you often need an ftp-server you should probably look at a more full-featured ftp-server (such as pure ftpd).

Linux Mint: OpenSSH Daemon

I’m in the process of reinstalling my work desktop. One of the mandatory packages which I install once the core system is up and running is a SSH Daemon.
Setting it up (on Linux Mint which I’m running) is pretty easy. To install the OpenSSH daemon go to the shell and write:

sudo apt-get install openssh-server

It’s a fairly small install, so in a few seconds it ought to be up and running. Next step is editing the default config file and change a few things.
Editing the config file is done by entering:

sudo vi /etc/ssh/sshd_config

The cofiguration options I usually edit these parameters:

PermitRootLogin no
#Banner /etc/issue.net
AllowUsers <username>
  • PermitRootLogin – The default option is yes, but frankly root should never be allowed to login remote unless absolutely needed.
  • Banner – Can allow a custom message be displayed at login (if needed).
  • AllowUsers – A space separated list of users allowed to login remotely.

Once the edits are done and saved, the openSSH Daemon needs to restarted which is done by:

sudo service ssh restart

Trying and failing (twice)

PHP like many other programming languages has facilities to handle exceptions. Using them is pretty easy, but sometimes lazy programmers seems to misuse them to suppress error messages. A try/catch in PHP is usually constructed something like this:

try {
	// Something can go (horribly) wrong...
} catch {

The lazy programmer may leave the catch empty, but frankly you should never do it. When you’re doing something – try’ing – it’s for a reason, and if it fails, someone quite possible need to know – the end user, a log file for the sysadm or someone else. Never leave your catch empty, and if you really have a case, where it’s applicable, at least leave a comment in catch block explaining why it’s okay to do nothing.

strpos in PHP – like being stung by a needle in a haystack

In PHP when you have a string and want to find out if it contains another string, there are a few ways to do it. You can use regular expressions, use the strstr functions and a few other methods.
The easiest way though is probably by using strpos, which returns the number of the character containing the first occurrence of the thing you’re looking for – and false if the string isn’t found.

Simple – yet with a slight danger.

$haystack = 'This is an example';
if (strpost('This', $haystack)) {
  echo "Found";
} else {
  echo "Not found";

In the example above, where we’re looking for the string “This”, the php code will echo “Not found”. The reason is, that the first (and only) occurrence of “This”, is at the begining of the string – character zero.
As strpos returns zero, the if statement is evaluated to false and thus the “Not found” is echoed to the screen.

Fixing the error is simple once you remember the “the first index in a string is zero with strpos” rule:

$haystack = 'This is an example';
if (strpost('This', $haystack) !== false) {
  echo "Found";
} else {
  echo "Not found";

Adding the !== false, forces a type check, and as the number zero is (exactly) false, the value echoed is “Found”.

Adding to php arrays

In PHP many things can be done several different ways. Picking which way to do something may be a matter of personal taste or habit. Sometimes however, things may be much clearer for the next developer, if you choose one way over another.

A very simple example of this, is adding a new item to an array. Often I come across this construct:

$valuepairs[] = 'Some value'

It’s valid and compact syntax, but in terms of clarity, I’d prefer this construct anytime:

array_push($valuepairs, 'some value');

Mysql: display row count for all tables in a database

When playing the role of the DBA, it’s often useful to get a quick listing of how many rows each table in a database contains. The syntax for this is pretty simple in Mysql:

SELECT TABLE_NAME, table_rows 
WHERE TABLE_SCHEMA = '***database name***';

Replace ***database name*** with the actual database name in the SQL above.

Notice that when using innodb tables, it’s only a rough estimate.

Which packages are installed (on Ubuntu)

If you’re using a Debian based Linux Desktop such as Ubuntu, Linux Mint – or Debian itself naturally – you can easily create a simple text file of all packages installed on the machine.

I have a habit of removing a lot of the “extras” (junk) which comes with the basic install – not religiously, but just to keep it reasonably tidy and not have too much stuff eating up the harddisk, requiring updates (without providing any value).

Creating a list of the currently installed packages is quite simple – just open a terminal and enter:

dkpg --get-selections

(you will probably want to redirect the output into a file – or at least pipe it through more).