Git Clone Error : unable to get local issuer certificate

Recently I was cloning an internal git repository and got the below error :
Git Clone Error : unable to get local issuer certificate
On linux (ubuntu) this error message was:
fatal: unable to access ‘https://***.git/’: server certificate verification failed. CAfile: /etc/ssl/certs/ca-certificates.crt CRLfile: none
After bit of google search, I tried few things and was able to solve it. I am noting down here steps for future use. It should not take more than 5 mins to solve.

Why part:

Before I go to the fix, first it needs to be understood why this error came in first place.
Git comes by default with some predefined ca-bundle. So these will be the list of CAs (Certificate Authorities) git will be trusting while making the SSL connection to the git repository. While most of the public repository will have their SSL certificate signed by known CA. Internal repository had self-signed certificate.
Since it was self-signed certificate, it’s CA was not in git’s trusted CA list, so git refused to trust the SSL connection and dropped the request there and then.

Fix:

Export certificate in PEM format from the browser

Open the repository your are trying to access in browser. I used mozilla firefox. On the top left corner of firefox, there will be a lock button.
Click on lock button -> click on arrow(>) -> More information .
From there Securty -> View Certificate -> Details -> Export Certificate.
While exporting certificate select format : X.509 certificate with chain (PEM)  and save it at some known location.

Find the ca-bundles file location used by git

Windows:
It is generally located at : ${git_home_directory}mingw64/ssl/certs/ca-bundle.crt
You can find out actual path by executing the command : git config –list
Look for the line starting with http.sslcainfo , on my machine it was :
http.sslcainfo=C:/Program Files/Git/mingw64/ssl/certs/ca-bundle.crt

Linux:
On ubuntu the path is /etc/ssl/certs/ca-certificates.crt .. It is also shown in the error message.

Copy the exported certificate into ca-bundle

Open the certificate you exported from browser in notepad or similar text tool. Copy everything, including — BEGIN CERTIFICATE — till — END CERTIFICATE —
Open the ca-bundle file (ca-bundles.crt) file and in the end paste all copied content.
Save the ca-bundle file.

Now restart your terminal and try whatever git command you were trying and it should work !! Bingo !! Enjoy !!

How to improve TPS of UDP Server

Once upon a time I was given a task to improve TPS (transaction per second) of a UDP Server. UDP Server was a very simple program written in Java using datagram socket server. Program was receiving udp message and doing some replacement in the message. Each message was of a small size(around 1024 bytes).
But huge packet loss was being reported on client side.

Analysis for finding the root cause was as follows :

  • Being UDP server, minor percentage of packet loss is acceptable and if network is heavily loaded, then its unavoidable. But in this case around 80%-90% packets were being dropped. Sysadmin also checked network congestion and it was withing normal limits. So something else was definitely going wrong
  • Server machine configuration was also quite good. (64 GB RAM, 4 core cpu with 2.x GHz clock speed ),  network card attached was of 10Gbps) so there does not  seemed to be an issue with the machine hardware configuration.
  • Other area of suspicion was multi-threading. Program was single threaded. It was reading message and processing. So first thing which comes to every programmer is that let’s try multi-threading. I also went ahead with that though process and started looking at the code to make it multi-threaded.
    • But when I looked at code, I realized that it was just doing simple find replace after reading the message, which should not take more than few instructions on processor. 80% around package loss due to processing time was very much unlikely.
    • After looking at the code, it seemed that if code was made multi-threaded, it might increase the overall time, instead of decreasing it, due to the synchronization, thread switch and other overhead introduced by multi-threading
  • At this point, I was sure that packet loss is not happening due to network congestion, hardware configuration, long message processing time. So what is going wrong than?? why packet loss ??
  • So I started looking at all the steps involved in UDP packet processing. It has got mainly 2 steps
    • Packets are first received by OS and buffered
    • Program reads packets from this queue, in this case java program’s datagram socket reads from the OS packet buffer
  • Since the program was very simpler one, I started exploring about first point and try to find some issue over there
  • I found that in linux systems, UDP and TCP packets which are received are queued.
    • There is separate buffer for reading packets and writing packets
    • Each buffer has some default memory allocated, but we can configure that memory as per our requirements.
  • Now this could be the problem, if there is not enough memory allocated to the buffer then packet loss can occur, consider following scenarios
    • Packets are coming at higher speed than the processing capability of the program. If that is the case then packets will be buffered for some time and when the buffer becomes full, packet starts to drop
    • Packet are coming at much higher rate, packet rate is so higher that even if processing is able to cope up with the packet rate, but there is not enough memory to store it. I will elaborate this point with an example
      • Suppose packets are coming at rate of 100 packets/second
      • Program is able to process 100 packets/second
      • But OS has allocated memory to read buffer such that only 10 packets can be stored
        • Now say on the 1st second 10 packets will come, program will process that packets and everything works fine
        • 2nd second 100 packets will come simultaneously, but os buffer can store only 10 packets at a time, so it will store 10 packets and drop rest of the 90 packets. Program will process that 10 packets
        • Here though the program is capable of processing 100 packets, packet loss occurs due to lower memory allocated to OS read buffer. This situation is out of scope for any program and needs to be handled at OS level
  • Linux has configuration parameter for fine tuning the UDP and TCP read/writer buffer. Following parameters were changed :
    • net.core.rmem_default : The default setting of the socket receive buffer in bytes
    • net.core.wmem_default : The default setting(in bytes) of the socet send buffer
    • net.core.rmem_max : The maximum receive socket bufer size in bytes
    • net.core.wmem_max : The maximum send socket buffer in bytes.
    • net.core.netdev_max_backlog : Maximum number of packets, queued on the INPUT side, when the interface receives packets faster than kernel can process them.

