Posts Tagged ‘count’

How to calculate connections from IP address with shell script and log to Zabbix graphic

Thursday, March 11th, 2021

We had to test the number of connections incoming IP sorted by its TCP / IP connection state.

For example:

TIME_WAIT, ESTABLISHED, LISTEN etc.


The reason behind is sometimes the IP address '192.168.0.1' does create more than 200 connections, a Cisco firewall gets triggered and the connection for that IP is filtered out. To be able to know in advance that this problem is upcoming. a Small userparameter script is set on the Linux servers, that does print out all connections from IP by its STATES sorted out.

 

The script is calc_total_ip_match_zabbix.sh is below:

#!/bin/bash
#  check ESTIMATED / FIN_WAIT etc. netstat output for IPs and calculate total
# UserParameter=count.connections,(/usr/local/bin/calc_total_ip_match_zabbix.sh)
CHECK_IP='192.168.0.1';
f=0; 

 

for i in $(netstat -nat | grep "$CHECK_IP" | awk '{print $6}' | sort | uniq -c | sort -n); do

echo -n "$i ";
f=$((f+i));
done;
echo
echo "Total: $f"

 

root@pcfreak:/bashscripts# ./calc_total_ip_match_zabbix.sh 
1 TIME_WAIT 2 ESTABLISHED 3 LISTEN 

Total: 6

 

root@pcfreak:/bashscripts# ./calc_total_ip_match_zabbix.sh 
2 ESTABLISHED 3 LISTEN 
Total: 5


images/zabbix-webgui-connection-check1

To make process with Zabbix it is necessery to have an Item created and a Depedent Item.

 

webguiconnection-check1

webguiconnection-check1
 

webgui-connection-check2-item

images/webguiconnection-check1

Finally create a trigger to trigger alarm if you have more than or eqaul to 100 Total overall connections.


images/zabbix-webgui-connection-check-trigger

The Zabbix userparameter script should be as this:

[root@host: ~]# cat /etc/zabbix/zabbix_agentd.d/userparameter_webgui_conn.conf
UserParameter=count.connections,(/usr/local/bin/webgui_conn_track.sh)

 

Some collleagues suggested more efficient shell script solution for suming the overall number of connections, below is less time consuming version of script, that can be used for the calculation.
 

