Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Issues

Select view

Select search mode

 
23 of 23

If SQL Server DB is restarted, lucee app doesn't recover nicely

Fixed

Description

When running our app. If we decide we want to restart the SQL Server database, the app will get errors stating that the TCP connection was closed on the other side of the connection (see end of the write up for an exception), even after the database is back up and alive and well. The Lucee connection pool doesn't seem to handle it super well (in the sense that it doesn't know if a connection is not valid until it tries to run something on it). Lucee DOES however eventually recover, as each connection is tried and failed in the pool, the app does eventually recover, but only after users get connection errors. We could restart the lucee server, but that also has downtime.

It turns out that Connection.isClosed will still return true on the web server side, if the database server closes the connection on it's side. For that reason, JDBC4.0 introduced connection.isValid() which does a small test of the connection to ensure the connection is good and it will return true if it passes.

I have a fix coming in a pull request shortly that fixes this.

This is the error that occurs.
lucee.runtime.exp.DatabaseException
I/O Error: Connection reset by peer: socket write error;
at net.sourceforge.jtds.jdbc.TdsCore.executeSQL(TdsCore.java:1093):1093
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:563):563
at net.sourceforge.jtds.jdbc.JtdsStatement.executeImpl(JtdsStatement.java:809):809
at net.sourceforge.jtds.jdbc.JtdsStatement.execute(JtdsStatement.java:1282):1282
at lucee.runtime.db.driver.state.StateUtil.execute(StateUtil.java:37):37
at lucee.runtime.db.driver.state.StateStatement.execute(StateStatement.java:43):43
at lucee.runtime.type.util.QueryUtil.execute(QueryUtil.java:232):232
at lucee.runtime.type.QueryImpl.<init>(QueryImpl.java:243):243
at lucee.runtime.tag.Query.executeDatasoure(Query.java:782):782
at lucee.runtime.tag.Query.doEndTag(Query.java:592):592

Environment

SQL Server as a database, using MSSQL driver or jTDS driver

Attachments

3

Details

Assignee

Reporter

Priority

Fix versions

New Issue warning screen

Before you create a new Issue, please post to the mailing list first https://dev.lucee.org

Once the issue has been verified, one of the Lucee team will ask you to file an issue

Sprint

Affects versions

Created 10 February 2016 at 22:15
Updated 8 October 2018 at 15:54
Resolved 8 October 2018 at 15:51

Activity

Show:

Michael Offner8 October 2018 at 15:46
Edited

add possibility to define "validate" in the application.cfc. admin now also shows this option in script template

https://github.com/lucee/Lucee/commit/adf816a8fe908ec089496e01abcdafe2de571933

Michael Offner17 July 2018 at 11:01

does setting the flag solve it for you?

Pothys - MitrahSoft29 June 2018 at 12:17
Edited

Hi ,

I've analyzed this ticket & confirmed the issue still happened on latest version of lucee 5.2.9.14. If we restart the SQL server & run the app it throw error like Connection reset by peer: socket write error;. But this is happen only on the first run on the app. After that it working as expected. Affected with only SQL server database

Note: If we set validate=true Issue goes away

I attached full stackTrace here

Grant Griffith25 October 2017 at 13:31

Curious if this might be able to be revisited? We have a situation where we have a MS SQL environment using Mirroring with 2 servers and a witness. If our database fails over for some reason, usually virtual backups, all of our Lucee App servers lose connection to the database and the only way to resolve is to restart the lucee service. We have both 4.5.5.006 and 5.2.1.9 versions of Lucee running and they both had the same issue.

So what happens is for some reason the database will fail over from db1 to db2 and when that happens the lucee application loses connection the database and never recovers. Only fix is to restart lucee_ctl to get the db connections back.

I can provide logs, just let me know which logs might provide some valid information and I will truncate them down to the time of the issues.

Michael Offner18 February 2016 at 12:30

Flag notifications