Web Developer Indonesia

Configure Timezone in Linux Carefully

2010-10-26T14:48:00.004+07:00

Yesterday i experienced bad thing in my RHEL cluster especially related to timezone. Unfortunately my applications depend highly with system time. As we know in Linux we usually configure our timezone machine using the following one of these ways:

1. By symbolic link or copying /etc/localtime to/from distro timezone files which usually located in /usr/share/zoneinfo/*


ln -sf /usr/share/zoneinfo/Asia/Singapore

2. By configuring /etc/sysconfig/clock

ZONE="Asia/Singapore" UTC=false
ARC=false

Both make user application written in php, python, and mysql running on the correct date/time based on the configured timezone. However this will cause a problem if we are not carefully configure it. One of example that i experience trouble was using combination of both method above differently and my auto update of RHEL package was active.

What i mean "differently" is i used BST for #2 and SGT (Singapore Time) for #1. When system apply new update the symbolic/file of /etc/localtime will be overridden by the timezone file that i set in #2 method.

Xrandr to fix screen resolution

2010-09-23T09:59:00.006+07:00

Headache in repairing Xconf.org ? and your screen resolution not listed in (System -> Preferences -> Monitor) and Need fast solution for your screen trouble. Actually in Ubuntu this can be easily repaired using tools that already included namely xrandr. Firstly you need to make sure you have file /etc/X11/xorg.conf

Ubuntu Karmic/Lucid by default doesn't have xorg.conf however this file can easily created using your console as follows:

1. Ctrl+Alt+F1 (switch to black screen)

2. #sudo service gdm stop (stop Xserver)

3. #sudo Xorg -configure

This will generatethe file in ~/xorg.conf.new.

4. # sudo mv ~/xorg.conf.new /etc/X11/xorg.conf

5. # sudo service gdm start

After xorg.conf file generated then you can manage to config your screen resolution using xrandr. I this case i one to add new resolution 1440x900.

1. Type xrandr -q

rizky@rizky:~$ xrandr -q
Screen 0: minimum 320 x 200, current 1360x768, maximum 1360x768
VGA1 connected 1440x900+0+0 (normal left inverted right x axis y axis) 0mm x 0mm
1360x768 59.8
1024x768 60.0
800x600 60.3 56.2
848x480 60.0
640x480 59.9 59.9

2. Since 1440x900 not listed then you nee need to gain modeline using this command cvt

rizky@rizky:~$ cvt 1440 900
# 1440x900 59.89 Hz (CVT 1.30MA) hsync: 55.93 kHz; pclk: 106.50 MHz
Modeline "1440x900_60.00"  106.50  1440 1528 1672 1904  900 903 909 934 -hsync +vsync

3. Type xrandr --newmode "1440x900_60.00" 106.50 1440 1528 1672 1904 900 903 909 934 -hsync +vsync

4. Type xrandr --addmode VGA1 1440x900.

5. Check you resolution om System -> Preferences -> Monitor.

Extraordinary Drupal Architecture

2010-04-16T10:24:00.005+07:00

You may know that Drupal is CMS but you may don't know its Framework can enable your Drupal website into Large Scale Website which is able to publish your own API and share your API to your website partner and your user.

In this post i only cover the architecture side of Drupal resulting 3 usages which one of them we usually use and know already; and some of them we never care and you always think it's hard to implement those things in Drupal.

The resulting usages from this architecture as follows:

1. Regular Web, This we know already and almost all CMS goal is for this. It outputs (X)HTML,RSS,XML requested by client via HTTP and drupal response using (X)HTML,RSS,XML

2. Web Services / Publish API Server, Many CMS today act only as client of web service however in drupal, we can publish API and act as Web service provider. And also drupal provides various format we can use such as JSON, JSONRPC, REST, SOAP, and XMLRPC.

3. Shell / Console application. You can run Drupal framework inside console. This application is really useful when you want to do large processing or manipulating nodes that need more time.

Those 3 features enable you web application into complex and excelent web application. Thanks to Drupal Community have provided to us those free and easy.

Got PageRank Now and listed first SERP

2010-04-05T14:56:00.003+07:00

Finally after doing some tunes in keyword density and doing little backlink campaign, This blog obtains various achievement in some major Search Engine like google, yahoo, and bing, however it still needs time to achieve localization search.

Fyi i had focused this blog on specific search term namely "web developer indonesia". Actually this search term is not popular term for search engine user. But this term is only my ego keyword to fight with other site using that search term.

Google

Bing

Yahoo

MySQL Timezone

2010-03-08T11:15:00.002+07:00

Accuracy of Date and time in Database Server is one of the most critical thing for any application you develop. They may cause trouble for your application when you have system with invalid date time information. MySQL depends on system OS timezone for its timezone.

mysql> show variables like 'time_zone';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| time_zone     | SYSTEM |
+---------------+--------+
1 row in set (0.00 sec)

This is a not problem when your target user of application is in the same timezone and but it will be a problem if it's different and you don't have root access to database server to be able to change based on your need.

Based on my experience a lot of developer rely on NOW() function in their query when they want to get current date time, and use date time data type and also don't know or even care the timezone configured in the server. So if your system time zone is "BST" and your target user is "Singapore" then the query contains NOW() function will follow the system timezone.

There are ways to avoid this trouble by using the following configuration.

1. Set session variable of time_zone firt time you open mysql connection. This happen when you don't have root access.

mysql> set session time_zone="+08:00";
Query OK, 0 rows affected (0.00 sec)

mysql> show session variables like "time_zone";
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| time_zone     | +08:00 |
+---------------+--------+
1 row in set (0.00 sec)

2. Set global variable of time_zone , this is server settings.

mysql> set session time_zone="+08:00";
Query OK, 0 rows affected (0.00 sec)

3. Avoid Now() function and use portable data type for storing datetime namely "timestamp"

Turning on Safe UPDATE and DELETE

2010-02-25T16:02:00.001+07:00

I had call last nite that one of my friend lose his content of table accidentally. He said that his developer forgot to place validation of WHERE statement in DELETE query. So the query sent to server will be like this:

DELETE FROM transactions WHERE 1;

Actually mySQL is able to handle this sort of issue, since it has validation to check whether query DELETE or UPDATE has missing WHERE statement However you need to activate it by turning on mySQL session variable namely sql_safe_updates;


mysql> select @@sql_safe_updates;
+--------------------+
| @@sql_safe_updates |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.00 sec)

mysql> set @@sql_safe_updates=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_safe_updates;
+--------------------+
| @@sql_safe_updates |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)

And now lets try to issue delete statement without WHERE


mysql> DELETE FROM transaction_table;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
mysql>

You can see mySQL will disallow any incomplete DELETE or UPDATE query.

Getting maximum value from group of rows without using MAX()

2010-02-21T09:34:00.008+07:00

MAX() is a SQL function that commonly used to get maximum value for each rows by grouping defined column inside a table. The following example "Get Max Score of Each Student" intended to show MAX() function commonly used.


mysql> show create table student;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                         |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`studentid` int(11) NOT NULL,
`subject` varchar(100) NOT NULL,
`score` float NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> select * from student;
+-----------+---------------+-------+
| studentid | subject       | score |
+-----------+---------------+-------+
|      1001 | Calculus      |  70.5 |
|      1001 | Mathematics   |  93.5 |
|      1001 | C Programming |   100 |
|      1002 | Mathematics   |  50.5 |
|      1002 | C Programming |  60.5 |
|      1002 | Calculus      |  40.5 |
|      1003 | Calculus      |   100 |
|      1003 | Mathematics   |  60.5 |
|      1003 | C Programming |    90 |
+-----------+---------------+-------+
9 rows in set (0.00 sec)

mysql> select studentid, max(score) as high_score from student GROUP BY studentid;
+-----------+------------+
| studentid | high_score |
+-----------+------------+
|      1001 |        100 |
|      1002 |       60.5 |
|      1003 |        100 |
+-----------+------------+
3 rows in set (0.03 sec)

However, Somtimes we may find an issue from above result query when we want to know "which subject for each student having highest score ?". We may think to modify the query by adding new column in the query or inside group by.


mysql> select studentid, subject, max(score) as high_score from student GROUP BY studentid;
+-----------+-------------+------------+
| studentid | subject     | high_score |
+-----------+-------------+------------+
|      1001 | Calculus    |        100 |
|      1002 | Mathematics |       60.5 |
|      1003 | Calculus    |        100 |
+-----------+-------------+------------+
3 rows in set (0.00 sec)

You can see above query is completely wrong result. And now see the result below by grouping 2 column. And this is also wrong.


mysql> select studentid, subject,  max(score) as high_score from student GROUP BY studentid, subject;
+-----------+---------------+------------+
| studentid | subject       | high_score |
+-----------+---------------+------------+
|      1001 | C Programming |        100 |
|      1001 | Calculus      |       70.5 |
|      1001 | Mathematics   |       93.5 |
|      1002 | C Programming |       60.5 |
|      1002 | Calculus      |       40.5 |
|      1002 | Mathematics   |       50.5 |
|      1003 | C Programming |         90 |
|      1003 | Calculus      |        100 |
|      1003 | Mathematics   |       60.5 |
+-----------+---------------+------------+
9 rows in set (0.00 sec)

There is a solution can be achieved without using SQL MAX() Function. And we are going to use LEFT JOIN to the same table:

SELECT s1.studentid, s1.subject, s1.score high_score
FROM student s1
LEFT JOIN student s2 ON s1.studentid = s2.studentid AND s1.score <> s2.score WHERE s2.studentid IS NULL;


mysql> SELECT s1.studentid, s1.subject, s1.score high_score
  -> FROM student s1
  -> LEFT JOIN student s2 ON s1.studentid = s2.studentid AND s1.score <> s2.score WHERE s2.studentid IS NULL;
+-----------+---------------+------------+
| studentid | subject       | high_score |
+-----------+---------------+------------+
|      1001 | C Programming |        100 |
|      1002 | C Programming |       60.5 |
|      1003 | Calculus      |        100 |
+-----------+---------------+------------+
3 rows in set (0.01 sec)

Now you have the correct result. Student 1001 and 1002 have highest score at C Programming and Student 1003 is at Calculus.

Removing page from google index using Webmaster tools

2010-02-09T17:35:00.007+07:00

Sometimes you may want to exclude some pages from google index or remove those pages from google index. Google has provided great and free tools for webmaster to manage interaction between our site and google search namely Webmaster Tools. With one of these tools you are able to remove your page from google index by following these step:

1. login http://www.google.com/webmaster and choose your registered site

2. Click the left menu Site configuration > Crawler access and Click Remove URL

3. Click New removal request

4. Choose your type of removal (in this post i choose "Individual URLs" and press Next

5. Enter the page or image you want remove for example : 2008_05_01_archive.html and press Submit. Your page or email will be listed with pending status and after this step you just wait google to remove your page from its index.

How if i got wrong url ? you can delete before it's excluded by google and you also to reinclude the page to its index with reinclude button. These 5 steps also need another simple action required by google to identify pages to be excluded namely you must add simple meta script to the page or to your robot.txt file otherwise google will deny your request.

Meta tag

<meta content='noindex' name='robots'/>

Robot.txt

Disallow: 2008_05_01_archive.html

Drupal works perfect with Load Balancer

2010-01-11T13:02:00.004+07:00

Scaling website means the website is getting more and more traffic from visitor. One of steps to scale our web server infratructure is to implement Load Balancer and adding more web servers behind it. This help increasing concurrent connection and response time from our web server.

Many Website CMS powered when adding Load Balancer they fail to keep the user state (session/cookie) for example when user login, servers should keep user state until the user loguot or the state expire. On the other hand, Drupal works perfect and no need to patch any script of it when Load Balancer implemented.

Why?

Drupal store user session-id in central database rather than store it in each webserver as a plain text file. How can drupal do it ? Since PHP give user capability to override session handler, Drupal override php session handler in /includes/session.inc.

PHP provides the following function to be able to control our session.


session_set_save_handler('sess_open', 'sess_close', 'sess_read', 'sess_write', 'sess_destroy_sid', 'sess_gc');

sess_open, sess_close, sess_read, sess_write, sess_destroy_sid and sess_gc are callback function that drupal implemented. Those functions are available in include/session.inc

Enabling HTTPS on Drupal User Login using htaccess

2010-01-11T11:28:00.006+07:00

You may find new requirement in your web project that require Drupal to use HTTPS when user login to the website. Fortunately this is hard to achieve by creating new custom module extention since all scripts related to user login and authentication/authorization written inside core module namely user.module.

I have trick to do this without hacking, touching core module or even create custom module. I use .htaccess to automate redirection only for certain drupal login url pattern from HTTP to HTTPS and HTTPS to HTTP.

Replace these scripts


RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^(.*)$ index.php?q=$1 [L,QSA]

with the following scripts:


#Redirect all non user url from https to http
RewriteCond %{SERVER_PORT} ^443$
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteCond $1 !^/user
RewriteRule ^(.*)$ http://%{HTTP_HOST}/$1 [R=301,L]

#Redirect to Referer when user login
RewriteCond %{SERVER_PORT} ^80$
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteCond $1 ^user
RewriteCond $1 !^user/register
RewriteCond $1 !^user/(.+)/edit
RewriteRule ^(.*)$  https://%{HTTP_HOST}/$1?dest=%{HTTP_REFERER} [R=301,L]

Project Documentation Template

2010-01-07T17:51:00.004+07:00

Documentation is one of hard part of web development project however if we manage it carefully and having nice template this work will be easier. There is ReadySet, an Opensource template can be used to help our documentation more structure and neat.

http://readyset.tigris.org/

The Best Scrum Video in 2009

2010-01-07T13:39:00.006+07:00

I think this is the best Scrum video i ever watched.

Cache that Web Developer Must Know

2010-01-07T11:27:00.009+07:00

Previously I would like to post and share about Cache (What, How, and Why). However i found very interesting and really useful posts regarding to the Cache. Here are definition, Technique and algorithm that web developer must know.

1. Introduction of Cache and the algorithm.

http://javalandscape.blogspot.com/2009/03/intro-to-cachingcaching-algorithms-and.html

2. HTTP Cache using HTTP Header to improve request and reponse cycle

http://www.ibm.com/developerworks/web/library/wa-aj-cache/index.html?ca=drs-

http://www.mnot.net/cache_docs/#PROXY

3. Technique that usually implemented in cache

http://www.mysqlperformanceblog.com/2006/08/08/caching-techinques

4. Cache Comparison such as APC, Memcache, MySQL, and File based cache.

http://www.mysqlperformanceblog.com/2006/08/09/cache-performance-comparison/

Building Continous Website Solution.

2009-12-26T23:58:00.003+07:00

This holiday i use to think and do a research about how to build continuous Website solutions that can be delivered to anyone who want their website to be as follows:
Scalable, Secure, High Performance, Usable, Accessible, and Profitable. And of course i know there are already a lot people living in Web Development. However my motivation to do this research cause many peoples still believe that website solutions are :

1. The same as software solution.

I saw some companies still use System Development Life Cycle it means after maintenance(final) phase they let the web site dead with none visit it. They don't even know how to drive traffic to their website and have strong faith that is "as long as they build website with unique idea the search engine will find us and user will visit their website"

2. 85% of building website are thinking about coding, performance, platform, operating system, and database system.

They always say one of this "PHP is slower than Ruby, Ruby is slower than Java, Java is slower than C/C++, C/C++ is slower than Assembly". If We still consider micro micro seconds Why don't we code the web using assembly ?

3. Only talk and focus on one of these : usability, user interface, content generation, accessibility, online marketing.
"Too many doing inbound link campaign then you forgot the content quality of your website. This means you are doing search engine spamming".

So, What is Continuous Web Solution ?.

The solution having continuity to achieve the client's goal. There are 3 simple stages namely

1. Planing

This stage needed to set the goal or objectives. Reaching the goal you need resources like right people, proper schedule, and proper budget.

2. Developing

Coding is one of the tasks for this stage and in this stage you can use well known software development process like TDD or BDD. Before coding there must be some people doing and thinking about application architecture and design, usability, accessibility, high quality content, and keyword research.

3. Growing & Monitoring

This is online marketing thingy like driving traffic using email marketing, search engine marketing, social media, and affiliate marketing. This stage also used to monitor each KPI set, performance/bug and sales volume of their website.

These continuous solutions i believe can help client rather than hire each stages partially.

You are not user !!!

2009-12-10T12:06:00.007+07:00

You may be like me facing many persons that have different background when building website. There are product owner, marketing, designer, and of course the developer.In fact, they have the same goal namely "making successful website". However process to achieve that is not as simple as we thought.

Every person involved here have different roles and responsibilities such as for developer, they have responsibilities to translate requirement into high quality of code, and maintain those code. For Marketing, they do campaign by promoting a website either using offline or online activity and of course they must monitor the ROI.

There is one thing that i always find and really sad is some person from one of those group pretending to be user. This group seem really know and have expertise about web user and they always request to change some part of website without any reason and any data to be proved that one or more of part of website is sux. Sometimes this happens if one of the following reasons happen:

1. None visit the website and those group of person start blaming production and user experience team. Actually this is marketing issue, not related to programming, ajax, or css or We may do wrong advertisement like doing advertisement on Radio. There are many marketing channel can be used here to drive more traffic such as SEM, PPC, Social Media, and Affiliation.

2. ROI or defined target can not be achieved and they also will start blaming production and user experience team. We can blame user production & experience team if bounce rate was so high (depend highly on your navigation) or after doing conversion and A/B Testing. Otherwise this is also marketing issue

3. There is "always-change" passions in decision maker from those group. This caused too many browsing experience so when they find something look nice on internet and they want to add that to the website like doing revamp, changing the flow, etc.
I think this is stupid idea if uncontrolled. Cos this can break indexed page and your rank on search engine SERP.

Facing this type of person you need extra extra power and work however if you don't have power to control it, will be better if you quit asap from them.

Really good related article to read:

http://www.sitepoint.com/blogs/2009/12/09/designing-for-conversion/

http://www.conversionrate.net/55-google-website-optimizer-tips

Why bounce rate was so high ?

2009-11-29T21:08:00.000+07:00

This question is always issued when someone see this rate on their web analytic. However before you judge to revamp your website, better you think on yourself why this can happen. Honestly i experienced this problem mostly in monitoring website growth. As technical person we can analyze this rate based on the other related data and not only feelings involved. You need to consider this thing before making changes on your system.

1. Content, as long as your content is sux or limited whoever visit your website will bounce automatically. I believe only robot love your website. This mostly is user from search engine.

2. Navigation structure, For example you have popular trafficked website abc123.com and you want to grow another new domain def456.com for different niche or business model. You put your new domain as part of your main menu in abc123.com. This, of course could generate high bounce rate especially when content not ready or only small number of content.

Finally, you can decide what you need to do.

Drupal, Joomla, and Wordpress World Trends

2009-11-25T22:26:00.006+07:00

Lately we must hear that whitehouse.gov website migrate to Drupal. This is Big Win for Drupal Community especially for Open Source CMS world. However there are many critics posted saying that is disaster. Besides whitehouse.gov Drupal this years also regains Packt 2009 Best Open Source PHP CMS.

I am wondering from which side and what reason they choosed Drupal. As Web Developer Drupal is the best among other CMS like Joomla or Wordpress. Drupal has great feature as follows :

1. Huge amount of community.
2. Availability of Modules.
3. Have Commercial Support.
4. Easy to extend or create new custom module.
5. Can work with MySQL and Postgresql.
6. Many high profile websites powered by Drupal.
7. Open Source.

But how about for the people don't know about coding or PHP ? Let's see the trend of 3 popular CMS Drupal, Joomla, and Wordpress using Google Trend. This trend reflects how many people from google search try to find information about those 3 CMS.

Based on the Trend above, Drupal is 3rd. so Can i say "Drupal is only friendly CMS for web developer who know about coding and PHP" ?

Ups .. Indonesia seems biggest users of Joomla and Wordpress.

Utilizing Google Search To Search Keyword From Content of a Website

2009-11-18T12:01:00.008+07:00

Sometimes you may have favorite blogs having good posted content and you want to search something that may be useful or needed for you. Almost all of the blog powered by some blog engines such as wordpress, drupal and any other blog engine have a search box feature to enable visitor to search the content inside. However you can't rely on this search since most of the result appeared is not what you expected execept that blog affiliates google custom search like mashable.com have. Why the content results is not what you expected ? this caused by the weakness of relevance, synonyms and other common search engine features and fyi this search depends heavily on simple database query utilizing "LIKE %%' syntax.

Google Search can help you to perform a search deeply to specific website you want with the power of google in their features like page rank, relevance, synonim, more targeted content etc.

In this example, i use uxbooth.com site since this site is so many talks about Usability then we test their Search Usability :-p.

I would like to search with "long tail" and "usability" keyword. See what happened in their search result and try to click top 5 links from the result and check whether the search term you type inside the content.

Let's compare those with the google result where previously i typed site:http://www.uxbooth.com usability and site:http://www.uxbooth.com long tail into google search box.

You see rite ? Google has better result and targeted whether you try to click the link result their should be information about the search term you typed in.

Conclusion

Thank to google, they have better result that mean less time needed to search useful information inside specific website. Don't forget to put "site:url" and followed with the keyword you wanna search when you want to search deeply to specific website using Google and you can combine that syntax with other useful google search syntax as explained detail in Google help

How to handle concurrency in PHP

2009-10-14T14:33:00.009+07:00

I know this is too academic to be explained further however this post is intended to be the practical way about how to handle concurrency issue in PHP+MySQL.
Sometimes you will face the problem when you develop application where there is a shared resource and used concurrently by many application process runing. If we are not aware about concurrency then that shared resouce could be broken or miscalculated when more than one process of your application hit the shared resource.

For java, C/C++/C#, Visual Basic and even classic ASP developer, i believe they have experienced about concurrecy and ACID feature of their database like MS-SQL, Oracle, PostgreSQL when implementing into business application like finance, inventory, accounting, etc but I found difficulty when explaining concurrency for PHP developer that only have used PHP+MYSQL(MyISAM) and has religious faith to certain Content Management System or Content Management Framework like Drupal, Joomla, and any other that relying on MySQL(MyISAM).

An example i use here is simple e-commerce stock control of product item. Stock plays important role here to control users when they try to buy/reserve/claim a product item. Logically user allowed to buy/reserve a product item if the product item is still available in the stock. Unless they will see or notified by system that product item is out of stock or sold out.

Lets have alook to the script


<?php

define('MYSQL_USER', 'testuser');
define('MYSQL_PWD', 'password');
define ('MYSQL_HOST', '192.168.56.2');
define('MYSQL_DB', 'concurrency');


function db_connect() {
static $db;
if (!isset($db)) {
  $db =new mysqli(MYSQL_HOST,MYSQL_USER, MYSQL_PWD, MYSQL_DB);
   if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
   }
}
 return $db;

}


$stock=db_connect()->query('select ammount from stock where productid=1 and ammount > 0');
$row=$stock->fetch_row();

if ($row[0]>0){

  db_connect()->query('UPDATE stock SET ammount=' . ($row[0] - 1)  .' WHERE productid=1');
  db_connect()->query('INSERT INTO order_product VALUES (null, 1,uuid(),1)');

  print '<h2>SUCCESS</h2>';
}else {

 print '<h2>STOCK is OUT</h2>';
}

?>

The problem of example(script) above will appear when many user concurrently try to buy the same product. Let's say we have 5 product item in our stock and the number of user want to buy is 10, 50, 100, or 500 concurrently. Do you still rely on this script:


<?php

stock=db_connect()->query('select ammount from stock where productid=1 and ammount > 0');
$row=$stock->fetch_row();

if ($row[0]>0){

db_connect()->query('UPDATE stock SET ammount=' . ($row[0] - 1) .' WHERE productid=1');
db_connect()->query('INSERT INTO order_product VALUES (null, 1,uuid(),1)'); //uuid function used to fake userid

 print '<h2>SUCCESS</h2>';
}else {

 print '<h2>STOCK is OUT</h2>';
}

?>

I always find some developer believe that the script above is OK and they never think about concurrency and they believe the process will be sequential automatically :-p. Lets do concurrent request to above script using apache benchmark (ab) :

Initial data of stock:


mysql> select stockid, productid from stock;
+---------+-----------+---------+
stockid  productid  ammount
+---------+-----------+---------+
      1          1        5
+---------+-----------+---------+
1 row in set (0.00 sec)

Initial data of Order of product:



mysql> show create table order_product \G
*************************** 1. row ***************************
      Table: order_product
Create Table: CREATE TABLE `order_product` (
 `orderid` int(4) NOT NULL AUTO_INCREMENT,
 `productid` int(4) NOT NULL,
 `uid` char(36) NOT NULL,
 `orderstatus` int(4) NOT NULL DEFAULT '0',
 PRIMARY KEY (`orderid`),
 KEY `productid` (`productid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)



mysql> select * from order_product;
Empty set (0.00 sec)

lets flood using ab and set 50 concurrent requests:


rizky@Elmo:~$ ab -c 50 -n 100 http://www.rb.com/test/db.php
This is ApacheBench, Version 2.3 <$Revision: 655654 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/

Benchmarking www.rb.com (be patient).....done


Server Software:        Apache/2.2.11
Server Hostname:        www.rb.com
Server Port:            80     

Document Path:          /test/db.php
Document Length:        19 bytes

Concurrency Level:      50
Time taken for tests:   0.887 seconds
Complete requests:      100    
Failed requests:        95     
  (Connect: 0, Receive: 0, Length: 95, Exceptions: 0)
Write errors:           0                       
Total transferred:      31175 bytes             
HTML transferred:       2375 bytes              
Requests per second:    112.77 [#/sec] (mean)   
Time per request:       443.362 [ms] (mean)     
Time per request:       8.867 [ms] (mean, across all concurrent requests)
Transfer rate:          34.33 [Kbytes/sec] received                

Connection Times (ms)
             min  mean[+/-sd] median   max
Connect:        0    4   5.1      2      27
Processing:    10   22  11.9     20      96
Waiting:       10   22  11.1     19      88
Total:         14   26  15.3     22     115

Percentage of the requests served within a certain time (ms)
 50%     22                                          
 66%     25                                          
 75%     29                                          
 80%     30                                          
 90%     34                                          
 95%     41                                          
 98%     99                                          
 99%    115                                          
100%    115 (longest request)

Check to our stock now:


mysql> select * from stock;
+---------+-----------+---------+
stockid  productid  ammount
+---------+-----------+---------+
      1          1        0
+---------+-----------+---------+
1 row in set (0.00 sec)

Check how many order of product we have now :


mysql> select * from order_product;
+---------+-----------+--------------------------------------+-------------+
orderid  productid  uid                                   orderstatus
+---------+-----------+--------------------------------------+-------------+
      1          1  71df191e-b889-11de-b26d-08002705b03f            1
      2          1  71edb0e6-b889-11de-b26d-08002705b03f            1
      3          1  71edbb9a-b889-11de-b26d-08002705b03f            1
      4          1  71edd79c-b889-11de-b26d-08002705b03f            1
      5          1  71eddf80-b889-11de-b26d-08002705b03f            1
      6          1  71ee06c2-b889-11de-b26d-08002705b03f            1
      7          1  71eec0da-b889-11de-b26d-08002705b03f            1
      8          1  71eee29a-b889-11de-b26d-08002705b03f            1
      9          1  71f1328e-b889-11de-b26d-08002705b03f            1
+---------+-----------+--------------------------------------+-------------+
9 rows in set (0.00 sec)

How come there are 9 orders ? should be 5 right ? cos you only have 5 product item in stock. For developers who believe the order must be 5 using this methods i think need to change you view and need more books to eat. Anyway i think this's fair cos i have seen a lot of PHP book never explain about concurrency and off cource the PHP itself it's not intended to be multithreaded languange.

Do you know PHP IS ALSO UNIX? YES. So We can use POSIX UNIX feature like semaphore to handle this concurrency problem. How ? Lets change the script using semaphore. You can read about semaphore, mutex, or race condition in unix programming book or freely at wikipedia.


<?php
define ('MYSQL_USER', 'testuser');
define('MYSQL_PWD', 'password');
define ('MYSQL_HOST', 'localhost');
define('MYSQL_DB', 'concurrency');


function db_connect() {
static $db;
if (!isset($db)) {
         $db =new mysqli(MYSQL_HOST,MYSQL_USER, MYSQL_PWD, MYSQL_DB);
          if (mysqli_connect_errno()) {
           printf("Connect failed: %s\n", mysqli_connect_error());
           exit();
          }
}
 return $db;

}

function openlock() {
$SEMKEY='123456'; //do you think better to change ftok()
$seg = sem_get( $SEMKey, 1, 0666, -1) ;
return $seg;

}
function releaselock($sem){
sem_release($sem);
}

function getlock($seg) {
return sem_acquire($seg);

}


$sem=openlock();
if (getlock($sem)) {
$stock=db_connect()->query('select ammount from stock where productid=1 and ammount > 0');
$row=$stock->fetch_row();

if ($row[0]>0){

  db_connect()->query('UPDATE stock SET ammount=' . ($row[0] - 1)  .' WHERE productid=1');
  db_connect()->query('INSERT INTO order_product VALUES (null, 1,uuid(),1)');

    print '<h2>SUCCESS</h2>';
}else {

 print '<h2>STOCK is OUT</h2>';
}
releaselock($sem);
}else {
print 'TRY AGAIN
';

}

?>

Semaphore is one of reliable way to protect variable or shared resource to be used simmultanously by many process although it does not prevent resource deadlock and it does not provide rollback or ACID feature.

Other way you can do are by using mysql transactional storage engine like innodb and mysql get_lock() function.

Using transaction in innodb:
I think this is more reliable than other since transaction has ACID features that guaranty your data to be more consistent and durable.


<?php

define('MYSQL_USER', 'testuser');
define('MYSQL_PWD', 'password');
define ('MYSQL_HOST', '192.168.56.2');
define('MYSQL_DB', 'concurrency');


function db_connect() {
static $db;
if (!isset($db)) {
  $db =new mysqli(MYSQL_HOST,MYSQL_USER, MYSQL_PWD, MYSQL_DB);
   if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
   }
}
 return $db;

}


db_connect()->query('SET autocommit=0');
db_connect()->query('START TRANSACTION');



$stock=db_connect()->query('select ammount from stock where productid=1 and ammount > 0 FOR UPDATE');
$row=$stock->fetch_row();

if ($row[0]>0){
  db_connect()->query('UPDATE stock SET ammount=' . ($row[0] - 1)  .' WHERE productid=1');
  db_connect()->query('INSERT INTO order_product VALUES (null, 1,uuid(),1)');
  db_connect()->query('COMMIT');
    print '<h2>SUCCESS</h2>';
}else {

 print '<h2>STOCK is OUT</h2>';
}


?>

Using mysql get_lock function:
I like this function we can define the timeout to acquire the lock in one of the argument.


query('select get_lock("mylock", 10)');
$lock=$mylock->fetch_row();
return $lock[0];
}
function releaselock(){
db_connect()->query('select release_lock("mylock")');

}

function isfreelock() {
$mylock=db_connect()->query('select is_free_lock("mylock")');
$lock=$mylock->fetch_row();
return $lock[0];
}



if (getlock()) {
$stock=db_connect()->query('select ammount from stock where productid=1 and ammount > 0');
$row=$stock->fetch_row();

if ($row[0]>0){

  db_connect()->query('UPDATE stock SET ammount=' . ($row[0] - 1)  .' WHERE productid=1');
  db_connect()->query('INSERT INTO order_product VALUES (null, 1,uuid(),1)');

  print '<h2&gtSUCCESS</h2>';
}else {

 print '<h2&gtSTOCK is OUT</h2>';
}
releaselock();
}else {
print '<h2>TRY AGAIN</h2>';

}

?>

You can test the script above using ab and you can set number of concurrent request you like.

Finally i have Drizzle installed

2009-10-09T13:57:00.002+07:00

After figthing with compilation on my Jaunty Ubuntu then i can have my own drizzle.

Start the server:


rizky@Elmo:/usr/local$ sudo drizzled --no-defaults --user=drizzle  --basedir=/usr/local/  --datadir=/usr/local/drizzle/data/ >> /usr/local/drizzle/drizzle.err &
[1] 9792
rizky@Elmo:/usr/local$ InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins.
091009 13:55:52  InnoDB: highest supported file format is Barracuda.
091009 13:55:52 InnoDB Plugin 1.0.3 started; log sequence number 48107
Listening on 0.0.0.0:4427
Listening on :::4427
drizzled: Forcing close of thread 0  user: '(null)'
drizzled: ready for connections.
Version: '2009.09.1144' Source distribution (trunk)

Connect as client


rizky@Elmo:~$ drizzle
Welcome to the Drizzle client..  Commands end with ; or \g.
Your Drizzle connection id is 2
Server version: 2009.09.1144 Source distribution (trunk)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

drizzle> select version();
+--------------+
| version()    |
+--------------+
| 2009.09.1144 |
+--------------+
1 row in set (0 sec)

drizzle>

There are alot different in feature than mysql http://drizzle.org/wiki/MySQL_Differences

How many tables in a certain database ?

2009-09-29T10:33:00.004+07:00

My friend asked me regarding how to count the number of table in a certain MySQL database. As we might know that we can do this using many ways however that i believe there is a fastest way to do this rather than making script or parsing SHOW TABLES statement.

MySQL provides Information_Schema containing meta information about your databases, tables, fields, index, privileges, views, and many more meaning we can query it to capture the number of table in a database using the following query :


SELECT count(*) FROM `TABLES` WHERE TABLE_SCHEMA='name-of-database'

When My Jasper Server Got Trouble

2009-09-11T09:21:00.008+07:00

As Business Intelligence tool, it's so frequently used by my company's client to keep track daily, monthly activities in their portal such as membership growth, number of each classifieds ads, trend of specific ad category and many more. These functionalities easily generated in Jasper Server.

Recently i face some error reported with the following error appears when user try to login:


java.lang.NoClassDefFoundError: Could not initialize class org.springframework.webflow.util.RandomGuid
...

root error: org.springframework.web.util.NestedServletException: Handler processing failed; nested exception is java.lang.NoClassDefFoundError: Could not initialize class org.springframework.webflow.util.RandomGuid

From the error text above i previously tot that it caused by no class loaded in lib (library) directory or undefined $CLASSPATH where in Java world there are two kind of classes related exception that i often face : java.lang.ClassNotFoundException and java.lang.NoClassDefFoundError. But when i went trough to RandomGuid of spring webflow source code :
http://www.docjar.com/html/api/org/springframework/webflow/util/RandomGuid.java.html
especially line #130

125   static {
126     secureRandom = new SecureRandom();
127     long secureInitializer = secureRandom.nextLong();
128     random = new Random(secureInitializer);
129     try {
130       id = InetAddress.getLocalHost().toString();
131     }
132     catch (UnknownHostException e) {
133       e.printStackTrace();
134     }
135
136   }

There's attempt to get Localhost by invoking InetAddress.getLocalHost().toString() then i believe the problem must be caused by undefined hostname in /etc/hosts so i put the following definition

127.0.0.1 localhost.localdomain localhost

on /etc/hosts of jasper server and i can login now without error prompted.

Profiling your query in MySQL

2009-09-04T08:23:00.003+07:00

This week i have a new task to plan to migrate some data by merging it from one site reebonz.com.hk to the other one namely reebonz.com. Reebonz is online exclusive shopping site where everyday have branded events running in.

There are two major requirements given of this migration namely without down time , only take < 2 hours and no fail. Since there are many table has relationship each other so i can't just copy or insert directly to the destination tables. What i will do is heavily depended on all query injection using INSERT INTO ... SELECT statement where the inside its SELECT statement there will be JOIN some table either INNER, LEFT or OUTER.

The two problems may be raised that i also currently face are the engine used is not transaction enabled engine, so i can't do atomic operation like COMMIT or ROLLBACK and i am afraid that the table could possibly be corrupted. I need good plan to make me confident for doing this. One of the plan that i do before executing the merging process is Profiling all query i use. MySQL has a good feature for this besides EXPLAIN command as query execution plan. To enable profiling in MySQL you can issue this command: Check the status first


mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set (0.00 sec)

That means the profiling need to be activated by issuing


mysql> set @@session.profiling=1;
Query OK, 0 rows affected (0.01 sec)

Then I execute my query:

SELECT hk.uid, hk.email_notification_promo
...

FROM (
SELECT b1.mail, b2.activation_status com_status, FROM_UNIXTIME( b1.created ) com_created, FROM_UNIXTIME( b1.access ) com_access
FROM users
..
WHERE b1.uid = b2.uid
AND (
...
)
)com RIGHT OUTER JOIN (

SELECT a1.*,
...
FROM ..
WHERE a1.uid = a2.uid AND (
...
)
) hk
ON com.mail = hk.mail WHERE com.mail IS NULL

For first time this query will take so long. After that query successfully executed then you can issue this command:


mysql> show profiles;
+----------+-------------+------------------------
| Query_ID | Duration | Query
+----------+-------------+-------------------------
| 1 | 0.00810900 | SHOW SESSION variables
| 2 | 0.00095100 | SELECT b1.mail, b2.activ
FROM users b1, bs_user b2
WHERE b1.uid = b2.uid
AND (
b1.mail NOT LIKE '%@efusion-group.com'
AND b1.mail NOT LIKE '%@efusion.com.sg'
AND b1.mail NOT LIKE '%@reebonz.com'
AN |
| 3 | 17.10912400 | SELECT hk.uid, hk.name,
| 4 | 0.00036000 | show profiles 2

and then


mysql> show profile all for query 2
-> ;
+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+--------------+-------------+
| Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function | Source_file | Source_line |
+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+--------------+-------------+
| starting | 0.000289 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL |
| checking query cache for query | 0.000408 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 4 | 0 | send_result_to_client | sql_cache.cc | 1262 |
| freeing items | 0.000195 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | mysql_parse | sql_parse.cc | 5827 |
| logging slow query | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | log_slow_statement | sql_parse.cc | 1628 |
| cleaning up | 0.000036 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | dispatch_command | sql_parse.cc | 1595 |
+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+--------------+-------------+

You can see from the result above the time need to execute the query and also cpu usage, however currently it has no memory usage feature but i can rely on this information for my migration purpose.

Another tune for this process is by disabling the slow query log and enabling query cache. The process logging slow query above no need to run and the query check the availability cache first if exist so no need to execute fully just grab from cache.

Our First Drupal Mashup Lauched

2009-08-18T13:38:00.001+07:00

After fighting with integration of many web service, finally we can launch our first Drupal mashup application for Singapore Community Site home http://home.tampineseast.org.sg. This site is collective integration of the following web services or APIs:

1. Authentication 3rd parties service with Facebook Connect, Google OpenID, and Yahoo Open ID.

2. Video with Youtube.

3. Geolocation with Google Map and Google Earth.

4. Directory Service with Yellow pages Singapore.

5. Photo with Flickr.

6. Comment and Status with Facebook.

Finding other websites using the same IP with You

2009-08-12T06:25:00.001+07:00

Sometimes when we need to host our website to hosting company, we are always wondering how good the hosting company server and how good they manage bandwidth and also how many website reside on single IP/Server.

Bing.com has a feature making you possible to know your neighbors that use the same IP/server by invoking the following command in its search box:

IP:[the ip address]

Here is example: i want to know who else use the same ip with myesia.com (this is website of CDMA operator in indonesia).

First i need to know the IP of myesia.com by issuing nslookup :

C:\Users\Rizky>nslookup -type=A myesia.com
...

Non-authoritative answer:
Name: myesia.com
Address: 203.130.198.38

then i found 203.130.198.38 used to served myesia.com and finally i go to bing.com to search:

It looks like a lot of website reside there. Is this good for you ? It depends on how your hosting company implement their resource/server and how you expect from this website, if they implement clustering then that will be OK for one IP serving many websites. On the other hand you will share bandwith, disk, and memory with your neighbor. Or it's also OK if your website has no traffic :-P.

Reducing PHP Memory Usage

2009-08-08T14:24:00.002+07:00

Previously i post about how to know php memory usage in that post i stated that php has useful function to retrieve memory usage of each request made by browser or http client. For simple php script the memory used is really small but when the application getting complex such as CMS , DMS or even Portal, the memory usage of these type applications is getting larger and larger ( 64 - 128 M).

There is a way provided to reduce memory usage by implementing opcode cache.

What is opcode cache ?

Common process occured when running php script

LEXING ---> PARSING ---> COMPILING --->RUN

(1) (2) (3) (4)

When the opcode cache enabled we omit process LEXING (1), PARSING(2), and COMPILING(3) and this cause memory reduction.

In PHP there are well known opcode cache that can be used such as apc, xcache and accelerator. For Windows user especially XAMMP you can just enable it within php.ini and for linux we can do yum / apt-get / compile manually. Currently i use eaccelerator and it is true, can reduce memory 40 -50% and make one of my clients portal run more scalable especially for HTTP server.

MySQL Timestamp saves us

2009-07-30T13:31:00.000+07:00

Previously i never care about timestamp datatype, The two things that i know about it just saving date data type into an efficient manner (INTEGER) and the other thing is it has limitation of range date can be stored (The range is '1970-01-01 00:00:01' UTC to '2038-01-09
03:14:07' UTC).

Currently we are planning to change our infrastructure strategy where previously we host and manage servers by ourselft or build our own data center internally to Cloud Environment (one of rackspace services). we are running our site for south east asia, hongkong, and australia users and a problem appear regarding date/time differences between the countries i mentioned and the country (US) of cloud service resides. And some of our application inside depend heavily on date and time.

The Cloud service we have no ssh access/root access of either in the OS or MySQL so both follow (US) timezone. This is problem for us and we try to think how can we present date info when our singaporean user visiting our site.

Lucky, this site powered by drupal where almost all table in drupal having datetime type are using timestamp data type. However there a few table built by our developers using conventional datetime data type.

Here is example node table from drupal. There are some field for storing datetime in INTEGER format namely created, accessed, and changed.


mysql> show create table node \G

*************************** 1. row ***************************

       Table: node                                           

Create Table: CREATE TABLE `node` (                          

  `nid` int(10) unsigned NOT NULL AUTO_INCREMENT,            

  `vid` int(10) unsigned NOT NULL DEFAULT '0',               

  `type` varchar(32) NOT NULL DEFAULT '',                    

  `language` varchar(12) NOT NULL DEFAULT '',                

  `title` varchar(255) NOT NULL DEFAULT '',                  

  `uid` int(11) NOT NULL DEFAULT '0',                        

  `status` int(11) NOT NULL DEFAULT '1',                     

  `created` int(11) NOT NULL DEFAULT '0',                    

  `changed` int(11) NOT NULL DEFAULT '0',                    

  `comment` int(11) NOT NULL DEFAULT '0',                    

  `promote` int(11) NOT NULL DEFAULT '0',                    

  `moderate` int(11) NOT NULL DEFAULT '0',                   

  `sticky` int(11) NOT NULL DEFAULT '0',                     

  `tnid` int(10) unsigned NOT NULL DEFAULT '0',              

  `translate` int(11) NOT NULL DEFAULT '0',                  

  PRIMARY KEY (`nid`),                                       

  UNIQUE KEY `vid` (`vid`),                                  

  KEY `node_changed` (`changed`),                            

  KEY `node_created` (`created`),                            

  KEY `node_moderate` (`moderate`),                          

  KEY `node_promote_status` (`promote`,`status`),            

  KEY `node_status_type` (`status`,`type`,`nid`),            

  KEY `node_title_type` (`title`,`type`(4)),                 

  KEY `node_type` (`type`(4)),                               

  KEY `uid` (`uid`),                                         

  KEY `tnid` (`tnid`),                                       

  KEY `translate` (`translate`)                              

) ENGINE=MyISAM AUTO_INCREMENT=2164 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

And another thing that make us easier that drupal has already made timezone module and perfectly fit with our requirement so we just configure the timezone for our drupal site then we dont have problem anymore regarding how to present date and time to our users and our application.

Finally i just can say, the timestamp is portable data for datetime when you need to build international web application. Whatever timezone you want you can dynamically set it for your application

Basic PHP Memory Usage

2009-07-26T04:54:00.006+07:00

Recently i am suffering out of memory at web server made and start swapping the process to the disk. This out of memory caused by many simultaneous connections and the apache configuration is not properly configured.

I tried to find the away and analyze the memory usage of my php web application. I found there are two ways to get memory usage in PHP/Apache and may be useful to reconfigure the web server as follows:

1. invoking php function memory_get_usage()


<?php
echo "Start App";

echo "First time memory allocated is:" . memory_get_usage() . "
";

/*
* Try to use memory by assigning variable $test_var

*/
$a = str_repeat("Hello", 4242);
$test_var=array( "Name" => "Rizky Rukmana",
              "Occupation" => "Development Manager"
             )  ;


