Configure SSL in Apache server and Monitoring Apache in Https mode using JMX

This blog mentions how to enable SSL in Apache Http server and use of JMX to monitor Apache Web server.

-: Configure SSL in Apache Web server and Monitoring Apache in Https mode using JMX :-

Contents:-
1) Downloading Software.
2) Installation of Apache web server.
3) Creation of key and certificate for SSL.
4) Changes required in Apache configuration files for enabling SSL.
5) Changes required in Apache configuration files for monitoring.
6) References.


1) First download Apache Http with OpenSSL feature from here:-
http://www.apache.org/dist/httpd/binaries/win32
Now you can download “apache_2.2.11-win32-x86-openssl-0.9.8i.msi” for windows.

For UNIX:-
http://www.apache.org/dist/httpd/binaries/
It will show you list of Operating systems. Choose your OS, then choose an rpm package and install it on Linux machine.

2) After downloading, install it properly in Operating System, we need “openssl” command for this. This comes with the software installation package.
Go to “bin” sub-directory after installation of Apache server. Copy “conf/openssl.conf” to bin directory.


3) Now create key and get certificate to use in Apache.

openssl req -config openssl.cnf -new - out apacheserver.csr -keyout apacheserver.pem

You'll be prompted to answer a bunch of questions, the answers to which can all be left blank except for:
•PEM pass phrase: This is the password associated with the private key (apacheserver.pem) that you're generating. This will only be used in the next step, so make it anything you like, but don't forget it.

•Common Name: This should be the fully-qualified domain name associated with this certificate. I have given here “vtpl4.vitagetech.com”.

Now we need to create a non-password protected key for Apache to use:

openssl rsa -in apacheserver.pem -out apacheserver.key

You'll be prompted for the password that you created above, after which a file called “apacheserver.key” should appear in your folder.

Finally, we need to create an X.509 certificate, which Apache also requires:

openssl x509 -in apacheserver.csr -out apacheserver.cert -req -signkey apacheserver.key -days 365

And that's it - you now have a self-signed certificate that Apache can use to enable SSL.

4) Now move these (apacheserver.key and apacheserver.cert) files to any of your directory. I created “ssl” folder inside “conf” folder of Apache_Install_Directory and pasted in newly created “ssl” folder.
By the way you have to give this address in Apache config files, explained later.

Open your httpd.conf file (which for me is in “conf” folder of Apache installation directory) and uncomment (remove the # sign) the following lines:
#LoadModule ssl_module modules/mod_ssl.so
#Include conf/extra/httpd-ssl.conf

Open your httpd-ssl.conf file (which for me is in “Apache_Install_Dir\conf\extra\”) and update the section entitled .
You'll need to update the values of ServerAdmin, DocumentRoot, ServerName, ErrorLog and CustomLog to match your environment. For me I have put like:-

Listen 443

#General setup for the virtual host
DocumentRoot "C:/Program Files/Apache Software Foundation/Apache2.2/htdocs"
ServerName vtpl4.vitagetech.com:443
ServerAdmin admin@vitagetech.com
ErrorLog “C:/Program Files/Apache Software Foundation/Apache2.2/logs/error.log"
TransferLog "C:/Program Files/Apache Software Foundation/Apache2.2/logs/access.log"
----------
----------

You'll also need to point SSLCertificateFile to your .cert file and SSLCertificateKeyFile to your .key file.
For me I gave like:-

SSLCertificateFile "C:/Program Files/Apache Software Foundation/Apache2.2/conf/ssl/apachessl.cert"
SSLCertificateKeyFile "C:/Program Files/Apache Software Foundation/Apache2.2/conf/ssl/apachessl.key"

Now you restart your Apache Http web server, your server will start working in https mode. Now type:-
https://Ip_address:443/
Or
https://vtpl4.vitagetech.com:443/


5) Monitoring Apache Web servers in HTTPS mode:-

The Collector uses HTTP to access Apache server status page. If you wish to monitor an Apache Web server running in HTTPS mode, you must create a virtual host running in HTTP mode on the Apache server, bind it to a specific port, then configure the Collector to use that port.

Enable this module in “httpd.conf” file.
LoadModule status_module modules/mod_status.so

And add these lines to “httpd.conf” file.
ExtendedStatus on

SetHandler server-status
Order deny,allow
Deny from none
Allow from 127.0.0.1


For example, to enable the Collector to access an Apache the server's status page on port 1234:

* Create a virtual host by adding the following configuration to the Apache Web server configuration file (httpd.conf file):
Listen 1234


SetHandler server-status
Order deny,allow
Deny from none
Allow from 127.0.0.1


After doing all changes, restart your apache server. It will listen Http at 80, 1234 port numbers and Https at 443 port number.

