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

i’m currently migrating from CF9 to lucee

i used 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!

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?

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;

(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:


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

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 Rebind jQuery Tablesorter