How to Change the WSO2 WSAS Database

WSO2 WSAS ships with an Apache Derby database, along with the Apache Derby EmbeddedDriver. This database is used for storing all data required by WSAS. One of the limitations of using this embedded configuration is that the database can only be used by a single Java process. This means, in order to query/update the database using a database administration tool, WSAS has to be shutdown. In a production environment, the database administrators/trouble-shooter will need to connect to the database without having to shutdown the server. Also, several instances of WSAS may have to share the same database, specially in the case of a clustered deployment. Date: Tue, 6th Jun, 2006 Level: Reads: 7262 Comments: 5 | Login or register to post comments

Afkham Azeez Dir WSO2

The solution is to switch to a different RDBMS such as MySQL, PostgreSQL or Oracle, which supports multiple connections. In this tutorial, we will look at how to configure WSO2 WSAS to work with MySQL & PostgreSQL RDBMSs.

My SQL

MySQL and WSAS have to be configured as follows:

1. Install and Configure MySQL. Installation and configuration information can be found at dev.mysql.com/doc/refman/5.0/en
2. Connect to the MySQL server using any MySQL client
3. Create the WSAS database if it does not exist. For example, in the case of the mysql command line client:

   mysql> create database wso2wsas_db;

4. Create the wso2wsas user in your MySQL database. See dev.mysql.com/doc/refman/5.0/en/adding-users.html for details.
5. Next you need to modify the database connection parameters as follows:
   1. In the WSAS standalone mode, locate the WSO2WSAS_HOME/conf/wso2wsas.hibernate.cfg.xml & modify it as follows:

      <?xml version='1.0' encoding='utf-8'?>
        <!DOCTYPE hibernate-configuration PUBLIC
            "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
            "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
      
        <hibernate-configuration>
        <session-factory>
      
        <!-- Database connection settings -->
        <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="connection.url">jdbc:mysql://127.0.0.1:3306/wso2wsas_db</property>
        <property name="connection.username">wso2wsas</property>
        <property name="connection.password">wso2wsas</property>
      
        <!-- JDBC connection pool (use the built-in) -->
        <property name="connection.pool_size">1</property>
      
        <!-- SQL dialect -->
        <property name="dialect">org.hibernate.dialect.MySQLDialect</property>
      
        <!-- Echo all executed SQL to stdout -->
        <!--property name="show_sql">true</property-->
      
        <!-- Posible values for hbm2ddl.auto (update | create | create-drop)-->
        <property name="hbm2ddl.auto">create</property>
      
        <mapping resource="wso2wsas.hbm.xml"/>
        </session-factory>
        </hibernate-configuration>

      Note that you have to specify the correct values for the connection.url, connection.username and connection.password properties. Also, the value of the hbm2ddl.auto property is "true", which will create all the tables when WSAS is started up for the first time.
   2. Embeddable distribution
      1. Tomcat
         • 5.5.x
           In the Tomcat server.xml, locate the following segment;

           <Context path="/wso2wsas" >
               <Resource name="jdbc/wso2wsas_db"
                   auth="Container"
                   type="javax.sql.DataSource"
                   maxActive="100"
                   maxIdle="30"
                   maxWait="10000"
                   username="wso2wsas"
                   password="wso2wsas"
                   driverClassName="org.apache.derby.jdbc.EmbeddedDriver"
                   url="jdbc:derby:../database/WSO2WSAS_DB;create=true"/>
             </Context>

           Change this to:

           <Context path="/wso2wsas" >
                <Resource name="jdbc/wso2wsas_db"
                   auth="Container"
                   type="javax.sql.DataSource"
                   maxActive="100"
                   maxIdle="30"
                   maxWait="10000"
                   username="wso2wsas"
                   password="wso2wsas"
                   driverClassName="com.mysql.jdbc.Driver"
                   url="jdbc:mysql://127.0.0.1:3306/wso2wsas_db"/>
             </Context>

         • 5.0.x & 4.1.x
           In the Tomcat server.xml, locate the following segment:

           <Context path="/wso2wsas" docBase="wso2wsas" reloadable="true" 
                crossContext="true">
                <Resource name="jdbc/wso2wsas_db" auth="Container"
                   type="javax.sql.DataSource"/>
                <ResourceParams name="jdbc/wso2wsas_db">                
                <parameter><name>username</name><value>
                   wso2wsas</value></parameter>
                <parameter><name>password</name><value>
                   wso2wsas</value></parameter>
                <parameter><name>driverClassName</name>
                <value>org.apache.derby.jdbc.EmbeddedDriver</value></parameter>
                <parameter><name>url</name>
                <value>jdbc:derby:../database/WSO2WSAS_DB;create=true</value>
                </parameter>
                </ResourceParams>
             </Context>

           Change this to:

           <Context path="/wso2wsas" docBase="wso2wsas"
                reloadable="true" crossContext="true">
                <Resource name="jdbc/wso2wsas_db" auth="Container"
                  type="javax.sql.DataSource"/>
                <ResourceParams name="jdbc/wso2wsas_db">
                <parameter><name>username</name><value>
                   wso2wsas</value></parameter>
                <parameter><name>password</name><value>
                   wso2wsas</value></parameter>
                <parameter><name>driverClassName</name>
                <value>com.mysql.jdbc.Driver</value></parameter>
                <parameter><name>url</name>
                <value>jdbc:mysql://127.0.0.1:3306/wso2wsas_db</value>
                </parameter>
                </ResourceParams>
             </Context>

      2. JBoss Locate the wso2wsas-derby-ds.xml in the JBoss deploy directory, and change it to correspond to the following:

         <datasources>
               <local-tx-datasource>
               <jndi-name>jdbc/wso2wsas_db</jndi-name>
               <connection-url>
                 jdbc:mysql://127.0.0.1:3306/wso2wsas_db</connection-url>
               <driver-class>com.mysql.jdbc.Driver</driver-class>
               <user-name>wso2wsas</user-name>
               <password>wso2wsas</password>
               </local-tx-datasource>
         
               </datasources>

      Once you have changed the above mentioned segments in Tomcat/JBoss, next locate the wso2wsas.war/WEB-INF/classes/hibernate.cfg.xml, and change it to correspond to the following:

      <hibernate-configuration>
         <session-factory>
           <!--Settings to connect to the database via a JNDI datasource-->
           <property name="connection.datasource">
             java:comp/env/jdbc/wso2wsas_db</property>
           <property name="connection.pool_size">1</property>
           <property name="dialect">
             org.hibernate.dialect.MySQLDialect</property>
           <property name="show_sql">false</property>
           <property name="hbm2ddl.auto">create</property>
           <mapping resource="wso2wsas.hbm.xml"/>
         </session-factory>
       </hibernate-configuration>