6) Reference:-
http://www.silverwareconsulting.com/index.cfm/2009/3/31/Enabling-SSL-on-Apache-on-Windows

-------------------------------------------------END--------------------------------------------------

Drive doesn't open by double click. C, D drives doesn't open by double clicking.

Hi,
If you try to open a drive in windows machine by double clicking it, and unable to do, I mean, you have to right click and choose explore and then open a drive, then here is the solution:-

It means your system gets infected by a virus or trojan or some malicious code:-

Open Command prompt, type "regedit" and press "ctrl+f" to find "mountpoints2" and delete it.
Again find "mountpoints2" and delete it. I expect, you have to do this 3 times as 3 entries are there.

Then close this, you are done. Now you should be able to open a drive by double clicking it.

Deepak kumar modi

Very very useful URLs/Links for tutorials

Design pattern:-
http://www.javacamp.org/designPattern/


Spring web application sites:-
http://www.techfaq360.com/tutorial/spring/springwebapp.jsp
http://www.techfaq360.com/tutorial/spring/springsecond.jsp
http://www.techfaq360.com/tutorial/spring/springvalidation.jsp
http://www.techfaq360.com/tutorial/spring/struts_spring_hibernate.jsp

Spring with ACEGI/CAS form based security:-
http://www.tfo-eservices.eu/wb_tutorials/media/SpringAcegiTutorial/HTML/SpringAcegiTutorial-1_1-html.html?wb_session_id=6085175978eb26edcd0a9d90a99ca42a

Think tank tutorial:-
http://sharat.wordpress.com/

Running multiple instances in JBOSS:-
http://manoharviswanathan.com/blog/tech/running-multiple-jboss-instances-on-the-same-machine/

Implementation of License management in a Application:
http://manoharviswanathan.com/blog/tech/licence-management-for-java-web-applications-using-aspects/

How ProGuard works(ProGuard is a Java class file shrinker, optimizer, obfuscator, and preverifier):-
http://community.decisci.com/node/64

Java Tutorials:-
http://www.indijava.in/community/tutorials

Web services in Java using Apache Axis2

Dear Reader,

WebService is basically an application which allows communication between two software components. 
It means both the software components(client and server here) should understand a common language format, then 
only they can talk to each other. 

So, the basic Web services platform is XML + HTTP. All the standard Web Services works using following components:
    a. SOAP (Simple Object Access Protocol)
    b. UDDI (Universal Description, Discovery and Integration)
    c. WSDL (Web Services Description Language)

We are discussing here the point c. WSDL is nothing but an XML File only. So if you want to talk to a web service, 
that service must be in the form of *.wsdl. Each service has some operations. Operations are actual methods which 
are available for communication (in Java terminology: Service is a program, Operations are methods).

NOTE: In WebServices your request and response both will be of XML type and hence it must comply with SCHEMA or DTD.

Now let us see the working example from scratch. I am explaining how to create a web service and how to invoke it in 
a very simple manner in Windows OS. This is done using Apache Axis2 server.

You need:
1) Axis2 server, download it from below link and extract it in "C:\" drive. I have renamed the folder as "axis2-1.5-bin".
http://apache.ziply.com//axis/axis2/java/core/1.5.6/axis2-1.5.6-bin.zip

2) Go to "C:\axis2-1.5-bin\bin" directory. Then type "axis2server.bat", you will see:-
C:\axis2-1.5\bin>axis2server.bat
    Using JAVA_HOME C:\Program Files\Java\jdk1.5.0_13
    Using AXIS2_HOME C:\axis2-1.5
    [INFO] [SimpleAxisServer] Starting
    [INFO] [SimpleAxisServer] Using the Axis2 RepositoryC:\axis2-1.5\repository
    [SimpleAxisServer] Using the Axis2 RepositoryC:\axis2-1.5\repository
    [SimpleAxisServer] Using the Axis2 Configuration FileC:\axis2-1.5\conf\axis2.xml
    [INFO] Clustering has been disabled
    [INFO] Deploying module: addressing-1.5 - file:/C:/axis2-1.5/repository/modules/addressing-1.5.mar
    [INFO] Deploying module: metadataExchange-1.5 - file:/C:/axis2-1.5/repository/modules/mex-1.5.mar
    [INFO] Deploying module: mtompolicy-1.5 - file:/C:/axis2-1.5/repository/modules/mtompolicy-1.5.mar
    [INFO] Deploying module: ping-1.5 - file:/C:/axis2-1.5/repository/modules/ping-1.5.mar
    [INFO] Deploying module: script-1.5 - file:/C:/axis2-1.5/repository/modules/scripting-1.5.mar
    [INFO] Deploying module: soapmonitor-1.5 - file:/C:/axis2-1.5/repository/modules/soapmonitor-1.5.mar
    [INFO] Deploying module: metadataExchange-1.5 - file:/C:/axis2-1.5/lib/mex-1.5.jar
    [INFO] Deploying Web service: ArithmeticService.aar - file:/C:/axis2-1.5/repository/services/ArithmeticService.aar
    [INFO] Deploying Web service: AsynchronousService.aar - file:/C:/axis2-1.5/repository/services/AsynchronousService.aar
    [INFO] Deploying Web service: version.aar - file:/C:/axis2-1.5/repository/services/version.aar
    [INFO] Deploying Web service: WeatherService.aar - file:/C:/axis2-1.5/repository/services/WeatherService.aar
    [INFO] Listening on port 8080
    [INFO] [SimpleAxisServer] Started
    [SimpleAxisServer] Started
