Statement Cancelled Due to Timeout or Client Request

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.

Error when loading gists from https://gist.github.com/.select * from document
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 cfsqltype with cf_sql_integer, and works fine for me.

Error when loading gists from https://gist.github.com/.select * from document
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.