Sunday, April 25, 2010

[Level 2] Use mkisofs to backup your filesystem.

You can use mkisofs to backup your filesystem.
# mkisofs --allow-lowercase --allow-multidot -r -o ./stanley.iso ./stanley
# mount -F hsfs `pwd`/stanley.iso /tmp/t

Wish this helps.
regards,
Stanley Huang

Friday, April 23, 2010

[Level 3] MySQL Stored Procedure Template.

This is my stored procedure template:

--
myparent_block:BEGIN
  -- declare variables
  DECLARE fetch_id INT DEFAULT NULL;
  DECLARE loop_done BOOLEAN DEFAULT FALSE;
  -- declare conditions
  DECLARE condition_not_found CONDITION FOR SQLSTATE '02000';
  -- declare cursors
  DECLARE cursor_myparent CURSOR FOR
    SELECT id FROM myparent WHERE serialnumber=sn;
  -- declare handlers
  DECLARE continue HANDLER FOR condition_not_found
    SET loop_done := TRUE;
  OPEN cursor_myparent;
  REPEAT
    FETCH cursor_myparent into fetch_id;
    DELETE FROM mychild WHERE pid=fetch_id;
  UNTIL loop_done END REPEAT;
  CLOSE cursor_device;
  DELETE FROM myparent WHERE fkid=fetchfkid;
END myparent_block;
--

Wish this helps.
regards,
Stanley Huang

Wednesday, April 21, 2010

[Level 3] South for Django 1.2

in south project:

http://south.aeracode.org/docs/databaseapi.html#database-specific-issues
South automatically exposes the correct set of database API operations as south.db.db; it detects which database backend you’re using from your Django settings file. It’s usually imported using:
from south.db import db
If you’re using multiple database support (Django 1.2 and higher), there’s a corresponding south.db.dbs dictionary which contains a DatabaseOperations object (the object which has the methods defined above) for each database alias in your configuration file:
from south.db import dbs
dbs['users'].create_table(...)
You can tell which backend you’re talking to inside of a migration by examining db.backend_name - it will be one of postgres, mysql, sqlite3, pyodbc or oracle.


It should worth to try~

Wish this helps.

regards,
Stanley Huang

[level 3] Customize Django Data Type Field.

I cannot fine char data type in Django 1.2 beta 1 .
So I try to build up my own data type field.
My sample code as following:

from django.db import models

# Create your models here.

from django.db.models import *
#from django.db import connection
#from django.db.models.fields.subclassing import LegacyConnection
#from django.db.models.query_utils import QueryWrapper
#from django.conf import settings
#from django import forms
#from django.core import exceptions, validators
#from django.utils.datastructures import DictWrapper
#from django.utils.functional import curry
#from django.utils.itercompat import tee
#from django.utils.text import capfirst
from django.utils.translation import ugettext_lazy as _
#from django.utils.encoding import smart_unicode, force_unicode, smart_str
#from django.utils import datetime_safe


# Create your models here.

# This is a much more flexible example.
class myCharField(Field):
    def __init__(self, *args, **kwargs):
        self.max_length = kwargs['max_length']
        super(myCharField, self).__init__(*args, **kwargs)

    def db_type(self, connection):
        if connection.settings_dict['ENGINE'] == 'django.db.backends.mysql':
            return 'char(%s)' % self.max_length
        else:
            #raise exception
            pass

class myCharClass(models.Model):
  myCF = myCharField(max_length=30)
       

''' not recommend
class myCharField2(Field):
    empty_strings_allowed = False
    description = _("IP address")
    def __init__(self, *args, **kwargs):
        Field.__init__(self, *args, **kwargs)

    def get_internal_type(self):
        return "Char" ## add 'Char' type in django/db/backends/mysql/creation.py

    def formfield(self, **kwargs):
        defaults = {'form_class': forms.IPAddressField}
        defaults.update(kwargs)
        return super(IPAddressField, self).formfield(**defaults)

class myCharClass2(models.Model):
  myCF = myCharField2(max_length=20)
'''

Wish this helps.

regards,
Stanley Huang

Monday, April 19, 2010

[Level 2] Remove all folders without any file.

If you want to remove all folders without any file, you can use the command to remove it.
# find ./ | sort -r | xargs rmdir 2>/dev/null

Wish this helps.