Following are the commands to change parameters on RHEL:

  • sysctl -w net.core.rmem_default=73400320
  • sysctl -w net.core.wmem_default=73400320
  • sysctl -w net.core.rmem_max=73400320
  • sysctl -w net.core.wmem_max=73400320
  • sysctl -w net.core.netdev_max_backlog=3000

Above values should also be specified in /etc/sysctl.conf file so that values will be persisted during machine restart.

Follow below steps to add these values in /etc/sysctl.conf file

  • Open the /etc/sysctl.conf file
    • vi /etc/sysctl.conf
  • Now add the following properties in the file if not already present, if any property is already present in the file then please change the value for that property in the file
    • core.rmem_default = 73400320
    • core.wmem_default = 73400320
    • core.rmem_max = 73400320
    • core.wmem_max = 73400320
    • core.netdev_max_backlog = 3000
  • Save the /etc/sysctl.conf file

Here in my case I had configured 70MB of buffer and max_backlog of 3000, which turned out to be sufficient. Depending on the traffic requirement this might need to be adjusted.

Before these changes UDP server’s TPS was around 1000, after these changes TPS went up to 9000 and still there was a potential if traffic increases.

 

Eclipse useful shortcuts

Some of the useful shortcuts which I use :

  • Ctrl + Shft + r – Open Resource
  • Ctrl + Shft + t – Open Type
  • Ctrl + Shft + s – Then getter and setters
  • Ctrl + d – Remove Line
  • Ctrl + L – Go to the line number
  • Ctrl + / – Comment/Uncomment code
  • Ctrl + Shift + / – Add/Remove Block comment
  • Ctrl + O – Go to method/variable declration
  • Ctrl + Shft + O – Organize the Inputs
  • Ctrl + Shft + F – Formats the code
  • Alt + Left/Right Arrow – Move between last edited and where u were
  • Alt + Up/down arrow – Move the selection to up/down
  • Ctrl + 1 – Quick fix, Its important while implementing the interface methods
  • Ctrl + E – List of all the open edits
  • Ctrl + F6 (Move between Editor), Ctrl + F7 (Move between Views), Ctrl + F8 (Move between perspective)
  • Ctrl + F11 – Run the application
  • Ctrl + M – Maximize/Minimize the current tab
  • Ctrl + N – Create New Resouce
  • Ctrl + I – Corrects the intendataion
  • Ctrl + J – Incremental Search
  • Ctrl + Shift + L – Shows currently defined shortcut keys
  • F12 – Activate Editor
  • Ctrl + Shift + M – Add imports
  • Alt + Shft + J – Add JavaDoc Comment
  • F3 – Go to the Declration
  • Ctrl + Shift + W – Close all windows
  • Ctrl + r  – Run till the cursor (Useful during debugging the code)

 

Linux – Comparing two files byte by byte


cmp:
command can be used to compare two files byte by byte.
Some useful option of this command, which I use are as below :

-b : print differing bytes
-l : output byte numbers and values of all differing bytes

Example: 

cat test1.txt 
Hello World
cat test2.txt
hello world

cmp with no option set

cmp test1.txt test2.txt 
test1.txt test2.txt differ: byte 1, line 1

cmp with verbose output

cmp -bl test1.txt test2.txt 
1 110 H 150 h
7 127 W 167 w

 

 

Accessing spring managed beans from non spring managed classes

There may be situation when some part of application uses Springs for dependency injection and bean management and other part of application is outside the spring managed environment. If classes which are not managed by Spring requires access to the Spring managed classes, then directory creating instance of spring managed class with new operator will not work. Because if we use new for creating instance, then spring will not be able to wire the dependency correctly.

In such situations we need to create bridge between Spring managed beans and classes outside the spring management scope. Following is the code for accessing Spring Managed beans from non Spring managed classes.

import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;

/**
 * Bridge Between Spring Managed beans and non spring managed classes
 * @author Pranav Maniar
 */
@Component
public class SpringBridge implements ApplicationContextAware {

    // Maintain reference to Spring's Application Context
    private static ApplicationContext context;

    public void setApplicationContext(ApplicationContext context)
            throws BeansException {
        this.context = context;
    }

