Query Timeout in Coldfusion, but not in MySQL Workbench

[johnornd]

I am developing a webapp on Coldfusion 8,0,1,195765 Enterprise running on a SUSE Linux Enterprise 10 SP2 Server, backed with a MySQL 5.0.26 database.

I am using the following query to select records from the database.  Usually the query is generated from a form, but I have set it with fixed attributes to get the issue I am having figured out:

SELECT i.building_id, i.room_number, i.last_service, i.po_number, i.comments, i.cost, i.serial_number, t.full_name, i.last_user, i.barcode, i.age, i.id, i.scratch FROM item i, item_type t WHERE i.item_type = t.short_code AND building_id = 'TEST' ORDER BY i.id

The item table contains a list of items, i.item_type is a 4 character short code.  item_type contains records with the 4 character code and its associated full name.

This query only returns 1 row with my current database contents.  When I run the query within MySQL workbench connected to the server, it returns the one row in around 0.050 seconds.  However, when I test the query through Coldfusion with the following code:

<cfquery name="test" datasource=#Application.dsn#>

SELECT i.building_id, i.room_number, i.last_service, i.po_number, i.comments, i.cost, i.serial_number, t.full_name, i.last_user, i.barcode, i.age, i.id, i.scratch FROM item i, item_type t WHERE i.item_type = t.short_code AND building_id = 'TEST' ORDER BY i.id

</cfquery>

<cfdump var="#test#">

The query hangs and eventually times out.

The extra strange part is that the query executes fine from Coldfusion if I remove the ORDER BY statament...

I have been unable to figure out what is causing the problem, since everything works fine outside of Coldfusion, and it is a pretty simple query.

Thanks for any help!

[meensi]

Hi,

Its just a guess of mine, may be the CFDUMP is taking long time to dump the values??

Remove the CFDUMP and check the query running time in the debugging information.

[Adam Cameron.]

Its just a guess of mine, may be the CFDUMP is taking long time to dump the values??

Remove the CFDUMP and check the query running time in the debugging information.

This seems unlikely, doesn't it?  If the query runs fine without the ORDER BY clause... the <cfdump> is going to be the same size...

--

Adam

[Adam Cameron.]

Is there any sort of profiling one can do on the MySQL server to see what's going on when CF is running the query?  I've never really used mySQL seriously, so dunno what its capabilities are in that area.  Or similarly on the JDBC end of things: can you do some profiling on what's going on there (using FusionReactor, or maybe CF's own monitoring stuff - never used it myself - or JConsole or something)?

--

Adam