re-openning a connection throughout request

[Guest]

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?

[BKBK]

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.

[Guest]

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]

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.

[Guest]

Thank you.

(1) Our MySQL version is up to date.

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

Since I have control over the DB (I am also the DBA) I can easily configure MySQL with a high wait_timeout. But as I mentioned, this is undesired since it will affect all other clients, where I definitely don't want to have idle connections lying around.

(2) 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.

[Guest]

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

[BKBK · Accepted solution]

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.

[Guest]

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'.

This only applies to cross-request, but not within a request.

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.

Ah! This is a workaround I can live with -- will proceed to test it. Thanks!