msgbartop
A cronological documentation test project, nothing serious, really!
msgbarbottom

12 May 2010 Howto remove orphan packages in Ubuntu

There is usually some orphan packages left on the system after upgrading Ubuntu from one major version to another. These packages can cause strange errors like Ubuntu bug #151045. The solution to this problem is a package named deborphan. deborphan removes orphan packages that are left on your Ubuntu installation and you should run this program after any major Ubuntu upgrade.

Install and run deborphan

# sudo aptitude install deborphan
# sudo deborphan

Result

liblzo1
libdb4.3
libsasl2

These files were listed on one of my Ubuntu 8.04 installations. This installation had been upgraded from a previous LTS version.

All we have to do now is to remove those orphaned packages

# sudo deborphan | xargs sudo aptitude purge -y

This howto has not been tested on a Debian distribution, but I guess it will work there also.

Tags: , , , , ,

Posted by Hans-Henry Jakobsen

02 Nov 2009 Howto backup mysql stored functions and stored procedures

This is how you can backup you MySQL database(s) and stored procedures

# mysqldump --routines <dbname>

Or you can backup only the stored procedures

# mysqldump --no-create-db --no-create-info --no-data --routines <dbname>

Tags: , , ,

Posted by Hans-Henry Jakobsen

23 Oct 2009 mysql alternative to PHP substr_count function

The substr_count function in PHP counts the number of substring occurrences. This post describes how to create a mysql stored function to behave just like PHP’s substr_count function.

This function can be created from your mysql console

delimiter ||
DROP FUNCTION IF EXISTS substrCount||
CREATE FUNCTION substrCount(s VARCHAR(255), ss VARCHAR(255)) RETURNS TINYINT(3) UNSIGNED LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA
BEGIN
DECLARE count TINYINT(3) UNSIGNED;
DECLARE offset TINYINT(3) UNSIGNED;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s = NULL;

SET count = 0;
SET offset = 1;

REPEAT
IF NOT ISNULL(s) AND offset > 0 THEN
SET offset = LOCATE(ss, s, offset);
IF offset > 0 THEN
SET count = count + 1;
SET offset = offset + 1;
END IF;
END IF;
UNTIL ISNULL(s) OR offset = 0 END REPEAT;

RETURN count;
END;

||

delimiter ;

Usage

Example 1

SELECT substrCount('/this/is/a/path', '/') `count`;

`count` would return 4 in this case. Can be used in such cases where you might want to find the “depth” of a path, or for many other uses.
This function is great to count the content of mysql ENUM and SET field data types.

Example 2

SELECT substrcount(
                `tablename` , ','
        ) as tablename
        FROM `tablename`
        where substrcount(
                `tablename` , ','
        ); 

The content of table named tablename is a comma separated list generated from mysql ENUM datatype

2000/2001,2001/2002,2002/2003,2003/2004,2004/2005,2005/2006,2006/2007,2007/2008,2008/2009,2009/2010

In Example 2 the result from this query would be 9, telling us that there are 9 commas in this tablerow.

Source: Posted by Andrew Hanna on August 24 2006 8:04pm

Tags: , , ,

Posted by Hans-Henry Jakobsen

07 Oct 2009 Create a Debian Lenny installation USB stick

This is a short post that describes how to prepare your USB memory stick for a base installation of Debian Lenny. This will most likely also work on Debian based distributions like Ubuntu.

Download the latest Debian boot.img.gz file

# wget http://people.debian.org/~joeyh/d-i/images/daily/hd-media/boot.img.gz

Download the latest Debian netinst ISO image

# wget http://cdimage.debian.org/cdimage/daily-builds/daily/arch-latest/i386/iso-cd/debian-testing-i386-netinst.iso

Connect the USB stick in the computer and verify that the drive is recognized in /var/log/messages.

It is now time to write the downloaded files onto your USB stick.

First write the boot information to the stick

# zcat boot.img.gz > /dev/sdb

I am assuming that /dev/sdb is the memory stick. Always check that you are writing to the right device!

Mount the USB stick and copy the installation files from the ISO image

# mount /dev/sdb /media/memstick
# cp debian-testing-i386-netinst.iso /media/memstick

The USB stick is now ready to be used as a boot media just like a CDROM.

Edit:
You can also use UnetBootin, a graphical (GUI) thats lets you choose distributions etc and create a bootable USB stick.

Tags: , , ,

Posted by Hans-Henry Jakobsen

06 Sep 2009 Howto combine two columns into one in mysql

This is how you can combine (or concatenate or merge) data from two table columns into one column using a mysql SQL query.

SELECT CONCAT(firstname,lastname) AS 'name' FROM tablename

The result would be “firstname lastname”

If you would like to insert this data into a new column

INSERT INTO tablename (full_name) SELECT CONCAT(firstname,lastname) AS 'name' FROM tablename

Tags: , ,

Posted by Hans-Henry Jakobsen