    // Make constructor private, so that the class can not be instantiated
    private SpringBridge() {
    }

    /**
     * Get Spring Managed bean from Non Spring Managed (outside of spring) classes
     * 
     * @param type, Class of Bean whose instance is required
     * @return Spring managed bean
     */
    public static <T> T getBean(Class<T> type) {
        return (T) context.getBean(type);
    }
}

For e.g. If there is Spring managed bean UserService, which needs to be accessed from some other class. In following way spring managed beans can be accessed in outside of spring (non managed) class.

 UserService userService = SpringBridge.getBean(UserService.class);

 

How does it work?

Spring Managed Component

First SpringBridge needs to be registered as spring managed component. In the above code it is done by using @Component annotation on top of the SpringBridge class. Also, SpringBridge class needs to be on the location of Spring’s component scan, so that spring can recognize it as spring managed component.

ApplicationContextAware Interface

By implementing this interface object will be notified of the ApplicationCotext that it runs in. Normally setApplicationContext method will be invoked after population of normal bean properties but before an init callback. SpringBridge class stores reference to the applicationContext in the static variable.  Using this applicationContext any bean can be looked up.

Generic method for returning the bean

Static method public static <T> T getBean(Class<T> type)  takes class name as a parameter and returns the instance of the bean. It looks up for the bean in ApplicationContext and if instance is found there, then it is returned.

Build Hadoop 2.7.x from source code on ubuntu

Following are the steps for building Hadoop 2.7.x from source code on ubuntu.

Checout Hadoop Code from Git

git clone git://git.apache.org/hadoop.git
git checkout branch-2.7.3

 

Install Dependencies

Install dependencies from apt-get

sudo apt-get update
sudo apt-get install openjdk-7-jdk maven git openssl dh-autoreconf cmake zlib1g-dev libssl-dev ssh rsync pkg-config

Install protocol buffer

wget https://github.com/google/protobuf/archive/v2.5.0.tar.gz
tar xvf v2.5.0.tar.gz
cd protobuf-2.5.0
./autogen.sh
./configure --prefix=/usr
make
make install

Install findbugs

wget https://sourceforge.net/projects/findbugs/files/findbugs/3.0.0/findbugs-3.0.0.zip/download
unzip download

 

Set Environment Variables

vim ~/.bashrc

## Put following lines at end of ~/.bashrc file
## Please change the path with appropriate value for your installation

export JAVA_HOME=/usr/lib/jvm/java-7-openjdk-amd64/
export FINDBUGS_HOME=/opt/findbugs-3.0.0/

 

Build Hadoop Distribution

mvn clean install -Pdist,native,docs,src -Dtar -DskipTests

Hadoop tar.gz distribution will be created in hadoop-dist/target/ directory

Hibernate GenericDao

Need for GenericDao

When we use hibernate in a typical J2EE project, there are some basic operations which must be supported on all hibernate entities. These operations are create, update, delete, find, findAll entities.  A generic code can be written which performs these basic operations for the all entities. This approach has following advantages

  • Generic code allows in removing the code duplication. It will save lot of time spent in writing/testing duplicate code.
  • It ensures the consistent interface. Consistent interface helps developer understand and implement code much faster

Sometimes the second point consistent interface does not seem too much of value, but I have seen projects where the interface was not consistent and due to which developer spent hours and hours of time just to understand the code and implement the things which were redundant.

 

How to write GenericDao

GenericDao needs to be written using Generics, so that it can support any hibernate entity. There is an interface GenericDao and it’s implementation GenericDaoImpl. All the Dao interface should extend the GenericDao interface and all the dao implementation should extend the GenerficDaoImpl implementation.

package in.pm.hibernate.genericdao_example.dao;

import java.io.Serializable;
import java.util.List;

public interface GenericDao&lt;T, PK extends Serializable&gt; {

    public PK create(T object);
    
    public T find(PK primaryKey);
    
    public List&lt;T&gt; finalAll();
    
    public void update(T object);
    
    public void delete(T object);
}

Following is the implementation for GenericDao. Here hibernate is configured directly with Spring and no JPA is used. But if we want to use the JPA then also the approach remains the same. We get the actual type of class by looking at the type argument of the generic class.

package in.pm.hibernate.genericdao_example.dao.impl;

import in.pm.hibernate.genericdao_example.dao.GenericDao;

import java.io.Serializable;
import java.lang.reflect.ParameterizedType;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;

