c3p0 ConnectionTester socket read stuck after SQLException

Tapomay Dey

20 Dec 2012 20 Dec '12

7:26 a.m.

Please find the thread dump below. Running MonetDB Database Server v1.6 (Oct2012-SP1). Compiled from MonetDB-11.13.5 src distribution. C3P0 version: 0.9.1.2. Also tried with 0.9.2. Occurs only after SQLException sometimes: INSERT INTO xyz : table xyz doesn't exist. Otherwise it works great. Also tried setting c3p0's preferredQuery for ConnectionTester to "select 1;". Problem persists. Name: pool-15-thread-2 State: RUNNABLE Total blocked: 0 Total waited: 1 Stack trace: java.net.SocketInputStream.socketRead0(Native Method) java.net.SocketInputStream.read(SocketInputStream.java:150) java.net.SocketInputStream.read(SocketInputStream.java:121) java.io.BufferedInputStream.fill(BufferedInputStream.java:235) java.io.BufferedInputStream.read1(BufferedInputStream.java:275) java.io.BufferedInputStream.read(BufferedInputStream.java:334) - locked java.io.BufferedInputStream@5cec5796 nl.cwi.monetdb.mcl.net.MapiSocket$BlockInputStream._read(MapiSocket.java:846) nl.cwi.monetdb.mcl.net.MapiSocket$BlockInputStream.readBlock(MapiSocket.java:895) nl.cwi.monetdb.mcl.net.MapiSocket$BlockInputStream.read(MapiSocket.java:965) sun.nio.cs.StreamDecoder.readBytes(StreamDecoder.java:283) sun.nio.cs.StreamDecoder.implRead(StreamDecoder.java:325) sun.nio.cs.StreamDecoder.read(StreamDecoder.java:177) - locked java.io.InputStreamReader@206a6883 java.io.InputStreamReader.read(InputStreamReader.java:184) java.io.BufferedReader.fill(BufferedReader.java:154) java.io.BufferedReader.readLine(BufferedReader.java:317) - locked java.io.InputStreamReader@206a6883 java.io.BufferedReader.readLine(BufferedReader.java:382) nl.cwi.monetdb.mcl.io.BufferedMCLReader.readLine(BufferedMCLReader.java:119) nl.cwi.monetdb.mcl.io.BufferedMCLReader.waitForPrompt(BufferedMCLReader.java:196) - locked nl.cwi.monetdb.mcl.io.BufferedMCLReader@29f38115 nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(MonetConnection.java:2296) - locked nl.cwi.monetdb.mcl.net.MapiSocket@319f9f37 nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.processQuery(MonetConnection.java:2274) nl.cwi.monetdb.jdbc.MonetStatement.internalExecute(MonetStatement.java:508) nl.cwi.monetdb.jdbc.MonetStatement.execute(MonetStatement.java:349) nl.cwi.monetdb.jdbc.MonetStatement.executeQuery(MonetStatement.java:525) com.mchange.v2.c3p0.impl.DefaultConnectionTester.activeCheckConnection(DefaultConnectionTester.java:73) com.mchange.v2.c3p0.impl.DefaultConnectionTester.statusOnException(DefaultConnectionTester.java:143) com.mchange.v2.c3p0.AbstractConnectionTester.statusOnException(AbstractConnectionTester.java:82) com.mchange.v2.c3p0.impl.NewPooledConnection.handleThrowable(NewPooledConnection.java:437) - locked com.mchange.v2.c3p0.impl.NewPooledConnection@3340302c com.mchange.v2.c3p0.impl.NewProxyStatement.executeUpdate(NewProxyStatement.java:78) com.sokrati.moneta.mclient.JdbcMonetdbClient.executeUpdate(JdbcMonetdbClient.java:154) com.sokrati.moneta.multiplexer.MonetdbClientCallable.call(MonetdbClientCallable.java:46) com.sokrati.moneta.multiplexer.MonetdbClientCallable.call(MonetdbClientCallable.java:1) java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334) java.util.concurrent.FutureTask.run(FutureTask.java:166) java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110) java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603) java.lang.Thread.run(Thread.java:722) Regards, Tapomay. _______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

Attachments:

attachment.html (text/html — 10.5 KB)

Show replies by date

Tapomay Dey

20 Dec 20 Dec

8:46 a.m.

FYI: Looks like my app is doomed. As a workaround I implemented custom c3p0 ConnectionTester that skips the active connection check when SQLException occurs. When I try to close the connection later ... Name: pool-16-thread-1 State: RUNNABLE Total blocked: 0 Total waited: 18 Stack trace: java.net.SocketInputStream.socketRead0(Native Method) java.net.SocketInputStream.read(SocketInputStream.java:150) java.net.SocketInputStream.read(SocketInputStream.java:121) java.io.BufferedInputStream.fill(BufferedInputStream.java:235) java.io.BufferedInputStream.read1(BufferedInputStream.java:275) java.io.BufferedInputStream.read(BufferedInputStream.java:334) - locked java.io.BufferedInputStream@3fd537d nl.cwi.monetdb.mcl.net.MapiSocket$BlockInputStream._read(MapiSocket.java:846) nl.cwi.monetdb.mcl.net.MapiSocket$BlockInputStream.readBlock(MapiSocket.java:895) nl.cwi.monetdb.mcl.net.MapiSocket$BlockInputStream.read(MapiSocket.java:965) sun.nio.cs.StreamDecoder.readBytes(StreamDecoder.java:283) sun.nio.cs.StreamDecoder.implRead(StreamDecoder.java:325) sun.nio.cs.StreamDecoder.read(StreamDecoder.java:177) - locked java.io.InputStreamReader@6c743596 java.io.InputStreamReader.read(InputStreamReader.java:184) java.io.BufferedReader.fill(BufferedReader.java:154) java.io.BufferedReader.readLine(BufferedReader.java:317) - locked java.io.InputStreamReader@6c743596 java.io.BufferedReader.readLine(BufferedReader.java:382) nl.cwi.monetdb.mcl.io.BufferedMCLReader.readLine(BufferedMCLReader.java:119) nl.cwi.monetdb.mcl.io.BufferedMCLReader.waitForPrompt(BufferedMCLReader.java:196) - locked nl.cwi.monetdb.mcl.io.BufferedMCLReader@416a427a nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(MonetConnection.java:2296) - locked nl.cwi.monetdb.mcl.net.MapiSocket@27327923 nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.processQuery(MonetConnection.java:2274) nl.cwi.monetdb.jdbc.MonetConnection.rollback(MonetConnection.java:1019) com.mchange.v2.c3p0.impl.C3P0ImplUtils.resetTxnState(C3P0ImplUtils.java:275) com.mchange.v2.c3p0.impl.NewPooledConnection.reset(NewPooledConnection.java:388) com.mchange.v2.c3p0.impl.NewPooledConnection.markClosedProxyConnection(NewPooledConnection.java:362) - locked com.mchange.v2.c3p0.impl.NewPooledConnection@18d223e6 com.mchange.v2.c3p0.impl.NewProxyConnection.close(NewProxyConnection.java:1220) - locked com.mchange.v2.c3p0.impl.NewProxyConnection@62aefcef com.sokrati.moneta.commands.EndTransactionCommand.run(EndTransactionCommand.java:101) com.sokrati.commandManager.CommandExecutor.execute(CommandExecutor.java:49) com.sokrati.moneta.channels.ChannelBulkQueryExecutor.execute(ChannelBulkQueryExecutor.java:91) com.sokrati.moneta.channels.ChannelProcessorCallable.call(ChannelProcessorCallable.java:91) com.sokrati.moneta.channels.ChannelProcessorCallable.call(ChannelProcessorCallable.java:1) java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334) java.util.concurrent.FutureTask.run(FutureTask.java:166) java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110) java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603) java.lang.Thread.run(Thread.java:722) ________________________________ From: Tapomay Dey To: Communication channel for Monet DB users Sent: Thursday, December 20, 2012 12:56 PM Subject: c3p0 ConnectionTester socket read stuck after SQLException Please find the thread dump below. Running MonetDB Database Server v1.6 (Oct2012-SP1). Compiled from MonetDB-11.13.5 src distribution. C3P0 version: 0.9.1.2. Also tried with 0.9.2. Occurs only after SQLException sometimes: INSERT INTO xyz : table xyz doesn't exist. Otherwise it works great. Also tried setting c3p0's preferredQuery for ConnectionTester to "select 1;". Problem persists. Name: pool-15-thread-2 State: RUNNABLE Total blocked: 0 Total waited: 1 Stack trace: java.net.SocketInputStream.socketRead0(Native Method) java.net.SocketInputStream.read(SocketInputStream.java:150) java.net.SocketInputStream.read(SocketInputStream.java:121) java.io.BufferedInputStream.fill(BufferedInputStream.java:235) java.io.BufferedInputStream.read1(BufferedInputStream.java:275) java.io.BufferedInputStream.read(BufferedInputStream.java:334) - locked java.io.BufferedInputStream@5cec5796 nl.cwi.monetdb.mcl.net.MapiSocket$BlockInputStream._read(MapiSocket.java:846) nl.cwi.monetdb.mcl.net.MapiSocket$BlockInputStream.readBlock(MapiSocket.java:895) nl.cwi.monetdb.mcl.net.MapiSocket$BlockInputStream.read(MapiSocket.java:965) sun.nio.cs.StreamDecoder.readBytes(StreamDecoder.java:283) sun.nio.cs.StreamDecoder.implRead(StreamDecoder.java:325) sun.nio.cs.StreamDecoder.read(StreamDecoder.java:177) - locked java.io.InputStreamReader@206a6883 java.io.InputStreamReader.read(InputStreamReader.java:184) java.io.BufferedReader.fill(BufferedReader.java:154) java.io.BufferedReader.readLine(BufferedReader.java:317) - locked java.io.InputStreamReader@206a6883 java.io.BufferedReader.readLine(BufferedReader.java:382) nl.cwi.monetdb.mcl.io.BufferedMCLReader.readLine(BufferedMCLReader.java:119) nl.cwi.monetdb.mcl.io.BufferedMCLReader.waitForPrompt(BufferedMCLReader.java:196) - locked nl.cwi.monetdb.mcl.io.BufferedMCLReader@29f38115 nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(MonetConnection.java:2296) - locked nl.cwi.monetdb.mcl.net.MapiSocket@319f9f37 nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.processQuery(MonetConnection.java:2274) nl.cwi.monetdb.jdbc.MonetStatement.internalExecute(MonetStatement.java:508) nl.cwi.monetdb.jdbc.MonetStatement.execute(MonetStatement.java:349) nl.cwi.monetdb.jdbc.MonetStatement.executeQuery(MonetStatement.java:525) com.mchange.v2.c3p0.impl.DefaultConnectionTester.activeCheckConnection(DefaultConnectionTester.java:73) com.mchange.v2.c3p0.impl.DefaultConnectionTester.statusOnException(DefaultConnectionTester.java:143) com.mchange.v2.c3p0.AbstractConnectionTester.statusOnException(AbstractConnectionTester.java:82) com.mchange.v2.c3p0.impl.NewPooledConnection.handleThrowable(NewPooledConnection.java:437) - locked com.mchange.v2.c3p0.impl.NewPooledConnection@3340302c com.mchange.v2.c3p0.impl.NewProxyStatement.executeUpdate(NewProxyStatement.java:78) com.sokrati.moneta.mclient.JdbcMonetdbClient.executeUpdate(JdbcMonetdbClient.java:154) com.sokrati.moneta.multiplexer.MonetdbClientCallable.call(MonetdbClientCallable.java:46) com.sokrati.moneta.multiplexer.MonetdbClientCallable.call(MonetdbClientCallable.java:1) java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334) java.util.concurrent.FutureTask.run(FutureTask.java:166) java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110) java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603) java.lang.Thread.run(Thread.java:722) Regards, Tapomay. _______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list _______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