//This shows your server is started, now you have to create application and deploy.

3) Now open one more command prompt, go to "C:\axis2-1.5\repository\services" directory. 
This services directory is having list of all web services deployed as *.aar files. These *.aar files are actully
like JAR/WAR/EAR files which Apache Axis treats as "Apache Archive". Axis2 supports hot deployment, means
create *.aar file and paste to this directory, If server is already running, your web service will be deployed.

4) Now let us write a Java program. Create a directory anywhere in hard disk, assume "D:\axis2_example_dmodi\dmodi\". You will 
write Java code (Web service at Server side) here. This will be your service:-

//HelloWorldService.java
package dmodi;
public class HelloWorldService {
    public String sayHello(String name) {
        return "Hello, I am server.." + name;
    }

    public String sum(String n1, String n2) {
        int num = Integer.parseInt(n1) +Integer.parseInt(n2);
        return "The summation:" + num;
    }
    public String sub(String n1, String n2) {
        int num = Integer.parseInt(n1) -Integer.parseInt(n2);
        return "The substraction:" + num;
    }
    public String div(String n1, String n2) {
        int num = Integer.parseInt(n1) / Integer.parseInt(n2);
        return "The division:" + num;
    }
    public String mul(String n1, String n2) {
        int num = Integer.parseInt(n1) * Integer.parseInt(n2);
        return "The multiple:" + num;
    }
}

//Create "META-INF" folder inside "D:\axis2_example_dmodi\" and then write below code and save it as "services.xml" file and save 
//inside "META-INF". 
//services.xml: 
<?xml version="1.0" encoding="UTF-8"?>
<service name="HelloService">
    <parameter name="ServiceClass">dmodi.HelloWorldService</parameter>
    <operation name="sum">
        <messageReceiver
            class="org.apache.axis2.rpc.receivers.RPCMessageReceiver" />
    </operation>
    <operation name="sayHello">
        <messageReceiver
            class="org.apache.axis2.rpc.receivers.RPCMessageReceiver" />
    </operation>
    <operation name="mul">
        <messageReceiver
            class="org.apache.axis2.rpc.receivers.RPCMessageReceiver" />
    </operation>
    <operation name="sub">
        <messageReceiver
            class="org.apache.axis2.rpc.receivers.RPCMessageReceiver" />
    </operation>
     
    <operation name="div">
        <messageReceiver
            class="org.apache.axis2.rpc.receivers.RPCMessageReceiver" />
    </operation>
</service>

=======================================================================
That's it. Now you have to compile the Java code and create *.aar File as below:
Compilation of java code:
D:\axis2_example_dmodi\dmodi>javac HelloWorldService.java
D:\axis2_example_dmodi\dmodi>cd ..

//Creating Service file(.aar):-
D:\axis2_example_dmodi>jar -cvf HelloService.aar META-INF\services.xml dmodi\HelloWorldService.class
added manifest
adding: META-INF/services.xml(in = 895) (out= 212)(deflated 76%)
adding: dmodi/HelloWorldService.class(in = 1056) (out= 497)(deflated 52%)

---HelloService.aar created.

Now drop this HelloService.aar into "C:\axis2-1.5-bin\repository\services" folder. It will be automatically deployed 
if server is running else start the server (Command: C:\axis2-1.5\bin>axis2server.bat).

//Open browser and type:- http://localhost:8080/axis2/services/HelloService?wsdl
//localhost: can be replaced with IP address of your system. After hitting above URL, you will see the HelloService.wsdl file.
Wow, your first web service at server side is ready now.
===================================================================================================

Now since your web service is ready, you have to write a Java client to invoke it, pass some request and get the output.
After all this is what we need to do else what is the use of doing all these stuffs :-).