public class GenericDaoImpl&lt;T, PK extends Serializable&gt; implements
        GenericDao&lt;T, PK&gt; {

    private Class&lt;T&gt; actualType;

    @Autowired
    private SessionFactory sessionFactory;

    @SuppressWarnings("unchecked")
    public GenericDaoImpl() {

        // get superclass declaration
        ParameterizedType genericSuperClass = (ParameterizedType) getClass()
                .getGenericSuperclass();
        // Find out the actual type
        this.actualType = (Class&lt;T&gt;) genericSuperClass.getActualTypeArguments()[0];

    }

    public Session getSession() {
        return sessionFactory.getCurrentSession();
    }

    @SuppressWarnings("unchecked")
    @Transactional
    public PK create(T object) {
        PK id = (PK) getSession().save(object);
        return id;
    }

    public T find(PK primaryKey) {
        T object = getSession().get(actualType, primaryKey);
        return object;
    }

    @SuppressWarnings("unchecked")
    public List&lt;T&gt; finalAll() {
        String findAllQueryStr = "from " + actualType.getName();
        Query findAllQuery = getSession().createQuery(findAllQueryStr);
        List&lt;T&gt; objects = (List&lt;T&gt;) findAllQuery.list();
        return objects;
    }

    public void update(T object) {
        getSession().update(object);
    }

    public void delete(T object) {
        getSession().delete(object);
    }

}

 

Usage of GenericDao

We will take two entities (Device, User) and try to see how GenericDao is useful in doing db operations for these two entities.

package in.pm.hibernate.genericdao_example.entity;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;

import org.hibernate.annotations.GenericGenerator;

@Entity
@Table(name = "Device")
public class Device {

    @Id
    @GeneratedValue(generator="increment")
    @GenericGenerator(name= "increment", strategy="increment")
    private Long id;
    
    private String deviceName;
    
    private String deviceType;

    
    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getDeviceName() {
        return deviceName;
    }

    public void setDeviceName(String deviceName) {
        this.deviceName = deviceName;
    }

    public String getDeviceType() {
        return deviceType;
    }

    public void setDeviceType(String deviceType) {
        this.deviceType = deviceType;
    }
    
     
}

 

package in.pm.hibernate.genericdao_example.entity;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;

import org.hibernate.annotations.GenericGenerator;

@Entity
@Table(name = "USER")
public class User {

    @Id
    @GeneratedValue(generator = "increment")
    @GenericGenerator(name = "increment", strategy = "increment")
    private Long id;

    private String firstName;
    private String lastName;
    private String email;
    
    
    public Long getId() {
        return id;
    }
    public void setId(Long id) {
        this.id = id;
    }
    public String getFirstName() {
        return firstName;
    }
    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }
    public String getLastName() {
        return lastName;
    }
    public void setLastName(String lastName) {
        this.lastName = lastName;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    
    
}

 

Dao for Entity

For each entity Dao class needs to be created which will be responsible for doing basic CRUD operation on entity and will support additional query if required.

Dao Interfaces

package in.pm.hibernate.genericdao_example.dao;

import in.pm.hibernate.genericdao_example.entity.Device;

public interface DeviceDao extends GenericDao&lt;Device, Long&gt;{

}
package in.pm.hibernate.genericdao_example.dao;

import in.pm.hibernate.genericdao_example.entity.User;

public interface UserDao extends GenericDao&lt;User, Long&gt; {
    
    public User getUserByName(String firstName);
}

 

Dao Implementation

package in.pm.hibernate.genericdao_example.dao.impl;

import org.springframework.stereotype.Repository;

import in.pm.hibernate.genericdao_example.dao.DeviceDao;
import in.pm.hibernate.genericdao_example.entity.Device;

@Repository
public class DeviceDaoImpl extends GenericDaoImpl&lt;Device, Long&gt; implements DeviceDao{

}
package in.pm.hibernate.genericdao_example.dao.impl;

import org.hibernate.Query;
import org.springframework.stereotype.Repository;

import in.pm.hibernate.genericdao_example.dao.UserDao;
import in.pm.hibernate.genericdao_example.entity.User;

@Repository
public class UserDaoImpl extends GenericDaoImpl&lt;User, Long&gt; implements UserDao{

    public User getUserByName(String firstName) {
        String userByName = "from User where user.firstName = :firstName ";
        Query userByNameQuery = getSession().createQuery(userByName);
        userByNameQuery.setString("firstName", firstName);
        return (User)userByNameQuery.uniqueResult();
    }
    
}

 

As we can see that since we have used generic dao, methods (create, update, delete, find and findAll)  are automatically inherited by both dao (DeviceDao and UserDao).  UserDao had additional requirement for finding user according to its name, so only that method is implemented in the UserDao.

This has ensured code is reused and consistent interface. For e.g. if we want to store any entity, we just have to call create method of that entity’s dao. Similar is the case for update, delete, find and findAll.

 

Test Application

Below is the test class which was used for testing. It creates two entity (User and Device) and inserts it into database.

package in.pm.hibernate.genericdao_example.app;

import in.pm.hibernate.genericdao_example.dao.DeviceDao;
import in.pm.hibernate.genericdao_example.dao.UserDao;
import in.pm.hibernate.genericdao_example.entity.Device;
import in.pm.hibernate.genericdao_example.entity.User;

import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;

public class App {
    