echo "After assigning the memory allocated is: " . memory_get_usage() . "
";

?>

it will output:

Start App
First time memory allocated is:60416
After assigning the memory allocated is: 81920

2. watching RSS of each apache process by running the following command Fortunately the server is using MPM prefork:


~ www1# ps -ylC httpd --sort:rss

S UID PID PPID C PRI NI RSS SZ WCHAN TTY TIME CMD
S 0 2552 1 0 78 0 11388 7421 - ? 00:00:01 httpd
S 48 9021 2552 0 75 0 25316 11359 semtim ? 00:02:09 httpd
S 48 9900 2552 0 75 0 25540 11359 semtim ? 00:01:54 httpd
S 48 9374 2552 0 75 0 25548 11359 semtim ? 00:02:03 httpd
S 48 9772 2552 0 75 0 25572 11359 semtim ? 00:01:48 httpd
S 48 9882 2552 0 75 0 25580 11361 semtim ? 00:01:41 httpd
S 48 9637 2552 0 75 0 25592 11366 semtim ? 00:02:02 httpd
S 48 13613 2552 0 75 0 25624 11373 semtim ? 00:00:58 httpd
S 48 7749 2552 0 75 0 25688 11365 - ? 00:02:39 httpd
S 48 13619 2552 0 75 0 25688 11366 semtim ? 00:01:04 httpd