Writing Client:
Writing client is as simple, you just need a Java file, dependent JARs and run the Java (Client) code.
Supporting JARs are already available in the "lib" directory which comes with downloaded Apache Axis2.
For me it is in "C:\Axis2-1.5.6\lib". Here I have written Java code in Eclipse where setting classpath is too easy.
Just add all the JARs from "C:\Axis2-1.5.6\lib" directory for setting classpath in "Java Build Path" in eclipse. 
However if you don't want to add all the jar files, then below jar files are for you (check the image below):
















//Client code: ArithmeticClient.java
import javax.xml.namespace.QName;
import org.apache.axiom.om.OMElement;
import org.apache.axis2.AxisFault;
import org.apache.axis2.addressing.EndpointReference;
import org.apache.axis2.client.Options;
import org.apache.axis2.rpc.client.RPCServiceClient;
public class ArithmeticClient {
    public static void main(String[] args1) throws AxisFault {
        RPCServiceClient serviceClient = new RPCServiceClient();
        Options options = serviceClient.getOptions();
        EndpointReference targetEPR = new EndpointReference("http://localhost:8080/axis2/services/HelloService?wsdl");
        options.setTo(targetEPR);

        QName opSum = new QName("http://dmodi", "sum");  //http://dmodi is the namespace, you can see while hitting WSDL.
        Object[] opSumArgs = new Object[] { 14,7 };                    
        serviceClient.invokeRobust(opSum, opSumArgs);
        OMElement response = serviceClient.invokeBlocking(opSum, opSumArgs);
        System.out.println("Sum Called:"+response);
        
        opSum = new QName("http://dmodi", "mul");
        opSumArgs = new Object[] { 14,7 };
        serviceClient.invokeRobust(opSum, opSumArgs);
        response = serviceClient.invokeBlocking(opSum, opSumArgs);
        System.out.println("Multiple Called:"+response);
        
        opSum = new QName("http://dmodi", "sub");
        opSumArgs = new Object[] { 14,7 };
        serviceClient.invokeRobust(opSum, opSumArgs);
        response = serviceClient.invokeBlocking(opSum, opSumArgs);
        System.out.println("Substraction Called:"+response);
        
        opSum = new QName("http://dmodi", "div");
        opSumArgs = new Object[] { 14,7 };
        serviceClient.invokeRobust(opSum, opSumArgs);
        response = serviceClient.invokeBlocking(opSum, opSumArgs);
        System.out.println("Division Called:"+response);
    }
}
//Compile and Run this. Output is given below:
--------------------------------------------------------
Sum Called:<ns:sumResponse xmlns:ns="http://dmodi"><ns:return>The summation:21</ns:return></ns:sumResponse>
Multiple Called:<ns:mulResponse xmlns:ns="http://dmodi"><ns:return>The multiple:98</ns:return></ns:mulResponse>
Substraction Called:<ns:subResponse xmlns:ns="http://dmodi"><ns:return>The substraction:7</ns:return></ns:subResponse>
Division Called:<ns:divResponse xmlns:ns="http://dmodi"><ns:return>The division:2</ns:return></ns:divResponse>
--------------------------------------------------------
You see, Client is passing parameter (14, 7). Web service is returning the data after doing operations in XML format.
Now you need to parse the response XML to get the exact data.


--------------------------------------------------------
Some more stuffs:
Web service invocation by client can be done in two ways:
1) Synchronous invocation:  Client will be waiting to get the response after submitting request.
2) Asynchronous invocation: Client will send the request and will not wait for response.

The above example was showing Synchronous communication.
One more example is given below (Synchronous itself) where we are defining our own NAMESPACE.
//QuoteService.java  (WebService for Server side)
import java.util.HashMap;
import org.apache.axiom.om.OMAbstractFactory;
import org.apache.axiom.om.OMElement;
import org.apache.axiom.om.OMFactory;
import org.apache.axiom.om.OMNamespace;
public class QuoteService {
    private HashMap map=null;
    public OMElement sayQuote(OMElement in){
        HashMap map=new HashMap();
        map.put("Numbers","0 to 9");
        map.put("Letters","A-Z and a-z");
        map.put("Name","Deepak Kumar Modi");
        String name=in.getText();
        String info=(String)map.get(name);
        OMFactory fac=OMAbstractFactory.getOMFactory();
        OMNamespace omNs=fac.createOMNamespace("http://www.deepakmodi2006.blogspot.com/","deepak");
        OMElement resp=fac.createOMElement("sayResponse",omNs);
        resp.setText(info);
        return resp;
    }
}

//services.xml
<?xml version="1.0" encoding="UTF-8"?>
<service name="QuoteService">
    <description>
        This is a sample Web Service, designed by Deepak.Modi.
    </description>
    <parameter name="ServiceClass" locked="false">
        QuoteService
    </parameter>
    <operation name="sayQuote">
        <messageReceiver
            class="org.apache.axis2.receivers.RawXMLINOutMessageReceiver" />
    </operation>