Hannes Mühleisen

8:47 a.m.

Hello Tapomay, I will have a look later today, could you please also provide the source code that you have used to provoke the issue? Thanks, Hannes On 20/12/12 08:26, Tapomay Dey wrote:

...

Please find the thread dump below. Running MonetDB Database Server v1.6 (Oct2012-SP1). Compiled from MonetDB-11.13.5 src distribution. C3P0 version: 0.9.1.2. Also tried with 0.9.2. Occurs only after SQLException sometimes: INSERT INTO xyz : table xyz doesn't exist. Otherwise it works great. Also tried setting c3p0's preferredQuery for ConnectionTester to "select 1;". Problem persists.

Name: pool-15-thread-2 State: RUNNABLE Total blocked: 0 Total waited: 1

Stack trace: java.net.SocketInputStream.socketRead0(Native Method) java.net.SocketInputStream.read(SocketInputStream.java:150) java.net.SocketInputStream.read(SocketInputStream.java:121) java.io.BufferedInputStream.fill(BufferedInputStream.java:235) java.io.BufferedInputStream.read1(BufferedInputStream.java:275) java.io.BufferedInputStream.read(BufferedInputStream.java:334) - locked java.io.BufferedInputStream@5cec5796 nl.cwi.monetdb.mcl.net.MapiSocket$BlockInputStream._read(MapiSocket.java:846) nl.cwi.monetdb.mcl.net.MapiSocket$BlockInputStream.readBlock(MapiSocket.java:895) nl.cwi.monetdb.mcl.net.MapiSocket$BlockInputStream.read(MapiSocket.java:965) sun.nio.cs.StreamDecoder.readBytes(StreamDecoder.java:283) sun.nio.cs.StreamDecoder.implRead(StreamDecoder.java:325) sun.nio.cs.StreamDecoder.read(StreamDecoder.java:177) - locked java.io.InputStreamReader@206a6883 java.io.InputStreamReader.read(InputStreamReader.java:184) java.io.BufferedReader.fill(BufferedReader.java:154) java.io.BufferedReader.readLine(BufferedReader.java:317) - locked java.io.InputStreamReader@206a6883 java.io.BufferedReader.readLine(BufferedReader.java:382) nl.cwi.monetdb.mcl.io.BufferedMCLReader.readLine(BufferedMCLReader.java:119) nl.cwi.monetdb.mcl.io.BufferedMCLReader.waitForPrompt(BufferedMCLReader.java:196) - locked nl.cwi.monetdb.mcl.io.BufferedMCLReader@29f38115 nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(MonetConnection.java:2296) - locked nl.cwi.monetdb.mcl.net.MapiSocket@319f9f37 nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.processQuery(MonetConnection.java:2274) nl.cwi.monetdb.jdbc.MonetStatement.internalExecute(MonetStatement.java:508) nl.cwi.monetdb.jdbc.MonetStatement.execute(MonetStatement.java:349) nl.cwi.monetdb.jdbc.MonetStatement.executeQuery(MonetStatement.java:525) com.mchange.v2.c3p0.impl.DefaultConnectionTester.activeCheckConnection(DefaultConnectionTester.java:73) com.mchange.v2.c3p0.impl.DefaultConnectionTester.statusOnException(DefaultConnectionTester.java:143) com.mchange.v2.c3p0.AbstractConnectionTester.statusOnException(AbstractConnectionTester.java:82) com.mchange.v2.c3p0.impl.NewPooledConnection.handleThrowable(NewPooledConnection.java:437) - locked com.mchange.v2.c3p0.impl.NewPooledConnection@3340302c com.mchange.v2.c3p0.impl.NewProxyStatement.executeUpdate(NewProxyStatement.java:78) com.sokrati.moneta.mclient.JdbcMonetdbClient.executeUpdate(JdbcMonetdbClient.java:154) com.sokrati.moneta.multiplexer.MonetdbClientCallable.call(MonetdbClientCallable.java:46) com.sokrati.moneta.multiplexer.MonetdbClientCallable.call(MonetdbClientCallable.java:1) java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334) java.util.concurrent.FutureTask.run(FutureTask.java:166) java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110) java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603) java.lang.Thread.run(Thread.java:722)