RSS is resident set size, the non-swapped physical memory that a task has used (in kiloBytes). (alias rssize, rsz).

From those options above i can collect the memory usage at web server and calculate to define maximum concurrent connection allowed and avoiding disk swapping.

Menjadikan Google sebagai Integrated Research Tools.

2009-06-04T10:36:00.001+07:00

Semua orang telah mengenal google sebagai search engine dengan kemampuan pencarian dan hasil pencarian yang menakjubkan. Namun banyak orang yang hanya mengetahui dan menggunakan google hanya untuk mencari sesuatu yang diinginkan dengan mensupply keyword-keyword yang pas.

Google memiliki berapa macam perangkat yang dapat membantu kita dalam menganalisa pengguna internet(demand) dan penyedia internet (supply).Perangkat tersebut diantaranya:

1. Google Trend,
Perangkat yang biasa saya gunakan untuk mengetahui trends dari search tertentu. Google Trend mampu menampilkan hasil berdasarkan negara dan per kota.

2. Google Keyword Tools,

Perangkat yang juga biasa saya gunakan untuk mencari search volume dari search term dan hasil bisa diklasifikasikan berdasarkan negara (local) dan global search volume. Sebenarnya perngkat ini digunakan para internet marketer untuk menganalisa search term yang akan mereka iklan kan dengan menggunakan Google Adwords.