</service>

//build.xml to create *.aar file
<?xml version="1.0" encoding="UTF-8"?>
<project name="Deepak kumar Modi" default="main" basedir=".">
    <property name="src.dir" value="src" />
    <property name="classes.dir" value="classes" />
    <property name="lib.dir" value="C:\Axis2-1.5.6\lib" />
    
    <path id="project.classpath">    
    <fileset dir="${lib.dir}">
    <include name="**/*.jar" />
    </fileset>        
    </path>
    
    <target name="clean">
        <delete dir="${classes.dir}" />
        <delete file="QuoteService.aar"/>
    </target>

    <target name="prepare" depends="clean">
        <mkdir dir="${classes.dir}" />
    </target>

    <target name="compile" depends="prepare">
        <javac srcdir="${src.dir}" destdir="${classes.dir}" classpathref="project.classpath" />
    </target>

    <target name="main" depends="compile" >
        <copydir  src="META-INF"   dest="${classes.dir}/META-INF">
        </copydir>
            
        <jar jarfile="QuoteService.aar" basedir="${classes.dir}"/>    
        <echo message="Main target completed.." />
    </target>
</project>

//Create QuoteService.aar and deploy at the axis2 services directory.

//TestClient.java
import org.apache.axiom.om.OMAbstractFactory;
import org.apache.axiom.om.OMElement;
import org.apache.axiom.om.OMFactory;
import org.apache.axiom.om.OMNamespace;
import org.apache.axis2.addressing.EndpointReference;
import org.apache.axis2.client.Options;
import org.apache.axis2.client.ServiceClient;
public class TestClient {
    private static EndpointReference targetEPR=new EndpointReference("http://localhost:8080/axis2/services/QuoteService?wsdl");
    public static OMElement getSayHelloOMElement(){
        OMFactory fac=OMAbstractFactory.getOMFactory();
        OMNamespace omNs=fac.createOMNamespace("http://www.deepakmodi2006.blogspot.com/","deepak");
        OMElement method=fac.createOMElement("sayQuote",omNs);
        method.setText("Numbers");
        System.out.println(method);
        return method;
    }
    public static void main(String[] args){
        try{
            Options options=new Options();
            options.setTo(targetEPR);
            ServiceClient sender=new ServiceClient();
            sender.setOptions(options);
            OMElement sayQuote=TestClient.getSayHelloOMElement();
            OMElement result=sender.sendReceive(sayQuote);
            System.out.println(result);
        }catch(Exception axisFault){
            axisFault.printStackTrace();
        }
    }
}
//Output:
------------------------------------------------
<deepak:sayQuote xmlns:deepak="http://www.deepakmodi2006.blogspot.com/">Numbers</deepak:sayQuote>
<deepak:sayResponse xmlns:deepak="http://www.deepakmodi2006.blogspot.com/">0 to 9</deepak:sayResponse>
------------------------------------------------END------------------------------------------------

Partitioning a table in Mysql

Partitioning a table makes your query execution faster. As your database will look for a record in near vicinity because you have partitioned the table.
See in this way:

You have 100000 of records in a table call "COLLTable" where Time is a column.
Now if you will look for a record where Time is like this:-'2008-06-12 12:12:23'
Then Database will have to look in all 100000 rows, but if you will do the partition based on (assume)month wise, it will look only in June month table partition.


Partitioning can be done on 4 basis:-
* range
* list
* hash
* key


But remember, Partitioning in mysql is available in version 5.1.x onwards.
Anyway you can check this by firing query:

*) SHOW VARIABLES LIKE '%partition%';

To see the mysql version:-
*) select version();


Now how to achieve partition:-

//If you are creating new table, fire this query, 3-months in one partition, I assume Time is one column here in the table:
*) CREATE TABLE COLLTable(
Id INT not null,
Time Timestamp NULL,
DISK_IOWRITE FLOAT not null,
PRIMARY KEY(Id,Time) )

PARTTIION BY LIST (MONTH(Time)) (
PARTITION p1 VALUES IN (3,4,5),
PARTITION p2 VALUES IN (6,7,8),
PARTITION p3 VALUES IN (9,10,11),
PARTITION p4 VALUES IN (12,1,2)
);


//If you have already a table with "Time" column, add partitions there, use following query:-
*) Alter table COLLTable PARTITION BY LIST (MONTH(Time)) (PARTITION P1 VALUES IN (1),PARTITION P2 VALUES IN (2),PARTITION P3 VALUES IN (3),PARTITION P4 VALUES IN (4),PARTITION P5 VALUES IN (5),PARTITION P6 VALUES IN (6),PARTITION P7 VALUES IN (7),PARTITION P8 VALUES IN (8),PARTITION P9 VALUES IN (9),PARTITION P10 VALUES IN (10),PARTITION P11 VALUES IN (11),PARTITION P12 VALUES IN (12));