Regards, Tapomay.

_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

Tapomay Dey

2:41 p.m.

As the source code has many unrelated dependencies I tried to extract the relevant code. Please find attached an eclipse project for the same. However I haven't been able to reproduce the issue in this test project. It should give you a fair idea of the sequence of calls made. Plz note that a single insert query used in the live project is much much larger (10K approx.) than the one in the test. I reconfirmed that the issue is still there in the larger project. PS: I had sent this code long back and it got moderated just now(due to some 40KB limit). Thus I have removed the dependencies monetdb-jdbc-2.6.jar and c3p0-0.9.1.2.jar from lib. Plz add them to lib folder and everything should work(after u set the correct DB names in the code).(Anyways this code doesn't reproduce the issue I am facing so I guess just a manual overview should suffice) Regards, Tapomay. ________________________________ From: Hannes Mühleisen To: Communication channel for MonetDB users Sent: Thursday, December 20, 2012 2:17 PM Subject: Re: c3p0 ConnectionTester socket read stuck after SQLException Hello Tapomay, I will have a look later today, could you please also provide the source code that you have used to provoke the issue? Thanks, Hannes On 20/12/12 08:26, Tapomay Dey wrote:

...

Please find the thread dump below. Running MonetDB Database Server v1.6 (Oct2012-SP1). Compiled from MonetDB-11.13.5 src distribution. C3P0 version: 0.9.1.2. Also tried with 0.9.2. Occurs only after SQLException sometimes: INSERT INTO xyz : table xyz doesn't exist. Otherwise it works great. Also tried setting c3p0's preferredQuery for ConnectionTester to "select 1;". Problem persists.