regards,
Stanley Huang

Thursday, April 15, 2010

[Level 2] Can't find message file on MySQL

If you have the same problem, you can use --language to assign language directory, Ex:

# ./bin/mysqld --user=mysql
100415 14:43:50 [ERROR] Can't find messagefile '/usr/share/english/errmsg.sys'
100415 14:43:50 [Note] Plugin 'FEDERATED' is disabled.
100415 14:43:50  InnoDB: Started; log sequence number 0 4604259
100415 14:43:50 [ERROR] Aborting
100415 14:43:50  InnoDB: Starting shutdown...
100415 14:43:56  InnoDB: Shutdown completed; log sequence number 0 4604259
100415 14:43:56 [Note]
#
# ./bin/mysqld --user=mysql --language=./share/english
100415 14:43:58 [Note] Plugin 'FEDERATED' is disabled.
100415 14:43:58  InnoDB: Started; log sequence number 0 4604259
100415 14:43:58 [Note] Event Scheduler: Loaded 0 events
100415 14:43:58 [Note] ./bin/mysqld: ready for connections.
Version: '5.1.45'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server (GPL)

Wish this helps.

regards,
Stanley Huang

[Level 2] Check the port is used by process on Ubutun

If you want to check a port which is used by what process.
You can use the command to get information:

# netstat -an -tap | grep :::443
tcp6       0      0 :::443                  :::*                    LISTEN      31462/java
# ps -fp 31462
root     31462     1  7 10:46 pts/1    00:00:43 /opt/java/jre//bin/java ...

Wish this helps.

regards,
Stanley Huang

Wednesday, April 14, 2010

[Level 3] Install script for Ubuntu apt-get Command

I wrote a shell script for install ubuntu package:

#!/bin/bash
#apt-get update; apt-cache search key word; ## update apt-get cache and search key word
#apt-get install pkg-name; apt-get upgrade; ## install package
#apt-get remove pkg-name;                   ## remove package
#grep install /var/log/dpkg.log             ## apt-get install history
#ls /var/lib/dpkg/info                      ## apt-get installed packages
#dpkg -L tomcat6                            ## list apt-get specific installed packages

## show usage
showUsage() {
  cat <<EOF
Usage:
  $0 [-u] [-s] package1 package2
  $0 [-u] [-s] all
  $0 -l
option:
  -l: list all package
  -u: force to update apt-get cache
  -s: get source only
Ex.
  $0 vim
  $0 -u all
Package:
`egrep "^install_.*()" $0 | sort | grep -v "install_template()" | cut -d'_' -f2 | cut -d'(' -f1 | sed -e 's/^/  /'`
EOF
}

listAllPackages() {
  echo "All packages list:"
  egrep "^[_]?install_.*()" $0 | sed -e 's/^_//' | sort | grep -v "install_template()" | cut -d'_' -f2 | cut -d'(' -f1 | sed -e 's/^/  /'
}

## initialize
init_apt_get() {
  declare -i nReturn=0

  if ! grep "apt update done" $apt_get_init_file > /dev/null 2>&1
  then
    echo $update
    eval $update || nReturn=$nReturn+1
    eval $updatedone
  fi

  if ! grep "apt upgrade done" $apt_get_init_file > /dev/null 2>&1
  then
    echo $upgrade
    eval $upgrade || nReturn=$nReturn+2
    eval $upgradedone
  fi

  return $nReturn
}

## mysql server
_install_mysql-server() {
  echo $install mysql-server
  eval $install mysql-server && return $?
}

## vim
install_vim() {
  echo $install vim
  eval $install vim && return $?
}

## java
install_sun-java6-jdk() {
  echo $install sun-java6-jdk
  eval $install sun-java6-jdk && return $?
}

## eclipse
install_eclipse() {
  echo $install eclipse
  eval $install eclipse && return $?
}

## python-mysqldb
install_python-mysqldb() {
  echo $install python-mysqldb
  eval $install python-mysqldb && return $?
}

## django, use source file to install django (1.2 beta), python ./setup.py build, python ./setup.py install
_install_python-django() {
  echo $install python-django python-django-doc
  eval $install python-django python-django-doc && return $?
}

## tomcat6
_install_tomcat6() {
  echo $install tomcat6
  eval $install tomcat6 && return $?
}