//Now check this details, The entry goes to INFORMACTION_SCHEMA database and in PARTITION table:-
*) select TABLE_SCHEMA, TABLE_NAME,TABLE_ROWS, PARTITION_NAME, PARTITION_EXPRESSION from information_schema.PARTITIONS where TABLE_NAME='COLLTable';


//You can drop the partition also, but remember after dropping a partition the data will also be removed from that partition.
//Mysql creates different data files for different partitions, so dropping a partition will drop that file too.
//If you don't have "Time" as column, you can create partition for "Range" that takes integer type of values like:-

CREATE TABLE CollTable ( empId integer not null, salary float ) PARTITION BY RANGE (empId)
(
PARTITION P1 VALUES LESS THAN (1000),
PARTITION P2 VALUES LESS THAN (10000)
);


//End

Mysql Sample Queries

Mysql Sample Queries:

1) Create a duplicate table:
CREATE TABLE DUPLICATE_TABLE LIKE BANK_ID;   
//Will create duplicate empty table with same version of original table storage format. Indexes/Constraints will be copied.

CREATE TABLE DUPLICATE_TABLE as SELECT * FROM BANK_ID;  
//Will create table with data, Indexes/Constraints will not be copied. Need to re-create indexes/constraints.

INSERT into NEW_TABLE SELECT * from OLD_TABLE where Id=999999;

2) Dumping databases:
mysqldump --lock-all-tables -u root -p --all-databases > filename.sql     //For all Databases
mysqldump -u root dbname > filename.sql                                   //For one database without locking tables.

3) Install new version of mysql:
sudo apt-get install mysql-client-5.6 mysql-client-core-5.6
sudo apt-get install mysql-server-5.6

4) Import the dump file to mysql:
mysql -u root -p < filename.sql

5) Stopping/Starting mysql in unix:
service mysql status
service mysql stop
service mysql start
service mysql restart

or

/etc/init.d/mysql status
/etc/init.d/mysql start
/etc/init.d/mysql stop

6) Mysql config file is found in /etc directory in Unix: /etc/my.cnf 

7) Create Databse:
Create database NEW_Database;
use NEW_Database;
show tables;

8) Show variables in mysql:
show variables;
show variables where variable_name like '%connectio%';
show variables where variable_name like '%table_names%';  (lower_case_table_names=1 means case in-sensitive).

9) Setting variable value:
set global max_connections=200;

10) Create new user:
Create user 'root'@'X.X.X.54' identified by 'root';
Grant all privileges on *.* to 'root'@'X.X.X.54';
Grant all privileges on *.* to 'root'@'X.X.X.54' identified by 'root';
Select Host, User, Password from mysql.user;

11) Alter table:
alter table TABLE_NAME add NEW_COLUMN_NAME int not null;
alter table TABLE_NAME add foreign key(NEW_COLUMN_NAME) references OTHER_TABLE(Id);
alter table TABLE_NAME change COL_NAME NEW_COL_NAME float not null;
alter table TABLE_NAME modify COL_NAME bigint not null;
alter table TABLE_NAME add (TimeZoneId varchar(32) not null, TimeOffsetFromGMT int not null);

CREATE TABLE TABLE_NAME (Id int(11) NOT NULL auto_increment, TxnSetId int(11) NOT NULL, PRIMARY KEY (Id)) ENGINE=MyISAM DEFAULT CHARSET=latin1;
---------------END---------------------

JDBC and Java

JDBC defines how a Java program can communicate with a database. This section focuses mainly on JDBC 2.0 API.  
JDBC API provides two packages that are java.sql and javax.sql. By using JDBC API, you can connect virtually 
any database, send SQL queries to the database and process the results.

JDBC architecture defines different layers to work with any database and java, 
1) First Layer: JDBC API interfaces to work with Java
2) Second Layer: JDBC driver which is at middle layer (implements the JDBC API interfaces that maps 
   java to database specific language which are vendor dependent)
3) End/Bottom Layer: Database to store physical data.

JDBC API provides interfaces and classes to work with databases. 
Connection interface encapsulates database connection functionality, 
Statement interface encapsulates SQL query representation and execution functionality and 
ResultSet interface encapsulates retrieving data which comes from execution of SQL query using Statement.

The following are the basic steps to write a JDBC program
        1. Import java.sql and javax.sql packages
        2. Load JDBC driver
        3. Establish connection to the database using Connection interface
        4. Create a Statement by passing SQL query
        5. Execute the Statement
        6. Retrieve results by using ResultSet interface
        7. Close Statement and Connection