    @SuppressWarnings("resource")
    public static void main(String[] args) {
        
        ApplicationContext context = 
                new AnnotationConfigApplicationContext(AppConfiguration.class);
        
        UserDao userDao = context.getBean(UserDao.class);
        DeviceDao deviceDao = context.getBean(DeviceDao.class);
        
        User user = new User();
        user.setFirstName("Pranav");
        user.setLastName("Maniar");
        user.setEmail("pranav9428@gmail.com");
        
        Device device = new Device();
        device.setDeviceName("Moto G");
        device.setDeviceType("Mobile");
        
        userDao.create(user);
        deviceDao.create(device);
        
    }

}

 

Configure Hibernate5 with Spring4 using java configuration

To configure Hibernate5 with Spring4 using java configuration Spring Configuration class needs to be created. Also one property file containing information about database username/password , connection string, hibernate settings, etc will be required.

Below is the property file which is used during the configuration.

## jdbc configuration 
driverclass = com.mysql.jdbc.Driver
jdbcurl = jdbc:mysql://localhost/db
username = test
password = test

## hibernate configuration
hibernate.dialect = org.hibernate.dialect.MySQLDialect
hibernate.show_sql = true
hibernate.hbm2ddl = create

 

Configuration Class

@Configuration
@ComponentScan(basePackages = { "in.pm.hibernate.genericdao_example" })
@EnableTransactionManagement
public class AppConfiguration {

}

First create a configuration class and annotate it with following annotations

  • @Configuration : Through this annotation Spring will know that this is the java config class
  • @ComponentScan : Specifies the list of base packages which contains Spring beans and hibernate entities. When the spring container starts it will scan these packages and register the beans by reading annotations
  • @EnableTranscationManagement : This will enable spring’s annotation driven transaction management capability

 

 
    @Value("${driverclass}") 
    private String driverClass;
    
    @Value("${jdbcurl}")
    private String jdbcURL;
    
    @Value("${username}")
    private String userName;
    
    @Value("${password}")
    private String password;
    
    @Value("${hibernate.dialect}")
    private String hibernateDialect;
    
    @Value("${hibernate.show_sql}")
    private String hibernateShowSql;
    
    @Value("${hibernate.hbm2ddl}")
    private String hibernateHbm2ddlAuto;

Add properties and annotate it with appropriate value expression, so that value will be taken from property file and bound to the property

 

    @Bean
    public PropertyPlaceholderConfigurer getPropertyPlaceHolderConfigurer() {
        PropertyPlaceholderConfigurer ppc = new PropertyPlaceholderConfigurer();
        ppc.setLocation(new ClassPathResource("application.properties"));
        ppc.setIgnoreUnresolvablePlaceholders(true);
        return ppc;
    }

Create PropertyPlaceHolderConfigurer and provide it with the location of the property file. It will read the properties and populate fields defined in above step

 

    @Bean
    public DataSource getDataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(jdbcURL);
        dataSource.setUsername(userName);
        dataSource.setPassword(password);
        return dataSource;
    }

    public Properties getHibernateProperties() {
        Properties properties = new Properties();
        properties.put("hibernate.dialect", hibernateDialect);
        properties.put("hibernate.show_sql", hibernateShowSql);
        properties.put("hibernate.hbm2ddl.auto", hibernateHbm2ddlAuto);
        return properties;
    }

    //Create a LocalSessionFactoryBean which will be used to create hibernate SessionFactory
    @Bean
    @Autowired
    public LocalSessionFactoryBean getSessionFactory(DataSource dataSource) {
        LocalSessionFactoryBean sfb = new LocalSessionFactoryBean();
        sfb.setDataSource(dataSource);
        sfb.setPackagesToScan("in.pm.hibernate.genericdao_example.entity");
        sfb.setHibernateProperties(getHibernateProperties());
        return sfb;
    }

Now, create datasource and inject this datasource into the function which is used to create SessionFactory.

Create LocalSessionFactoryBean and set the datasource and hibernate properties. Also set the packages to be scanned for hibernate entities. LocalSessionFactoryBean is a Spring FactoryBean which is used to create Hibernate SessionFactory.

 

    @Bean
    @Autowired
    public HibernateTransactionManager transactionManager(SessionFactory sessionFactory) {
        HibernateTransactionManager tm = new HibernateTransactionManager();
        tm.setSessionFactory(sessionFactory);
        return tm;
    }

Finally create HibernateTransactionManager and assigned it the sessionFactory which was created in the previous step.

NOTE: Please use LocalSessionFactoryBean and HibernateTransactionManager which is under package “org.springframework.orm.hibernate5”.

 

Complete configuration class looks as below :

package in.pm.hibernate.genericdao_example.app;

import java.util.Properties;

import javax.sql.DataSource;

import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.beans.factory.config.PropertyPlaceholderConfigurer;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.orm.hibernate5.HibernateTransactionManager;
import org.springframework.orm.hibernate5.LocalSessionFactoryBean;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@ComponentScan(basePackages = { "in.pm.hibernate.genericdao_example" })
@EnableTransactionManagement
public class AppConfiguration {

    
    @Value("${driverclass}") 
    private String driverClass;
    