## dpkg-dev, for get source from apt repository
install_dpkg-dev() {
  echo $install dpkg-dev
  eval $install dpkg-dev && return $?
}

## vnc4server
install_vnc4server() {
  echo $install vnc4server
  eval $install vnc4server && return $?
}

## template, remove the underscore at the head of function name, then replace all "template", and remove/uncomment the line "return 0".
__install__template() {
  return 0 ## uncomment it for real function
  echo $install template
  eval $install template && return $?
}


####################################################### main

## sudo init
clear
sudo id > /dev/null 2>&1

# environment variables
apt_get_init_file=/etc/.apt_get_init_file && sudo touch $apt_get_init_file
declare -i nShift
bSourceOnly=false
bForceUpdate=false
sOption=""

# command alias, called by $cmd
#echo 'Y' > /tmp/Y
search='sudo apt-cache search'
update='sudo apt-get -y --force-yes update'
install='sudo apt-get -y --force-yes install'
remove='sudo apt-get -y --force-yes remove'
upgrade='sudo apt-get -y --force-yes upgrade'
source='sudo apt-get -y --force-yes source'

updatedone="sudo echo apt update done... >> $apt_get_init_file"
upgradedone="sudo echo apt upgrade done... >> $apt_get_init_file"


sGetopts=":lsu"
while getopts $sGetopts o
do
  case "$o" in
  l)
    listAllPackages
    exit 0
    ;;
  s)
    bSourceOnly=true
    sOption="$sOption -s"
    ;;
  u)
    bForceUpdate=true
    sOption="$sOption -u"
    ;;
  \?)
    showUsage
    exit 1
    ;;
  esac
done
nShift=$OPTIND-1
shift $nShift

if [ -z $1 ]
then
    showUsage && echo "Error without args, exit program..." && exit 1
fi

## init or update
if (! init_apt_get) || $bForceUpdate
then
  ## update cache
  eval $update
fi


## replace install option with source
if $bSourceOnly
then
  install=$source
fi


while true
do
  if [ -z $1 ]
  then
    break
  elif [ $1 == "all" ]
  then
    egrep "^install_.*()" $0 | grep -v "install_template()" | cut -d'_' -f2 | cut -d'(' -f1 | xargs $0 $sOption
    break
  elif ! grep "^install_$1()" $0 > /dev/null 2>&1
  then
    echo "Cannot find $1, exit program..." && exit 2
  elif ! install_$1
  then
    echo "Error in install $1, exit program..." && exit 3
  else
    shift
  fi
done



Wish this helps.

regards,
Stanley Huang

[Level 2] Install chewing in Ubuntu

If you want to use Chinese Input in Ubuntu, you can setup the environment with scim. The commands as the following:
# apt-get install scim scim-pinyin scim-chewing scim-tables-zh scim-qtimm im-switch
# im-switch -s scim
Relogin again, and enjoy it!

Wish this helps.

regards,
Stanley Huang

Tuesday, April 13, 2010

[Level 1] Install unrar on CentOS

Install unrar on CentOS 5.4
# wget http://dag.wieers.com/packages/unrar/unrar-3.6.2-1.el4.rf.i386.rpm
# rpm -ivh unrar-3.6.2-1.el4.rf.i386.rpm

Run it:
# unrar e -kb myRar.rar

Wish this helps.

regards,
Publish Post

Stanley Huang

Monday, April 12, 2010

[Level 3] Python Desing Pattern -- Episode 0 -- Intro.

One Pattern a day, keep refactoring away~

Wish this helps.

regards,
Stanley Huang

Friday, April 9, 2010

[Level 2] MySQLdb Python API -- Cursor Objects

In myCxn.py:
import MySQLdb
cxn = MySQLdb.connect(host='localhost', db='test', user='root', passwd='admin' )
cur = cxn.cursor()


In main.py
## import object from file
from myCxn import  cur, cxn

cxn2 = cur.connection # pass the connection object from cursor
# cxn2.close() # close() will cause cur.execute() fail...

## before execute()
print "before execute():"
print "description:", cur.description
print "lastrowid:", cur.lastrowid # lastrowid => The id of last modified row.
print "rowcount:", cur.rowcount