3. Google Suggest,
Perangkat yang membantu kita untuk mengetahui search term yang biasa digunakan orang. Google menyimpan search term yang sering dicari dan menampilkannya untuk membantu pengguna search engine dalam mendefinisikan keywordnya.

4. Google Analytics
Perangkat yang digunakan untuk tracking pengunjung website seperti visits, page views dan tracking usability website seperi bounch rate. Walaupun perangkat ini tidak realtime memberikan data statistik dan memiliki jeda 3 jam - 1 harian namun perangkat ini sangat kaya akan keperluan analisa Key Performance Index dari sebuah website.

5. Google Alerts
Dengan perangkat ini kita dapat mengetahui conversation yang terjadi dari berbagai blog dan situs social media pada saat itu. Hanya dengan mensupply keyword yang hendak di track akan google akan mengirimi email notifikasi dari website-website yang cocok dengan keyword kita.

Kelima perangkat plus google search (local+internasional) memungkinkan kita untuk membuat Integrated Research Tool yang free, reliable, dan mudah.

New Pattern in Recruitment

2009-05-28T11:56:00.002+07:00

Last week i was so busy to interview some people for developers and team leaders position. These guys will be posted on our new team to develop something related to marketing and sales system. We expect to get experienced developer in PHP, CRUD operation on MySQL, PHP Framework like Zend, Javascript, and CMS like Drupal. However after filtering the application then we decide to remove Framework or CMS skill from our expectation cos we think they can learn asap when they get hired and i dont mind for this decision since we still have alot time to invest to it.