Name: pool-15-thread-2 State: RUNNABLE Total blocked: 0 Total waited: 1

Stack trace: java.net.SocketInputStream.socketRead0(Native Method) java.net.SocketInputStream.read(SocketInputStream.java:150) java.net.SocketInputStream.read(SocketInputStream.java:121) java.io.BufferedInputStream.fill(BufferedInputStream.java:235) java.io.BufferedInputStream.read1(BufferedInputStream.java:275) java.io.BufferedInputStream.read(BufferedInputStream.java:334) - locked java.io.BufferedInputStream@5cec5796 nl.cwi.monetdb.mcl.net.MapiSocket$BlockInputStream._read(MapiSocket.java:846) nl.cwi.monetdb.mcl.net.MapiSocket$BlockInputStream.readBlock(MapiSocket.java:895) nl.cwi.monetdb.mcl.net.MapiSocket$BlockInputStream.read(MapiSocket.java:965) sun.nio.cs.StreamDecoder.readBytes(StreamDecoder.java:283) sun.nio.cs.StreamDecoder.implRead(StreamDecoder.java:325) sun.nio.cs.StreamDecoder.read(StreamDecoder.java:177) - locked java.io.InputStreamReader@206a6883 java.io.InputStreamReader.read(InputStreamReader.java:184) java.io.BufferedReader.fill(BufferedReader.java:154) java.io.BufferedReader.readLine(BufferedReader.java:317) - locked java.io.InputStreamReader@206a6883 java.io.BufferedReader.readLine(BufferedReader.java:382) nl.cwi.monetdb.mcl.io.BufferedMCLReader.readLine(BufferedMCLReader.java:119) nl.cwi.monetdb.mcl.io.BufferedMCLReader.waitForPrompt(BufferedMCLReader.java:196) - locked nl.cwi.monetdb.mcl.io.BufferedMCLReader@29f38115 nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(MonetConnection.java:2296) - locked nl.cwi.monetdb.mcl.net.MapiSocket@319f9f37 nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.processQuery(MonetConnection.java:2274) nl.cwi.monetdb.jdbc.MonetStatement.internalExecute(MonetStatement.java:508) nl.cwi.monetdb.jdbc.MonetStatement.execute(MonetStatement.java:349) nl.cwi.monetdb.jdbc.MonetStatement.executeQuery(MonetStatement.java:525) com.mchange.v2.c3p0.impl.DefaultConnectionTester.activeCheckConnection(DefaultConnectionTester.java:73) com.mchange.v2.c3p0.impl.DefaultConnectionTester.statusOnException(DefaultConnectionTester.java:143) com.mchange.v2.c3p0.AbstractConnectionTester.statusOnException(AbstractConnectionTester.java:82) com.mchange.v2.c3p0.impl.NewPooledConnection.handleThrowable(NewPooledConnection.java:437) - locked com.mchange.v2.c3p0.impl.NewPooledConnection@3340302c com.mchange.v2.c3p0.impl.NewProxyStatement.executeUpdate(NewProxyStatement.java:78) com.sokrati.moneta.mclient.JdbcMonetdbClient.executeUpdate(JdbcMonetdbClient.java:154) com.sokrati.moneta.multiplexer.MonetdbClientCallable.call(MonetdbClientCallable.java:46) com.sokrati.moneta.multiplexer.MonetdbClientCallable.call(MonetdbClientCallable.java:1) java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334) java.util.concurrent.FutureTask.run(FutureTask.java:166) java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110) java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603) java.lang.Thread.run(Thread.java:722)

