Monitor database calls using log4jdbc jar file.

When testing WSO2 API manager gateway resource cache I needed to monitor database calls in order to verify whether resources are taken from the cache or not. In this blog post I will explain how to use "log4jdbc.jar" to monitor db calls.

Note: 

• I have used mysql database as my database connection. You can also use this for other databases as h2, Oracle.  
• Create database tables before configuring 'log4jdbc'. To do that you can remove 'log4jdbc.jar' from the /repository/components/lib folder start with -Dsetup, once server is started successfully shutdown server add log4jdbc.jar and restart. 

How to configure

1. Add 'log4jdbc.jar' to /repository/component/lib folder. You may also have the  JDBC connector jar in this folder based on the database type you are using. You can download 'log4jdbc.jar' from here 

2. Edit /repository/conf/datasources/master-datasources.xml by giving "net.sf.log4jdbc.DriverSpy" as the driverClassName and  specify URL as "jdbc:log4jdbc:mysql://localhost:3306/apim1100_carbon".

        

3. Add below entries to /repository/conf/log4j.properties file

#Log all JDBC calls except for ResultSet calls 
#Log timing information about the SQL that is executed. 
log4j.logger.jdbc.sqltiming=DEBUG,sqltiming 
log4j.additivity.jdbc.sqltiming=false 

#the appender used for the JDBC API layer call logging above, sql timing 
log4j.appender.sqltiming=org.apache.log4j.FileAppender 
log4j.appender.sqltiming.File=./repository/logs/sqltiming.log 
log4j.appender.sqltiming.Append=false 
log4j.appender.sqltiming.layout=org.apache.log4j.PatternLayout 

log4j.appender.sqltiming.layout.ConversionPattern=-----> %d{yyyy-MM-dd HH:mm:ss.SSS} %m%n%n 

4. you can navigate to /repository/logs and tail 'sqltiming.log' file in order to monitor which queries are been executed. 

Reference

[1]. http://cnapagoda.blogspot.com/2014/06/wso2-governance-registry-monitor.html

Fix for WSO2 API Manager startup error "Caused by: java.sql.SQLException: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging..." With MariaDB 5.5

Exception