cur.setoutputsizes(2) # seems not work, Does nothing, required by DB API.
cur.execute("select * from test.t;")
for data in cur.fetchall():
  print("cur1_fetchall.rownumber:", cur.rownumber)
  print("cur1_fetchall.rowrowcount:", cur.rowcount)
  print(data[0])
 
cur.execute("select * from test.t;")
for data in cur.fetchall():
  print("cur2_fetchall.rownumber:", cur.rownumber)
  print("cur2_fetchall.rowrowcount:", cur.rowcount)
  print(data[0]) 

cur.execute("select * from test.t;")
for data in cur.fetchone():
  print("cur3_fetchone.rownumber:", cur.rownumber)
  print("cur3_fetchone.rowrowcount:", cur.rowcount)
  print(data)

cur.execute("select * from test.t;")
cur.arraysize=3
for data in cur.fetchmany():
  print("cur4_fetchmany.arraysize:", cur.arraysize)
  print("cur4_fetchmany.rownumber:", cur.rownumber)
  print("cur4_fetchmany.rowrowcount:", cur.rowcount)
  print(data[0])
 
cur.execute("truncate table test.t;")
#cxn.query("drop procedure if exists test.proc1;")   # not show warning if not exists proc1
#cur.execute("drop procedure if exists test.proc1;") # still work, but if proc1 not exist, show warning

#cxn.execute("drop procedure if exists proc1;") # fail, no such attribute
#cur.query("drop procedure if exists proc1;")   # fail, no such attribute

## cannot query DDL for create stored procedure...
#cxn.query("delimiter $$")
#cxn.query("""
#create procedure test.proc1(in p1 int, in p2 int, in p3 int)
#begin
#insert into test.t values(p1),(p2),(p3);
#end$$
#""")
#cxn.query("delimiter ;")

results = cur.callproc("proc1",(1,2,3))
#cur.connection.commit()
print "results:", results
#results = cur.callproc("proc1",('a','b','c')) # error
#cur.connection.commit()
#print "results:", results

## after execute()
print "after execute():"
print "description:", cur.description
print "lastrowid:", cur.lastrowid # lastrowid => The id of last modified row.
print "rowcount:", cur.rowcount

## execute many
cur.setinputsizes(2) # seems not work, Does nothing, required by DB API.
cur.executemany("insert into test.t values(%s)",(10,20,30,40,50))           ## work
#cur.executemany("insert into test.t values(%s)",[(10),(20),(30),(40),(50)]) ## work
#cur.executemany("insert into test.t values(%s)",[10,20,30,40,50])           ## not work


for data in cur.fetchmany():
  #print(data[0],data[1],data[2])
  #print(data[0],data[1])
  print(data[0])

## after execute() 
print "after execute():"
print "description:", cur.description
print "lastrowid:", cur.lastrowid # lastrowid => The id of last modified row.
print "rowcount:", cur.rowcount

cur.connection.commit()
cur.close()
cxn.close()


print "End helloworld!"




Wish this helps.

regards,
Stanley Huang

[Level 3] MySQL Build Options Sample

A sample MySQL build options from MySQL Performance Blog:
# ./configure '--localstatedir=/var/db/mysql' '--without-debug' '--without-readline' '--without-libedit' '--without-bench' '--without-extra-tools' '--with-libwrap' '--with-mysqlfs' '--with-low-memory' '--with-comment=FreeBSD port: mysql-client-5.0.67_1' '--enable-thread-safe-client''--with-charset=cp1251' '--with-collation=cp1251_general_ci' '--with-extra-charset=all' '--with-named-thread-libs=-pthread' '--without-server''--prefix=/usr/local' '--mandir=/usr/local/man' '--infodir=/usr/local/info/''--build=amd64-portbld-freebsd7.0' 'CC=cc' 'CFLAGS=-O2 -mmmx -msse -msse2 -msse3 -pipe -march=nocona  -fno-strict-aliasing' 'CXXFLAGS=-O2 -mmmx -msse -msse2 -msse3 -pipe -march=nocona -fno-strict-aliasing -O2 -mmmx -msse -msse2 -msse3 -pipe -march=nocona  -fno-strict-aliasing -felide-constructors -fno-rtti -fno-exceptions' 'CXX=c++' 'build_alias=amd64-portbld-freebsd7.0'

Wish this helps.

regards,
Stanley Huang

Thursday, April 8, 2010

[Level 1] Install Django on CentOS 5.4

Just following the steps to install Django on CentOS 5.4
# yum install subversion
# cd $APACHE_HOME
# mkdir django-src
# cd django-src
# svn co http://code.djangoproject.com/svn/django/trunk/
# cd trunk
# python setup.py install

Wish this helps.

regards,
Stanley Huang

Wednesday, April 7, 2010

[Level 2] Case sensitive in MySQL.

The MySQL in Unix system, the database name and table name are case sensitive as default. But in Windows, they are case insensitive. That is because the file system in Unix are case sensitive but in Windows is not.
So, the easy way to solve the problem or for compatibility between different platform, you can choose the case sensitive or not in your MySQL database.
The way to setting it is the by the option "low-case-table-name", and there are three values (0,1,2) for this option.

0: Table and database names are stored on disk using the lettercase specified as DDL statement.
1: Table names are stored in lowercase on disk and name comparisons are not case sensitive.
2: Table and database names are stored on disk using the lettercase specified as DDL statement, but MySQL converts them to lowercase on lookup.

Wish this helps.

regards,
Stanley Huang

[Level 2] Script for mysql.

Because I have multi MySQL environments, so I have to assign different parameter (especially the "--socket" option) in different environment,
and it make me crazy.
So I create a folder "/shell" for store my scripts and create a alias "mysql" to the script in /shell.
My sample as the following:

.bashrc:
# cat ~/.bashrc | tail -3
export MYSQL_HOME=/usr/local/mysql/5.1
export PATH=$PATH:$MYSQL_HOME/bin
alias mysql="/shell/mysql"

Script /shell/mysql:
 #!/bin/bash -vx

echo "call $0..."
sMySQLCmdList="/usr/mysql/bin/mysql /usr/local/mysql/bin/mysql"
#sMySQLCmd=""
for sItem in $sMySQLCmdList
do
  if [ -f $sItem ]
  then
    sMySQLCmd=$sItem
    break
  fi
done
if pgrep mysqld
then
  mysql_socket=`ps -ef | grep mysqld | grep -v monitor | grep socket | sed -e 's/.*--socket=//' |     cut -d' ' -f1`
 
  if [ `echo $@ | grep -c -- -h` -eq 1 ]
  then
    $sMySQLCmd $@
  elif [ ! -z $mysql_socket ]
  then
    $sMySQLCmd --socket=$mysql_socket $@
  else
    $sMySQLCmd $@
  fi
else
  echo "No Running MySQL Server, Exit mysql" && exit 1
fi
 


Wish this helps.

regards,
Stanley Huang

[Level 2] How to backup MySQL partitioning table.

Sometimes, we will use partitioning to split the data into different files.
But how do we backup the data by the command mysqldump?
The same as the following:

Create table and insert data:
mysql> create table test.tr1 (
    ->   id int
    -> ) engine = InnoDB
    ->   partition by range (id)
    ->   (
    ->     partition p0 values less than (10),
    ->     partition p1 values less than (20),
    ->     partition pm values less than (maxvalue)
    ->   );
Query OK, 0 rows affected (0.17 sec)

mysql> insert into test.tr1 values (1),(2),(11),(12),(21),(22);
Query OK, 6 rows affected (0.30 sec)
Records: 6  Duplicates: 0  Warnings: 0


Dump data and list files:
# mysqldump --user=root --password=password --no-create-info --where="id<10" test tr1 > ./test.tr1.p0.sql;
# mysqldump --user=root --password=password --no-create-info --where="id>=10 and id<20" test tr1 > ./test.tr1.p1.sql;
# mysqldump --user=root --password=password --no-create-info --where="id>=20" test tr1 > ./test.tr1.p2.sql;
# ls -al ./test.tr1.p*.sql
-rw-r--r-- 1 root root 1476 2010-04-07 09:02 ./test.tr1.p0.sql
-rw-r--r-- 1
root root 1489 2010-04-07 09:02 ./test.tr1.p1.sql
-rw-r--r-- 1
root root 1479 2010-04-07 09:02 ./test.tr1.p2.sql




Wish this helps.

regards,
Stanley Huang

Friday, April 2, 2010

[Level 3] Install memcached user defined function for MySQL on CentOS 5.4

Memcaced is a good solution for scale out your system.
And there is a project for MySQL udf by implement memcached.

