Cfquery table name appears to be case sensitive (how to fix?)

dev support
1

i’m currently migrating from CF9 to lucee

i used GitHub - foundeo/ubuntu-nginx-lucee: Script for standing up a Lucee server using nginx and Tomcat on Ubuntu to get setup

after some basic dsn setup etc, when I run my app it appears cfquery wants the tables to be case sensitive

i.e. I’m getting this error

Table 'mydatabase.myTable' doesn't exist

even though I see the table “mytable” (note it’s all lowercase)

is this a mysql issue or a lucee issue? and how do I fix it? Sure, I could replace-all in my codebase, but uhh … yea

thanks in advance!

2

I’m not very familiar with MySQL but a couple of questions come to mind:

  • Is there a schema name missing maybe? Perhaps it was set as default schema in the CF9 setup

  • Are you using quotes around the table name in your query? Can you show a simpler query code?

3

MySQL table identifiers are usually case sensitive on non-Windows systems. If your code has mixed case table names, but the database has all lower case table names, then my guess is that you used to use a MySQL server where lower case table names were turned on and you’re migrating to a new one that doesn’t have the same option set?

If you set lower_case_table_names=1 in your MySQL config (my.ini) it should resolve the issue. For reference see;
https://dev.mysql.com/doc/refman/5.7/en/identifier-case-sensitivity.html

(Or if you’re using something like AWS RDS then you’d need to set that in an Option Group since you don’t have direct access to the config file itself).

Hope that helps :slight_smile:

4 Likes
4

That did it! Thank you very much, and for the quick reply!

This codebase is over 10 years old, so 10 years ago I probably wanted camelCase table names for readability. I’ve migrated this code/database a few times over the years, and with ColdFusion being case-insensitive I guess I’ve never had this problem, and always ran CF on Windows – or maybe I had to fix this years ago and don’t remember.

This fix actually fixed another issue where it said something like KEY NOT FOUND when doing a simple <cfoutput>#ucase(BRAND_DOMAIN)#</cfoutput>. I tried to change it to brand_domain, but that didn’t work.

Now I just have to figure out how to keep the machine from crashing.

I’m using Amazon Lightsail w/ Ubuntu OS only. Installed Lucee & MySQL manually. I’m only testing so I started with a 512RAM and have since upgraded to a 1GB RAM server. Maybe I need to move to 2 or 4GB, and probably will once I’m ready for production.

Thanks again!

1 Like
5

Common pitfall. I fell in it too back in the day.

The SQL specification actually calls for case-insensitivity, and most DBMSs will automatically lowercase or UPPERCASE your names for you unless you quote them.

I’m actually migrating a database from SQL Server, which maintains the original CaSe, to Postgres, which follows the specification and does not, resulting in the less readable camelcase names, so I am converting the camelCase tables to snake_case. I posted about it recently at https://lucee.daemonite.io/t/rebind-jquery-tablesorter/2711/14?u=21solutions