    @Value("${jdbcurl}")
    private String jdbcURL;
    
    @Value("${username}")
    private String userName;
    
    @Value("${password}")
    private String password;
    
    @Value("${hibernate.dialect}")
    private String hibernateDialect;
    
    @Value("${hibernate.show_sql}")
    private String hibernateShowSql;
    
    @Value("${hibernate.hbm2ddl}")
    private String hibernateHbm2ddlAuto;
    
    @Bean
    public PropertyPlaceholderConfigurer getPropertyPlaceHolderConfigurer() {
        PropertyPlaceholderConfigurer ppc = new PropertyPlaceholderConfigurer();
        ppc.setLocation(new ClassPathResource("application.properties"));
        ppc.setIgnoreUnresolvablePlaceholders(true);
        return ppc;
    }

    @Bean
    public DataSource getDataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(jdbcURL);
        dataSource.setUsername(userName);
        dataSource.setPassword(password);
        return dataSource;
    }

    public Properties getHibernateProperties() {
        Properties properties = new Properties();
        properties.put("hibernate.dialect", hibernateDialect);
        properties.put("hibernate.show_sql", hibernateShowSql);
        properties.put("hibernate.hbm2ddl.auto", hibernateHbm2ddlAuto);
        return properties;
    }

    //Create a LocalSessionFactoryBean which will be used to create hibernate SessionFactory
    @Bean
    @Autowired
    public LocalSessionFactoryBean getSessionFactory(DataSource dataSource) {
        LocalSessionFactoryBean sfb = new LocalSessionFactoryBean();
        sfb.setDataSource(dataSource);
        sfb.setPackagesToScan("in.pm.hibernate.genericdao_example.entity");
        sfb.setHibernateProperties(getHibernateProperties());
        return sfb;
    }

    //NOTE: Use HibernateTransactionManager which is under hibernate5 package only.
    @Bean
    @Autowired
    public HibernateTransactionManager transactionManager(SessionFactory sessionFactory) {
        HibernateTransactionManager tm = new HibernateTransactionManager();
        tm.setSessionFactory(sessionFactory);
        return tm;
    }

}

 

Hibernate Operation timed out error seen intermittently

Once we were intermittently getting “java.sql.SQLException: Io exception: Operation timed out” error on production environment.
After few trial and error, we came to the conclusion that it was due to the proxy between application server and new database server. The proxy was terminating the idle connection after some time.

Solution:

Hibernate connection pool configuration was changed to test the connection validity every few minutes using “idle_test_period” and “prefferedTestQuery
Connection pool parameter were changed from :

<property name="hibernate.c3p0.max_size">30</property>
<property name="hibernate.c3p0.min_size">10</property>
<property name="hibernate.c3p0.timeout">5000</property>
<property name="hibernate.c3p0.max_statements">0</property>
<property name="hibernate.c3p0.idle_test_period">3000</property>
<property name="hibernate.c3p0.acquire_increment">2</property>
<property name="hibernate.c3p0.validate">false</property>

to following

<property name="hibernate.c3p0.max_size">30</property>
<property name="hibernate.c3p0.min_size">0</property>
<property name="hibernate.c3p0.timeout">1200</property>
<property name="hibernate.c3p0.max_statements">0</property>
<property name="hibernate.c3p0.idle_test_period">300</property>
<property name="hibernate.c3p0.acquire_increment">2</property>
<property name="hibernate.c3p0.validate">false</property>
<property name="c3p0.preferredTestQuery">SELECT 1 from dual</property>

And after this small change, we never received the Operation timed out error again. 🙂 🙂

 

 

Detailed troubleshooting of the Issue (Short Story Long 😉 )

In my experience, I have found that intermittent production issue like this are very hard to debug. In the end issue and solution both might seem very trivial, but person who actually works on live issue might not be able to narrow down the root cause so easily. So I will share my experience on how the issue was debugged and narrowed down. It might help other in troubleshooting similar issues.

 

Once upon a time we were receiving annoying error “java.sql.SQLException: Io exception: Operation timed out” intermittently on our production server. And the only thing which had changed was that production application was moved to new faster server and production database was moved to new database server, the network speed was even faster than the previous environment. :O network speed was faster then it rules out the possibility of intermittent issues in network (network might be congested or something like that) . So I knew that operation timed out error is not due to network problem.

On new environment, there was a proxy between production application and production database and on previous environment proxy was not there between production application and production database.But how come this has caused problem? If proxy allows to establish connection between two machine once, it will allow all the time, It does not intermittently deny connections.

One possibility was that query was taking more time to execute and due to which operation was being timed out. I searched for setting of hibernate that increases query timeout to some greater value then the default and thus could buy some more time before operation times out. But then I remembered my earlier findings that query which was failing, used to execute within a second and operation time out error was coming after wait of 80 seconds. If database is heavily loaded then also it will not take 80 seconds to execute a query that used to be executed within seconds. Even if proxy was also heavily loaded, then also it was very unlikely that request would not go through till 80 seconds.