Regards, Tapomay.

_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

Tapomay Dey

24 Dec 24 Dec

6:20 a.m.

Verified that this issue persists even on the stable release installed from http://dev.monetdb.org/downloads/Fedora/ Occurs every-time we hit com.mchange.v2.c3p0.impl.NewPooledConnection.handleThrowable(NewPooledConnection.java:437) Regards, Tapomay. ________________________________ From: Tapomay Dey To: Communication channel for MonetDB users Sent: Thursday, December 20, 2012 8:11 PM Subject: Re: c3p0 ConnectionTester socket read stuck after SQLException As the source code has many unrelated dependencies I tried to extract the relevant code. Please find attached an eclipse project for the same. However I haven't been able to reproduce the issue in this test project. It should give you a fair idea of the sequence of calls made. Plz note that a single insert query used in the live project is much much larger (10K approx.) than the one in the test. I reconfirmed that the issue is still there in the larger project. PS: I had sent this code long back and it got moderated just now(due to some 40KB limit). Thus I have removed the dependencies monetdb-jdbc-2.6.jar and c3p0-0.9.1.2.jar from lib. Plz add them to lib folder and everything should work(after u set the correct DB names in the code).(Anyways this code doesn't reproduce the issue I am facing so I guess just a manual overview should suffice) Regards, Tapomay. ________________________________ From: Hannes Mühleisen To: Communication channel for MonetDB users Sent: Thursday, December 20, 2012 2:17 PM Subject: Re: c3p0 ConnectionTester socket read stuck after SQLException Hello Tapomay, I will have a look later today, could you please also provide the source code that you have used to provoke the issue? Thanks, Hannes On 20/12/12 08:26, Tapomay Dey wrote:

...

Please find the thread dump below. Running MonetDB Database Server v1.6 (Oct2012-SP1). Compiled from MonetDB-11.13.5 src distribution. C3P0 version: 0.9.1.2. Also tried with 0.9.2. Occurs only after SQLException sometimes: INSERT INTO xyz : table xyz doesn't exist. Otherwise it works great. Also tried setting c3p0's preferredQuery for ConnectionTester to "select 1;". Problem persists.

Name: pool-15-thread-2 State: RUNNABLE Total blocked: 0 Total waited: 1

Stack trace: java.net.SocketInputStream.socketRead0(Native Method) java.net.SocketInputStream.read(SocketInputStream.java:150)

java.net.SocketInputStream.read(SocketInputStream.java:121)

...

java.io.BufferedInputStream.fill(BufferedInputStream.java:235) java.io.BufferedInputStream.read1(BufferedInputStream.java:275) java.io.BufferedInputStream.read(BufferedInputStream.java:334) - locked java.io.BufferedInputStream@5cec5796 nl.cwi.monetdb.mcl.net.MapiSocket$BlockInputStream._read(MapiSocket.java:846) nl.cwi.monetdb.mcl.net.MapiSocket$BlockInputStream.readBlock(MapiSocket.java:895) nl.cwi.monetdb.mcl.net.MapiSocket$BlockInputStream.read(MapiSocket.java:965) sun.nio.cs.StreamDecoder.readBytes(StreamDecoder.java:283) sun.nio.cs.StreamDecoder.implRead(StreamDecoder.java:325) sun.nio.cs.StreamDecoder.read(StreamDecoder.java:177) - locked java.io.InputStreamReader@206a6883 java.io.InputStreamReader.read(InputStreamReader.java:184)

java.io.BufferedReader.fill(BufferedReader.java:154)

...

java.io.BufferedReader.readLine(BufferedReader.java:317) - locked java.io.InputStreamReader@206a6883 java.io.BufferedReader.readLine(BufferedReader.java:382) nl.cwi.monetdb.mcl.io.BufferedMCLReader.readLine(BufferedMCLReader.java:119) nl.cwi.monetdb.mcl.io.BufferedMCLReader.waitForPrompt(BufferedMCLReader.java:196) - locked nl.cwi.monetdb.mcl.io.BufferedMCLReader@29f38115 nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(MonetConnection.java:2296) - locked nl.cwi.monetdb.mcl.net.MapiSocket@319f9f37 nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.processQuery(MonetConnection.java:2274) nl.cwi.monetdb.jdbc.MonetStatement.internalExecute(MonetStatement.java:508) nl.cwi.monetdb.jdbc.MonetStatement.execute(MonetStatement.java:349)

