Background: I am using hibernate with my sql for my model layer with spring to expose REST services. I have a mobile client that uses the rest services on the backend. The code is currently deployed on an AWS micro instance.
Problem: As soon as the nunber of concurrent users grow beyond 30-40 I start getting a "com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: Too many connections". I understand that the mysql db on a micro isntance is configured to have a maximum of 30-35 connections, but in a case where the load is high I expect the calls to wait and not error out. Becoming a little slow is acceptable, but it should not error out.
Code: Here is by hibernate cfg file:
com.jolbox.bonecp.provider.BoneCPConnectionProvider
org.hibernate.dialect.MySQLDialect
com.mysql.jdbc.Driver
<property name="bonecp.driverClass">com.mysql.jdbc.Driver</property>
<property name="bonecp.jdbcUrl">jdbc:mysql://***.rds.amazonaws.com:3306/jooky</property>
<property name="bonecp.username">**</property>
<property name="bonecp.password">**</property>
<property name="bonecp.maxConnectionAgeInSeconds">30</property>
<property name="bonecp.idleConnectionTestPeriodInMinutes">60</property>
<property name="bonecp.maxConnectionsPerPartition">25</property>
<property name="bonecp.minConnectionsPerPartition">10</property>
<property name="bonecp.partitionCount">1</property>
<property name="bonecp.acquireIncrement">5</property>
<property name="bonecp.statementsCacheSize">50</property>
<property name="acquireRetryDelayInMs">500</property>
<property name="acquireRetryAttempts">200</property>
<mapping class="jooky.model.entity.Party" />
<mapping class="jooky.model.entity.LocationRequest" />
<mapping class="jooky.model.entity.Location" />
<mapping class="jooky.model.entity.UserCheckinHistory" />
<mapping class="jooky.model.entity.Person" />
<mapping class="jooky.model.entity.UserLocSearchHistory"/>
</session-factory>
The code I have to expose REST services is:
@RequestMapping(value = "/getLocations", method = RequestMethod.POST)
public @ResponseBody
String getLocations(@RequestBody String input) {
Gson gson = new GsonBuilder().create();
NearbyLocationsRequest request = gson.fromJson(input,
NearbyLocationsRequest.class);
SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
//SessionFactory sessionFactory = ControllerUtil.getSessionFactory();
Session session = sessionFactory.openSession();
session.beginTransaction();
String ret="Error:UNKNOWN_EXCEPTION: Server error.";
List results = null;
try {
// NearbyLocationQueryInput inputGson = gson.fromJson(input,
// NearbyLocationQueryInput.class);
if(request.getPersonId()== null )
{
ret="Error:CANNOT_CONNECT_PER_ID_NULL: Person Id cannot be null";
session.getTransaction().rollback();
session.close();
sessionFactory.close();
return ret;
}
List users = session
.createCriteria(Person.class)
.add(Restrictions.eq("personId", request.getPersonId()))
.list();
Person person = null;
if (users != null && users.size() > 0) {
person = (Person) users.get(0);
if(person.getPoints()==null)
person.setPoints(0);
}
else
{
ret="Error:CANNOT_CONNECT_PER_NOT_FOUND: No person found with id:"+request.getPersonId();
session.getTransaction().rollback();
session.close();
sessionFactory.close();
return ret;
}
double latitude = request.getLatitude();
double longitude = request.getLongitude();
// To search by kilometers instead of miles, replace 3959 with 6371
int distConstant = 6371;
if ("MILES".equals(request.getDistanceUnit()))
distConstant = 3959;
int queryRadius = 100;
if (request.getQueryRadius() != null)
queryRadius = request.getQueryRadius();
int maxResults = 100;
if (request.getMaxResults() != null)
maxResults = request.getMaxResults();
// latitude = input.getLatitude();
// longitude = input.getLongitude();
// Closest within radius of 25 Miles
Query query = session
.createSQLQuery(
" SELECT LOCATION_ID, LOCATION_NAME, LOC_FB_ID, LOC_GOOGLE_ID, LOCATION_EMAIL, FB_PAGE, IMAGE_LOCATION, "
+ " ADDRESS1, ADDRESS2, ADDRESS3, CITY, STATE, COUNTRY, ZIP, PHONE1, PHONE2, "
+ " PLAYLIST_NAME, PLAYER_STATUS, LOCATION_TYPE, CUSINE, LATITUDE, LONGITUDE, OPENING_TIME, CLOSING_TIME, HEARTBEAT_TIME, PLAYLIST_VERSION, "
+ " LOCATION_MESSAGE, WEBSITE, HIGHLIGHTS , "
+ " ( "
+ distConstant
+ " * acos( cos( radians(:bind_lat) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(:bind_long) ) + sin( radians(:bind_lat) ) * sin( radians( latitude ) ) ) ) AS DISTANCE "
+ " FROM location HAVING distance < "
+ queryRadius
+ " "
+ " ORDER BY distance LIMIT " + maxResults)
.addEntity(Location.class);
query.setParameter("bind_long", longitude);
query.setParameter("bind_lat", latitude);
results = query.list();
UserLocSearchHistory history = new UserLocSearchHistory();
history.setId(new UserLocSearchHistoryId(request.getPersonId(), new Date()));
history.setAppVersion(request.getAppVersion());
history.setLatitude(new BigDecimal(latitude));
history.setLongitude(new BigDecimal(longitude));
session.save(history);
NearbyLocationsResponse response = new NearbyLocationsResponse();
response.setUserPoints(person.getPoints());
response.setLocations(results);
session.getTransaction().commit();
session.flush();
session.close();
sessionFactory.close();
ret=gson.toJson(response);
} catch (Exception e) {
session.getTransaction().rollback();
session.flush();
session.close();
sessionFactory.close();
e.printStackTrace();
}
return ret;
}
Error Stack: HTTP Status 500 - Request processing failed; nested exception is org.hibernate.HibernateException: org.hibernate.HibernateException: java.sql.SQLException: Unable to open a test connection to the given database. JDBC url = jdbc:mysql://***.rds.amazonaws.com:3306/jooky, username = jooky. Terminating connection pool. Original Exception: ------type Exception report
message Request processing failed; nested exception is org.hibernate.HibernateException: org.hibernate.HibernateException: java.sql.SQLException: Unable to open a test connection to the given database. JDBC url = jdbc:mysql://****.amazonaws.com:3306/jooky, username = ****. Terminating connection pool. Original Exception: ------
description The server encountered an internal error that prevented it from fulfilling this request.
exception
org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.hibernate.HibernateException: org.hibernate.HibernateException: java.sql.SQLException: Unable to open a test connection to the given database. JDBC url = jdbc:mysql://*****.com:3306/jooky, username = jooky. Terminating connection pool. Original Exception: ------
com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: Too many connections
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:921)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:885)
at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3421)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1247)
at com.mysql.jdbc.Connection.createNewIO(Connection.java:2775)
at com.mysql.jdbc.Connection.<init>(Connection.java:1555)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285)
at java.sql.DriverManager.getConnection(DriverManager.java:571)
at java.sql.DriverManager.getConnection(DriverManager.java:215)
at com.jolbox.bonecp.BoneCP.obtainRawInternalConnection(BoneCP.java:309)
at com.jolbox.bonecp.BoneCP.<init>(BoneCP.java:346)
at com.jolbox.bonecp.provider.BoneCPConnectionProvider.createPool(BoneCPConnectionProvider.java:167)
at com.jolbox.bonecp.provider.BoneCPConnectionProvider.configure(BoneCPConnectionProvider.java:141)
at com.jolbox.bonecp.provider.BoneCPConnectionProvider.configure(BoneCPConnectionProvider.java:271)
at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.configureService(StandardServiceRegistryImpl.java:89)
at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:206)
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:973)
org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:863)
javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837)
javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
root cause org.hibernate.HibernateException: org.hibernate.HibernateException: java.sql.SQLException: Unable to open a test connection to the given database. JDBC url = jdbc:mysql://aa1hw2wctsxa60n.ckmpnrdgeoee.ap-southeast-1.rds.amazonaws.com:3306/jooky, username = jooky. Terminating connection pool. Original Exception: ------ com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: Too many connections at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:921) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:885) at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3421) at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1247) at com.mysql.jdbc.Connection.createNewIO(Connection.java:2775) at com.mysql.jdbc.Connection.<init>(Connection.java:1555) at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285) at java.sql.DriverManager.getConnection(DriverManager.java:571) at java.sql.DriverManager.getConnection(DriverManager.java:215) at com.jolbox.bonecp.BoneCP.obtainRawInternalConnection(BoneCP.java:309) at com.jolbox.bonecp.BoneCP.<init>(BoneCP.java:346) at com.jolbox.bonecp.provider.BoneCPConnectionProvider.createPool(BoneCPConnectionProvider.java:167) at com.jolbox.bonecp.provider.BoneCPConnectionProvider.configure(BoneCPConnectionProvider.java:141) at com.jolbox.bonecp.provider.BoneCPConnectionProvider.configure(BoneCPConnectionProvider.java:271) at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.configureService(StandardServiceRegistryImpl.java:89) at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:206) at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:178) at org.hibernate.engine.jdbc.internal.JdbcServicesImpl.buildJdbcConnectionAccess(JdbcServicesImpl.java:260) at org.hibernate.engine.jdbc.internal.JdbcServicesImpl.configure(JdbcServicesImpl.java:94) at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.configureService(StandardServiceRegistryImpl.java:89) at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:206) at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:178) at org.hibernate.cfg.Configuration.buildTypeRegistrations(Configuration.java:1885) at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1843) at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1928) at jooky.controller.JookyController.getLocations(JookyController.java:72)
This is due to mysql connection limit. To remove such type of error/exception you need increase
max_connectionvariable in mysql.