Here we will see types of drivers, availability of drivers, use of drivers in different situations, and 
then we will discuss about which driver suits your application best.

Driver is the key player in a JDBC application, it acts as a mediator between Java application and database. 
It implements JDBC API interfaces for a database, for example Oracle driver for oracle database, 
Sybase driver for Sybase database. 

JDBC defines four types of drivers to work with. Depending on your requirement you can choose one among them.

Here is a brief description of each type of driver :
Type 1:  JDBC-ODBC:     This driver converts JDBC calls to ODBC calls through JDBC-ODBC Bridge driver 
                        which in turn converts to database calls. Client requires ODBC libraries.
                        
Type 2:  Native API (Partly - Java driver):  This driver converts JDBC calls to database specific native 
                                             calls. Client requires database specific libraries.
                                             
Type 3:     JDBC-Net-All Java driver:     This driver passes calls to proxy server through network protocol 
                                    which in turn converts to database calls and passes through database 
                                    specific protocol. Client doesn't require any driver.
                                    
Type 4:  Native protocol-All-Java driver:     This driver directly calls database. Client doesn't require any 
                                            driver. Also called Thin driver.

Obviously the choice of choosing a driver depends on availability of driver and requirement. 
Generally all the databases support their own drivers or from third party vendors. If you don't 
have driver for your database, JDBC-ODBC driver is the only choice because all most all the vendors 
support ODBC. If you want to connect to database from java applet, then you have to use Type four 
driver because it is only the driver which supports that feature. 

Type 3 & 4 drivers are faster than other drivers because Type 3 gives facility for optimization 
techniques provided by application server such as connection pooling, caching, load balancing etc 
and Type 4 driver need not translate database calls to ODBC or native connectivity interface. 
Type 1 drivers are slow because they have to convert JDBC calls to ODBC through JDBC-ODBC Bridge 
driver initially and then ODBC Driver converts them into database specific calls. 
Type 2 drivers give average performance when compared to Type 3 & 4 drivers because the database 
calls have to be converted into database specific calls. 
Type 2 drivers give better performance than Type 1 drivers. 

So the hierarchy is in better performance wise: Type4 > Type3 > Type2 > Type1.
----------------------------------------------------------------------------------

Below I am writing a complete coding using Type4 driver in Mysql and Oracle database:

Mysql and JDBC connection:
Create a Java file named "DBConnection.java". The example is very basic for connectivity.
=Add the "mysql-connector-java-5.1.5-bin.jar" file to class path. 

-----------------------Start of DBConnection.java----------------
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class DBConnection {
    Connection con=null;
    public DBConnection () throws Exception{
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        con = DriverManager.getConnection("jdbc:mysql://192.168.111.117:3306/deepak","root","password");
        System.out.println("Connected");
        
        //Driver for Oracle
        //Class.forName("oracle.jdbc.OracleDriver");
        //String connQuery = "jdbc:oracle:thin:@192.168.12.37:1521:XE";  //XE is the schema name.
        //Connection conn = DriverManager.getConnection(connQuery,"username","password");        
    }
    public static void main(String[] args) throws Exception {
        new DBConnection();
    }
}
-----------------------End of DBConnection.java----------------
Please see below for complete DB operation I mean query update and fetching from Database.
If you are using PreparedStatements, it will improve performance:
A query execution in DB has to pass through 3 steps:
    1) Lexical and Syntax check(also refers as Compilation of query),
    2) Query optimization,
    3) Execution of query.

If you use PreparedStatements, Step 1 and 2 will be executed only once and 3 will be repeated 
but in Statement API all three steps will be repeated for all queries. So PreparedStatements 
will improve the performance of database while executing query. See below code snippet..
--------------------------------
try{
    connection.setAutoCommit(false);
    PreparedStatement ps = connection.preareStatement( "UPDATE employee SET Address=? WHERE name=?");
    ps.setString(1,"Bangalore, RT Nagar");
    ps.setString(2,"Deepak.Modi");
    ps.executeUpdate();

    PreparedStatement ps1 = connection.prepareStatement( "UPDATE account SET salary=? WHERE name=?");
    ps1.setDouble(1, 50000.00);
    ps1.setString(2,"Deepak.Modi");
    ps1.executeUpdate();

    connection.commit();
    connection.setAutoCommit(true);
}
catch(SQLException e){ connection.rollback();}
finally{
       if(ps != null){ ps.close();}
       if(ps1 != null){ps1.close();}
       if(connection != null){connection.close();}
}
--------------------------------
Below is complete URL can be set while making DB connection for CACHING queries and enhancement
of PreparedStatements...

"jdbc:mysql://192.168.111.17:3306/deepak?useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=200&prepStmtCacheSqlLimit=1000"