nl.cwi.monetdb.jdbc.MonetStatement.executeQuery(MonetStatement.java:525)

...

com.mchange.v2.c3p0.impl.DefaultConnectionTester.activeCheckConnection(DefaultConnectionTester.java:73) com.mchange.v2.c3p0.impl.DefaultConnectionTester.statusOnException(DefaultConnectionTester.java:143) com.mchange.v2.c3p0.AbstractConnectionTester.statusOnException(AbstractConnectionTester.java:82) com.mchange.v2.c3p0.impl.NewPooledConnection.handleThrowable(NewPooledConnection.java:437) - locked com.mchange.v2.c3p0.impl.NewPooledConnection@3340302c com.mchange.v2.c3p0.impl.NewProxyStatement.executeUpdate(NewProxyStatement.java:78) com.sokrati.moneta.mclient.JdbcMonetdbClient.executeUpdate(JdbcMonetdbClient.java:154) com.sokrati.moneta.multiplexer.MonetdbClientCallable.call(MonetdbClientCallable.java:46)

com.sokrati.moneta.multiplexer.MonetdbClientCallable.call(MonetdbClientCallable.java:1)

...

java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334) java.util.concurrent.FutureTask.run(FutureTask.java:166) java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110) java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603) java.lang.Thread.run(Thread.java:722)

Regards, Tapomay.

_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list _______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list _______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

Hannes Mühleisen

3 Jan 3 Jan

1:29 p.m.

Hello Tapomay (and list), I have a theory to your issue: MonetDB (and the MonetDB JDBC driver for that matter) requires you to rollback a failed transaction before you can work with that connection again. This is consistent with the JDBC documentation [1]. The reason why you get the error in the connection check query is that C3P0 transparently executes this query in the background, which results in the error "!25005!current transaction is aborted (please ROLLBACK)". In your sample code, I could find a rollback() method but no evidence it is actually used, so please check this again in you application. That all being said, looking at the stack trace you have provided (process hanging on socket IO), tells me that there is a possibility for a protocol misunderstanding between MonetDB and JDBC. However, without further information from you (and preferably a failing-yet-semantically-correct test case) it is very difficult to investigate this further. Best, Hannes [1] http://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html#call_ro... _______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

Hannes Mühleisen

10:49 a.m.

Hello Tapomay, after looking at your code, I was wondering why you chose the double management of connections in your Main/JdbcMonetdbTestClient classes. C3P0 provides a way to define "connection customizers" (com.mchange.v2.c3p0.ComboPooledDataSource.setConnectionCustomizerClassName(String)), which you can use to disable autocommit on new connections. This should simplify your example considerably. Nevertheless, I will try to provoke the exceptions you have mentioned and see how the JDBC driver handles them together with C3P0. Best, Hannes On 20/12/12 15:41, Tapomay Dey wrote:

...

As the source code has many unrelated dependencies I tried to extract the relevant code. Please find attached an eclipse project for the same. However I haven't been able to reproduce the issue in this test project. It should give you a fair idea of the sequence of calls made. Plz note that a single insert query used in the live project is much much larger (10K approx.) than the one in the test. I reconfirmed that the issue is still there in the larger project.