Prepare libmemcached:
# wget http://download.tangent.org/libmemcached-0.37.tar.gz
# export PKG_CONFIG_PATH=/usr/local/lib/pkgconfig
# ./configure --with-mysql=/usr/local/mysql/bin/mysql_config
# make && make install
PS. when I use libmemcached 0.38 and complie source, I get a error like below:
# wget http://download.tangent.org/memcached_functions_mysql-0.8.tar.gz
servers.c: In function 'memc_servers_set':
servers.c:122: error: 'memcached_st' has no member named 'hosts'
servers.c:123: error: 'memcached_st' has no member named 'hosts'
servers.c:124: error: 'memcached_st' has no member named 'hosts'
But after down-grade the version from 0.38 to 0.37 then works.

Compile memcahced function:
# wget http://download.tangent.org/memcached_functions_mysql-0.8.tar.gz
# tar zxvf ./memcached_functions_mysql-0.8.tar.gz
# cd ./memcached_functions_mysql-0.8
# export PKG_CONFIG_PATH=/usr/local/lib/pkgconfig 
# ./configure
# make && make install

Check MySQL plugin folder:
mysql> show variables like 'plugin_dir';
+---------------+-----------------------------------+
| Variable_name | Value                             |
+---------------+-----------------------------------+
| plugin_dir    | /usr/local/mysql/lib/mysql/plugin |
+---------------+-----------------------------------+
1 row in set (0.00 sec)
mysql>

Copy library to MySQL plugin folder:
# cp libmemcached_functions_mysql.* /usr/local/mysql/lib/mysql/plugin/

Create UDF:
mysql> source ./sql/install_functions.sql;
ERROR 1126 (HY000): Can't open shared library 'libmemcached_functions_mysql.so' (errno: 22 /usr/local/mysql/lib/mysql/plugin/libmemcached_functions_mysql.so: undefined symbol: memcached_string_append)
ERROR 1126 (HY000): Can't open shared library 'libmemcached_functions_mysql.so' (errno: 22 /usr/local/mysql/lib/mysql/plugin/libmemcached_functions_mysql.so: undefined symbol: memcached_string_append)
...
mysql>
Create UDF Fail...


So I try to use another project called "Drizzle"
# wget http://protobuf.googlecode.com/files/protobuf-2.3.0.tar.gz
# tar zxvf ./protobuf-2.3.0.tar.gz
# cd protobuf-2.3.0
# ./configure && make && make install

# wget http://launchpad.net/libdrizzle/trunk/0.8/+download/libdrizzle-0.8.tar.gz
# tar zxvf ./libdrizzle-0.8.tar.gz
# cd ./libdrizzle-0.8
# ./configure && make && make install

# wget ftp://ftp.pbone.net/mirror/ftp.rpmhelp.net/pub/releases/1.0-CURRENT/SRPMS/pcre-4.3-6sls.src.rpm
# cd /usr/src/redhat/SOURCE
# bzip2 -cd ./pcre-4.3.tar.gz | tar xvf -
# cd ./pcre-4.3
# ./configure && make & make install

# wget http://launchpad.net/drizzle/cherry/2010-03-29/+download/drizzle-2010.03.1412.tar.gz
# tar zxvf ./drizzle-2010.03.1412.tar.gz
# cd ./drizzle-2010.03.1412
# ./configure --with-libprotobuf-prefix=/usr/local/lib --with-libdrizzle-prefix=/usr/local/lib

[ To Be Continue... ]

Wish this helps.

regards,
Stanley Huang

[Level 3] Show MySQL Porcess in CentOS

Three processes to get process info:

1. show all process:
mysq> show processlist;
+----+------+-----------------+------+---------+------+-------+------------------+
| Id | User | Host            | db   | Command | Time | State | Info             |
+----+------+-----------------+------+---------+------+-------+------------------+
|  1 | root | localhost:47605 | NULL | Sleep   |   27 |       | NULL             |
| 16 | root | localhost       | test | Query   |    0 | NULL  | show processlist |
+----+------+-----------------+------+---------+------+-------+------------------+
2 rows in set (0.00 sec)

2. get process that use port 47605:

mysql> \! netstat -ntp | grep 47605
tcp        0      0 127.0.0.1:47605             127.0.0.1:3306              ESTABLISHED 2629/mysql-monitor-
tcp        0      0 127.0.0.1:3306              127.0.0.1:47605             ESTABLISHED 17920/mysqld