useServerPrepStmts=true //Use server-side prepared statements if the server supports them.
cachePrepStmts=true     //Keeps caching of prepared statements.
prepStmtCacheSize=200     //No of sql queries to cache.
prepStmtCacheSqlLimit=1000 //Maximum no of characters in one query to be cached.

------------------END-------------------------------

WebService invocation in Java using Axis2 (Async)

Web services using Asynchronous communication
Dear reader,
Web services can be invoked by two ways:
1) Synchronous communication, here client will be waiting till it gets the response from web service. This
    is called sequence execution of web service.
2) Asynchronous communication, here client will not wait for response from web service, once it sends the 
    request. This is like a parallel execution (calling web service and doing its own task).

How to work with Web service and Synchronous Communication is already discussed in my another blog in link
"http://deepakmodi2006.blogspot.com/2009/08/web-service-in-java-using-axis2.html". 
This is for Asynchronous communication. I am writing only client code here as web service at 
Server side will be same, only Client execution needs to be changed. Please see the below link for 
Web service deployed at Server.

//Writing 
import org.apache.axiom.om.OMAbstractFactory;
import org.apache.axiom.om.OMElement;
import org.apache.axiom.om.OMFactory;
import org.apache.axiom.om.OMNamespace;
import org.apache.axis2.addressing.EndpointReference;
import org.apache.axis2.client.Options;
import org.apache.axis2.client.ServiceClient;
import org.apache.axis2.client.async.AsyncResult;
import org.apache.axis2.client.async.Callback;
public class ArithmeticAsyncClient {
    private static EndpointReference targetEPR=new EndpointReference
    ("http://10.200.101.137:8080/axis2/services/QuoteService?wsdl");
    public static OMElement getSayHelloOMElement(){
        OMFactory fac=OMAbstractFactory.getOMFactory();
        OMNamespace omNs=fac.createOMNamespace("http://www.deepakmodi2006.blogspot.com/","deepak");
        OMElement method=fac.createOMElement("sayQuote",omNs);
        method.setText("Numbers");
        System.out.println(method);
        return method;
    }
    public static void main(String[] args){
        try{
            Options options=new Options();
            options.setTo(targetEPR);
            ServiceClient sender=new ServiceClient();
            sender.setOptions(options);
            OMElement sayQuote=TestClient.getSayHelloOMElement();
            
            Callback callback2 = new Callback() {
                public void onComplete(AsyncResult result) {
                    String responseString = result.getResponseEnvelope().toString();
                    System.out.println("Got response :"+responseString);
                }
                public void onError(Exception e) {
                    e.printStackTrace();
                }
            };

            sender.sendReceiveNonBlocking(sayQuote,callback2);
            System.out.println("Asynchronous web service already invoked...");

            while(!callback2.isComplete()) {
                Thread.sleep(100);
                System.out.println("Thread is waiting to get the response");
                //This block will keep executing till the response comes.
            }
        }catch(Exception axisFault){
            axisFault.printStackTrace();
        }
    }
}    
//Output:
log4j:WARN No appenders could be found for logger (org.apache.axis2.util.Loader).
log4j:WARN Please initialize the log4j system properly.
<deepak:sayQuote xmlns:deepak="http://www.deepakmodi2006.blogspot.com/">Numbers</deepak:sayQuote>
Asynchronous web service already invoked...
Thread is waiting to get the response
Thread is waiting to get the response
Thread is waiting to get the response
Got response :
  <?xml version='1.0' encoding='utf-8'?>
    <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
     <soapenv:Body>
       <deepak:sayResponse xmlns:deepak="http://www.deepakmodi2006.blogspot.com/">0 to 9</deepak:sayResponse>
     </soapenv:Body>
  </soapenv:Envelope>
Thread is waiting to get the response
---------------------------------------------------------
//The containts "Got response :" and then XML contents come in single line in output, just to show reader,
I have broken this in line.
==========================================END==============================

Communication link failure, Mysql JDBC connection

Dear Reader,
  I am using mysql 5.0.x connector and tomcat 5.5.x DBCP. If you get this error in your application:-

com.mysql.jdbc.CommunicationsException: Communications link failure Last packet sent to 
the server was 1553186 ms ago.
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1070)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3246) 
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1917)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2536)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2465)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1383) at
org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
at com.adobe.gep.database.managers.DBConnection.executeQuery(DBConnection.java:76)

Caused by: java.net.SocketException: Connection reset
at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:96)
at java.net.SocketOutputStream.write(SocketOutputStream.java:136)
at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:65)
at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:123)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3227)

Don't panic:-
Just see whether firewall is disabled on that mysql port or not. If not, then disable that firewall.
You can see that using this command:
//Command
service iptables status

Then you can stop this using below command:
service iptables stop

-------------------End------------------