ERROR - DefaultRealmService Cannot initialize the realm.
org.wso2.carbon.user.core.UserStoreException: nullType class java.lang.reflect.InvocationTargetException
at org.wso2.carbon.user.core.common.DefaultRealm.createObjectWithOptions(DefaultRealm.java:377)
at org.wso2.carbon.user.core.common.DefaultRealm.initializeObjects(DefaultRealm.java:199)
at org.wso2.carbon.user.core.common.DefaultRealm.init(DefaultRealm.java:108)
at org.wso2.carbon.user.core.common.DefaultRealmService.initializeRealm(DefaultRealmService.java:225)
at org.wso2.carbon.user.core.common.DefaultRealmService.(DefaultRealmService.java:96)
at org.wso2.carbon.user.core.common.DefaultRealmService.(DefaultRealmService.java:109)
at org.wso2.carbon.user.core.internal.Activator.startDeploy(Activator.java:68)
at org.wso2.carbon.user.core.internal.BundleCheckActivator.start(BundleCheckActivator.java:61)
at org.eclipse.osgi.framework.internal.core.BundleContextImpl$1.run(BundleContextImpl.java:711)
at java.security.AccessController.doPrivileged(Native Method)
at org.eclipse.osgi.framework.internal.core.BundleContextImpl.startActivator(BundleContextImpl.java:702)
at org.eclipse.osgi.framework.internal.core.BundleContextImpl.start(BundleContextImpl.java:683)
at org.eclipse.osgi.framework.internal.core.BundleHost.startWorker(BundleHost.java:381)
at org.eclipse.osgi.framework.internal.core.AbstractBundle.resume(AbstractBundle.java:390)
at org.eclipse.osgi.framework.internal.core.Framework.resumeBundle(Framework.java:1176)
at org.eclipse.osgi.framework.internal.core.StartLevelManager.resumeBundles(StartLevelManager.java:559)
at org.eclipse.osgi.framework.internal.core.StartLevelManager.resumeBundles(StartLevelManager.java:544)
at org.eclipse.osgi.framework.internal.core.StartLevelManager.incFWSL(StartLevelManager.java:457)
at org.eclipse.osgi.framework.internal.core.StartLevelManager.doSetStartLevel(StartLevelManager.java:243)
at org.eclipse.osgi.framework.internal.core.StartLevelManager.dispatchEvent(StartLevelManager.java:438)
at org.eclipse.osgi.framework.internal.core.StartLevelManager.dispatchEvent(StartLevelManager.java:1)
at org.eclipse.osgi.framework.eventmgr.EventManager.dispatchEvent(EventManager.java:230)
at org.eclipse.osgi.framework.eventmgr.EventManager$EventThread.run(EventManager.java:340)
Caused by: java.lang.reflect.InvocationTargetException
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
at org.wso2.carbon.user.core.common.DefaultRealm.createObjectWithOptions(DefaultRealm.java:334)
... 22 more
Caused by: org.wso2.carbon.user.core.UserStoreException: Error occurred while updating database
at org.wso2.carbon.user.core.util.UserCoreUtil.persistDomain(UserCoreUtil.java:733)
at org.wso2.carbon.user.core.common.AbstractUserStoreManager.persistDomain(AbstractUserStoreManager.java:3445)
at org.wso2.carbon.user.core.jdbc.JDBCUserStoreManager.(JDBCUserStoreManager.java:252)
at org.wso2.carbon.user.core.jdbc.JDBCUserStoreManager.(JDBCUserStoreManager.java:194)
... 27 more
Caused by: org.wso2.carbon.user.core.UserStoreException: Error occurred while updating database
at org.wso2.carbon.user.core.util.DatabaseUtil.updateDatabase(DatabaseUtil.java:534)
at org.wso2.carbon.user.core.util.UserCoreUtil.persistDomain(UserCoreUtil.java:729)
... 30 more
Caused by: java.sql.SQLException: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:998)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3847)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3783)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2447)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2594)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1901)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2113)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2049)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2034)
at org.wso2.carbon.user.core.util.DatabaseUtil.updateDatabase(DatabaseUtil.java:524)
... 31 more
[2015-08-21 10:54:27,698] ERROR - Activator Cannot start User Manager Core bundle
org.wso2.carbon.user.core.UserStoreException: Cannot initialize the realm.
at org.wso2.carbon.user.core.common.DefaultRealmService.initializeRealm(DefaultRealmService.java:233)
at org.wso2.carbon.user.core.common.DefaultRealmService.(DefaultRealmService.java:96)
at org.wso2.carbon.user.core.common.DefaultRealmService.(DefaultRealmService.java:109)
at org.wso2.carbon.user.core.internal.Activator.startDeploy(Activator.java:68)
at org.wso2.carbon.user.core.internal.BundleCheckActivator.start(BundleCheckActivator.java:61)
at org.eclipse.osgi.framework.internal.core.BundleContextImpl$1.run(BundleContextImpl.java:711)
at java.security.AccessController.doPrivileged(Native Method)
at org.eclipse.osgi.framework.internal.core.BundleContextImpl.startActivator(BundleContextImpl.java:702)
at org.eclipse.osgi.framework.internal.core.BundleContextImpl.start(BundleContextImpl.java:683)
at org.eclipse.osgi.framework.internal.core.BundleHost.startWorker(BundleHost.java:381)
at org.eclipse.osgi.framework.internal.core.AbstractBundle.resume(AbstractBundle.java:390)
at org.eclipse.osgi.framework.internal.core.Framework.resumeBundle(Framework.java:1176)
at org.eclipse.osgi.framework.internal.core.StartLevelManager.resumeBundles(StartLevelManager.java:559)
at org.eclipse.osgi.framework.internal.core.StartLevelManager.resumeBundles(StartLevelManager.java:544)
at org.eclipse.osgi.framework.internal.core.StartLevelManager.incFWSL(StartLevelManager.java:457)
at org.eclipse.osgi.framework.internal.core.StartLevelManager.doSetStartLevel(StartLevelManager.java:243)
at org.eclipse.osgi.framework.internal.core.StartLevelManager.dispatchEvent(StartLevelManager.java:438)
at org.eclipse.osgi.framework.internal.core.StartLevelManager.dispatchEvent(StartLevelManager.java:1)
at org.eclipse.osgi.framework.eventmgr.EventManager.dispatchEvent(EventManager.java:230)
at org.eclipse.osgi.framework.eventmgr.EventManager$EventThread.run(EventManager.java:340)
Caused by: org.wso2.carbon.user.core.UserStoreException: nullType class java.lang.reflect.InvocationTargetException
at org.wso2.carbon.user.core.common.DefaultRealm.createObjectWithOptions(DefaultRealm.java:377)
at org.wso2.carbon.user.core.common.DefaultRealm.initializeObjects(DefaultRealm.java:199)
at org.wso2.carbon.user.core.common.DefaultRealm.init(DefaultRealm.java:108)
at org.wso2.carbon.user.core.common.DefaultRealmService.initializeRealm(DefaultRealmService.java:225)
... 19 more
Caused by: java.lang.reflect.InvocationTargetException
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
at org.wso2.carbon.user.core.common.DefaultRealm.createObjectWithOptions(DefaultRealm.java:334)
... 22 more
Caused by: org.wso2.carbon.user.core.UserStoreException: Error occurred while updating database
at org.wso2.carbon.user.core.util.UserCoreUtil.persistDomain(UserCoreUtil.java:733)
at org.wso2.carbon.user.core.common.AbstractUserStoreManager.persistDomain(AbstractUserStoreManager.java:3445)
at org.wso2.carbon.user.core.jdbc.JDBCUserStoreManager.(JDBCUserStoreManager.java:252)
at org.wso2.carbon.user.core.jdbc.JDBCUserStoreManager.(JDBCUserStoreManager.java:194)
... 27 more
Caused by: org.wso2.carbon.user.core.UserStoreException: Error occurred while updating database
at org.wso2.carbon.user.core.util.DatabaseUtil.updateDatabase(DatabaseUtil.java:534)
at org.wso2.carbon.user.core.util.UserCoreUtil.persistDomain(UserCoreUtil.java:729)
... 30 more
Caused by: java.sql.SQLException: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:998)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3847)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3783)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2447)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2594)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1901)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2113)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2049)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2034)
at org.wso2.carbon.user.core.util.DatabaseUtil.updateDatabase(DatabaseUtil.java:524)
... 31 more

Fix

1. Shutdown Confluence and your MySQL database

2. Open the MySQL configuration file (my.cnf) in a text editor

3.Locate the binlog_format property in this file in the [mysqld] section and ensure that its value is    

         row, such that you end up with:

         binlog_format=row

4. Save your changes to this file

5. Restart Confluence and your MySQL database

References

[1]. http://bugs.mysql.com/bug.php?id=40360

[2]. https://confluence.atlassian.com/display/STASHKB/Stash+2.7+fails+to+start+with+MySQL+when+binary+logging+is+enabled

Note : 

When using WSO2 products with MariaDB it is recommended to use compatible version MySQL JDBC connector. EX: MariaDB 10.0.20 with mysql-connector-java-5.1.36-bin.jar 

How to trouble shoot when you get a "Connection refused" when accessing an application running on a different server (centOS / RHELs)

Say you are trying to connect to server running on a remote machine you get a connection refused.

EX: Error massage says connection refused when trying to connect to https://192.168.1.1:8090/server/home

One of the reasons for this error can be your remote machine doesn't allow other IPs to connect through the particular port. In other words the the port which your trying connect is closed.  To check that

1. Ping to the particular IP

Ex : In the terminal you can run
ping 192.168.1.1

If it is successful;

2. Telnet and see whether you can connect to the port

Ex: telnet 192.168.1.1 8090

3. If you get a connection refused for above command then try below command.

sudo iptables -F INPUT

** Above command flushes all rules and open all ports for the logged it session.

4. Then when you telnet to the port and if you successfully get connected with the port the issue is the particular port is closed.

5. To open the particular port you need to add it to /etc/sysconfig/iptables.

EX: -A INPUT -m state --state NEW -m tcp -p tcp --dport 8090 -j ACCEPT

Note : When adding make sure you add it before REJECT rules.

6.  After adding you run

sudo service iptables restart

Now you have opened the particular port and other machine should be successfully connect using that port. 

Common configurations which can go wrong when setting up WSO2 API Manager cluster setup. 

Background 

In this post I will be focusing on a set up which has a publisher node, store node, two key manager nodes, one gateway manager node and one gateway worker node. Key manager and gateway cluster is fronted using Nginx.

Observation 01

• 1.1. Published APIs cannot be seen in the store.
• 1.2. API can be seen under APIs section of the carbon console of both publisher and store. 
• 1.3. When you search given the published API is doesn't appear in the search results however when user restart the store node it user can see the API only in search result when user search the API.       
• When user reindex by deleting the content inside <APIM_Home>/solar folder and changing the last access location under "<indexingConfiguration>" tag in <APIM_Home>/repository/conf/registry.xml, all the APIs created so far get lists yet when her publishes an API after 

Reason for this issue : When you haven't configured cacheId property in registry.xml given the same username which you have used in datasource in  <APIM_Home>/repository/conf/datasources/master-datasources.xml this issue occurs

Observation 02

After starting cluster with no exceptions if your getting "401 username or password incorrect" when trying to login into the system. 

Things to be checked:

• Check whether hard coded username password inside 
• publisher 
• store
• gateway nodes are correct (should be same as the admin username password given inside <AM_HOME>/repository/conf/user-mgt.xml)
• If you have configured a load balancer, say Nginx when ip hash or sticky sessions doesn't work properly this can happen.  There issue can occur when authentication call goes one of the key manager node and authorisation call goes to another one. 

How to fix Soap UI - 5.0.0 freezing issue on OS X Mavericks (10.9.5)

In mac os Soap UI gets installed into /Applications

You can simply fix the freezing issue by disabling browser component. Bellow I have included the steps.

1. Stop soap UI processes if running.
2. Go to /Applications/SoapUI-5.0.0.app/Contents/Resources/app/bin
3. Open soapui.sh
4. Uncomment "JAVA_OPTS="$JAVA_OPTS -Dsoapui.browser.disabled=true"
5. Save and close
6. Run ./soapui.sh  in order to start Soap UI.

How to create a rdbms data service as a capp deployable artifact using WSO2 Developer Studio.

Introduction

In this blog post I will be creating a sample data service which has two operations to get all employees under a one department by giving department id and insert employee details. I will be covering following steps in order to create sample.

1. Prerequisites 
2. Create Database 
3. Create data service as a .car using WSO2 developer studio
4. Deploy data service on WSO2 data service server.

Prerequisites

1. Eclipse IDE with Maven and WSO2 developer studio plugins. For more information see: How to install WSO2 developer studio plugin in eclipse.
2. WSO2 data service server. Click here to download.
3. MySQL JDBC connector. Click here to download. 

Create Database

For this example I have provided two sql scripts, mysql.sql which can be used to create database and relevant tables, and data.sql to insert sample data into tables created. In order to create database used in this example please follow given steps below. 

Download and install MySQL Server.

   1. Enter following command in the terminal, where username is the username you want to        use to access the database. 

       mysql> mysql -u username -p 

   2. When prompted enter the password of the specified user name. 
   3. Source mysql.sql file downloaded using below command.

       mysql> source <path to mysql.sql file>/mysql.sql 

   4. Create a new database user given username as 'sewmi' and password as password.

   5. Give all privileges to database 'finance' for created user sewmi. 

    mysql> CREATE USER 'sewmi'@'localhost' IDENTIFIED BY 'password';

   mysql> GRANT ALL PRIVILEGES ON finance . * TO 'sewmi'@'localhost';

   mysql> FLUSH PRIVILEGES;

   6. Source data.sql file downloaded using below command 

     mysql> source <path to data.sql file>/data.sql

   7. To check database finance is created use below command

     mysql> show databases;

   8. To check whether the tables are created use;

     mysql> use finance;

     mysql> show tables;

   9. Output of query 7 and 8 should be as below.

   10. To check whether data is being successfully inserted;

     mysql> select * from <table name>;

If data.sql scrip successfully executed user should be able to see data in created data     tables.

Employee table with sample data

Create a carbon deployable data service

1. Open Eclipse IDE

2. Select developer studio > Open Dashboard.

3. Select composite application project.

4. In the wizard give project name as 'TestApp' and click 'finish'.
5. Right click on project  and select TestApp > new > Data service project.

 

6. Data service artifact creation operation wizard will open. Select 'create new data service'     radio button and click 'next'.
7. Give project name as 'RdbmsDataServiceSample' and data service name as          EmployeeDataService and click 'next' 
8. Configure datasource given FinanceDB as datasource id, 'RDBMS' as datasource type,       JDBC URL, username as sewmi password as 'password' and click 'finish'. 

9. Right click on 'EmployeeDataService' and select Add Query.

10. Give query is as 'getEmployeesUnderSameDepartment' and save. (Data source should       be already selected as FinanceDB)

11. Right click on added query in the previous and select 'Add Sql'. Editor will open to    enter sql query. Enter below query as shown in the screenshot and save. 

SELECT department.dept_name, employee.first_name, employee.last_name FROM department INNER JOIN Dept_Emp ON department.dept_no = Dept_Emp.dept_no INNER JOIN employee ON Dept_Emp.emp_no = employee.emp_no WHERE department.dept_no = ?

12. Right click on the query and select Add input mapping.

Note: We use an input mapping when ever we have a variable to pass in order to execute 

query. In this example in above step department number should be given when invoking the service therefore we need to add properties of the variable as an input mapping. As shown below mapping name can be any name which represent the parameter we pass. Parameter type is given as SCALR since it is a single value. (Not an array). sql type is given as integer since parameter is an integer. 

13. Right click on added query and select 'Add Output Mapping'.

14. Enter employees 'In Group by Element' field, employee in 'Row Name' and save. 

15. Right click on the query and select added output mapping, > Add Output Mapping > Add Element.

Note : As shown in the screenshot below we need to configure elements for the information return by the query. In this example when user pass department id he should get department name, user's first name and last name. therefore we need to add three elements for department name, first name and last name, under result which specifies a mapping between actual database column name and name which should be display as output field name. 

Element - 01

Element - 02

Element - 03

16. Right click on 'EmployeeDataService' and select Add operation. 
17. Click on newly added operation and give it a meaningful name. 
Click on the arrow head in front of the operation, when the list expands select 'call-query' element. You will get an option to incorporate  query you created in the previous step with the newly added operation. Select correct query relate to operation. If you 

18. Right click on 'EmployeeDataService' and select Add resource. Give resource method as 'get' Specify resource path; in this example i have used resource1 as the resource path. Then click on the query under resource in the project outline window. select 'getEmployeeDetails' as the query Id. 

Note : Adding a resource is not a compulsory step. Using resources we can expose the data service as a rest service. 

Now we can build the project in order to get the .car deployable. When building the project,
1. Build the data service pom file.
2. Once the project is build then build the pom file of the main project. 

After successfully building you will be able to find the .dbs file under the target folder of the data service and the .car file under the target folder of the main project. 

Deploy data service under WSO2 data service server. 

1. Unzip wso2 data serve server (DSS).

2. Copy downloaded jdbc connector jar into <DSS_home>/repository/components/libs

3. Go to <DSS_home>/bin and run ./wso2server.sh in the command line. 

4. Once server is up you will see "WSO2 Carbon started in 15 sec" and the URL to access  DSS in the log.

5. go to https://192.168.1.7:9443/carbon/.

6.Login to the mgt console given username and password as admin.

7. Then go to main > carbon applications > Add

8. Select .car deployable file we created and upload.

9. Once it is uploaded data service will be deployed on the server and to view it go to main > services > list.