MySQL Bugs: #40630: Getting Out Of memory error while performing the con.setAutoCommit(false)

Bug #40630	Getting Out Of memory error while performing the con.setAutoCommit(false)
Submitted:	11 Nov 2008 7:48	Modified:	12 Dec 2008 10:36
Reporter:	ravi paramasivam	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.11-standard	OS:	Solaris (sun4u sparc SUNW,Netra-440)
Assigned to:		CPU Architecture:	Any
Tags:	Getting Out Of memory error while performing the con.setAutoCommit(false)

Description:
Our application is running on JES2004q2 Application Server(7) with Mysql 4.1.11.On a heavy load when we perform the below operation then we are getting out of memory error.
Code Snippet:-
************
Connection con = null;
PreparedStatement pstmt = null;
try {
   con = DBUtil.getConnection("writeToTimer");
   .....

DBUtil.getConnection()
********************
try {
				con = cConnectionPool.getConnection();  				        con.setAutoCommit(false);//problem is occured here
				return con;
.......

Connection Pool initialization:-
******************************
Context env = null;
		
		if (cConnectionPool == null) {
			
			try {
				
				env = (Context) new InitialContext(); //get context
				
				cConnectionPool = (DataSource) env.lookup(ConfigData.POOL_NAME);
.....

StackTrace:-
***********
com.sun.enterprise.resource.PoolingException: Communication link failure: java.net.SocketException, underlying cause: Connection reset

** BEGIN NESTED EXCEPTION ** 

java.net.SocketException
MESSAGE: Connection reset

STACKTRACE:

java.net.SocketException: Connection reset
	at java.net.SocketInputStream.read(SocketInputStream.java:168)
	at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1391)
	at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:1538)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1929)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1906)
	at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:2520)
	at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:817)
	at com.mysql.jdbc.Connection.createNewIO(Connection.java:1786)
	at com.mysql.jdbc.Connection.<init>(Connection.java:450)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:411)
	at com.mysql.jdbc.jdbc2.optional.MysqlDataSource.getConnection(MysqlDataSource.java:398)
	at com.mysql.jdbc.jdbc2.optional.MysqlDataSource.getConnection(MysqlDataSource.java:131)
	at com.sun.enterprise.resource.JdbcUrlAllocator.createResource(JdbcUrlAllocator.java:88)
	at com.sun.enterprise.resource.IASNonSharedResourcePool.internalGetResource(IASNonSharedResourcePool.java:745)
	at com.sun.enterprise.resource.IASNonSharedResourcePool.getResource(IASNonSharedResourcePool.java:520)
	at com.sun.enterprise.resource.PoolManagerImpl.getResourceFromPool(PoolManagerImpl.java:271)
	at com.sun.enterprise.resource.JdbcXAConnection$JdbcConnection.getCurrentConnection(JdbcXAConnection.java:642)
	at com.sun.enterprise.resource.JdbcXAConnection$JdbcConnection.setAutoCommit(JdbcXAConnection.java:402)
	at com.mot.gama.siepoc.utilities.dbutils.DBUtil.getConnection(DBUtil.java:144)
	at com.mot.gama.siepoc.utilities.CdrUtils.writeToTimer(CdrUtils.java:120)
	at com.mot.gama.siepoc.common.mdrgenerator.MDRGenerator.generateTimerCDR(MDRGenerator.java:143)
	at com.mot.gama.siepoc.common.timers.ChargingSessionTimerTask.run(ChargingSessionTimerTask.java:84)
	at java.util.TimerThread.mainLoop(Timer.java:432)
	at java.util.TimerThread.run(Timer.java:382)

** END NESTED EXCEPTION **

	at com.sun.enterprise.resource.JdbcUrlAllocator.createResource(JdbcUrlAllocator.java:102)
	at com.sun.enterprise.resource.IASNonSharedResourcePool.internalGetResource(IASNonSharedResourcePool.java:745)
	at com.sun.enterprise.resource.IASNonSharedResourcePool.getResource(IASNonSharedResourcePool.java:520)
	at com.sun.enterprise.resource.PoolManagerImpl.getResourceFromPool(PoolManagerImpl.java:271)
	at com.sun.enterprise.resource.JdbcXAConnection$JdbcConnection.getCurrentConnection(JdbcXAConnection.java:642)
	at com.sun.enterprise.resource.JdbcXAConnection$JdbcConnection.setAutoCommit(JdbcXAConnection.java:402)
	at com.mot.gama.siepoc.utilities.dbutils.DBUtil.getConnection(DBUtil.java:144)
	at com.mot.gama.siepoc.utilities.CdrUtils.writeToTimer(CdrUtils.java:120)
	at com.mot.gama.siepoc.common.mdrgenerator.MDRGenerator.generateTimerCDR(MDRGenerator.java:143)
	at com.mot.gama.siepoc.common.timers.ChargingSessionTimerTask.run(ChargingSessionTimerTask.java:84)
	at java.util.TimerThread.mainLoop(Timer.java:432)
	at java.util.TimerThread.run(Timer.java:382)
java.sql.SQLException: Communication link failure: java.net.SocketException, underlying cause: Connection reset

** BEGIN NESTED EXCEPTION ** 

java.net.SocketException
MESSAGE: Connection reset

STACKTRACE:

