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

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

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

05 Oct 2009 iptables connection tracking table full

I’ve recently experienced that my workstation (Ubuntu Jaunty Jackalope, 9.04, x86_64) “hangs” periodically when my internet browser, Mozilla Firefox, has a lot of active tabs.
In my case I had > 100 active tabs in more than 20 windows. I know I should have closed some of them but that is not an option right now.

The problem reveiled itself doing a dmesg on my workstation

# dmesg

[1737157.057528] nf_conntrack: table full, dropping packet.
[1737157.160357] nf_conntrack: table full, dropping packet.
[1737157.260534] nf_conntrack: table full, dropping packet.
[1737157.361837] nf_conntrack: table full, dropping packet.
[1737157.462305] nf_conntrack: table full, dropping packet.
[1737157.564270] nf_conntrack: table full, dropping packet.
[1737157.666836] nf_conntrack: table full, dropping packet.
[1737157.767348] nf_conntrack: table full, dropping packet.
[1737157.868338] nf_conntrack: table full, dropping packet.
[1737157.969828] nf_conntrack: table full, dropping packet.
[1737162.214064] __ratelimit: 53 callbacks suppressed

This bahaviour looks like Denial-Of-Service and is caused by a full iptables connection_table.

The default size of the iptables connection tracing table is

# cat /proc/sys/net/ipv4/netfilter/ip_conntrack_max
65536

The solution I went for was to double the size of connection table to 131072 and restarted firefox.

# echo "131072" > /proc/sys/net/ipv4/netfilter/ip_conntrack_max

This value is a 32-bit integer so the table size can be quite large and you will need much more RAM before you can use this size.

To make this solution permanent I added the following line to /etc/sysctl.conf

net.ipv4.netfilter.ip_conntrack_max = 131072

You can test this by executing the following command

# sysctl -p
131072

This command loads the /etc/sysctl.conf settings.

Tags: , ,

Posted by