3. get process that pid equal 2629:
mysql> \! netstat -ntp | grep 2629



Wish this helps.

regards,
Stanley Huang

[Level 3] Install Memcached Storage Engine for MySQL on CentOS 5.4

Prepare the package first for build the memcached storage engine.

Download libmemacached 0.34 and install:
# wget http://download.tangent.org/libmemcached-0.34.tar.gz
# tar zxvf ./libmemcached-0.34.tar.gz
# cd ./libmemcached-0.34
# export PKG_CONFIG_PATH=/usr/local/lib/pkgconfig
# ./configure && make && make install

Download libxml2 and install:
# wget ftp://xmlsoft.org/libxml2/libxml2-2.7.7.tar.gz
# tar zxvf libxml2-2.7.7.tar.gz
# cd ./libxml2-2.7.7
# ./configure && make && make install

Download  libxmlrow and install:
# wget http://download.tangent.org/libxmlrow-0.2.tar.gz
# tar zxvf ./libxmlrow-0.2.tar.gz
# cd ./libxmlrow-0.2
export PKG_CONFIG_PATH=/usr/local/lib/pkgconfig
# ./configure && make && make install 


Download  memcached storage and install:
# export PKG_CONFIG_PATH=/usr/local/lib/pkgconfig
# sed -i "s#uint16_t#uint32_t#g" ./src/ha_memcache.cc
# ./configure --with-mysql=/usr/src/redhat/SOURCES/mysql-5.1.44 --libdir=/usr/local/mysq/lib/mysql
# make && make install
# cp /usr/local/mysq/lib/mysql/libmemcache_engine.so.0.0.0 /usr/local/mysql/lib/mysql/plugin/libmemcache_engine.so


Install plugin storage:
mysql> INSTALL PLUGIN memcache SONAME 'libmemcache_engine.so';
mysql> show plugins;
+------------+--------+----------------+-----------------------+---------+
| Name       | Status | Type           | Library               | License |
+------------+--------+----------------+-----------------------+---------+
| binlog     | ACTIVE | STORAGE ENGINE | NULL                  | GPL     |
| partition  | ACTIVE | STORAGE ENGINE | NULL                  | GPL     |
| CSV        | ACTIVE | STORAGE ENGINE | NULL                  | GPL     |
| MEMORY     | ACTIVE | STORAGE ENGINE | NULL                  | GPL     |
| InnoDB     | ACTIVE | STORAGE ENGINE | NULL                  | GPL     |
| MyISAM     | ACTIVE | STORAGE ENGINE | NULL                  | GPL     |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL                  | GPL     |
| MEMCACHE   | ACTIVE | STORAGE ENGINE | libmemcache_engine.so | GPL     |
+------------+--------+----------------+-----------------------+---------+
8 rows in set (0.00 sec)

Test it:
mysql> CREATE TABLE `test`.`testMemSE` (
`id` int(11) NOT NULL DEFAULT '0',
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL
) ENGINE=MEMCACHE DEFAULT CHARSET=latin1
CONNECTION='192.168.56.101:11211,192.168.56.102:11211';

