MySQL Lock tables and subqueries

Example error:

Table 'table' was not locked with LOCK TABLES

Example:

LOCK TABLES x write, y write;

select * from x; #works

select * from y; #works

But:

select *,

(select fieldname from y where fieldcond=x.field2) as yname

from x where field1='xxx'; #failed with

#y tables is not locked

Solution:

LOCK TABLES x write, y write, y as yalias write;

select *,

(select fieldname from y yalias where fieldcond=x.field2) as yname

from x where field1='xxx';

#WORKS!

Check system file changes from installed package

On redhat based system:

rpm --verify -all

On Debian based system:

dpkg -l \*|while read s n rest; do if [ "$s" == "ii" ]; then echo $n;
fi; done > ~/tmp.txt
for f in `cat ~/tmp.txt`; do debsums -s -a $f; done

select a field by first row via left join

There are times when we need to select first record of a table in a join tables.

Problem with mysql is that after joining tables together, there will be no indexing,

making query extremely slow.

First, makesure all inner join uses on query to limit the records instead of where condition.

This way, the record is filtered based on table indexes (make sure you index'em first) in the joining process.

2nd, to only populate a field by first row which matches the criteria, do a left join

select x.field1, x.field2 from table1 x inner join table2 x2 on x.field3=x2.field2

left join table2 x3 on x2.field1=x3.field1 and x3.fields=[some condition to filter to first row]

group by ...

Roundcube Error 500

It seems that kloxo always have issue updating roundcube.

Most of the time, the webmail is updated, but the database is not.

to fix it:

mysql -u root -p roundcubemail

source /home/kloxo/httpd/webmail/roundcube/SQL/mysql.update.sql

MySQL data contains chinese charactor become äöüßæåéé

It took me hours figuring this.

All my mysql data became symbols.

Then finally, i found the culprit.

1st, i converted my field and default charset of my table to unicode.

But i did not update the existing data to unicode. So it was my mistake.

2nd, in php, i changed my mysql_set_charset("UTF-8");

So now all the data from the table which contains original latin will be forced to utf-8.

So the symbol appear in my html output, so does phpmyadmin.

To fix this, i wrote a program to retrieve the data from latin charset, and

then output it as a code in array to replace the data later back to the database as utf-8.

Example:

mysql_set_charset("latin1", $conndb);

mysql_query("select ...");

$aData = mysql_fetch_assoc($query);

mysql_set_charset("utf-8", $conndb);

$aData["fieldname"] = utf8_encode($aData["fieldname"]);

//then update those record with newly encoded utf8

mysql_query("update table ... ");

so, the next time we query the database, ensure to use

mysql_set_charset("utf-8", $conndb);

utf encoding and json

If someone tell you that json doesnt work with utf8, they are wrong.

It does support utf8.

The main problem is the database side.

Ensure to set encoding of mysql connection charset to utf8 to have it working properly.

mysql_set_charset("utf8", $conn);//support for unicode

Banning ip from DDOS

After surfing around, ive found some very handy commands to handle blocking of ips

#to list all ip and # of connections

sudo netstat -plan|grep :80|awk {'print $5'}|cut -d: -f 1|sort|uniq -c|sort -n
# list all ports
sudo netstat -plan|awk {'print $5'}|cut -d: -f 1|sort|uniq -c|sort -n

#to block a specific ip

sudo iptables -I INPUT 1 -s xx.xx.xx.xx -j DROP

#to block a class B ip

sudo iptables -I INPUT 1 -s xx.xx.0.0/16 -j DROP

#to block a class C ip

sudo iptables -I INPUT 1 -s xx.xx.xx.0/24 -j DROP

#to block a class A ip

sudo iptables -I INPUT 1 -s xx.0.0.0/16 -j DROP

Normally you might want to use Class B block if you want to block entire ip sets from a particular country...

*be very careful when blocking class B address, some ip range might be shared among different countries.

To know which country the ip resides on:

http://ip-to-country.webhosting.info/node/view/36

To list all the iptables rules for incoming traffic:

sudo iptables -L INPUT -n -v

A very good list of cidr ip for each particular country:

http://www.ipdeny.com/ipblocks/

Another way of attack is tcp level flood, aka: SYN FLOOD

#check tcp on SYN_RECV

sudo netstat -n -p TCP tcp | grep SYN_RECV

Good article about hardening system:

http://www.symantec.com/connect/articles/hardening-tcpip-stack-syn-attacks

#Very handy command to limit # of connections to a specific port from an ip

sudo iptables -A INPUT -p tcp --syn --dport 80 -m connlimit --connlimit-above 20 -j REJECT --reject-with tcp-reset

sudo iptables -A INPUT -p tcp --syn --dport 110 -m connlimit --connlimit-above 10 -j REJECT --reject-with tcp-reset

sudo iptables -A INPUT -p tcp --syn --dport 25 -m connlimit --connlimit-above 10 -j REJECT --reject-with tcp-reset

#To remove a rule (example):

sudo iptables -D INPUT -p tcp --syn --dport 80 -m connlimit --connlimit-above 20 -j REJECT --reject-with tcp-reset

#To save it:

sudo service iptables save

http://www.cyberciti.biz/faq/iptables-connection-limits-howto/

Updated 2012 Feb 28:
Encountered another type of DOS attack using TIME_OUT ip status.
One way to solve this is to reduce the # of seconds the tcp will timeout to a incomplete disconnected tcp.
vi /etc/sysctl.conf

net.ipv4.tcp_fin_timeout = 35
net.ipv4.tcp_keepalive_time = 1800
net.ipv4.tcp_keepalive_intvl = 35
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_tw_reuse = 1

sudo sysctl -p

Then restart the services effected, such as http and mysql.
To show all states of tcp in the system, run this:
netstat -an|awk '/tcp/ {print $6}'|sort|uniq -c

Hope this help :)