We have two processes when we recruit for technical/developer position namely written test (we give list od simple questions) and technical interview. If both ok then we recruit.

Actually there was what i am wondering that mostly of the applications and CVs they sent to me is intended to fill team leader positions and of course i dont mind cos i need to prepare some questions for them according to the level applied. Before conducting the interview previously i saw thoroughly their written test and their CV position cos i (and may be my bos) expect so much to this position.

OMG i saw some of them can't do written test and even wrong in writing select query :-P But ok, i still has faith cos i see some of them based on the CV have experience using other languange like java,vb or c. The interview begins and almost all of them has the same question and do you believe they have Almost the same answer ? :-P

1.Why do you think you are fit to the position you applied (teamleader) ?
[applicants] cos now i am system analyst or team leader.

2. How good are you in PHP ?
[applicants]Yes i know php.

3. Why your written test seems not php and more like javac#cvbsimple english ?
[applicants] Cos at momment we are not using php so forget.

4. What language you currently really really remember?
[applicants]java c# c

5. Do you know inhiritance (java) do you know polymorpsm (c#) do you know malloc() (c)?
[applicants] oh sorry currently i am involved in bussiness analyst part.

6. How do you manage when your developer ask you about PHP,SQL since you dont know about PHP, SQL ?
[applicants] Oh i am genius/Einstein i can learn fast.

7. Ok bye

I share my experience above cos i found new the pattern in this recruitment that someone want to get higher position but dont have competency at all. Finally, after consuming 3 weeks for recruiting process, there are 2-4 web developers/teamleader candidates that save me to end this process and they are really good in PHP and has design pattern knowledge.

Refresh: Ubuntu Package Management

2009-01-02T13:33:00.005+07:00

Tampaknya harus merefresh lagi memory mengenai ubuntu khususnya mengenai package management-nya karena terlalu banyak bermain Redhat Family : RHEL, Centos, dan Fedora.

Seperti yang kita ketahui di RedHat Family terdapat perintah rpm dan yum untuk package management-nya begitu juga dengan apa yang terdapat di Ubuntu ia memiliki tools/perintah berbeda yaitu dpkg dan apt-get dalam package management-nya.

Berikut ini command singkat yang biasa kita pergunakan sehari-hari dalam memanage
package di ubuntu:

dpkg

Perintah ini digunakan untuk meninstall package file tipe *.deb yang terdapat di local, network computer dan internet.

Install package aplikasi dengan perintah dpkg -i


rizky@Elmo:/var/cache/apt/archives$ sudo dpkg -i nginx_0.6.35-0ubuntu1_i386.deb
Selecting previously deselected package nginx.
(Reading database ... 103766 files and directories currently installed.)
Unpacking nginx (from nginx_0.6.35-0ubuntu1_i386.deb) ...
Setting up nginx (0.6.35-0ubuntu1) ...

Processing triggers for man-db ...
rizky@Elmo:/var/cache/apt/archives$

Remove/Uninstall package dengan perintah dpkg -r dan dpkg -P


rizky@Elmo:/var/cache/apt/archives$ sudo dpkg -i nginx_0.6.35-0ubuntu1_i386.deb
Selecting previously deselected package nginx.
(Reading database ... 103766 files and directories currently installed.)
Unpacking nginx (from nginx_0.6.35-0ubuntu1_i386.deb) ...
Setting up nginx (0.6.35-0ubuntu1) ...

Processing triggers for man-db ...
rizky@Elmo:/var/cache/apt/archives$

Note: Dengan perintah dpkg -r hanya menghapus file binary dan file configurasi tidak akan dihapus. Pada contoh unintall aplikasi nginx diatas, nginx memiliki file config terletak di /etc/nginx.


rizky@Elmo:/var/www$ ll /etc/nginx/
total 32
-rw-r--r-- 1 root root  909 2009-02-21 04:12 fastcgi_params
-rw-r--r-- 1 root root 2837 2009-02-21 04:12 koi-utf
-rw-r--r-- 1 root root 2223 2009-02-21 04:12 koi-win
-rw-r--r-- 1 root root 2944 2009-02-21 04:12 mime.types
-rw-r--r-- 1 root root  510 2009-02-21 04:12 nginx.conf
drwxr-xr-x 2 root root 4096 2009-09-30 11:23 sites-available
drwxr-xr-x 2 root root 4096 2009-09-30 11:19 sites-enabled
-rw-r--r-- 1 root root 3610 2009-02-21 04:12 win-utf
rizky@Elmo:/var/www$

Semua file didalam directory tersebut tidak akan terhapus selama anda menggunakan perintah dpkg -r nginx Untuk mengapusnya secara otomatis semua file yang berhubungan dengan nginx baik binary file dan config file atau Purge, kita dapat melakukannya dengan perintah dpkg -P


rizky@Elmo:/var/cache/apt/archives$ sudo dpkg -P nginx
(Reading database ... 103790 files and directories currently installed.)
Removing nginx ...
Stopping nginx: nginx.
Purging configuration files for nginx ...
Processing triggers for man-db ...
rizky@Elmo:/var/cache/apt/archives$

Check keberadaaan config file:


rizky@Elmo:/var/cache/apt/archives$ sudo dpkg -P nginx
(Reading database ... 103790 files and directories currently installed.)
Removing nginx ...
Stopping nginx: nginx.
Purging configuration files for nginx ...
Processing triggers for man-db ...
rizky@Elmo:/var/cache/apt/archives$

Status package

dpkg -s berguna untuk mengetahui status sebuah package apakah package tersebut sudah terinstall (installed), telah didelete (deinstall), atau purge.


rizky@Elmo:/var/cache/apt/archives$ dpkg -s nginx
Package: nginx
Status: purge ok not-installed
Priority: optional
Section: web

Perintah lain

dpkg -S, perintah ini sangat berguna untuk mengetahui asal package dari sebuah file.


rizky@Elmo:/etc/nginx$ sudo dpkg -S nginx.conf
nginx: /etc/nginx/nginx.conf

dpkg -L, perinta ini juga sangat berguna untuk mengetahui struktur directory dari package yang telah terinstall. Kita dapat langsung mengetahui dimana sebuah package menyimpan config, library dan binaru filenya.


rizky@Elmo:~$ sudo dpkg -L nginx
/.
/usr
/usr/sbin
/usr/sbin/nginx
/usr/share
/usr/share/doc
/usr/share/doc/nginx
/usr/share/doc/nginx/changelog.gz
/usr/share/doc/nginx/README
/usr/share/doc/nginx/copyright
/usr/share/doc/nginx/NEWS.Debian.gz
/usr/share/doc/nginx/changelog.Debian.gz
/usr/share/man
/usr/share/man/man1
/usr/share/man/man1/nginx.1.gz
/etc
/etc/nginx
/etc/nginx/sites-available
/etc/nginx/sites-available/default
/etc/nginx/sites-enabled
/etc/nginx/conf.d
/etc/nginx/fastcgi_params
/etc/nginx/koi-utf
/etc/nginx/koi-win
/etc/nginx/mime.types
/etc/nginx/nginx.conf
/etc/nginx/win-utf
/etc/init.d
/etc/init.d/nginx
/etc/logrotate.d
/etc/logrotate.d/nginx
/var
/var/log
/var/log/nginx
/var/lib
/var/lib/nginx
/var/www
/var/www/nginx-default
/var/www/nginx-default/50x.html
/var/www/nginx-default/index.html

apt-get, apt-cache

apt-get digunakan untuk installasi package aplikasi yang berada disebuah repository baik local, network atau central repository ubuntu. Repository dapat kita define sendiri dengan mengkonfigurasinya di /etc/apt/sources.list atau menggunakan repository dari apa yang telah didefinisikan didalamnya.

Install package aplikasi dengan perintah apt-get install


rizky@Elmo:/var/cache/apt/archives$ sudo apt-get install nginx
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages were automatically installed and are no longer required:
  libruby1.9 libreadline-ruby1.9
Use 'apt-get autoremove' to remove them.
The following NEW packages will be installed:
  nginx
0 upgraded, 1 newly installed, 0 to remove and 185 not upgraded.
Need to get 241kB of archives.
After this operation, 618kB of additional disk space will be used.
Get:1 http://id.archive.ubuntu.com jaunty/universe nginx 0.6.35-0ubuntu1 [241kB]
Fetched 241kB in 6s (38.3kB/s)
Selecting previously deselected package nginx.
(Reading database ... 103766 files and directories currently installed.)
Unpacking nginx (from .../nginx_0.6.35-0ubuntu1_i386.deb) ...
Processing triggers for man-db ...
Setting up nginx (0.6.35-0ubuntu1) ...

Note: apt-get memiliki cache aplikasi yang terinstall bertipe file *.deb di folder /var/cache/apt/archives/.

Remove/Uninstall dan purge package dengan perintah apt-get remove dan apt-get purge


rizky@Elmo:/var/cache/apt/archives$ sudo apt-get remove nginx
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages were automatically installed and are no longer required:
  libruby1.9 libreadline-ruby1.9
Use 'apt-get autoremove' to remove them.
The following packages will be REMOVED:
  nginx
0 upgraded, 0 newly installed, 1 to remove and 185 not upgraded.
After this operation, 618kB disk space will be freed.
Do you want to continue [Y/n]? Y
(Reading database ... 103790 files and directories currently installed.)
Removing nginx ...
Stopping nginx: nginx.
Processing triggers for man-db ...

Pencarian package di listed repository dengan perintah apt-cache search


rizky@Elmo:~$ sudo apt-cache search php5-cli
php5-cli - command-line interpreter for the php5 scripting language

Argument list too long when invoke rm

2008-11-02T22:15:00.000+07:00

When you face this error it means your rm command unable to delete or remove so many files in certain directory where in my case i found when i delete sendmail spool inside /var/spool/mqueue and /var/spool/clientmqueue. To solve this problem you can change it using this command:

[rizky@elmo mqueue]# sudo find . -name "*" | xargs rm -rf

MySQL Scalability Planning for Our Drupal

2008-10-24T09:22:00.005+07:00

Recently our client portal were reaching maximum number of MySQL connection and suffering connection lost and a lot of connection waiting for database MySQL server. After we investigate deeply those problem caused by:

1. Traffic (page views) getting higher.
2. A lot of writing process like UPDATE, doing table locking.
3. Bad Index plan.
4. Only have 1 database to serve all.

There are MySQL scalability patterns as many MySQL experts share on the net and i can use to scale our DB. One of pattern i plan to use is to distribute database traffic load between WRITE traffic and READ traffic using MySQL Replication. This pattern has long story and has very good reputation in Web 2.0 development. However there are challenged and pitfalls when implements Replications.

Anyway what is WRITE traffic? it means all query doing writing physically to the tables will be said as WRITE traffic. For instance, UPDATE, INSERT query. And READ traffic is all query that only doing read to the database. For instance, SELECT query.

Challenges and Pitfalls

There will be many scripts to be changed and also change mindset of developer when they write and read the database. Unfortunately we are using Drupal as our Framework. Drupal currently no has module natively or patch for people plaining to distribute the traffic load and this is big problem for us since we also use some contrib modules for our current Drupal and Those contrib module is not aware with WRITE and READ separation.

After looking all the scripts inside Drupal and contrib modules and also our custom modules , I find a hope for doing this. Drupal has good database abstraction functions and all query either WRITE or READ exactly use db_query() function and Drupal also has capability in their database abstraction to build more than 1 connection.

Start Designing Implementing

This is our plan for temporary for our database server. However currently there is no High Availability Plan for our WRITE but we will plan this shortly.

For the coding side, firstly we have a method splinting db_query into two functions namely db_query_write() that will query to Master and db_query_ read() that will query to Slaves. However if we use this method it means that we need to change all scripts and this is very bad and not efficient To simplify this I just patch db_query() function to be able to detect automatically the SQL SCRIPT by using regular expression.

Other things I add in script is by implementing two database connection in settings.php


$db_url = array(

   'default' => 'mysqli://userdb:dbpwd@masterdb.xxx.com:3306/mydb', //Master

   'slave' => 'mysqli://userdb:dbpwd@slavelb.xxx.con:3306/mydb',



);

With scripts above I can easily get connection for both Master and Slaves.When I want to switch to slave I just invoke the function using this argument:


db_set_active('slave');

To master


db_set_active();

CONCLUSION

This plan is only intended to scale on database server side however when you run big site there is many thing also need to be optimized such as cache implementation, splitting static files, front-end optimization, CDNed, Message Queue implementation, etc.

You can contact me at rizzblow 'at' gmail dot com if you want full script of this patches and I can help you design to scale you MySQL.

Binlog yang semakin makan tempat

2008-10-05T15:34:00.003+07:00

Binlog adalah log di-generate mysql yang biasanya digunakan untuk keperluan replikasi. Akhir-akhir ini serangan DDOS semakin deras mengalir ke salah satu web portal client baik yang sengaja dan tidak disengaja. Hal ini menyebabkan mysql men-generate binlog yang semakin cepat dan makan tempat. Binlog ini berisi tampungan statement-statement SQL yang pernah dieksekusi di Master MYSQL Server sebelum dibaca oleh IO Thread di SLAVE dan di-write sebagai RELAY LOG di SLAVE. BinLog ada jika kita mengaktifkanya dengan menepatkan variable log-bin=base-name di my.cnf atau --log-bin=base-name di console ketika service mysql start.

Untuk mengecheck keberadaan binlogs, bisa menggunakan perintah berikut:


mysql> show binary logs;
+-------------------+------------+
| Log_name          | File_size  |
+-------------------+------------+
| master-log.000001 | 1073818338 |
| master-log.000002 | 1073818338 |
| master-log.000003 | 1073818338 |
| master-log.000004 | 1073818338 |
| master-log.000005 | 1073818338 |
| master-log.000006 | 1073818338 |
| master-log.000007 | 1073818338 |
| master-log.000008 | 1073818338 |
| master-log.000009 | 1073818338 |
| master-log.0000010 | 1073818338 |
| master-log.0000011 | 1073818338 |
| master-log.0000012 | 1073818338 |
| master-log.0000013 | 1073818338 |
| master-log.0000014 | 1073818338 |
||master-log.000015 | 1073818338 |
| master-log.000020 | 1073818338 |
| master-log.000021 | 1073828820 |
| master-log.000022 | 1073749079 |
| master-log.000023 | 1073775050 |
| master-log.000024 | 1073795367 |
| master-log.000025 | 1073822141 |
| master-log.000026 | 1073835802 |
| master-log.000027 |  411482420 |
| master-log.000028 |  720980071 |
+-------------------+------------+
28 rows in set (0.00 sec)

Dan bisa juga dengan command :


mysql> show master status;
+-------------------+-----------+--------------+------------------+
| File              | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+-----------+--------------+------------------+
| master-log.000028 | 720996589 | Rizky-DB       |                  |
+-------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)