ERROR 1173 (42000): This table type requires a primary key
mysql> CREATE TABLE `test`.`testMemSE` (
`id` int(11) NOT NULL DEFAULT '0',
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MEMCACHE DEFAULT CHARSET=latin1
CONNECTION='192.168.56.101:11211,192.168.56.102:11211';

mysql> insert into test.testMemSE values (1,2,3);
ERROR 1598 (HY000): Binary logging not possible. Message: Statement-based format required for this statement, but not allowed by this combination of engines

## after stop bin-log
mysql> insert into test.testMemSE values (1,1,1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test.testMemSE where id=1;

...
Wish this helps.

regards,
Stanley Huang

Thursday, April 1, 2010

[Level 2] MySQLdb Python API -- Connection Objects

Connection have such methods:
1. close() => close database connection.
2. commit() => commit transaction.
3. rollback() => rollback transaction.
4. cursor() => create curosr object and return.
5. begin() => start transaction. (deprecated, and will be removed from 1.3)

The sample code for connection/cursor testing:
Ex1:
#/usr/bin/python
import MySQLdb
cxn = MySQLdb.connect(host='localhost', db='test', user='root', passwd='admin123' )


## create cursor
cur = cxn.cursor()


## query from database
#cur.query("
select * from test.t limit 5;") ## no query attribute for cursor
cur.execute("select * from test.t limit 5;")
for data in cur.fetchall():
  #print(data[0],data[1],data[2])
  print(data[0],data[1])

cur.close()
cxn.close()

Ex2:
#/usr/bin/python
import MySQLdb
cxn = MySQLdb.connect(host='localhost', db='test', user='root', passwd='admin123' )
cur = cxn.cursor()

## test cursor attributes
cur.arraysize=1
print "select * from test.t limit 5;"
cur.execute("select * from test.t limit 5;")
data = cur.fetchmany(2)
## fetch 2 rows
print data
while True:
  #data = cur.fetchmany() ## use default value => cur.arraysize
  #data = cur.fetchmany(cur.arraysize) ## cur.arraysize
  data = cur.fetchmany(3) ## fetch 3 rows
  if data:
    for row in data:
      print(row)
  else:
    break
print "cur.rowcount=" + str(cur.rowcount)
## get row count from cursor

cur.close()
cxn.close()

Ex3:
#/usr/bin/python
import MySQLdb
cxn = MySQLdb.connect(host='localhost', db='test', user='root', passwd='admin123' )
cur=cxn.cursor()

print "test cur.next(): select * from test.t limit 10;"
cur.execute("select * from test.t limit 10;")
iter = cur.__iter__();
while True:
  try:
    data = iter.next()
    print data
  except StopIteration:
    print "Catch StopIteration Exception..."
    break
cur.close()
cxn.close()



Ex4:
#/usr/bin/python
## test transaction
## deprecated, and will remove from 1.3

import MySQLdb
cxn = MySQLdb.connect(host='localhost', db='test', user='root', passwd='admin123' )
#cxn.autocommit = True


cur = cxn.cursor()
#cur = cxn.begin()
## no begin attribute for cursor
#cur.begin()

print "insert into test.t values(1),(2),(3);"
cur.execute("insert into test.t values(1),(2),(3);")
print "cur.rowcount=" + str(cur.rowcount)
#cxn.rollback()
cxn.commit() ## default auto-commit off

cur.close()
cxn.close()



Ex5:
#/usr/bin/python
import MySQLdb
cxn = MySQLdb.connect(host='localhost', db='test', user='root', passwd='admin123' )

cur=cxn.cursor()

## test exception
try:
  cur.execute("select * from test.t wheree 1=1;")
except Exception:
  print "Exception..."

cxn.close()



Wish this helps.
regards,
Stanley Huang

[Level 2] MySQLdb Python API -- Module Attributes

If you want to get some information about MySQLdb API,
you can use the following script to get the info.

#!/usr/bin/python
try:
  import MySQLdb
  db = MySQLdb
  if db:
    print "db.apilevel=" + db.apilevel
    print "db.threadsafety=" + str(db.threadsafety)
    print "db.paramstyle=" + db.paramstyle
except ImportError:
  print "import MySQLdb fail"
  exit

The attributes description:
a. apilevel => Version of API.
b. threadsafety => Level of thread safety
    0: Not threadsafe. Should not share the module at all.
    1: Minimally threadsafe. Could share module but not connection. (Default)
    2: Moderately threadsafe. Could share both module and connection but not cursors.
    3: Fully threadsafe. Could share module, connection and cursor.
c. paramstyle => Parameter style of the module
    numeric: WHERE addr=:1
    named: WHERE addr=:addr
    pyformat: WHERE addr=%(addr)s
    qmark: WHERE addr=?
    format: WHERE addr=%s (Default)
Run it:
# ./testMySQLdb_Module_Attrs.py
db.apilevel=2.0
db.threadsafety=1
db.paramstyle=format
#




Wish this helps.

regards,
Stanley Huang

[Level 1] Install Django on OpenSolaris.

Use pkg command to install Django:
# pfexec pkg set-authority -O http://pkg.opensolaris.org/webstack 
# webstackpfexec pkg refresh 

Launch Pkg Manager by downloading.
# firefox http://pkg.opensolaris.org/webstack
[search "Django"]
[click "install"]

Wish this helps.

regards,
Stanley Huang