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: howto, MySQL, PHP, stored functions
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: Debian, howto, memory stick, Ubuntu
Posted by Hans-Henry Jakobsen
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: iptables, Ubuntu, ubuntu jaunty
Posted by Hans-Henry Jakobsen