#!/bin/bash -x
# show FIN_WAIT2 / ESTIMATED etc. and calcuate total
count=$(netstat -n | grep "192.168.0.1" | awk ' { print $6 } ' | sort -n | uniq -c | sort -nr)
total=$((${count// /+}))
echo "$count"
echo "Total:" "$total"

      2 ESTABLISHED
      1 TIME_WAIT
Total: 3

 


Below is the graph built with Zabbix showing all the fluctuations from connections from monitored IP. ebgui-check_ip_graph

 

Check the count and monitor of established / time_wait TCP, UDP connections on Linux and Windows with netstat command

Wednesday, February 6th, 2019

netstat-windows-linux-commands-to-better-understand-your-server-type-of-networrk-tcp-udp-connections

For me as a GNU / Linux sysadmin it is intuitive to check on a server the number of established connections / connections in time_wait state and so on .

I will not explain why this is necessery as every system administrator out there who had a performance or network issues due to server / applications connection overload or have been a target of Denial of Service (DoS)
or Distributed Denial of Service attacks (DDoS)  
is well aware that a number of connections in different states such as SYN_ACK /  TIME_WAIT or ESTABLISHED state could be very nasty thing and could cause a productive application or Infrastructure service to be downed for some time causing from thousands of Euros to even millions to some bussinesses as well as some amount of data loss …

To prevent this therefore sysadmins should always take a look periodically on the Connection states on the adminned server (and in this number I say not only sys admins but DevOps guys who are deploying micro-services for a customer in the Cloud – yes I believe Richard Stallman is right here they're clouding your minds :).

Even though cloud services could provide a very high amount of Hardware (CPU / Memory / Storage) resources, often for custom applications migrating the application in the Cloud does not solve it's design faults or even problems on a purely classical system administration level.

 

1. Get a statistic for FIN_WAIT1, FOREIGN, SYNC_RECV, LAST_ACK, TIME_WAIT, LISTEN and ESTABLISHED  Connections on GNU / Linux

 

On GNU / Linux and other Linux like UNIXes the way to do it is to grep out the TCP / UDP connection type you need via netstat a very useful cmd in that case is:

 

root@pcfreak:~# netstat -nat | awk '{print $6}' | sort | uniq -c | sort -n
      1 established)
      1 FIN_WAIT1
      1 Foreign
      1 SYN_RECV
      3 LAST_ACK
      4 FIN_WAIT2
      8 TIME_WAIT
     45 LISTEN
    147 ESTABLISHED

 

2. Netstat 1 liner to Get only established and time_wait connections state 

 

Other ways to check only TCP ESTABLISHED connections on Linux I use frequently are:

 

root@pcfreak:~# netstat -etna|grep -i establi|wc -l
145

 

netstat-connection-types-statistics-linux-established-time-wait-check-count

Or to get whole list of connections including the ones who are about to be esatablished in FIN_WAIT2, TIME_WAIT, SYN_RECV state:

 

root@pcfreak:~# netstat -tupen |wc -l
164

 

3. Other Linux useful one liner commands to track your connection types
 

netstat -n -p | grep SYN_REC | sort -u

List out the all IP addresses involved instead of just count.

netstat -n -p | grep SYN_REC | awk '{print $5}' | awk -F: '{print $1}'

 

List all the unique IP addresses of the node that are sending SYN_REC connection status.

netstat -ntu | awk '{print $5}' | cut -d: -f1 | sort | uniq -c | sort -n

 

Use netstat command to calculate and count the number of connections each IP address makes to the server.

netstat -anp |grep 'tcp\|udp' | awk '{print $5}' | cut -d: -f1 | sort | uniq -c | sort -n

 

List count of number of connections the IPs are connected to the server using TCP or UDP protocol.

netstat -ntu | grep ESTAB | awk '{print $5}' | cut -d: -f1 | sort | uniq -c | sort -nr

 

Check on ESTABLISHED connections instead of all connections, and displays the connections count for each IP.

 

netstat -plan|grep :80|awk {'print $5'}|cut -d: -f 1|sort|uniq -c|sort -nk 1

 

Show and list IP address and its connection count that connect to port 80 on the server. Port 80 is used mainly by HTTP web page request.

Examples are taken from this nice blog post

 

4. Check the count of esatblished connections on M$ Windows

 

As I'm forced to optimize a couple of Microsoft Windows DNS servers which are really slow to resolve the
The logical question for me was how the Established and TIME_WAIT state connections then could be checked on Windows OS, after a quick investigation online I've come up with this:

 

C:\Users\admin> netstat -nao | find /i "estab" /c
78

 

netstat-check-number-of-established-ports-connections-windows
 

 

C:\Users\admin> netsatt -nao | find /i "time_wait" /c
333

 

 

If you're used to Linux watch command, then to do same on Windows OS (e.g. check the output of netstat) command every second
and print output use:

 

netstat –an 1 | find “3334”

 

Below commands will show stats for services listening on TCP port 3334

To find out which process on system sends packets to remote destination:

 

netstat –ano 1 | find “Dest_IP_Addr”

 

The -o parameter outputs the process ID (PID) responsible for the connection.
then if you need further you can find the respective process name with tasklist< cmd.
Another handy Windows netstat option is -b which will show EXE file running as long as
the related used DLL Libraries which use TCP / UDP .

Other useful netsatat Win example is to grep for a port and show all established connections for it with:

 

netstat –an 1 | find “8080” | find “ESTABLISHED”

 

5. Closure


Hopefully this article will give you some idea on what is eating your bandwidth connections or overloading your GNU / Linux – Windows systems. And will point you to the next in line logical thing to do optimization / tuning
settings to be made on your system for example if Linux with sysctl – see my previous relater article here

I'll be intested to hear from sysadm colleagoes for other useful ways to track connections perhaps with something like ss tool (a utility to investigate sockets).
Also any optimization hints that would cause servers less downtime and improve network / performance thouroughput is mostly welcome.

 

No space left on device with free disk space / Why no space left on device while there is plenty of disk space on drive – Running out of Inodes

Tuesday, November 17th, 2015

no_space_left-on-device-while-there-is-disk-space-running-out-of-file-inodes-unix_linux_file_system_diagram.gif

 

On one of the servers, I'm administrating the websites started showing some Mysql database table corrup errors like:
 

 

Table './database_name/site_news_list_com' is marked as crashed and last (automatic?) repair failed

The server is using Oracle MySQL server community stable edition on Debian GNU / Linux 6.0, so I first thought during work the server crashed either due to some bug issue in MySQL or it crashed due to some PHP cron job that did something messy. Thus to solve the crashed tables, tried using mysqlcheck tool which helped pretty fine, at many times whether there were database / table corruptions. I've run the following set of mysqlcheck commands with root (superuser) in a bash shell after logging in through SSH:

:

server:~# /usr/bin/mysqlcheck –defaults-extra-file=/etc/mysql/debian.cnf \–check –all-databases -u root -p`grep -i password /root/.my.cnf |sed -e 's#password=##g'`>> /var/log/cronwork.log
server:~# /usr/bin/mysqlcheck –defaults-extra-file=/etc/mysql/debian.cnf –analyze –all-databases -u root -p`grep -i password /root/.my.cnf |sed -e 's#password=##g'`>> /var/log/cronwork.log
server:~# /usr/bin/mysqlcheck –defaults-extra-file=/etc/mysql/debian.cnf \–auto-repair –optimize –all-databases -u root -p`grep -i password /root/.my.cnf |sed -e 's#password=##g'`>> /var/log/cronwork.log
server:~# /usr/bin/mysqlcheck –defaults-extra-file=/etc/mysql/debian.cnf \–optimize –all-databases -u root -p`grep -i password /root/.my.cnf |sed -e 's#password=##g'`>> /var/log/cronwork.log


In order for above commands to work, I've created the /root/.my.cnf containing my root (mysql CLI) mysql username and password, e.g. file has content like below:

 

[client]
user=root
password=MySecretPassword8821238

 

Btw a good note here is its generally a good idea (if you want to have consistent mysql databases) to automatically execute via a cron job 2 times a month, I've in root cronjob the following:

 

crontab -u root -l |grep -i mysqlcheck
04 06 5,10,15,20,25,1 * * /usr/bin/mysqlcheck –defaults-extra-file=/etc/mysql/debian.cnf \–check –all-databases –silent -u root -p`grep -i password /root/.my.cnf |sed -e 's#password=##g'`>> /var/log/cronwork.log 07 06 5,10,15,20,25,1 * * /usr/bin/mysqlcheck –defaults-extra-file=/etc/mysql/debian.cnf –analyze –all-databases –silent -u root -p`grep -i password /root/.my.cnf |sed -e 's#password=##g'`>> /var/log/cronwork.log 12 06 5,10,15,20,25,1 * * /usr/bin/mysqlcheck –defaults-extra-file=/etc/mysql/debian.cnf \–auto-repair –optimize –all-databases –silent -u root -p`grep -i password /root/.my.cnf |sed -e 's#password=##g'`>> /var/log/cronwork.log 17 06 5,10,15,20,25,1 * * /usr/bin/mysqlcheck –defaults-extra-file=/etc/mysql/debian.cnf \–optimize –all-databases –silent -u root -p`grep -i password /root/.my.cnf |sed -e 's#password=##g'`>> /var/log/cronwork.log


Strangely I got a lot of errors that some .MYI / .MYD .frm temp files, necessery for the mysql tables recovery can't be written inside /home/mysql/database_name

That was pretty weird and I thought there might be some issues with permissions, causing the inability to write, due to some bug or something so I went straight and checked /home/mysql/database_name permissions, e.g.::

 

server:/home/mysql/database_name# ls -ld soccerfame
drwx—— 2 mysql mysql 36864 Nov 17 12:00 soccerfame
server:/home/mysql/database_name# ls -al1|head -n 10
total 1979012
drwx—— 2 mysql mysql 36864 Nov 17 12:00 .
drwx—— 36 mysql mysql 4096 Nov 17 11:12 ..
-rw-rw—- 1 mysql mysql 8712 Nov 17 10:26 1_campaigns_diez.frm
-rw-rw—- 1 mysql mysql 14672 Jul 8 18:57 1_campaigns_diez.MYD
-rw-rw—- 1 mysql mysql 1024 Nov 17 11:38 1_campaigns_diez.MYI
-rw-rw—- 1 mysql mysql 8938 Nov 17 10:26 1_campaigns.frm
-rw-rw—- 1 mysql mysql 8738 Nov 17 10:26 1_campaigns_logs.frm
-rw-rw—- 1 mysql mysql 883404 Nov 16 22:01 1_campaigns_logs.MYD
-rw-rw—- 1 mysql mysql 330752 Nov 17 11:38 1_campaigns_logs.MYI


As seen from above output, all was perfect with permissions, so it should have been something else, so I decided to try to create a random file with touch command inside /home/mysql/database_name directory:

 

touch /home/mysql/database_name/somefile-to-test-writtability.txt touch: cannot touch ‘/scr1/data/somefile-to-test-writtability.txt‘: No space left on device


Then logically I thought the /home/mysql/ mounted ext4 partition got filled, because of crashed SQL database or a bug thus, checked with disk free command df whether there is enough space on server:

server:~# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/md1 20G 7.6G 11G 42% /
udev 10M 0 10M 0% /dev
tmpfs 13G 1.3G 12G 10% /run
tmpfs 32G 0 32G 0% /dev/shm
tmpfs 5.0M 0 5.0M 0% /run/lock
tmpfs 32G 0 32G 0% /sys/fs/cgroup
/dev/md2 256G 134G 110G 55% /home

Well that's weird? Obviously only 55% of available disk space is used and available 134G which was more than enough so I got totally puzzled why, files can't be written.

Then very logically, I thought it might be that /home directory has remounted as read only, because the SSD memory disk on server is failing and checked for errors in dmesg, i.e.:

 

server:~# dmesg|grep -i error


Also checked how exactly was partition mounted, to check whether it is (RO) read-only:

 

server:~# mount -l|grep -i /home
/dev/md2 on /home type ext4 (rw,relatime,discard,data=ordered)


Now everything become even more weirder, as obviously the disk continued to be claiming no space left on device, while in reality there was plenty of disk space.

Then after running a quick research on the internet for the no space left on device with free disk space, I've come across this great superuser.com thread which let me realize the partition run out of inodes and that's why no new file inodes could be assigned and therefore, the linux kernel is refusing to write the file on ext4 partition.

For those who haven't heard of Linux Partition Inodes here is link to Wikipedia and a quick quote:

 

In a Unix-style file system, the inode is a data structure used to represent a filesystem object, which can be one of various things including a file or a directory. Each inode stores the attributes and disk block location(s) of the filesystem object's data.[1] Filesystem object attributes may include manipulation metadata (e.g. change,[2] access, modify time), as well as owner and permission data (e.g. group-id, user-id, permissions).[3]
Directories are lists of names assigned to inodes. The directory contains an entry for itself, its parent, and each of its children.


Once I understood it is the inodes, I checked how many of them are occupied with cmd:

 

server:~# df -i /home
Filesystem Inodes IUsed IFree IUse% Mounted on
/dev/md2 17006592 17006592 0 100% /home


You see, there were 0 (zero) free file inodes on server and that was the reason for no space left on device while there was actually free disk space

To clean up (free) some inodes on partition, first thing I did is to delete all old logs which were inside /home and files I positively know not to be necessery, then to find which directories allocating most innodes used:

 

server:~# find . -xdev -type f | cut -d "/" -f 2 | sort | uniq -c | sort -n


If you're on a regular old fashined IDE Hard Drive and not SSD or you have too much files inside this command will take really long …:

Therefore a better solution might be to frist:

a) Try to find root folders with large inodes count:

for i in /home/*; do echo $i; find $i |wc -l; done
Try to find specific folders:


You should get output like:

 

/home/new_website
606692
/home/common
73
/home/pcfreak
5661
/home/hipo
33
/home/blog
13570
/home/log
123
/home/lost+found
1

b) Then once you know the directory allocating most inodes, run the command again to see the sub-directories with most files (eating) partition innodes:

 

for i in /home/webservice/*; do echo $i; find $i |wc -l; done

 

One usual large folder which could free you some nodes is the linux source headers, but in my case it was simply a lot of tiny old logs being logged on the system for few years in the past without cleaning:

After deleting the log dirs and cache folder in my case /home/new_website/{log,cache}:

server:~# rm -rf /home/new_website/log/*
server:~# rm -rf /home/new_website/cache/*

 

 

a) Then, stopping Apache webserver to check prevent Apache to use MySQl databases while running database repair and restaring MySQL:
 

server:~# /etc/init.d/apache2 stop Restarting MySQL server
..
server:~# /etc/init.d/mysql restart
..


b) And re-issuing MySQL Check / Repair / Optimize database commands:
 

 

mysqlcheck –defaults-extra-file=/etc/mysql/debian.cnf \–check –all-databases -u root -p`grep -i password /root/.my.cnf |sed -e 's#password=##g'`>> /var/log/cronwork.log

mysqlcheck –defaults-extra-file=/etc/mysql/debian.cnf –analyze –all-databases -u root -p`grep -i password /root/.my.cnf |sed -e 's#password=##g'`>> /var/log/cronwork.log

mysqlcheck –defaults-extra-file=/etc/mysql/debian.cnf \–auto-repair –optimize –all-databases -u root -p`grep -i password /root/.my.cnf |sed -e 's#password=##g'`>> /var/log/cronwork.log

mysqlcheck –defaults-extra-file=/etc/mysql/debian.cnf \–optimize –all-databases -u root -p`grep -i password /root/.my.cnf |sed -e 's#password=##g'`>> /var/log/cronwork.log

c) And finally starting the Apache Webserver again:
 

server:~# /etc/init.d/apache2 start


Some innodse got freed up:
 

server:~# df -i /home Filesystem Inodes IUsed IFree IUse% Mounted on
/dev/md2 17006592 16797196 209396 99% /home


And hooray by God's Grace and with help of prayers of The most Holy Theotokos (Virgin) Mary, websites started again !

Resolving “nf_conntrack: table full, dropping packet.” flood message in dmesg Linux kernel log

Wednesday, March 28th, 2012

nf_conntrack_table_full_dropping_packet
On many busy servers, you might encounter in /var/log/syslog or dmesg kernel log messages like

nf_conntrack: table full, dropping packet

to appear repeatingly:

[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.
[1737157.969928] nf_conntrack: table full, dropping packet
[1737157.989828] nf_conntrack: table full, dropping packet
[1737162.214084] __ratelimit: 83 callbacks suppressed

There are two type of servers, I've encountered this message on:

1. Xen OpenVZ / VPS (Virtual Private Servers)
2. ISPs – Internet Providers with heavy traffic NAT network routers
 

I. What is the meaning of nf_conntrack: table full dropping packet error message

In short, this message is received because the nf_conntrack kernel maximum number assigned value gets reached.
The common reason for that is a heavy traffic passing by the server or very often a DoS or DDoS (Distributed Denial of Service) attack. Sometimes encountering the err is a result of a bad server planning (incorrect data about expected traffic load by a company/companeis) or simply a sys admin error…

– Checking the current maximum nf_conntrack value assigned on host:

linux:~# cat /proc/sys/net/ipv4/netfilter/ip_conntrack_max
65536

– Alternative way to check the current kernel values for nf_conntrack is through:

linux:~# /sbin/sysctl -a|grep -i nf_conntrack_max
error: permission denied on key 'net.ipv4.route.flush'
net.netfilter.nf_conntrack_max = 65536
error: permission denied on key 'net.ipv6.route.flush'
net.nf_conntrack_max = 65536

– Check the current sysctl nf_conntrack active connections

To check present connection tracking opened on a system:

:

linux:~# /sbin/sysctl net.netfilter.nf_conntrack_count
net.netfilter.nf_conntrack_count = 12742

The shown connections are assigned dynamicly on each new succesful TCP / IP NAT-ted connection. Btw, on a systems that work normally without the dmesg log being flooded with the message, the output of lsmod is:

linux:~# /sbin/lsmod | egrep 'ip_tables|conntrack'
ip_tables 9899 1 iptable_filter
x_tables 14175 1 ip_tables

On servers which are encountering nf_conntrack: table full, dropping packet error, you can see, when issuing lsmod, extra modules related to nf_conntrack are shown as loaded:

linux:~# /sbin/lsmod | egrep 'ip_tables|conntrack'
nf_conntrack_ipv4 10346 3 iptable_nat,nf_nat
nf_conntrack 60975 4 ipt_MASQUERADE,iptable_nat,nf_nat,nf_conntrack_ipv4
nf_defrag_ipv4 1073 1 nf_conntrack_ipv4
ip_tables 9899 2 iptable_nat,iptable_filter
x_tables 14175 3 ipt_MASQUERADE,iptable_nat,ip_tables

 

II. Remove completely nf_conntrack support if it is not really necessery

It is a good practice to limit or try to omit completely use of any iptables NAT rules to prevent yourself from ending with flooding your kernel log with the messages and respectively stop your system from dropping connections.

Another option is to completely remove any modules related to nf_conntrack, iptables_nat and nf_nat.
To remove nf_conntrack support from the Linux kernel, if for instance the system is not used for Network Address Translation use:

/sbin/rmmod iptable_nat
/sbin/rmmod ipt_MASQUERADE
/sbin/rmmod rmmod nf_nat
/sbin/rmmod rmmod nf_conntrack_ipv4
/sbin/rmmod nf_conntrack
/sbin/rmmod nf_defrag_ipv4

Once the modules are removed, be sure to not use iptables -t nat .. rules. Even attempt to list, if there are any NAT related rules with iptables -t nat -L -n will force the kernel to load the nf_conntrack modules again.

Btw nf_conntrack: table full, dropping packet. message is observable across all GNU / Linux distributions, so this is not some kind of local distribution bug or Linux kernel (distro) customization.
 

III. Fixing the nf_conntrack … dropping packets error

– One temporary, fix if you need to keep your iptables NAT rules is:

linux:~# sysctl -w net.netfilter.nf_conntrack_max=131072

I say temporary, because raising the nf_conntrack_max doesn't guarantee, things will get smoothly from now on.
However on many not so heavily traffic loaded servers just raising the net.netfilter.nf_conntrack_max=131072 to a high enough value will be enough to resolve the hassle.

– Increasing the size of nf_conntrack hash-table

The Hash table hashsize value, which stores lists of conntrack-entries should be increased propertionally, whenever net.netfilter.nf_conntrack_max is raised.

linux:~# echo 32768 > /sys/module/nf_conntrack/parameters/hashsize
The rule to calculate the right value to set is:
hashsize = nf_conntrack_max / 4

– To permanently store the made changes ;a) put into /etc/sysctl.conf:

linux:~# echo 'net.netfilter.nf_conntrack_count = 131072' >> /etc/sysctl.conf
linux:~# /sbin/sysct -p

b) put in /etc/rc.local (before the exit 0 line):

echo 32768 > /sys/module/nf_conntrack/parameters/hashsize

Note: Be careful with this variable, according to my experience raising it to too high value (especially on XEN patched kernels) could freeze the system.
Also raising the value to a too high number can freeze a regular Linux server running on old hardware.

– For the diagnosis of nf_conntrack stuff there is ;

/proc/sys/net/netfilter kernel memory stored directory. There you can find some values dynamically stored which gives info concerning nf_conntrack operations in "real time":

linux:~# cd /proc/sys/net/netfilter
linux:/proc/sys/net/netfilter# ls -al nf_log/

total 0
dr-xr-xr-x 0 root root 0 Mar 23 23:02 ./
dr-xr-xr-x 0 root root 0 Mar 23 23:02 ../
-rw-r--r-- 1 root root 0 Mar 23 23:02 0
-rw-r--r-- 1 root root 0 Mar 23 23:02 1
-rw-r--r-- 1 root root 0 Mar 23 23:02 10
-rw-r--r-- 1 root root 0 Mar 23 23:02 11
-rw-r--r-- 1 root root 0 Mar 23 23:02 12
-rw-r--r-- 1 root root 0 Mar 23 23:02 2
-rw-r--r-- 1 root root 0 Mar 23 23:02 3
-rw-r--r-- 1 root root 0 Mar 23 23:02 4
-rw-r--r-- 1 root root 0 Mar 23 23:02 5
-rw-r--r-- 1 root root 0 Mar 23 23:02 6
-rw-r--r-- 1 root root 0 Mar 23 23:02 7
-rw-r--r-- 1 root root 0 Mar 23 23:02 8
-rw-r--r-- 1 root root 0 Mar 23 23:02 9

 

IV. Decreasing other nf_conntrack NAT time-out values to prevent server against DoS attacks

Generally, the default value for nf_conntrack_* time-outs are (unnecessery) large.
Therefore, for large flows of traffic even if you increase nf_conntrack_max, still shorty you can get a nf_conntrack overflow table resulting in dropping server connections. To make this not happen, check and decrease the other nf_conntrack timeout connection tracking values:

linux:~# sysctl -a | grep conntrack | grep timeout
net.netfilter.nf_conntrack_generic_timeout = 600
net.netfilter.nf_conntrack_tcp_timeout_syn_sent = 120
net.netfilter.nf_conntrack_tcp_timeout_syn_recv = 60
net.netfilter.nf_conntrack_tcp_timeout_established = 432000
net.netfilter.nf_conntrack_tcp_timeout_fin_wait = 120
net.netfilter.nf_conntrack_tcp_timeout_close_wait = 60
net.netfilter.nf_conntrack_tcp_timeout_last_ack = 30
net.netfilter.nf_conntrack_tcp_timeout_time_wait = 120
net.netfilter.nf_conntrack_tcp_timeout_close = 10
net.netfilter.nf_conntrack_tcp_timeout_max_retrans = 300
net.netfilter.nf_conntrack_tcp_timeout_unacknowledged = 300
net.netfilter.nf_conntrack_udp_timeout = 30
net.netfilter.nf_conntrack_udp_timeout_stream = 180
net.netfilter.nf_conntrack_icmp_timeout = 30
net.netfilter.nf_conntrack_events_retry_timeout = 15
net.ipv4.netfilter.ip_conntrack_generic_timeout = 600
net.ipv4.netfilter.ip_conntrack_tcp_timeout_syn_sent = 120
net.ipv4.netfilter.ip_conntrack_tcp_timeout_syn_sent2 = 120
net.ipv4.netfilter.ip_conntrack_tcp_timeout_syn_recv = 60
net.ipv4.netfilter.ip_conntrack_tcp_timeout_established = 432000
net.ipv4.netfilter.ip_conntrack_tcp_timeout_fin_wait = 120
net.ipv4.netfilter.ip_conntrack_tcp_timeout_close_wait = 60
net.ipv4.netfilter.ip_conntrack_tcp_timeout_last_ack = 30
net.ipv4.netfilter.ip_conntrack_tcp_timeout_time_wait = 120
net.ipv4.netfilter.ip_conntrack_tcp_timeout_close = 10
net.ipv4.netfilter.ip_conntrack_tcp_timeout_max_retrans = 300
net.ipv4.netfilter.ip_conntrack_udp_timeout = 30
net.ipv4.netfilter.ip_conntrack_udp_timeout_stream = 180
net.ipv4.netfilter.ip_conntrack_icmp_timeout = 30

All the timeouts are in seconds. net.netfilter.nf_conntrack_generic_timeout as you see is quite high – 600 secs = (10 minutes).
This kind of value means any NAT-ted connection not responding can stay hanging for 10 minutes!

The value net.netfilter.nf_conntrack_tcp_timeout_established = 432000 is quite high too (5 days!)
If this values, are not lowered the server will be an easy target for anyone who would like to flood it with excessive connections, once this happens the server will quick reach even the raised up value for net.nf_conntrack_max and the initial connection dropping will re-occur again …

With all said, to prevent the server from malicious users, situated behind the NAT plaguing you with Denial of Service attacks:

Lower net.ipv4.netfilter.ip_conntrack_generic_timeout to 60 – 120 seconds and net.ipv4.netfilter.ip_conntrack_tcp_timeout_established to stmh. like 54000

linux:~# sysctl -w net.ipv4.netfilter.ip_conntrack_generic_timeout = 120
linux:~# sysctl -w net.ipv4.netfilter.ip_conntrack_tcp_timeout_established = 54000

This timeout should work fine on the router without creating interruptions for regular NAT users. After changing the values and monitoring for at least few days make the changes permanent by adding them to /etc/sysctl.conf

linux:~# echo 'net.ipv4.netfilter.ip_conntrack_generic_timeout = 120' >> /etc/sysctl.conf
linux:~# echo 'net.ipv4.netfilter.ip_conntrack_tcp_timeout_established = 54000' >> /etc/sysctl.conf

Howto install XCache Debian on GNU / Linux to accelerate Apache Webserver – XCache Best alternative to outdated PHP cacher EAccelerator

Thursday, February 26th, 2015

xcache_install-and-enable-best-alternative-php-cacher-to-eaccelerator-logo
I was using Eaccelerator until recently on all Apache / PHP / MySQL  (LAMP) web-servers as a caching engine (Webserver accelerator) across all Debian GNU / Linux Lenny / Squeeze / Etch servers.
However recently, I've noticed in phpinfo output on some of the Debian hosts, that eaccelerator was loaded but showed:

 Caching Enabled false

eaccelerator_caching_enabled_false-phpinfo-screenshot-apache-debian-linux.

 

Our servers are quite busy serving about 50 000 to 100 000 requests and thus not having enabled caching puts a lot of extra load on the CPU and eats a lot of memory which were usually saved by eAccelerator.
Logically I tried fixing the issues following some Stackoverflow threads recommendations such as this one but didn't work I tried playing manually spending hours trying to make eaccelerator run again and as a final mean, I even tried to upgrade eaccelerator to newer version but noticed the latest available eaccelerator version 0.9.6 was 2.5 years old (from 03.09.2012). Thus while there is no new release, just make s so just to make sure I didn't break the module with (default Debian bundled distribution package which is also installed on the servers)  re-installed eAccelerator from source 

This didn't worked either and since I was totally pissed off by the worsened systems performance (CPU load increased with to 10-30%) per server, I looked for some alternatives I can use and in the mean time I learned a bit more about history of PHP Accelerators, I learned some interesting things such as that  ionCube (PHPA) was the  first PHP Accelerator Apache like module (encoding PHP code),  created in 2001, later it become inspirational for  birth to PHP-APC (Alternative PHP Cache) Apache module. 
There is also Zend Opcache PHP accelerator (available since PHP 5.5 onwards)  but since Zend OpCache caches well PHP Zend written PHP code and servers run PHP 5.4 + sites are not using Zend PHP Framewosk  this was an option.
Further investigation lead me to MMCache which is already too obsolete (latest release is from 2013), PHPExpress – PHP Encoder which  was said to run on Windows, Linux, FreeBSD, NetBSD, Mac OS X, and Solaris) but already looks dead as there were no new releases since January 2012) and finally Lighttpd's XCache.

To give you an idea on what exactly is the difference between Apache Webserver with PHP-APC Caching or other PHP Cacher enabled and the Standard way PHP Interprets PHP scripts below is a diagram:

php-apc-cache-how-php-caching-works-with-and-without-encoding-php-code-diagram

Obviously my short research shows that from all the available PHP Cache Encoder / Accelerators only ones that seemed to be recently updated (under active development) are APC and XCache.
I've already used PHP-APC earlier on some servers and was having having some random Apache Webservers crashes and weird empty pages with some PHP pages and besides that APC is known to give lower speed in PHP caching than Eaccelerator and XCache, leaving me with the only and logical choise to use XCACHE.

Here is how Xcache developers describe their opcacher:
 

XCache is a free, open source operation code cacher, it is designed to enhance the performance of PHP scripts execution on servers. It optimizes the performance by eliminating the compilation time of PHP code by caching the compiled version of code into the memory and this way the compiled version loads the PHP script directly from the memory. This will surety accelerate the page generation time by up to 5 times faster and also optimizes and increases many other aspects of php scripts and reduce website/server load.

 


Thanksfully XCache is shipped by default with all Debians (Etch /Lenny / Squeeze / Wheezy)  Linuces so to install it just run the standard apt cmd:
 

apt-get install –yes php5-xcache


Then to enable XCache all I had to do is edit /etc/php5/apache2/php.ini and place below code
 

debian-server:~# vim /etc/php5/apache2/php.ini

 

[xcache-common]
;; install as zend extension (recommended), normally "$extension_dir/xcache.so"
;;zend_extension = /usr/lib/php5/20100525/xcache.so

 

[xcache.admin]
xcache.admin.enable_auth = On
; Configure this to use admin pages
; xcache.admin.user = "mOo"
; xcache.admin.pass = md5($your_password)
; xcache.admin.pass = ""

[xcache]
; ini only settings, all the values here is default unless explained

; select low level shm/allocator scheme implemenation
xcache.shm_scheme =        "mmap"
; to disable: xcache.size=0
; to enable : xcache.size=64M etc (any size > 0) and your system mmap allows
xcache.size  =                16M
; set to cpu count (cat /proc/cpuinfo |grep -c processor)
xcache.count =                 1
; just a hash hints, you can always store count(items) > slots
xcache.slots =                8K
; ttl of the cache item, 0=forever
xcache.ttl   =                 0
; interval of gc scanning expired items, 0=no scan, other values is in seconds
xcache.gc_interval =           0
; same as aboves but for variable cache

Note that Debian location which instructs xcache to load in Apache as a module is xcache.ini – e.g. /usr/share/php5/xcache/xcache.ini, so instead of placing above configuration right into php.ini you might prefer to place it in xcache.ini (though I personally prefer php.ini) because it is easier for me to later control how PHP behaves from single location.

To test whether XCache is enabled for Apache Webserver:

Create phpinfo.php somewhere in DocumentRoot (in my case this was /var/www/php_info.php)

debian-server:~# vim /var/www/php_info.php

 

<php?
phpinfo()
?>


When you access the php_info.php in browser you will get XCache loaded as in below screenshot:

 

xcache_loaded-in-php-apache-phpinfo-output-debian-gnu-linux-server

To Test whether Xcache is enabled also for PHP CLI (applications set to run as a crontab – cronjob) :
 

debian-server:~# php -v
PHP 5.4.37-1~dotdeb.0 (cli) (built: Feb  2 2015 05:03:00)
Copyright (c) 1997-2014 The PHP Group
Zend Engine v2.4.0, Copyright (c) 1998-2014 Zend Technologies
    with XCache v3.2.0, Copyright (c) 2005-2014, by mOo
    with XCache Cacher v3.2.0, Copyright (c) 2005-2014, by mOo

 

Once it is tested as successful install you might want to enable the XCache admin (which is disabled by default), to enable XCache Admin on Debian you need to generate new password for it first like so:

 

echo -n "xcache_rulez" | md5sum
acbf5ba4a44f03058aa0ad11e0a6b645

 


Then you need to add in /etc/php5/mods-available/xcache.ini

debian-server:~# vim /etc/php5/mods-available/xcache.ini
[xcache.admin]
xcache.admin.enable_auth = On
; Configure this to use admin pages
 xcache.admin.user = "admin"
; xcache.admin.pass = md5($your_password)
 xcache.admin.pass = "change_with_above_generated_password_here"

 

To enable admin and be able to access it in a browser (if you're using as a documentroot /var/www/ and docroot supports interpretting php scripts and (has AllowOverride All) enabled to also support htaccess authentication do:
 

debian-server:~# cd /var/www/
debian-server:~# ln -sf /usr/share/xcache/htdocs/ xcache

When you access http://your-url-address.com/xcache/ you should see in browser some statistics along with all configured xcache options:

xcacher-admin-on-debian-gnu-linux-in-chrome-browser-screenshot-enable-xcacher-admin-howto

If you have time you can play with the options and get some speed minor speed improvements. The overall increase in page opening XCache should give you is between 100% – 190% !

Enjoy 🙂

Monitoring MySQL server queries and debunning performance (slow query) issues with native MySQL commands and with mtop, mytop

Thursday, May 10th, 2012

If you're a Linux server administrator running MySQL server, you need to troubleshoot performance and bottleneck issues with the SQL database every now and then. In this article, I will pinpoint few methods to debug basic issues with MySQL database servers.

1. Troubleshooting MySQL database queries with native SQL commands

a)One way to debug errors and get general statistics is by logging in with mysql cli and check the mysql server status:

# mysql -u root -p
mysql> SHOW STATUS;
+-----------------------------------+------------+
| Variable_name | Value |
+-----------------------------------+------------+
| Aborted_clients | 1132 |
| Aborted_connects | 58 |
| Binlog_cache_disk_use | 185 |
| Binlog_cache_use | 2542 |
| Bytes_received | 115 |
.....
.....
| Com_xa_start | 0 |
| Compression | OFF |
| Connections | 150000 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 221 |
| Created_tmp_tables | 1 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
.....
.....
| Handler_write | 132 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 6204 |
| Innodb_pages_read | 8859 |
| Innodb_pages_written | 21931 |
.....
.....
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
| Table_locks_immediate | 4065218 |
| Table_locks_waited | 196 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 51 |
| Threads_connected | 1 |
| Threads_created | 52 |
| Threads_running | 1 |
| Uptime | 334856 |
+-----------------------------------+------------+
225 rows in set (0.00 sec)

SHOW STATUS; command gives plenty of useful info, however it is not showing the exact list of queries currently processed by the SQL server. Therefore sometimes it is exactly a stucked (slow queries) execution, you need to debug in order to fix a lagging SQL. One way to track this slow queries is via enabling mysql slow-query.log. Anyways enabling the slow-query requires a MySQL server restart and some critical productive database servers are not so easy to restart and the SQL slow queries have to be tracked "on the fly" so to say.
Therefore, to check the exact (slow) queries processed by the SQL server (without restarting it), do
 

mysql> SHOW processlist;
+——+——+—————+——+———+——+————–+——————————————————————————————————+
| Id | User | Host | db | Command | Time | State | Info |
+——+——+—————+——+———+——+————–+——————————————————————————————————+
| 609 | root | localhost | blog | Sleep | 5 | | NULL |
| 1258 | root | localhost | NULL | Sleep | 85 | | NULL |
| 1308 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 1310 | blog | pcfreak:64033 | blog | Query | 0 | Sending data | SELECT comment_author, comment_author_url, comment_content, comment_post_ID, comment_ID, comment_aut |
+——+——+—————+——+———+——+————–+——————————————————————————————————+
4 rows in set (0.00 sec)
mysql>

SHOW processlist gives a good view on what is happening inside the SQL.

To get more complete information on SQL query threads use the full extra option:

mysql> SHOW full processlist;

This gives pretty full info on running threads, but unfortunately it is annoying to re-run the command again and again – constantly to press UP Arrow + Enter keys.

Hence it is useful to get the same command output, refresh periodically every few seconds. This is possible by running it through the watch command:

debian:~# watch "'show processlist' | mysql -u root -p'secret_password'"

watch will run SHOW processlist every 2 secs (this is default watch refresh time, for other timing use watch -n 1, watch -n 10 etc. etc.

The produced output will be similar to:

Every 2.0s: echo 'show processlist' | mysql -u root -p'secret_password' Thu May 10 17:24:19 2012

Id User Host db Command Time State Info
609 root localhost blog Sleep 3 NULL1258 root localhost NULL Sleep 649 NULL1542 blog pcfreak:64981 blog Query 0 Copying to tmp table \
SELECT p.ID, p.post_title, p.post_content,p.post_excerpt, p.pos
t_date, p.comment_count, count(t_r.o
1543 root localhost NULL Query 0 NULL show processlist

Though this "hack" is one of the possible ways to get some interactivity on what is happening inside SQL server databases and tables table. for administering hundred or thousand SQL servers running dozens of queries per second – monitor their behaviour few times aday using mytop or mtop is times easier.

Though, the names of the two tools are quite similar and I used to think both tools are one and the same, actually they're not but both are suitable for monitoring sql database execution in real time.

As a sys admin, I've used mytop and mtop, on almost each Linux server with MySQL server installed.
Both tools has helped me many times in debugging oddities with sql servers. Therefore my personal view is mytop and mtop should be along with the Linux sysadmin most useful command tools outfit, still I'm sure many administrators still haven't heard about this nice goodies.

1. Installing mytop on Debian, Ubuntu and other deb based GNU / Linux-es

mytop is available for easy install on Debian and across all debian / ubuntu and deb derivative distributions via apt.

Here is info obtained with apt-cache show

debian:~# apt-cache show mytop|grep -i description -A 3
Description: top like query monitor for MySQL
Mytop is a console-based tool for monitoring queries and the performance
of MySQL. It supports version 3.22.x, 3.23.x, 4.x and 5.x servers.
It's written in Perl and support connections using TCP/IP and UNIX sockets.

Installing the tool is done with the trivial:

debian:~# apt-get --yes install mytop
....

mtop used to be available for apt-get-ting in Debian Lenny and prior Debian releases but in Squeeze onwards, only mytop is included (probably due to some licensing incompitabilities with mtop??).

For those curious on how mtop / mytop works – both are perl scripts written to periodically connects to the SQL server and run commands similar to SHOW FULL PROCESSLIST;. Then, the output is parsed and displayed to the user.

Here how mytop running, looks like:

MyTOP showing queries running on Ubuntu 8.04 Linux - Debugging interactively top like MySQL

2. Installing mytop on RHEL and CentOS

By default in RHEL and CentOS and probably other RedHat based Linux-es, there is neither mtop nor mytop available in package repositories. Hence installing the tools on those is only available from 3rd parties. As of time of writting an rpm builds for RHEL and CentOS, as well as (universal rpm distros) src.rpm package is available on http://pkgs.repoforge.org/mytop/. For the sake of preservation – if in future those RPMs disappear, I made a mirror of mytop rpm's here

Mytop rpm builds depend on a package perl(Term::ReadKey), my attempt to install it on CentOS 5.6, returned following err:

[root@cenots ~]# rpm -ivh mytop-1.4-2.el5.rf.noarch.rpm
warning: mytop-1.4-2.el5.rf.noarch.rpm: Header V3 DSA signature: NOKEY, key ID 6b8d79e6
error: Failed dependencies:
perl(Term::ReadKey) is needed by mytop-1.4-2.el5.rf.noarch

The perl(Term::ReadKey package is not available in CentOS 5.6 and (probably other centos releases default repositories so I had to google perl(Term::ReadKey) I found it on http://rpm.pbone.net/ package repository, the exact url to the rpm dependency as of time of writting this post is:

ftp://ftp.pbone.net/mirror/yum.trixbox.org/centos/5/old/perl-Term-ReadKey-2.30-2.rf.i386.rpm

Quickest, way to install it is:

[root@centos ~]# rpm -ivh ftp://ftp.pbone.net/mirror/yum.trixbox.org/centos/5/old/perl-Term-ReadKey-2.30-2.rf.i386.rpmRetrieving ftp://ftp.pbone.net/mirror/yum.trixbox.org/centos/5/old/perl-Term-ReadKey-2.30-2.rf.i386.rpmPreparing... ########################################### [100%]
1:perl-Term-ReadKey ########################################### [100%]

This time mytop, install went fine:

[root@centos ~]# rpm -ivh mytop-1.4-2.el5.rf.noarch.rpm
warning: mytop-1.4-2.el5.rf.noarch.rpm: Header V3 DSA signature: NOKEY, key ID 6b8d79e6
Preparing... ########################################### [100%]
1:mytop ########################################### [100%]

To use it further, it is the usual syntax:

mytop -u username -p 'secret_password' -d database

CentOS Linux MyTOP MySQL query benchmark screenshot - vpopmail query

3. Installing mytop and mtop on FreeBSD and other BSDs

To debug the running SQL queries in a MySQL server running on FreeBSD, one could use both mytop and mtop – both are installable via ports:

a) To install mtop exec:

freebsd# cd /usr/ports/sysutils/mtop
freebsd# make install clean
....

b) To install mytop exec:

freebsd# cd /usr/ports/databases/mytop
freebsd# make install clean
....

I personally prefer to use mtop on FreeBSD, because once run it runs prompts the user to interactively type in the user/pass

freebsd# mtop

Then mtop prompts the user with "interactive" dialog screen to type in user and pass:

Mtop interactive type in username and password screenshot on FreeBSD 7.2

It is pretty annoying, same mtop like syntax don't show user/pass prompt:

freebsd# mytop
Cannot connect to MySQL server. Please check the:

* database you specified "test" (default is "test")
* username you specified "root" (default is "root")
* password you specified "" (default is "")
* hostname you specified "localhost" (default is "localhost")
* port you specified "3306" (default is 3306)
* socket you specified "" (default is "")
The options my be specified on the command-line or in a ~/.mytop
config file. See the manual (perldoc mytop) for details.
Here's the exact error from DBI. It might help you debug:
Unknown database 'test'

The correct syntax to run mytop instead is:

freebsd# mytop -u root -p 'secret_password' -d 'blog'

Or the longer more descriptive:

freebsd# mytop --user root --pass 'secret_password' --database 'blog'

By the way if you take a look at mytop's manual you will notice a tiny error in documentation, where the three options –user, –pass and –database are wrongly said to be used as -user, -pass, -database:

freebsd# mytop -user root -pass 'secret_password' -database 'blog'
Cannot connect to MySQL server. Please check the:

* database you specified "atabase" (default is "test")
* username you specified "ser" (default is "root")
* password you specified "ass" (default is "")
* hostname you specified "localhost" (default is "localhost")
* port you specified "3306" (default is 3306)
* socket you specified "" (default is "")a
...
Access denied for user 'ser'@'localhost' (using password: YES)

Actually it is interesting mytop, precededed historically mtop.
mtop was later written (probably based on mytop), to run on FreeBSD OS by a famous MySQL (IT) spec — Jeremy Zawodny .
Anyone who has to do frequent MySQL administration tasks, should already heard Zawodny's name.
For those who haven't, Jeremy used to be a head database administrators and developer in Yahoo! Inc. some few years ago.
His website contains plenty of interesting thoughts and writtings on MySQL server and database management
 

How to fix “ERROR 1577 (HY000) at line 1: Cannot proceed because system tables used by Event Scheduler were found damaged at server start”

Saturday, May 12th, 2012

After migrating databases data from FreeBSD MySQL 5.0.83 server to a Debian Squeeze Linux MySQL version 5.1.61, below is a mysql –version issued on both the FreeBSD and the Debian servers

freebsd# mysql --version
mysql Ver 14.12 Distrib 5.0.83, for portbld-freebsd7.2 (i386) using 5.2

debian:~# mysql --version
mysql Ver 14.14 Distrib 5.1.61, for debian-linux-gnu (i486) using readline 6.1

The data SQL dump from the FreeBSD server was dumped with following command arguments:

freebsd# mysqldump --opt --allow-keywords --add-drop-table --all-databases -u root -p > complete_db_dump.sql

Then I used sftp to transfer complete_db_dump.sql dump to the a brand new installed latest Debian Squeeze 6.0.2. The Debian server was installed using a "clean Debian install" without graphical environment with CD downloaded from debian.org's site.

On the Debian machine I imported the dump with command:

debian:~# mysq -u root -p < complete_db_dump.sql

Right After the dump was imported I re-started SQL server which was previously installed with:

debian:~# apt-get install mysql-server
The error I got after restarting the mysql server:

debian:~# #/etc/init.d/mysql restart

was:

ERROR 1577 (HY000) at line 1: Cannot proceed because system tables used by Event Scheduler were found damaged at server start
ERROR 1547 (HY000) at line 1: Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted

This error cost me a lot of nerves and searching in google to solve. It took me like half an hour of serious googling ,until I finally found the FIX!!!:

debian:~# mysql_upgrade -u root -h localhost -p --verbose --force
Enter password:
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/run/mysqld/mysqld.sock' '--host=localhost'
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/run/mysqld/mysqld.sock' '--host=localhost'
bible.holy_bible OK
bible.holybible OK
bible.quotes_meta OK

Afterwards finally I had to restart the mysql server once again in order to finally get rid of the shitty:

ERROR 1547 (HY000) at line 1: Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted error!

debian:~# /etc/init.d/mysql restart
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing tables..

This solved the insane Column count of mysql.proc is wrong. Expected 20, found 16 once and for all!

Before I came with this fix I tried all kind of forum suggested fixes like:

debian:~# mysql_upgrade -u root -p
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
This installation of MySQL is already upgraded to 5.1.61, use --force if you still need to run mysql_upgrade

debian:~# mysql_upgrade -p
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
This installation of MySQL is already upgraded to 5.1.61, use --force if you still need to run mysql_upgrade

And few more, none of them worked the only one that worked was:

debian:~# #mysql_upgrade -u root -h localhost -p --verbose --force

I have to say big thanks to Mats Lindth wonderful blog post which provided me with the solution.

It seems, since Oracle bought the Community edition of MySQL thinks with this database server are getting more and more messy and backwards incompatible day by day.
Lately, I'm experiencing too much hassles with MySQL version incompitabilities. Maybe I should think for migrating permanently to Postgre …

By the way the ERROR 1547 (HY000) at line 1: Column count of mysql.proc is wrong. is most probably caused of some kind of password hashing incompitability between the password hashing between the BSD and Debian SQL versions, as mysql -u root -p < dump.sql, does override default stored user passwords in the mysql database tables… Such password, hashing issues were common in prior MySQL 4 to MySQL 5 migrations I've done, however since MySQL 5+ is already storing its password strings encrypted with md5 encryption I wonder why on earth this mess happens ….
 

PHP system(); hide command output – How to hide displayed output with exec();

Saturday, April 7th, 2012

I've recently wanted to use PHP's embedded system(""); – external command execute function in order to use ls + wc to calculate the number of files stored in a directory. I know many would argue, this is not a good practice and from a performance view point it is absolutely bad idea. However as I was lazy to code ti in PHP, I used the below line of code to do the task:

<?
echo "Hello, ";
$line_count = system("ls -1 /dir/|wc -l");
echo "File count in /dir is $line_count \n";
?>

This example worked fine for me to calculate the number of files in my /dir, but unfortunately the execution output was also visialized in the browser. It seems this is some kind of default behaviour in both libphp and php cli. I didn't liked the behaviour so I checked online for a solution to prevent the system(); from printing its output.

What I found as a recommendations on many pages is instead of system(); to prevent command execution output one should use exec();.
Therefore I used instead of my above code:

<?
echo "Hello, ";
$line_count = exec("ls -1 /dir/|wc -l");
echo "File count in /dir is $line_count \n";
?>

By the way insetad of using exec();, it is also possible to just use ` (backtick) – in same way like in bash scripting's .

Hence the above code can be also written for short like this:

<?
echo "Hello, ";
$line_count = `ls -1 /dir/|wc -l`;
echo "File count in /dir is $line_count \n";
?>

🙂

My pleasent time back home in Bulgaria

Sunday, January 4th, 2009

I experienced mostly a great time in Bulgaria. Refreshing days went to Kavarna with Nomen to confess my sins in front of a priest. I purposely has selected Kavarna’s Church and father “Vasilii” because I liked him as a priest. I’ve been on a liturgy once during the summer in the church where he takes care for. After the Liturgy I drived Mitko’s Audi A3 towards Dobrich. I’m a new driver so I need to practice. I drived during this days few more times. We went several times for a coffees and pubs with Nomen and Alex. We also went out with Nicki Mitko’s brother a couple of times and we had a great time together coffee-ing. I also went to a dentist. It seems that I had caries and they had to fix my teeth. The caries was in the last stage that it could be so the dentist had to kill and remove my nerve and he did so, the nerve was killed (with arsenic fluid) and removed and the tooth was filled in. I also went out for a drink 3 times with Lily. I went to liturgy 2 times. I met a nice priest (Father Veliko). In Varna when I came back from the Netherlands I met Order and had a great time with him. A lot of other things that I can hardly remember. The Christmas this year. My whole family was there except my grandma who had to be in the hospital (however the Christmas was just perfect!) Thanks to God! I and my family gathered together and had a nice talk and dinner even we said the Lord’s prayer before we started eating it was so nice … The New Year eve was a nice one as well. Even my grandma was there. If we don’t count few minor downfalls like a small quarrel with my sis all went well. I was home with my family until 1.30 in the morning and afterwards went to Denica’s who is a close friend of Nomen, Pavlin was there as well he is an old friend from the metal days of my past. I drinked a couple of glasses of beer and wine. In the morning I had a little hang-over as as a consequence from the drinking. The 13 days I was in Bulgaria has passed so quickly. I shared with my grandma and mother that I feel bad physically, emotionally and mentally. During my stay I also have been aguest in my aunt a couple of times, so nice time there as well.The food in Bulgaria is so great I was very, very happy to eat the delicios food there. The food here in the Netherlands is completely terrible compared to the one at home! In short that was how my days passed there. And I’m so happy about them! I’m looking forward to go home as fast as possible I realize my place is not abroad. When I firstly came here I thoiught that maybe my place is somewhere in some of the developed countries like here in The Netherlands. Now I think differently. It’s quite interesting that after I went out of Bulgaria I became much more a patriot. When I was living there I always has praised the developed countries way of living and thinking. Now it’s a completely different story I sometimes even like the bad things in Bulgaria. I wonder if other people who went out of Bulgaria to work or study has experienced the same.Anyways thanks to the Lord I had a safe journey from Bulgaria to the Netherlands. And here I’m here in the dorm again. I’m gonna tell in short my experiences from the trip back from Bulgaria to the Netherlands in a different post in a minute.END—–

End of Management Games / Lectures

Wednesday, April 16th, 2008

Today was the last day of the lectures with Joop Vinke. Here is how my day passed. I woke up at 9:00, washed my teeth dressed combed my hair and went to the police station to look for our quarter police officer. I need to renew my personal ID card because it has been expired already for 4 years already. Thanks God everything went smoothly in the police station. After that I went to school we had lectures with Joop Vinke. After the school I went home and played around with my FreeBSD system. I succesfully upgraded gnome 2.20 to 2.22.

Using the binary packages from

http://www.marcuscom.com/tb/packages/7-STABLE-FreeBSD/gnome/. I’m trying to upgrade gnome from source for already almost 3 weeks with portmanager. After all of the required ports rebuilded still gnome wasn’t functioning, so in order to make it working I downloaded packages from http://www.marcuscom.com/tb/packages/7-STABLE-FreeBSD/gnome/ and ran a little loop with

for i in *; do pkg_add -vf $i; done

 

to make all the .tbz files install I did that yesterday night today at the afternoon everything was installed and gnome ran just fine I only had to link few libraries because they were searched on a different places. All works just fine now I only have to rebuild few of my games because they’re now linked to an old libraries. In the evening we went out with Javor for a coffee. As very often we went to the fountain we had a nice talk and then we went to his apartment to watch a film. He recommended a film called 1984 and we watched that. My impression is that this film is totally psychodelic and freaky, but still interesting to see. After I went home I went to see my grandma and now I’m home tired on a few steps of my bed 🙂 I should also mention that today I upgraded clamav on 3 of the servers I maintain. It seems there are few configuration options which changed in the new clamav release (0.93). It was an easy day as a whole if we don’t count my physical infirmity.

END—–