Angka 1073818338 diatas berarti ukuran binlog telah mencapai maximal dan akan membuat file binlog baru dengan pattern penamaan master-log.0000xx dan proses seterusnya akan dilakukan hal yang sama. Di MySQL kita harus care minimal dengan 2 variable berikut untuk me-manage binlog:

max_binlog_size, dynamic variable untuk menentukan maximum ukuran file dari binlog yang di generate.


mysql> show variables like 'max_binlog_size';
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+
1 row in set (0.00 sec)

dan expire_logs_days,Umur maximum binlog jika melebih mysql akan menghapus binlog yang di nyatakan expire.


mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 0     |
+------------------+-------+
1 row in set (0.00 sec)

Penentuan value dari variable diatas dapat di sesuaikan atas kebutuhan anda yang mana saat ini di server mengunakn setting default :-p
max_binlog_size = 1073741824
expire_logs_days=0 // Never Expire

Dengan angka diatas pertumbuhan binlog sangat tinggi ketika website di DDOS dan server akan mengalami krisis disk space. Untuk menghindari hal ini kita bisa men-set kedua variable menjadi reasonable yaitu dengan mengimplementasikan binlog expiry.Untungnya kedua variable diatas adalah dynamic variable jadi kita di tak perlu melakukan restart service untuk mengconfigure ulang bin-log kita.


mysql>SET GLOBAL expire_logs_days=5; // dimana binlog akan di purge/remove setelah berumur 5 hari

Beberapa Counter/Statistik Variable yang berguna di MySQL

2008-08-23T08:39:00.001+07:00

Pada suatu saat kita mungkin ingin mengetahui dan memonitor statistik(counter) beberapa informasi seperti berapa jumlah koneksi yang telah dibuat atau gagal,berapa jumlah DELETE statement yang telah diexecute dan seberapa efektif implementasi Query Cache kita dari MySQL server.

Di MySQL informasi-informasi ini dengan mudah didapatkan dengan melakukan Query ke Status Variables dari MySQL. Berikut beberapa variable untuk mengetahui informasi diatas:

Connection statistik atau counter.
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Aborted_connects | 73 |
| Connections | 670043|
| Max_used_connections | 100 |
| Aborted_Clients | 30 |

- Connection jumlah koneksi yang datang ke MySQL baik yang berhasil atau gagal.

- Aborted_connects jumlah koneksi yang gagal yang biasanya disebabkan karena salah password atau karena tidak memiliki privilidge.

- Aborted_Clients jumlah koneksi yang gagal yang biasanya disebabkan karena koneksi putus.

- Max_used_connections jumlah maximum koneksi simultan yang pernah dibuat.

Executed Statement statistik atau counter.
mysql> show status like 'com%';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| Com_admin_commands | 0 |
| Com_assign_to_keycache | 0 |
| Com_alter_db | 0 |
| Com_alter_db_upgrade | 0 |
| Com_alter_event | 0 |
| Com_alter_function | 0 |
| Com_alter_procedure | 0 |
| Com_alter_server | 0 |
| Com_alter_table | 0 |
| Com_alter_tablespace | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_binlog | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 0 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_event | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_procedure | 0 |
| Com_create_server | 0 |
| Com_create_table | 0 |
| Com_create_trigger | 0 |
| Com_create_udf | 0 |
| Com_create_user | 0 |
| Com_create_view | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 0 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_event | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_procedure | 0 |
| Com_drop_server | 0 |
| Com_drop_table | 0 |
| Com_drop_trigger | 0 |
| Com_drop_user | 0 |
| Com_drop_view | 0 |
| Com_empty_query | 0 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 0 |
| Com_insert_select | 0 |
| Com_install_plugin | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_release_savepoint | 0 |
| Com_rename_table | 0 |
| Com_rename_user | 0 |
| Com_repair | 0 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_rollback_to_savepoint | 0 |
| Com_savepoint | 0 |
| Com_select | 1 |
| Com_set_option | 0 |
| Com_show_authors | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_column_types | 0 |
| Com_show_contributors | 0 |
| Com_show_create_db | 0 |
| Com_show_create_event | 0 |
| Com_show_create_func | 0 |
| Com_show_create_proc | 0 |
| Com_show_create_table | 0 |
| Com_show_create_trigger | 0 |
| Com_show_databases | 0 |
| Com_show_engine_logs | 0 |
| Com_show_engine_mutex | 0 |
| Com_show_engine_status | 0 |
| Com_show_events | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 0 |
| Com_show_function_status | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_plugins | 0 |
| Com_show_privileges | 0 |
| Com_show_procedure_status | 0 |
| Com_show_processlist | 0 |
| Com_show_profile | 0 |
| Com_show_profiles | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 5 |
| Com_show_storage_engines | 0 |
| Com_show_table_status | 0 |
| Com_show_tables | 0 |
| Com_show_triggers | 0 |
| Com_show_variables | 0 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reprepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_uninstall_plugin | 0 |
| Com_unlock_tables | 0 |
| Com_update | 0 |
| Com_update_multi | 0 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |

Contoh dari banyak variable diatas seperti:

-Com_select Jumlah SELECT statement yang berhasil atau gagal diexecute. Variable ini tergantung terhadap implementasi cache di MySQL jika cache itu aktifkan maka setiap statement SELECT yang dikirim ke MySQL akan dicek terlebih dahulu keberadaannya di Cache jika tidak ada makan variable ini akan di increment 1.
-Com_update Jumlah UPDATE statement yang berhasil atau gagal diexecute.

Cache statistik atau counter.

Variable ini bekerja jika anda mengaktifkan fitur cache di MySQL:
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Qcache_hits | 6753 |

-Qcache_hits sebagaimana dijelaskan di Com_select, Setiap SELECT statement akan di check keberadaannya di cache jika tidak ada maka Com_select akan di increment 1 namun jika terdapat di Query Cache maka variable Qcache_hits yang akan di increment 1.

Note: Status Variables ini akan hilang / direset kembali ketika kita melakukan restart service Mysql.

Thread dan Global Variable di MySQL

2008-08-15T21:39:00.000+07:00

Ada beberapa hal yang lucu saya alami pada minggu-minggu ini yaitu ketika proses deployment aplikasi web yang kami buat ke production server. Saat itu MySQL mengalami OOM (out of memory) dengan swapping yang besar dan menyebabkan banyak thread yang defunct yang pada akhirnya MySQL berhenti.

