Thanks Dave, good idea. Ill setup the separate database for this query.
Yes unfortunately this query specifically collects data from all databases. I
used to have it as a single query per database inside of a loop but this caused
an error signifying too many connections.
The query is as follows:
<cfquery name="userExistsInDatasource" datasource="rebasedb_manage">
<cfloop index="i" from="1" to="#getAllDatasources.recordcount#">
<!--- ENSURE ALL THE SELECT STATEMENTS GET ADDED TOGETHER --->
<cfif i gt 1>
UNION
</cfif>
SELECT #getAllDatasources.datasourceid[i]# as datasourceid,
userid
FROM rebasedb_#getAllDatasources.datasourceid[i]#.users
<!--- add the where clause which applies the filters entered into the grid
--->
WHERE email = <cfqueryparam value="#session.currentUser.email#"
cfsqltype="cf_sql_varchar" maxlength="50">
</cfloop>
</cfquery>
> > we are receiving the following intermittent CF error a lot recently
> and wondering if you could help diagnose this issue...
> >
> > Message: Can not read response from server. Expected to read 4 bytes,
> read 0 bytes before connection was unexpectedly
> > lost.
> >
> > Detail: Communications link failure The last packet successfully
> received from the server was 256,215 milliseconds ago. The
> > last packet sent successfully to the server was 0 milliseconds ago.
> >
> > This always seems to occur on a query that queries multiple tables
> in multiple database - specifically it is collecting user
> > email addresses from 73 databases on our server.
> >
> > Our hosting provider has stated this seems to be a CF error and
> advised us to uncheck the 'Maintain connection' options for
> > each datasource. They recommended this on the basis of reading the
> following post:
> > http://www.cjboco.com/blog.cfm/post/coldfusion-error/
> >
> > Before I try this I wanted to double check this was in fact the
> right advice and whether this would cause any additional problems?
>
> I'm unclear on what you're actually doing. You're running a single
> query that's querying 73 separate databases?
>
> If so, that seems like the problem you need to fix, rather than
> disabling the "maintain connections" option in your CF data sources.
> Disabling JDBC connection pooling will usually significantly degrade
> performance.
>
> If you're unable to change or fix that query, I'd recommend creating
> a
> separate CF data source that you use just to run that query, and
> disabling "maintain connections" for just that one data source.
>
> Dave Watts, CTO, Fig Leaf Software
> http://www.figleaf.com/
> http://training.figleaf.com/
>
> Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on
> GSA Schedule, and provides the highest caliber vendor-authorized
> instruction at our training centers, online, or onsite.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:355651
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
