cfstoredproc throwing 1146 error if the table name is capitalized in the query calls

Explorer ,
Jan 14, 2021 Jan 14, 2021

Copy link to clipboard

Copied

Good morning CF community,

 

      I have a new install of ColdFusion 2018 Entereprise on a CentOS 7 box, and have run into errors being thrown if on a web page that has cfstoredproc call, and the query scripts in my database procedures have a table name that is in all caps.  All table names on the database are lowercase. 

 

My companies current live ColdFusion setup, on a webserver we are migrating from, does not have this issue.  This setup processes queries from procedures just fine, even if the table names are capitalized in their scripts.  For example, on our current live setup, if a query has: "SELECT STU_LAST FROM xx_xxx.TABLE_NAME, it processes just fine with no errors. 

 

On the new server, it throws an 1146, Message: "Table 'xx_xxx.TABLE_NAME' doesn't exist", SQLState: 42S02, even though the table does exists but its name is all lowercase: 'xx_xxx.table_name'.  Is there a parameter I can set to ignore case senstivity from queries being ran and called in cfstoredproc, perhaps in the jvm or mysql settings, and in what location would these settings be found?  I'm assuming somewhere in the root directory: /opt/coldfusion2018/cfusion/.  I navigated the CF Administrator page, but couldn't find any related settings to accomplish what I'm looking to fix. 

 

One last note, the database procedures, on our new server, if ran straight from the database execute with no errors even if the table names in the query calls are in all caps.

 

Any insight will be greatly appreciated.

 

Thanks,

 

Daniel

Views

77

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct Answer

Adobe Community Professional , Jan 14, 2021 Jan 14, 2021

Likes

Translate

Translate
Adobe Community Professional ,
Jan 14, 2021 Jan 14, 2021

Copy link to clipboard

Copied

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jan 14, 2021 Jan 14, 2021

Copy link to clipboard

Copied

LATEST

Thank you Dave, your suggestion was spot on.  Steps I took to resolve the issue:

 - sudo nano /etc/my.cnf

 - Added line after the [mysqld] block: lower_case_table_names=1

 - Restarted Maria DB database: sudo systemctl restart mariadb

 

Issue resolved.

 

Thanks,

 

Daniel

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines