Skip to main content
A
Anonymous
August 4, 2014
Answered

re-openning a connection throughout request

  • August 4, 2014
  • 1 reply
  • 462 views

Reopenning/re-validation a connection during a request: I'm aware of pooling, recycling, test-on-borrow queries etc. However, I also realize that within a single request scope, a data source connection is only opened once, and is not tested throughout the request.

The case in hand I have is a two data sources based report, issuing a query on datasource1, then datasource2, then back on datasource1. The datasource2 query takes a long time, by which the datasource2 connection turns invalid.

Question being: is there a way to force CF to re-open/re-borrow/re-validate connections even throughout the request scope?

    This topic has been closed for replies.
    Correct answer BKBK

    (I should add that I have indeed configured all the above settings and more)


    Shlomi Noach wrote:

    (I should add that I have indeed configured all the above settings and more)

    It is indeed clear you are an old hand at this.

    There is a problem with CF holding a connection for a long duration, then *assuming* it's still fine without checking it again.

    You should then uncheck 'Maintain Connections'.

    All these settings apply *cross-request*. The problem is not there. The problem is that within a single request, two invocations of a query against same data source will use same connection *without checks*, and this is a somewhat naive approach on the CF side.

    You could force Coldfusion to open a new connection for every invocation of a query. For example, adding the attributes username and password to the cfquery tag will guarantee that Coldfusion will open a new connection for every cfquery request.

    1 reply

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    August 4, 2014

    shlomi noach wrote:

    The datasource2 query takes a long time, by which the datasource2 connection turns invalid.

    Shouldn't that be the issue to investigate and find a solution for? If the query is as slow as molasses, giving it a new connection won't make it any faster.

    A
    Anonymous
    August 4, 2014

    I guess that was called for.

    I'm aware that our queries are very slow. This is a technical debt, and we are evaluating other technologies (ie columnar datastores) to solve that.

    Nevertheless at this moment we have said situation. I'm not looking to make it faster (which is a DB task). I'm looking to have ColdFusion re-validate a connection throughout the request's lifetime, as in current status my problem is that it fails with an ugly error message.

    A solution would be to raise the wait_timout threshold on our (MySQL) servers. I prefer not to, since this would affect all clients of our database; I don't mind having long running queries, but long waiting connections I don mind. I would like CF to check upon its connections throughout the request just as it would across requests.

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    August 4, 2014

    Understandable. What can you do at the Coldfusion end, given that the query is in the hands of the DB administrator and the MySQL server polices the connection timeout?

    In what follows I have assumed that your request timeout settings are such that the request will outlast the query. Firstly, then, an option to rule out. In my opinion, there is little point rerunning the query after the connection closes. You would likely just be repeating the same troublesome scenario.

    There are 2 things you could do at your end: (1) advise the DB admin to switch, where necessary, to the latest MySQL version that your Coldfusion version supports:

    Coldfusion

    MySQL

    9

    4.0, 5.0, 5.1

    10

    5.0, 5.1, 5.6

    11

    5.7

    (2) tweak the 'Advanced Settings' (in the Coldfusion Administrator) for the datasource concerned:

    Maintain Connections: ColdFusion establishes a connection to a data source for every operation that requires one. Enable this option to improve performance by caching the data source connection.

    Timeout (min): The number of minutes that ColdFusion maintains an unused connection before destroying it.

    Interval (min): The time (in minutes) that the server waits between cycles to check for expired data source connections to close.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices