Wow. Figured it out. This goes back to my "Impossibly long insert
time for one object" e-mail conversation from last week.
I actually found a bug.
org.apache.ojb.broker.platforms.PlatformMsSQLServerImpl
This method to be exact:
getLastInsertIdentityQuery(java.lang.String tableName)
It returns a string that says:
"SELECT @@IDENTITY FROM " + tableName;
This, is in fact, incorrect.
It should be:
"SELECT @@IDENTITY";
Depending on table size, this query can run for MINUTES. As it
was doing for me. Try it out. Open up a query browser and
run this:
INSERT INTO TABLE (...) VALUES (...) SELECT @@IDENTITY FROM TABLE
And then compare to this:
INSERT INTO TABLE (...) VALUES (...) SELECT @@IDENTITY
Its silly, but the difference is huge. The first one scans every
row in the database, printing out 'null' for every row, then spits
out the last identity at the end. In a table of 9 million rows, this
ran for 15 minutes. Take the "FROM TABLE" off and it pulls the
identity value from a system table and returns in .0001 ms.
So. What do I do now? I modified my local version and re-deployed.
Problem solved. Whew. No more locking, socket closings and applications
falling over and shutting down.
Is there a place where I can attach a bug report? Can anyone else
test this out to make sure I'm not nuts (I'm 99.99% I'm not).
Thanks a lot for all the help over the past week.
Charlie
Charles Anthony said the following on 11/3/2005 2:35 AM:
It could simply be a timeout issue.
Do you have a connection pool setup ? Do you close "idle" connections - i.e.
if a connection has been unused in the pool for more than, say, 5 minutes -
do you close it ? This can be configured with the DBCP connection pool, and
I imagine it can with the Tomcat datasources too.
Basically, most database will close database connections if they have been
open but unused for a long period of time. I suspect that's what's happening
here.
The other possibility is that SQL server has been restarted (or the network
has been interupted by a router reboot or something) during the time the
webapp has been up - which closes the TCP/IP connection between MSSQl and
the jdbc connection. Which means the next time you come to use the
connection, you get the error message.The way to deal with this is to use a
validation query with your connection pool - this is a query that is
executed when you borrow a connection from the pool. If the query fails, the
connection is removed from the pool and another is borrowed. Again,
configuring this is possible with DBPC and probably Tomcat.
Both validation queries & closing idle connections may help here, and both
are probably worth implementing.
HTH
Cheers,
Charles.
-----Original Message-----
From: Charles Harvey III [mailto:[EMAIL PROTECTED]
Sent: 02 November 2005 21:56
To: OJB Users List
Subject: I/O Error: Socket closed
I keep getting "socket closed" errors when connecting to one database.
The DB server is pretty new and has lots of memory. And the machines
running Tomcat are just fine as well. So what is causing my problem?
I run linux for the Tomcat machine, and I am connecting to a SQL 2000 DB.
I am using the jTDS driver.
Is it something in my code? Something in the driver? Is my DB acting
up? Here is a snippet of stack trace.
[org.apache.ojb.broker.accesslayer.JdbcAccessImpl] ERROR: SQLException
during the execution of the query (for a com.alloyinc.quiz.bean.Quiz):
I/O Error: Socket closed
I/O Error: Socket closed
java.sql.SQLException: I/O Error: Socket closed
at net.sourceforge.jtds.jdbc.TdsCore.executeSQL(TdsCore.java:1038)
at
net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:3
60)
at
net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedSta
tement.java:672)
at
org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(JdbcAccessImpl
.java:283)
at
org.apache.ojb.broker.accesslayer.RsQueryObject.performQuery(RsQueryObject.j
ava:74)
at
org.apache.ojb.broker.accesslayer.RsIterator.<init>(RsIterator.java:183)
Caused by: java.net.SocketException: Socket closed
at
java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:99)
at java.net.SocketOutputStream.write(SocketOutputStream.java:136)
at java.io.DataOutputStream.write(DataOutputStream.java:85)
at
net.sourceforge.jtds.jdbc.SharedSocket.sendNetPacket(SharedSocket.java:644)
at
net.sourceforge.jtds.jdbc.RequestStream.putPacket(RequestStream.java:559)
at
net.sourceforge.jtds.jdbc.RequestStream.flush(RequestStream.java:507)
at net.sourceforge.jtds.jdbc.TdsCore.executeSQL(TdsCore.java:1025)
Any help is greatly appreciated.
Charlie
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
___________________________________________________________
HPD Software Ltd. - Helping Business Finance Business
Email terms and conditions: www.hpdsoftware.com/disclaimer
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