PS: I had sent this code long back and it got moderated just now(due to some 40KB limit). Thus I have removed the dependencies monetdb-jdbc-2.6.jar and c3p0-0.9.1.2.jar from lib. Plz add them to lib folder and everything should work(after u set the correct DB names in the code).(Anyways this code doesn't reproduce the issue I am facing so I guess just a manual overview should suffice)

Regards, Tapomay.

------------------------------------------------------------------------ *From:* Hannes Mühleisen *To:* Communication channel for MonetDB users *Sent:* Thursday, December 20, 2012 2:17 PM *Subject:* Re: c3p0 ConnectionTester socket read stuck after SQLException

Hello Tapomay,

I will have a look later today, could you please also provide the source code that you have used to provoke the issue?

Thanks,

Hannes

On 20/12/12 08:26, Tapomay Dey wrote:

...
Please find the thread dump below. Running MonetDB Database Server v1.6 (Oct2012-SP1). Compiled from MonetDB-11.13.5 src distribution. C3P0 version: 0.9.1.2. Also tried with 0.9.2. Occurs only after SQLException sometimes: INSERT INTO xyz : table xyz doesn't exist. Otherwise it works great. Also tried setting c3p0's preferredQuery for ConnectionTester to "select 1;". Problem persists.

Name: pool-15-thread-2 State: RUNNABLE Total blocked: 0 Total waited: 1

Stack trace: java.net.SocketInputStream.socketRead0(Native Method) java.net.SocketInputStream.read(SocketInputStream.java:150) java.net.SocketInputStream.read(SocketInputStream.java:121) java.io.BufferedInputStream.fill(BufferedInputStream.java:235) java.io.BufferedInputStream.read1(BufferedInputStream.java:275) java.io.BufferedInputStream.read(BufferedInputStream.java:334) - locked java.io.BufferedInputStream@5cec5796

nl.cwi.monetdb.mcl.net.MapiSocket$BlockInputStream._read(MapiSocket.java:846)

...
nl.cwi.monetdb.mcl.net.MapiSocket$BlockInputStream.readBlock(MapiSocket.java:895)

...
nl.cwi.monetdb.mcl.net.MapiSocket$BlockInputStream.read(MapiSocket.java:965)

...
sun.nio.cs.StreamDecoder.readBytes(StreamDecoder.java:283) sun.nio.cs.StreamDecoder.implRead(StreamDecoder.java:325) sun.nio.cs.StreamDecoder.read(StreamDecoder.java:177) - locked java.io.InputStreamReader@206a6883 java.io.InputStreamReader.read(InputStreamReader.java:184) java.io.BufferedReader.fill(BufferedReader.java:154) java.io.BufferedReader.readLine(BufferedReader.java:317) - locked java.io.InputStreamReader@206a6883 java.io.BufferedReader.readLine(BufferedReader.java:382)

nl.cwi.monetdb.mcl.io.BufferedMCLReader.readLine(BufferedMCLReader.java:119)

...
nl.cwi.monetdb.mcl.io.BufferedMCLReader.waitForPrompt(BufferedMCLReader.java:196)

...
- locked nl.cwi.monetdb.mcl.io.BufferedMCLReader@29f38115

nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(MonetConnection.java:2296)

...
- locked nl.cwi.monetdb.mcl.net.MapiSocket@319f9f37

nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.processQuery(MonetConnection.java:2274)

...
nl.cwi.monetdb.jdbc.MonetStatement.internalExecute(MonetStatement.java:508)

...
nl.cwi.monetdb.jdbc.MonetStatement.execute(MonetStatement.java:349) nl.cwi.monetdb.jdbc.MonetStatement.executeQuery(MonetStatement.java:525)

com.mchange.v2.c3p0.impl.DefaultConnectionTester.activeCheckConnection(DefaultConnectionTester.java:73)

...
com.mchange.v2.c3p0.impl.DefaultConnectionTester.statusOnException(DefaultConnectionTester.java:143)

...
com.mchange.v2.c3p0.AbstractConnectionTester.statusOnException(AbstractConnectionTester.java:82)

...
com.mchange.v2.c3p0.impl.NewPooledConnection.handleThrowable(NewPooledConnection.java:437)

...
- locked com.mchange.v2.c3p0.impl.NewPooledConnection@3340302c

com.mchange.v2.c3p0.impl.NewProxyStatement.executeUpdate(NewProxyStatement.java:78)

...
com.sokrati.moneta.mclient.JdbcMonetdbClient.executeUpdate(JdbcMonetdbClient.java:154)

...
com.sokrati.moneta.multiplexer.MonetdbClientCallable.call(MonetdbClientCallable.java:46)

...
com.sokrati.moneta.multiplexer.MonetdbClientCallable.call(MonetdbClientCallable.java:1)

...
java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334) java.util.concurrent.FutureTask.run(FutureTask.java:166)

java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)

...
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)

...
java.lang.Thread.run(Thread.java:722)

Regards, Tapomay.

_______________________________________________ users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

4376

Age (days ago)

4390

Last active (days ago)

List overview

Download

6 comments

2 participants

participants (2)

Hannes Mühleisen
Tapomay Dey