I do not have access to modify the MySQL Server itself, I can only manage the database itself. The issue I’m running into is that some of the tables are tableName and others are just tablename. In MySQL, the table is tablename. As a result, the query running against tableName retuns an error that the table cannot be found. I looked through the Lucee Web Admin and did not find anything about ignoring table name casing but I did find this other thread https://lucee.daemonite.io/t/cfquery-table-name-appears-to-be-case-sensitive-how-to-fix/3054/2. Unfortunately I can’t apply this fix to my situation so I was wondering if I could setup something in the Application.cfc upon initialization (if I setup the datasource connection in the Application.cfc instead of Lucee Admin). I’ve googled the topic but did not find anyway to do it.
https://lucee.daemonite.io/t/cfquery-table-name-appears-to-be-case-sensitive-how-to-fix/3054/2
Is the only option I have is to update code?
It’s lucee 5.x but the query is truly as simple as:
select column1, column2 from tableName
In MySQL, the table name is tablename and as a result of the casing difference, MySQL reports back the table is not found. The database is on a Linux environment as best as I can tell where as the webserver is Windows 2016.
MySQL tables are stored in files, and linux has a case sensitive file environment. Linux can have many tables with the same name (e.g. tbname, TBname, TBNAME… etc) while in Windows you can only have one table name. To avoid problems in Interoperability it is recomended to always use lowercase for tablenames. Otherwise you should configure mysql, see https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html ).
- In case you dont have access to the mysql environment and nothing works, you can try a possible workaround: a workaround could be a SHOW-Table-STATUS query in the application.cfc, store the tablenames into variables like “CaSensitiveTBNames.#tablename#”, and then do a select with
'SELECT column1, column2 FROM #CaSensitiveTBNames.tablename# this should work with a find/replace from a list of the SHOW-TABLE-STATUS-query. Better would be to rewrite your code.
The same problem you will have with database names. They are also case sensitive in Linux.
Hope that can help a little. Good luck.
I did a quick experiment and what you quoted is spot-on with my findings. Touching code will be the better way to go. Thank you for confirming.
Even better would be renaming the tables with upper case to lower case, or simply copy the complete table with a mysql client, such as HeidiSQL (e.g. copy the complete ‘TaBleName’ to a new table tablename_tmp, delete ‘TaBleName’ and then rename ‘tablename_tmp’ to ‘tablename’ and recode the sql-statement to lower case. That would be cleaner. It’s up to the time you have to do it.
Luckily the tables names are all lowercase so it is only code we need to touch. My guess is someone(s) didn’t follow our protocols… well, clearly they didn’t follow our protocols or I wouldn’t be here asking this today. LOL.