I explained our DBA about the problem and ask for his opinion and his first question was : Are you receiving any error starting with ORA. And I was like NOPE.  DBA explained to me that, if there is any error on oracle database side then it will always throw an error which will have ORA number. At that time I realized that ohh the error is not coming from the database. Then I looked at the error log again. The error “Io error : Operation timed” out was clearly saying that it was coming while jdbc was trying to read/write any data on the connection stream. connection stream??? Jdbc first creates connection with the database and it writes and reads data from that connection as we read/write data from any network stream. So now I knew that exception was generic java operation timed out exception and it was not coming from database.

If we think for java opeartion timed out error then it comes in the case when somebody is waiting on some operation(operation like getting resource, getting connection, etc ) and it does not completes in time. For e.g. If java program tries to connect to some url and that url cannot be connected in time then Operation timeout exception will be thrown. So in this case it should be trying to connect to the database and it somehow it should not be able to connect to database. But if I rethink about my previous statement (if it connects once it should connect always), I will straight away discard that possibility, because our another production application works perfectly on same server connecting to same database server. The only difference I see in other application is that it connects to database using direct jdbc connection and not through hibernate. Ok ok, so by now I know that there is a jdbc operation time out exception and it should not be coming while establishing connections.

After these findings, I again checked hibernate configurations file to look for any suspicion and I came across c3po connection pool configuration. Connection pool creates a connection in advance and when application wants to create a database connection it simply returns connection if it’s available in it’s pool and if it’s not available in the pool it creates a new connection and returns it. I looked at connection pool settings and found that connection pool was configured in such a way that at a time it should have minimum 10 connection in its pool and it number of connection should not exceed 30. Connection time out was set to 5000 seconds which is around 83 mins, :O 83 minutes, its quite high value. and time period to test if connection is idle was set to 3000 seconds, 50 mins, this interval for testing connection is idle is quite high.

If we consider at two important configuration then we can find out that timeout was set to 83 mins and idle test period was set to around 50 mins. It meant that after interval of 50 mins c3p0 will check for all connections that if connection are still valid or not by executing some validation query on the connection. and if the connection was idle till 83 mins then it will automatically time out.

So in our case c3p0 was checking connection was valid or not at the interval of 50 minutes. But proxy might have been configured to terminate idle connection before 50 mins.

Now at application startup c3p0 would have been creating and pooling min 10 connection. And they stays there in its pool. If application asks for connection it returns connection from its pool.If a application ask for connection before 50 mins say at 45 mins then c3p0 would not have checked that connection is still valid or not and it simply gives one connection from its pool assuming that they are still valid. But meanwhile if proxy is configured to disconnect connections that are idle for 30 mins then proxy should have dropped the connection to the database. So connection is not valid anymore but since c3p0 valid connection check interval is 50 mins it assumes that before 50 mins all connections are still valid and returns that connection to application.

Now application assumes that any connection returned from c3p0 is valid and it starts executing a query and that query does not executes since connection to the database is not valid and eventually this query execution operation times out giving error Io : Operation timeout error. and in this why we were receiving operation timed out error. Error must have been coming due to higher connection validation check timeout.

Solution for the problem is reduce connection timeout time and idle test period time so that c3p0 will execute a test query on connection before it is dropped by proxy. As c3p0 executes validation query every few minutes, connection no longer remains idle and proxy do not drops the connection and hence we do not get Operation time-out error anymore and which solves our problem. In addition to this we reduces min_size to 0 so that c3p0 will not have any connection in its pool.

So we changed connection timeout to 20 minutes, idle test period to 5 minutes and minimum connection to 0 and added preferredTestQuery. This query will be used to check validity of connection when c3p0 checks for validity of connection after idle test period.

<property name="hibernate.c3p0.min_size">0</property>
<property name="hibernate.c3p0.timeout">1200</property>
<property name="hibernate.c3p0.idle_test_period">300</property>
<property name="c3p0.preferredTestQuery">SELECT 1 from dual</property>

 

 

Oracle aggregate values into comma separated string

In Oracle many times we want to group rows of values in such a way that aggregated value is the comma separated list/string (csv) of all values.
Also, We might want that the aggregated csv value do not contain any duplicate inside the group function.

Below are two approaches to achieve this.

  1. Using LISTAGG function
  2. Using XMLAGG and XMLELEMENT function

 

Example Scenario:

To demonstrate the query, consider following sample scenario

There is an Employee table. It has three columns department_id, designation, first_name (There could be many other columns in the table, obvious ones are employee_id, grade, project, etc.. but for the sake of simplicity only three columns are included here. if more columns are there solution will work just fine with them also)

In each department, there could be various designations and multiple persons can have same designation. So for each department_id, there could be multiple designation and first_name

As a final solution following should be achieved,

  • For each department combine all designation and first_name into comma separated string.
  • Additionaly, duplicate values should be removed So for e.g. if there are multiple Employee has the same designation Software Developer then in the aggregated string Software Developer should appear only once

 