6. Place the jar file containing the MySQL JDBC driver in classpath of WSAS. We used the mysql-connector-java-3.0.16-ga-bin.jar along with MySQL 4.1.12 server.
7. Start WSAS. The startup should be successful, and all of the tables should have been created successfully, and some of the tables should have been populated. If you get an error while starting WSAS, the most probable reason will be the database driver being incompatible with your MySQL server.
8. Stop WSAS.
9. Locate the database parameter configuration file that was modified in step 5, and comment out the following line:

   <property name="hbm2ddl.auto">create</property>

   If this line is left uncommented, the startup time of WSAS would be unnecessarily high since Hibernate has to scan the entire database to check whether any database tables have to be created or updated.
10. Restart WSAS.

PostgreSQL

The steps are same as the above MySQL scenario. The only differences are the following parameters: Database driver : org.postgresql.Driver Database URL : jdbc:postgres:wso2wsas_db Hibernate SQL Dialect : org.hibernate.dialect.PostgreSQLDialect You would also have to use the proper jar containing the PostgreSQL JDBC driver. You can download the driver from jdbc.postgresql.org/download Create WSAS database as follows:

$ createdb wso2wsas_db -E unicode

You could add a new wso2wsas user with username 'wso2wsas' and password 'wso2wsas' as follows:

postgres@azeez:~$ psql wso2wsas_db
Welcome to psql 8.0.3, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

wso2wsas_db=# create user wso2wsas with password 'wso2wsas';

Similarly, for any other RDBMS, you could simply create the WSAS database, and configure the database connection parameters as explained above, and start WSAS, at which point the database tables should get created and populated.

Applies To

WSO2 WSAS 2.0 & 2.1

More Information

Rest of the WSO2 WSAS HOWTO Series

Author

Afkham Azeez, Architect & Product Manager of WSO2 WSO2 WSAS, WSO2 Inc. Azeez @ WSO2