CODEWITHSUNDEEP
javasql-serverazurepersistencedatabase-connection
Andrea
Andrea

Reputation: 436

Connection closed using SQL Azure

I have a Java server which continuously runs jobs using Quartz as a scheduler. The goal of this program is to periodically execute calculations, so every T minutes a job starts: it fetches data to verify, does some calculations and saves it back on the database.

The server is run from a VM in Microsoft Azure with Ubuntu 12.04 LTS, while the database is SQL Azure. The ORM I'm using is Ebean 3.3.1-RC2.

I keep getting the following exception:

javax.persistence.PersistenceException: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
at com.avaje.ebeaninternal.server.transaction.JdbcTransaction.rollback(JdbcTransaction.java:642)
at com.avaje.ebeaninternal.server.transaction.JdbcTransaction.rollback(JdbcTransaction.java:623)
at com.avaje.ebeaninternal.server.core.BeanRequest.rollbackTransIfRequired(BeanRequest.java:87)
at com.avaje.ebeaninternal.server.core.DefaultServer.findId(DefaultServer.java:1241)
at com.avaje.ebeaninternal.server.core.DefaultServer.findUnique(DefaultServer.java:1253)
at com.avaje.ebeaninternal.server.querydefn.DefaultOrmQuery.findUnique(DefaultOrmQuery.java:908)
at com.avaje.ebeaninternal.util.DefaultExpressionList.findUnique(DefaultExpressionList.java:189)
at com.db.DAO.findById(ItemDAO.java:89)
at com.jobs.Task.doTask(ItemTask.java:50)
at com.jobs.Job.execute(ItemJob.java:34)
at org.quartz.core.JobRunShell.run(JobRunShell.java:213)
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:557)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(SQLServerConnection.java:388)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.rollback(SQLServerConnection.java:1954)
at com.avaje.ebeaninternal.server.lib.sql.PooledConnection.rollback(PooledConnection.java:794)
at com.avaje.ebeaninternal.server.transaction.JdbcTransaction.rollback(JdbcTransaction.java:635)
... 11 more

This is my ebean.conf:

datasource.azuresql.username=username
datasource.azuresql.password=password
datasource.azuresql.databaseUrl=jdbc:sqlserver://server_name.database.windows.net:1433;database=database_name;user=user_name@server_name;password=password;encrypt=true;hostNameInCertificate=*.database.windows.net;loginTimeout=30;
datasource.azuresql.databaseDriver=com.microsoft.sqlserver.jdbc.SQLServerDriver
datasource.azuresql.minConnections=1
datasource.azuresql.maxConnections=400
datasource.azuresql.isolationlevel=read_committed
datasource.azuresql.capturestacktrace=true
datasource.azuresql.maxInactiveTimeSecs=30

I don't know what to do, I tried changing timeout values, min/max connection number but this exception keeps popping up. The one thing I noticed is that the system goes well for a bit of time, then I get about 30-40 exceptions like this one and then it all continues as nothing is wrong.

Please suggest a fix, thank you.

Upvotes: 1

Views: 2893

Answers (1)

Herve Roggero
Herve Roggero

Reputation: 5249

As stated by one of the comments above, from Peter, the connection pool will help you get a connection already opened. That's it's primary function. The connection pool may also help you by automatically removing connections that are closed; but the connection pool could have a hard time dealing with broken connections. In the .NET world, check out this blog post that explains that an update to the .NET library was needed to handle this specific condition. If Ebean doesn't implement a similar logic internally that would automatically reopen a broken connection for you, then the burden is on you to implement it.

In addition, adding connection retries against Azure SQL Database is a known and highly recommended pattern. Here is an older blog that shows earlier implementations of this pattern. And here is a more recent best practice document from Microsoft.

Upvotes: 1

Related Questions