java.
[23/Oct/2008:22:54:48] SEVERE (25797): net.SocketException: Connection reset
	at java.net.SocketInputStream.read(SocketInputStream.java:168)
	at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1391)
	at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:1538)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1929)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1906)
	at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:2520)
	at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:817)
	at com.mysql.jdbc.Connection.createNewIO(Connection.java:1786)
	at com.mysql.jdbc.Connection.<init>(Connection.java:450)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:411)
	at com.mysql.jdbc.jdbc2.optional.MysqlDataSource.getConnection(MysqlDataSource.java:398)
	at com.mysql.jdbc.jdbc2.optional.MysqlDataSource.getConnection(MysqlDataSource.java:131)
	at com.sun.enterprise.resource.JdbcUrlAllocator.createResource(JdbcUrlAllocator.java:88)
	at com.sun.enterprise.resource.IASNonSharedResourcePool.internalGetResource(IASNonSharedResourcePool.java:745)
	at com.sun.enterprise.resource.IASNonSharedResourcePool.getResource(IASNonSharedResourcePool.java:520)
	at com.sun.enterprise.resource.PoolManagerImpl.getResourceFromPool(PoolManagerImpl.java:271)
	at com.sun.enterprise.resource.JdbcXAConnection$JdbcConnection.getCurrentConnection(JdbcXAConnection.java:642)
	at com.sun.enterprise.resource.JdbcXAConnection$JdbcConnection.setAutoCommit(JdbcXAConnection.java:402)
	at com.mot.gama.siepoc.utilities.dbutils.DBUtil.getConnection(DBUtil.java:144)
	at com.mot.gama.siepoc.utilities.CdrUtils.writeToTimer(CdrUtils.java:120)
	at com.mot.gama.siepoc.common.mdrgenerator.MDRGenerator.generateTimerCDR(MDRGenerator.java:143)
	at com.mot.gama.siepoc.common.timers.ChargingSessionTimerTask.run(ChargingSessionTimerTask.java:84)
	at java.util.TimerThread.mainLoop(Timer.java:432)
	at java.util.TimerThread.run(Timer.java:382)

** END NESTED EXCEPTION **

	at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:1713)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1929)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1906)
	at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:2520)
	at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:817)
	at com.mysql.jdbc.Connection.createNewIO(Connection.java:1786)
	at com.mysql.jdbc.Connection.<init>(Connection.java:450)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:411)
	at com.mysql.jdbc.jdbc2.optional.MysqlDataSource.getConnection(MysqlDataSource.java:398)
	at com.mysql.jdbc.jdbc2.optional.MysqlDataSource.getConnection(MysqlDataSource.java:131)
	at com.sun.enterprise.resource.JdbcUrlAllocator.createResource(JdbcUrlAllocator.java:88)
	at com.sun.enterprise.resource.IASNonSharedResourcePool.internalGetResource(IASNonSharedResourcePool.java:745)
	at com.sun.enterprise.resource.IASNonSharedResourcePool.getResource(IASNonSharedResourcePool.java:520)
	at com.sun.enterprise.resource.PoolManagerImpl.getResourceFromPool(PoolManagerImpl.java:271)
	at com.sun.enterprise.resource.JdbcXAConnection$JdbcConnection.getCurrentConnection(JdbcXAConnection.java:642)
	at com.sun.enterprise.resource.JdbcXAConnection$JdbcConnection.setAutoCommit(JdbcXAConnection.java:402)
	at com.mot.gama.siepoc.utilities.dbutils.DBUtil.getConnection(DBUtil.java:144)

How to repeat:
On a heavy load this problem occured.

Hi Ravi and thanks for your report even though I do not understand it at all.
I see only communication exceptions probably due to bad network or server crash... Either can not be proved with out general query and error logs...

What happens, I *think*, is that:
o you start transaction -> connection breaks -> you are using separate pool manager that does not notify c/J that connection's bad -> c/J reuses connection and restarts transaction -> BANG

If server has truly gone away, exception will always be a subclass of communicationsException, but more importantly the exception *will* be a SQLException, with a SQLState that starts with "08" so please check this for me.

Second assumption about pools. If you're using a connection pool that does not use the ConnectionPoolDataSource API to create connections that have lifecycle callbacks, then it is the pool's responsibility to cleanup "logical" pooled connections when they're returned to the pool. Otherwise there is no way for a JDBC driver to tell that a connection is being pooled "above" it, and thus no event/method call where one can hook in to determine that you've been "logically" closed and returned to the pool. Some of pools automatically call rollback() on connections that are returned to the pool but some don't. You can see whether this is happening or not by looking at MySQL's general query log on the mysql server side of things, or by adding "profileSQL=true" to your JDBC URL configuration properties.

Third assumption would be that socket itself caused exception. Exception could be thrown by *unclosed* socket connection. Maybe you normally close DB connection/socket connection when no exception occurs. However, some connections are not closed (you should close the DB connection/socket connection in catch block). JVM specification state that GC does *not* release resources assigned to socket connection. After much database operations these unclosed socket connections exhaust JVM resources related to socket operation leading to SocketInputStream hanging your program. For this, you can use Socket.setSoTimeout(nn) on every connection and set bigger tcp receiver buffer like "tcpRcvBuf=nn" where both "nn" values can be as high or low as you need to make your socket connection stable.

1)whether any performance hit will occur for doing the con.setAutoCommit(false)
Please test and inform us of result.

Also, 4.1.11 is quite old, is there option for you to upgrade?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".