 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
Java Forum / Databases / February 2005SQLServer 2000 Driver for JDBC behaviour on SQL Server restart
1) My java code properly connects to the SQL Server DB via the SQL Server 2000 JDBC driver. 2) During one of our stress tests, we restarted the SQL Server to see how the code handles it. 3) My code doesn't really recognize that the SQL server is up and my calls to DB fail throwing the following exception (Note: I have tried this on Oracle 9i and it works properly. i.e my code is able to read from Oracle DB on db restart.) Caused by: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Broken pipe at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source) at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source) at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source) at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.submitRequest(Unknown Source) at com.microsoft.jdbc.sqlserver.tds.TDSCursorRequest.openCursor(Unknown Source) at com.microsoft.jdbc.sqlserver.SQLServerImplStatement.execute(Unknown Source) Any help would be appreciated. I would like to see how you made an oracle jdbc connection that passed this test. The exception you get is a common one, when the driver finds that the socket it had been using for it's connection to the DBMS is dead during a user call to a JDBC method. You should program to deal with this sort of failure if your DBMS may go away sometimes. Drivers typically do *not* transparently reconnect when a DBMS goes down and comes back up. Some drivers and DBMSes do have failover capability but this is never guaranteed to be transparent because the context of the connection cannot be guaranteed to be retained across the failover. Typically for a complicated JDBC environment even the failover drivers require the moral equivalent of making a new connection. Joe Weinstein at BEA > I would like to see how you made an oracle jdbc connection that > passed this test. I expect he used the ConnectionRetry and ConnectionDelay options in the connection URL. I'm not sure if the SQL Server driver has an equivalent. Luke > I expect he used the ConnectionRetry and ConnectionDelay options in the > connection URL. I'm not sure if the SQL Server driver has an equivalent. Those parameters instruct the (DataDirect) driver how many times to retry establishing a connection and how much to wait between succesive retries. So unless you actually request a new connection, those parameters have no effect whatsoever. And the OP implied that he managed to use the same connection after restarting Oracle. Which, I have to agree with Joe, is impossible. Alin. Thanks for the responses. I agree with what Alin says. I have not used the same connection after restarting Oracle DB, but instead used a new Connection. My connection pool is managed my BitMechanic code. In the creation of Connection Object: if (connection is not valid) then use ConnectionRetry and ConnectionDelay to make another call until you get a Connection. (Note: I don't have to have this above line on Windows, as my JDBC driver gets a new connection from the pool if the connection is null in the call to createStatement()) This way my connection.createStatement() would not throw any Broken Pipe Exception. >>I expect he used the ConnectionRetry and ConnectionDelay options in > [quoted text clipped - 10 lines] > managed to use the same connection after restarting Oracle. Which, I > have to agree with Joe, is impossible. Really? Then that's an unfortunate lack. One thing I like about the MySQL JDBC drivers is the "autoReconnect" feature. If other DBs don't have that, it's a big plus for MySQL. Luke > Really? Then that's an unfortunate lack. One thing I like about the > MySQL JDBC drivers is the "autoReconnect" feature. If other DBs don't > have that, it's a big plus for MySQL. I wouldn't necessarily put it that way. Think about what happens if you start a transaction on a connection, then after you do some work, the connection fails; it then auto reconnects, you do some more work while not knowing anything about what just happened; then you commit your work, which actually commits only the second half; your DB is now in an inconsistent state. I think it's not worth it. Alin. >>Really? Then that's an unfortunate lack. One thing I like about the >>MySQL JDBC drivers is the "autoReconnect" feature. If other DBs don't [quoted text clipped - 7 lines] > work, which actually commits only the second half; your DB is now in an > inconsistent state. I think it's not worth it. I don't think it would work that way, but it would be interesting to know. I wonder if Mark Matthews is following this thread? IME the autoReconnect feature is especially useful if you have something like a web app, which stays up for long periods of time. You really don't want to have to code around the need to reconnect every time somebody brings the database server down, even though your app probably weren't accessing the serrver when it failed. I'm fairly certain that, in the event that you are accessing the server when it goes down, you'll get an error, which is only fair. But if you're between operations, your connection will reestablish itself when the server comes back up. How many apps keep transactions open for more than the briefest instant, anyway? Sound like a Bad Idea to me. Luke >>Really? Then that's an unfortunate lack. One thing I like about the >>MySQL JDBC drivers is the "autoReconnect" feature. If other DBs don't [quoted text clipped - 9 lines] > > Alin. Alin, Autoreconnect only works if autoReconnect is true, and even still, you still get an exception, you just need to re-try the operation. However, we're planning on deprecating the feature, and requiring applications to acquire a new connection to retry their operation on. As applications get more-and-more stateful with the way they deal with JDBC connections and other session state, it's the only way to ensure proper operation. The real issue is that the original JDBC specification never specified how long a connection should remain alive, so many developers assumed 'forever' while most vendors assumed 'as long as possible', which is a mismatch of expectations, to say the least. _Robust_ transactional JDBC applications are coded to expect the JDBC connection to go south at any moment, and recover accordingly, which means sometimes you can retry the operation, other times you punt the exception back to the next layer up (and eventually to the user). On the other hand, it's a lot of overhead to do that if all you're doing is throwing up read-only pages of data. -Mark Mark, Thanks for the clarification. It's clearly obvious that a connection won't be able to stay up "forever"; not taking this into account is definitely bad coding. If an exception is thrown when the connection fails and is then reestablished, this doesn't really help a lot; the operation will probably be aborted anyway. All server-side applications have some sort of connection pool, usually DBCP. It's the connection pool's job to ensure the connections are still alive and to dump them and create new ones if not. If a connection pool can't do that then it's not much more than a list of objects. Luke, If the error occurs "between" two operations it will actually occur while the second operation executes and will probably cancel the operation. Having a decent connection pool behind it all will lead to the same result: any subsequent operation will get a new connection. Alin. > Mark, > [quoted text clipped - 9 lines] > ones if not. If a connection pool can't do that then it's not much more > than a list of objects. Alin, DBCP isn't all that robust in my experience, and the documentation Apache has put up unfortunately isn't real good and providing a decent default installation either, for example, here's what they say to do for MySQL: <Resource name="jdbc/TestDB" auth="Container" type="javax.sql.DataSource" maxActive="100" maxIdle="30" maxWait="10000" username="javauser" password="javadude" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/javatest?autoReconnect=true"/> Obviously, you don't want to use 'autoReconnect' with a connection pool, 100 connections is probably way too high for most applications, and they don't test idle connections by default by adding testWhileIdle="true" and validationQuery="SELECT 1". I've filed a bug on this at least once, maybe I'll just post to the tomcat mailing list and see if that gets any traction. DBCP also doesn't have (that I know of), any way to set a 'max idle time' of a connection, which is also very handy, especially if you set it to something less than 'wait_timeout' on the MySQL server...I use C3P0 a lot, and it has such a feature, as do many commercial appserver pools. Because connection setup is so quick in MySQL, I usually recommend a dynamic-sized pool, with some maximum cap (determined by how beefy your MySQL server is), and with low max-idle-time, on the order of minutes. If the system is under load, the connections don't go idle, when the load lightens up, idle connections are tossed. The only penalty you pay is a little extra time to respond to a load spike. In any case, _no_ connection pool will save you from having a connection die from the time you retrieve it from the pool until the time you return it, so of course, defensive coding is the rule! -Mark Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.