Kemudian pada saat itu juga saya dapat kepercayaan untuk melakukan beberapa tuning di MySQL. Karena telah mengetahui spesifikasi hardware server maka saya langsung memfokus diri pada MySQL dan saya yakin betul pasti ada konfigurasi yang salah. Akhirnya kami menemukannya:


max_connection=1024
join_buffer_size=500M
read_buffer_size=250M
key_buffer_size=64M
sort_buffer_size=500M

Perlu diketahui, saya menggunakan MyISAM dikarenakan Framework yang digunakan hanya support Storage Engine tersebut (MyISAM) dan dari konfigurasi diatas sudah pasti dapat membuat MySQL berhenti kehabisan memory hal ini dikarenakan hampir semua Thread/Per session variable diatas diset terlalu tinggi dan sebaliknya Global variable diset sangat minimum.

Apa itu Thread/Per Session Variable ?

MySQL adalah multithreaded server dimana setiap connection yang terbentuk oleh client yang melakukan request ke MySQL akan membuat thread baru untuknya sehingga untuk sebuah thread variable akan dialokasikan untuk thread tersebut. Nah untuk kasus diatas misalnya sort_buffer_size adalah variable untuk alokasi memori buffer untuk keperluan sorting diassign sebesar 500MB, maximum concurrent user 1024 dan total memory server hanya 8GB maka dengan 3~4 concurrent user saja MySQL akan kehabisan memory.

Dan apa itu Global Variable ?

Variable yang digunakan untuk semua thread. Mungkin anda akan bertanya-tanya bahwa variable ini juga akan issue seperti race condition atau mutex. Tentu kedua issue tersebut sudah diketahui dan dipikirkan matang-matang oleh para engineer di MySQL dan mereka telah mempunyai cara yang canggih untuk mengatasinya. Pada contoh diatas adalah key_buffer_size variable yang digunakan untuk mengalokasikan buffer memory dari blok index.

Referensi:

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

Mocca.com is online now

2008-08-01T14:41:00.002+07:00

Finally mocca.com, Singapore online market place is now online with better look and better user experience. We did 1.5 month sprint development. This project is the big win for open source solution where previously mocca.com powered by Vignette CMS and we know it is difficult to extend and extremely not friendly for developer and for search engine.

Previously mocca.com looked like:

And now:

Learning Asyncronous IO dengan LibEvent

2008-05-16T00:56:00.004+07:00

Akhir-akhir ini semakin banyak orang yang concern dengan permasalahan blocking IO terhadap scalability dari beberapa web server konvensional yang selama ini kita kenal.

Apa itu Syncronous Blocking IO ? Blocking IO atau Syncronous IO adalah suatu bentuk dari process Input/Output (Disk/Network) yang tidak mengizinkan proses lain untuk jalan bersamaan. Proses lain tersebut hanya boleh dijalankan ketika proses sebelumnya selesai. Bagi sebuah Multiproses/Multithreaded Web Server yang sibuk, blocking ini sangat tidak efisien dimana proses lain harus antri dan menunggu (idle).

Contoh script dibawah ini :



   h = gethostbyname(hostname);
   if (!h) {
       fprintf(stderr, "Couldn't lookup %s: %s", hostname, hstrerror(h_errno));
       return 1;
   }
   if (h->h_addrtype != AF_INET) {
       fprintf(stderr, "No ipv6 support, sorry.");
       return 1;
   }

gethostbyname() tidak akan return sampai ia selesai resolve variable hostname misalkan dalam hal ini saya assign hostname variable dengan www.yahoo.com.