Sample Input:

department_id designation first_name
1 Developer Pranav
1 Tester Ravi
1 Developer Krupal

Sample Output:

department_id aggregate_designation aggregate_first_name
1 Developer,Tester Krupal,Pranav,Ravi

 

1. Using LISTAGG function

LISTAGG function has following syntax

LISTAGG(column_name, separator_character) WITHIN GROUP (ORDER BY ord_col_name) [OVER (PARTITION BY)]

  • column_name : name of the column, whose values needs to be combined in csv form
  • seperator_character : character to be used for separating values
  • ord_col_name : column name based on which result will be ordered
  • [OVER(Partition by)] : frankly speaking I don’t understand what this does and this is optional so I haven’t used it.

Following query will generate the desired output

SELECT department_id, 
       (SELECT LISTAGG(n.fn, ',') within GROUP (ORDER BY n.fn) 
        FROM  ( SELECT DISTINCT department_id, designation AS fn 
                FROM   employees e 
                ORDER  BY e.designation ) n 
        WHERE  n.department_id = emp.department_id) AS designation, 
       (SELECT LISTAGG(n.fn, ',') within GROUP (ORDER BY n.fn) 
        FROM  ( SELECT DISTINCT department_id, 
                                first_name AS fn 
                FROM   employees e 
                ORDER  BY e.first_name ) n 
        WHERE  n.department_id = emp.department_id) AS first_name 
FROM   employees emp 
GROUP  BY department_id 
ORDER  BY department_id

In the query group by is used for department_id column. So for each department_id all designation and first_name will be combined by the LISTAGG function and csv value will be generated.

Why below select subquery is required, woudn’t ground by with LISTAGG work without it ??

SELECT Listagg(n.fn, ',') 
         within GROUP (ORDER BY n.fn) 
FROM   (SELECT DISTINCT department_id, 
                        designation AS fn 
        FROM   employees e 
        ORDER  BY e.designation) n 
WHERE  n.department_id = emp.department_id

If we use LISTAGG with group by and without select subquery, then the final concatenated values will include duplicate entries.
To remove the duplicate sub-query in the select clause is used. This sub-query first finds out the unique value for each department and then LISTAGG function is applied on the unique value so that final concatenated values contains no duplicate.
For e.g. in the above sub-query first unique designation for each department is found (DISTINCT department_id, designation) then from that list designation for current department is taken (where n.department_id = emp.department_id ). Now these unique values are concatenated and a final csv is generated by applying listagg function on these unique values.

 

2. Using XMLAGG and XMLELEMENT function

XMLAGG : This function aggregates the values of xml-elements.

xmlagg(xmlelement ORDER BY column_name)

  • xmlelement: XML Element
  • column_name: Name of the column on which ordering should be done

XMLELEMENT : This function generates xml element from the column value.

xmlelement( xml_element_name , column_name )

  • xml_element_name : Any valid XML Node name
  • column_name : Name of the column whose values needs to be combined. Here multiple column name can also be used using “||” if value from multiple columns needs to be combined. If some constant value needs to be appended after each row’s value then add that constant value after “||”

extract(‘//text()’) : This function extracts the text part of the xml element. For e.g. if we have node <a>data</a>, It will return “data”, the text part of xml element

GetClobVal : Use this function if in your data, if final CSV is going to contain more than 4000 characters, if in doubt then it is always better to user this function, because it can handle larger data set

RTRIM: It trims the trailing character.

The final query looks like below

 SELECT   department_id,
         (
                  SELECT   rtrim( xmlagg( Xmlelement(e, ie.designation
                                    || ',').extract('//text()') order BY ie.designation ).getclobval(), ',')
                  FROM     (
                                           SELECT DISTINCT department_id,
                                                           designation
                                           FROM            employees ) ie
                  WHERE    ie.department_id = e.department_id ) AS designation,
         (
                  SELECT   rtrim( xmlagg( xmlelement(e, first_name
                                    || ',').extract('//text()') ORDER BY first_name).getclobval(), ',')
                  FROM     (
                                           SELECT DISTINCT department_id,
                                                           first_name
                                           FROM            employees ) ie
                  WHERE    ie.department_id = e.department_id ) AS first_name
FROM     employees e
GROUP BY department_id
ORDER BY department_id 

Simlar to approach 1, sub-query is used for removing the duplicates from the result

SELECT   rtrim( xmlagg( Xmlelement(e, ie.designation
                  || ',').extract('//text()') order BY ie.designation ).getclobval(), ',')
FROM     (
                         SELECT DISTINCT department_id,
                                         designation
                         FROM            employees ) ie
WHERE    ie.department_id = e.department_id 

 
 
Summary:

1st  approach seems simple, but it has limitation that concatenated string should not exceed 4000 character(varchar2 max length)
2nd approach does not have such limitations and can be used for all situatuions
 

Hope this helps 🙂