- June 5, 2017
- Posted by: Mahavir Dhruv
- Category: Uncategorized
I would like to share one of the
cfsqltype issue that I face with Lucee CFML Engine.
Initially we use Railo as CFML Engine and recently we change it with Lucee and facing an MySQL query timeout issue. when I try to run that query into MySQL Workbench it works fine and quickly but the same query raises an error (Statement cancelled due to timeout or client request) on Lucee.
To find out an issue I check the query on query performance tool Jet Profiler for MySQL, and into that I found the actual query that executed on Lucee, and finally I got an issue.
The problem is with
cfsqltype attraibute of
cfqueryparam tag. The query written with the below statement into ColdFusion function where documentid passed as argument to the function.
where documentid = <cfqueryparam value="#arguments.documentid#" cfsqltype="cf_sql_numeric">
Here the datatype of column (documentid) is Integer and into
cfsqltype attribute specified value is
cf_sql_numeric means it convert value into decimal number. if passed documentid value is 10 then it converts into 10.0
So in the case, Lucee takes time to cast value from decimal to integer and at last query gets timeout.
So simply correct the value of
cf_sql_integer, and works fine for me.
where documentid = <cfqueryparam value="#arguments.documentid#" cfsqltype="cf_sql_integer">
So it’s important to specify appropriate
cfsqltype that match with column type to avoid such kind of issues.