dan script dibawah ini juga:


   /* Allocate a new socket */
   fd = socket(AF_INET, SOCK_STREAM, 0);
   if (fd < sin_family =" AF_INET;" sin_port =" htons(80);" sin_addr =" *(struct">h_addr;
   if (connect(fd, (struct sockaddr*) &sin, sizeof(sin))) {
       perror("connect");
       close(fd);
       return 1;
   }

connect() tidak akan return sampai ia berhasil terkoneksi.

Ada masalah dengan script diatas ? Tentu tidak karena hal diatas adalah yang biasa kita lakukan dalam socket programming. Meskipun demikian ada suatu saat dimana kita menginginkan sebuah program dapat menghandle multi koneksi secara bersamaan. gethostbyname dan connect dikebanyakan platform tidak thread safe.

Asyncrous IO memungkinkan process lain untuk berjalan. Di Linux dan kebanyakan Sistem Operasi Unix ada beberapa bentuk dari Asyncronous IO yang dapat dicapai seperti forking process, selec(), epoll(), dan poll(). Dari semua bentuk tersebut ada satu yang mungkin saat ini saya suka karena lebih mudah dan tergila-gila yaitu dengan callback yang ada di Libevent. Seperti yang kita ketahui libevent ini digunakan oleh memcache.

Berikut contoh simple http server script dengan menggunakan callback:


#include <sys/types.h>
#include <sys/time.h>
#include <sys/queue.h>#include <stdlib.h>

#include <err.h>

#include <event.h>
#include <evhttp.h>


void x_handler(struct evhttp_request *req, void *arg)
{
       struct evbuffer *buf;
       buf = evbuffer_new();

       if (buf == NULL)
           err(1, "failed to create response buffer");

       evbuffer_add_printf(buf, "Requested: %sn", evhttp_request_uri(req));
       evhttp_send_reply(req, HTTP_OK, "OK", buf);
}

int main(int argc, char **argv)
{
   struct evhttp *httpd;

   event_init();
   httpd = evhttp_start("127.0.0.1",80);


   /* Set a callback for all other requests. */
   evhttp_set_gencb(httpd, x_handler, NULL);

   event_dispatch();
/* Not reached in this code as it is now. */
   evhttp_free(httpd);

   return 0;
}

Simple bukan ?

With “www.” or without “www.” prefix

2008-04-15T11:24:00.002+07:00

I know this is small issue when you publish a website however you need to consider this as one of usability and accessibility of your website. Another reason to consider this is google or search engine could remove your site form their index cos they think that your site is duplicate.

There many web user I found when accessing web never put www. prefix and just access directly using domain name like google.com, yahoo.com and reebonz.com on browser address. Some sites still provide 2 versions of their site using “www.” prefix and without “www.”. And the other sites provide only 1 version using www only so when their user access without www that site will be “server not found”.

When you buy and read SEO books firstly they will guide you to choose which version of site you want to activate for instance, the will recommend you activate “www.” prefix thus all the pages crawled and indexed in google will use “www.”. How about without “www.” one ? They will recommend you to redirect it to www version permanently.

How to achieve this ?

1. Enable your apache virtual host configuration. (If you host your file in shared hosting I believe you will have this already otherwise just edit httpd.conf).



NameVirtualHost *:80

<VirtualHost *:80>
  DocumentRoot /webroot/aaaacom
  ServerName www.aaaa.com
  ServerAlias aaaa.com #This enable to use without www
  ErrorLog logs/aaaa.log
  CustomLog logs/aaaa.log combined
</VirtualHost>

2. Redirect permanently using 301 in .htaccess

  RewriteCond %{HTTP_HOST} ^aaaa\.com$ [NC]
  RewriteRule ^(.*)$ http://www.aaaa.com.hk/$1 [L,R=301]

After all those changes saved you can check it using your browser. (Restart apache needed when you change #1).

Do i need doing mysql_close() when i have mysql_open()

2008-02-19T12:10:00.001+07:00

No Need

Making scalable development environment

2008-02-02T00:47:00.005+07:00

This is my first month working with my current company. My superior asked me to build new team development environment to make the development process more efficient and effective. However i see most of the team haven't experienced working in group of development and individually prefer to use single code base development so this is one of challenge i will face as team leader for implementing this.

Firstly before implementing this i stated to the team regarding benefit will be gained when we have scalable development environment:

1. Fast development
2. Have maintainable code
3. Easy to do refactoring
4. Easy to change when marketing people require new features.

Since we will choose application framework to be used so we can inherit or no need to think about the coding standard and application architecture. Based on my experiences there are 3 major points for us to scale our development environment such as:

1. Use Source Control.

This is very important one and i think i will push the team to use SVN rather CSV. We can track history, doing diff, reverting, branching, merging, and someone can lock the file to be not used by others. To know how to install svn you can read to my previous post How to build your own svn and subversion.

2. One step build.

This makes more confident when deploying new release or bug fixing. So if something happened like error we can rollback to the previous version and we can commit/upload the new release or bug fixing automatically without worrying from development to staging and to production.

3. Bug tracking.

I think this is of what the company have now. There is many opensource software for this.

two of these are optional

4. Unit Testing

Do you think we need this ? OOP? Agile/Scrum/TDD/XP?

5. Collaborative Project Management (Optional).

MS Excel ? I think it will be better using software like phpprojekt or dotProject

All of those points will change the way programmer developing application to be more open, collaborative, and traceable.

Building Our Own SVN Repository in Ubuntu

2008-01-20T10:31:00.002+07:00

You use ubuntu? You can folow these steps to make your own svn repository in order to achieve scalable development environment.

Installation

1. Install Subversion by issuing this command and type Y:


rizky@grover:~$ sudo apt-get install subversion
Reading package lists... Done
Building dependency tree    
Reading state information... Done
The following extra packages will be installed:
  libapr1 libaprutil1 libsvn1
Suggested packages:
  subversion-tools db4.7-util patch
The following NEW packages will be installed:
  libapr1 libaprutil1 libsvn1 subversion
0 upgraded, 4 newly installed, 0 to remove and 0 not upgraded.
Need to get 1,464kB of archives.
After this operation, 6,935kB of additional disk space will be used.
Do you want to continue [Y/n]?

2. Install Apache to make SVN accessible through the web and type Y.


rizky@grover:~$ sudo apt-get install subversion
Reading package lists... Done
Building dependency tree    
Reading state information... Done
The following extra packages will be installed:
  libapr1 libaprutil1 libsvn1
Suggested packages:
  subversion-tools db4.7-util patch
The following NEW packages will be installed:
  libapr1 libaprutil1 libsvn1 subversion
0 upgraded, 4 newly installed, 0 to remove and 0 not upgraded.
Need to get 1,464kB of archives.
After this operation, 6,935kB of additional disk space will be used.
Do you want to continue [Y/n]?

3. By default apache2 on ubuntu installation already have mod_dav however you need to enable this apache module in by issuing this command.


rizky@grover:~$ sudo a2enmod dav

4. Install mod_dav_svn apache2 module by issuing this command


rizky@grover:~$ sudo apt-get install libapache2-svn

5.Dont forget to configure your ubuntu box ip address otherwise you only be able to use localhost address.

Repository Creation

6. Create new repository by issuing svnadmin command and create trunk directory


rizky@grover:~$sudo svnadmin create /svn/mydrupal


rizky@grover:~$sudo svn mkdir /svn/mydrupal/trunk -m 'initial trunk'

7. Importing existing source code into repository. For instance i have drupal cms source code located at /home/rizky/Directory/drupal-6.0/.


rizky@grover:~$sudo svn import file:///home/rizky/Directory/drupal-6.0 file:///svn/mydrupal -m 'initial loaded'

8.Edit dav_svn.conf file and add the following script


<location>
DAV svn
SVNPath /svn/mydrupal
#SVNPathAuthz off
AuthType basic
AuthName "My Subversion Repository"
AuthUserFile /etc/svn-auth


Require valid-user

</location>

9. Create basic authentication file and place/name it into as noted in #8 AuthUserFile /etc/svn-auth

http://www.webreference.com/programming/apache_authentication/

10. Start/Restart your apache


rizky@grover:~$sudo /etc/init.d/apache2 start

Those are 10 steps to build your own SVN Repository and you can test what you did by browsing it using web browser http://192.168.56.2/svn/mydrupal. The IP of 192.168.56.2 is my ubuntu IP. For the first time you will be popup login form and type your user and password as noted on #9

And do checkout using tortoiseSVN if your svn client use windows operating system.

Watch bandwidth usage real time in Linux

2008-01-09T10:07:00.003+07:00

After knowing your Ethernet information such as speed and Ethernet type. You may want to know and watch your bandwidth usage real time in your linux box. In Linux you can issue this command ifstat and you can watch your in/out packet.


[root@bigbird ~]# ifstat
     eth0                eth1                eth2     
KB/s in  KB/s out   KB/s in  KB/s out   KB/s in  KB/s out
  0.25      0.34      0.06      0.00     24.24    370.78
  0.07      0.16      0.12      0.00     14.75    196.73
  0.07      0.16      0.06      0.00      9.96    171.22
  0.07      0.16      0.06      0.00     10.73     26.94
  0.07      0.16      0.06      0.00     22.68    198.15
  0.07      0.16      0.06      0.00     25.26    208.07
  0.07      0.16      0.06      0.00     14.10    192.54
  0.07      0.16      0.12      0.00      9.20    170.70
  0.07      0.16      0.06      0.00      7.13     13.16
 14.86    141.54      0.06      0.00      3.83     21.34
  0.07      0.16      0.19      0.00      8.42     16.55
  0.07      0.16      0.12      0.00     19.86    204.91
  0.07      0.16      0.06      0.00     11.72     19.35
  0.07      0.16      0.06      0.00     11.83    188.96
  0.07      0.16      0.12      0.00     24.90    371.27
  0.07      0.16      0.06      0.00     27.68    378.79
  0.07      0.16      0.12      0.00      9.24     21.73
  0.07      0.16      0.12      0.00     82.37   2512.45
  0.07      0.16      0.06      0.00     20.97    215.73
  0.07      0.54      0.06      0.00     39.96    550.80
  0.07      0.16      0.19      0.00     14.98    183.67
  0.07      0.16      0.06      0.00     30.29    225.40
  0.07      0.16      0.12      0.00     20.01    351.26
  0.07      0.16      0.12      0.00      8.03     14.74
  0.07      0.16      0.06      0.00      8.86     11.19
  0.07      0.29      0.12      0.00     22.27    217.92
  0.07      0.16      0.06      0.00      3.71      7.57
  0.07      0.41      0.12      0.00     60.30   1601.00
  0.07      0.16      0.12      0.00     20.45    362.30
  0.07      0.22      0.06      0.00      7.04     27.83
  0.07      0.35      0.12      0.00     15.29    192.80
  0.07      0.16      0.25      0.00     20.11    345.56
  0.07      0.16      0.12      0.00     17.10    141.98
  0.07      0.16      0.19      0.00     48.36   1562.07
  0.07      0.16      0.12      0.00     33.38    736.35
  0.07      0.16      0.12      0.00     15.13    180.79
  0.07      0.16      0.13      0.00     36.73    572.21
  0.07      0.16      0.12      0.00    127.61   3242.01
  0.07      0.16      0.12      0.00     26.06    387.11
  0.07      0.16      0.19      0.00     22.68    2

Other tools can be used to watch your real time traffic are tcptrack and iftop.

How to query your Ethernet/NIC detail in Linux

2008-01-07T09:58:00.001+07:00

You may want to know the speed of your Ethernet line and query what type ethernet your server currently use or have. In Linux this can be queried easy using command line mii-tool and ethtool


[root@bigbird ~]# mii-tool
eth0: negotiated 100baseTx-FD, link ok
eth1: negotiated 100baseTx-FD, link ok
eth2: negotiated, link ok
SIOCGMIIPHY on 'eth3' failed: Resource temporarily unavailable
[root@296716-db2 ~]# ethtool eth0
Settings for eth0:
      Supported ports: [ TP ]
      Supported link modes:   10baseT/Half 10baseT/Full
                              100baseT/Half 100baseT/Full
                              1000baseT/Full
      Supports auto-negotiation: Yes
      Advertised link modes:  10baseT/Half 10baseT/Full
                              100baseT/Half 100baseT/Full
                              1000baseT/Full
      Advertised auto-negotiation: Yes
      Speed: 100Mb/s
      Duplex: Full
      Port: Twisted Pair
      PHYAD: 1
      Transceiver: internal
      Auto-negotiation: on
      Supports Wake-on: g
      Wake-on: d
      Link detected: yes
[root@bigbird ~]# ethtool eth1
Settings for eth1:
      Supported ports: [ TP ]
      Supported link modes:   10baseT/Half 10baseT/Full
                              100baseT/Half 100baseT/Full
                              1000baseT/Full
      Supports auto-negotiation: Yes
      Advertised link modes:  10baseT/Half 10baseT/Full
                              100baseT/Half 100baseT/Full
                              1000baseT/Full
      Advertised auto-negotiation: Yes
      Speed: 100Mb/s
      Duplex: Full
      Port: Twisted Pair
      PHYAD: 1
      Transceiver: internal
      Auto-negotiation: on
      Supports Wake-on: g
      Wake-on: d
      Link detected: yes
[root@bigbird ~]# ethtool eth2
Settings for eth2:
      Supported ports: [ TP ]
      Supported link modes:   10baseT/Half 10baseT/Full
                              100baseT/Half 100baseT/Full
                              1000baseT/Full
      Supports auto-negotiation: Yes
      Advertised link modes:  10baseT/Half 10baseT/Full
                              100baseT/Half 100baseT/Full
                              1000baseT/Full
      Advertised auto-negotiation: Yes
      Speed: 1000Mb/s
      Duplex: Full
      Port: Twisted Pair
      PHYAD: 1
      Transceiver: internal
      Auto-negotiation: on
      Supports Wake-on: g
      Wake-on: d
      Link detected: yes

How do i change mysql prompt ?

2007-10-21T09:48:00.002+07:00

I was wondering from my hosting company about how come their mysql prompt can be changed. As we usually see when we login to mysql server using mysql command, the prompt is always like this:


rizky@bigbird:~$ mysql -u u211 -p -h xxxxxx
mysql>

After digging deeply into mysql documentation then i found the way to change mysql prompt where previously i tot such change only can be made by hacking mysql source script. Here are the following way to change mysql prompt.

1. Using prompt session variable into my.cnf.


[mysql]
prompt=(\\u@\\h) [\\d]>\\_

2. Invoking mysql commandline variable


mysql --prompt="(\u@\h) [\d]> -u u211 "

3. Exporting MYSQL_PS1


rizky@bigbird:~$export MYSQL_PS1="(\u@\h) [\d]> "

4. Invoking prompt command in mysql prompt


mysql> prompt (\u@\h) [\d]>\_
PROMPT set to '(\u@\h) [\d]>\_'
(root@1289.my.xxxx.com) [(mydb]>

For #1 the change will be set every you start mysql service and others only per session.

My first J2ME apps

2007-03-12T15:56:00.001+07:00

Finally i have finished this project and ready to deploy, after 3 months learning many specs regarding mobile devices and doing coding based on the business requirement for J2ME side and also for the backend side.

The application itself intended to be used by individual and/ or group of organization to refill (top up) their prepaid card. This application is new channel to make member (agent) to sell prepaid topup service directly to customer where conventionally use sms and web application. This application also intended to replace the conventional one and to make more scalable.

Security is highest concern since this application interact with member's money that's why from the beginning i try to find the encryption engine like bouncycastle that can work with both side J2ME side and Backend Side (PHP).

How to get fastest help in mysql client console

2007-03-12T11:39:00.001+07:00

You may be like me that sometimes feel so hard to memorize command, statement,or even privilege command in MySQL. As Administrator having many servers with various platform running.

Lucky MySQL has help that make our life easier and faster when we are in console mode using mysql client utility. No need to waste your time on google for finding information such as date function like DATE_ADD or DATE SUB where in your mysql command propmt you can just type the following command:


mysql> HELP DATE_ADD
Name: 'DATE_ADD'    
Description:        
Syntax:             
DATE_ADD(date,INTERVAL expr unit), DATE_SUB(date,INTERVAL expr unit)

These functions perform date arithmetic. The date argument specifies
the starting date or datetime value. expr is an expression specifying
the interval value to be added or subtracted from the starting date.
expr is a string; it may start with a "-" for negative intervals. unit
is a keyword indicating the units in which the expression should be
interpreted.

URL: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

Examples:
mysql> SELECT '2008-12-31 23:59:59' + INTERVAL 1 SECOND;
        -> '2009-01-01 00:00:00'
mysql> SELECT INTERVAL 1 DAY + '2008-12-31';
        -> '2009-01-01'
mysql> SELECT '2005-01-01' - INTERVAL 1 SECOND;
        -> '2004-12-31 23:59:59'
mysql> SELECT DATE_ADD('2000-12-31 23:59:59',
    ->                 INTERVAL 1 SECOND);
        -> '2001-01-01 00:00:00'
mysql> SELECT DATE_ADD('2010-12-31 23:59:59',
    ->                 INTERVAL 1 DAY);
        -> '2011-01-01 23:59:59'
mysql> SELECT DATE_ADD('2100-12-31 23:59:59',
    ->                 INTERVAL '1:1' MINUTE_SECOND);
        -> '2101-01-01 00:01:00'
mysql> SELECT DATE_SUB('2005-01-01 00:00:00',
    ->                 INTERVAL '1 1:1:1' DAY_SECOND);
        -> '2004-12-30 22:58:59'
mysql> SELECT DATE_ADD('1900-01-01 00:00:00',
    ->                 INTERVAL '-1 10' DAY_HOUR);
        -> '1899-12-30 14:00:00'
mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
        -> '1997-12-02'
mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002',
    ->            INTERVAL '1.999999' SECOND_MICROSECOND);
        -> '1993-01-01 00:00:01.000001'

mysql>

As you see above it also provides example in the result of command. We can also browse the mysql help by categorizing it into some category by issuing the following command:


mysql> help date_add
Name: 'DATE_ADD'    
Description:        
Syntax:             
DATE_ADD(date,INTERVAL expr unit), DATE_SUB(date,INTERVAL expr unit)

These functions perform date arithmetic. The date argument specifies
the starting date or datetime value. expr is an expression specifying
the interval value to be added or subtracted from the starting date.
expr is a string; it may start with a "-" for negative intervals. unit
is a keyword indicating the units in which the expression should be
interpreted.

URL: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

Examples:
mysql> SELECT '2008-12-31 23:59:59' + INTERVAL 1 SECOND;
        -> '2009-01-01 00:00:00'
mysql> SELECT INTERVAL 1 DAY + '2008-12-31';
        -> '2009-01-01'
mysql> SELECT '2005-01-01' - INTERVAL 1 SECOND;
        -> '2004-12-31 23:59:59'
mysql> SELECT DATE_ADD('2000-12-31 23:59:59',
    ->                 INTERVAL 1 SECOND);
        -> '2001-01-01 00:00:00'
mysql> SELECT DATE_ADD('2010-12-31 23:59:59',
    ->                 INTERVAL 1 DAY);
        -> '2011-01-01 23:59:59'
mysql> SELECT DATE_ADD('2100-12-31 23:59:59',
    ->                 INTERVAL '1:1' MINUTE_SECOND);
        -> '2101-01-01 00:01:00'
mysql> SELECT DATE_SUB('2005-01-01 00:00:00',
    ->                 INTERVAL '1 1:1:1' DAY_SECOND);
        -> '2004-12-30 22:58:59'
mysql> SELECT DATE_ADD('1900-01-01 00:00:00',
    ->                 INTERVAL '-1 10' DAY_HOUR);
        -> '1899-12-30 14:00:00'
mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
        -> '1997-12-02'
mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002',
    ->            INTERVAL '1.999999' SECOND_MICROSECOND);
        -> '1993-01-01 00:00:01.000001'

mysql>