tracker issue : CF-3612846

Problem Description:
When attempting to configure a Cluster Setup for Scheduled Tasks, and utilizing a mySQL datasource, ColdFusion creates all upper case tables, but when requesting the tables uses mixed case SELECT statements.  

Steps to Reproduce:
1) Log in to CF Admin
2) Create a mySQL Datasource to a valid mySQL backend (we're using RHEL5, and mysql-server-5.5.28-3.el5.remi.x86_64)
3) Navigate to Server Settings --> Scheduled Tasks
4) Under Enable Custer Setup, select the mySQL DSN you created, and check "Create Tables for Cluster Setup"
5) Click Submit, and CF returns an error at the top of the screen.

Actual Result:
In our mySQL Database, the following tables get created:
+--------------------------+
| Tables_in_cftasks        |
+--------------------------+
| QRTZ_BLOB_TRIGGERS       |
| QRTZ_CALENDARS           |
| QRTZ_CRON_TRIGGERS       |
| QRTZ_FIRED_TRIGGERS      |
| QRTZ_JOB_DETAILS         |
| QRTZ_LOCKS               |
| QRTZ_PAUSED_TRIGGER_GRPS |
| QRTZ_SCHEDULER_STATE     |
| QRTZ_SIMPLE_TRIGGERS     |
| QRTZ_SIMPROP_TRIGGERS    |
| QRTZ_TRIGGERS            |
+--------------------------+

The ColdFusion exception log shows this, however:
"Error","catalina-exec-11","08/15/13","11:44:40",,"Couldn't obtain job groups: Table 'cftasks.qrtz_TRIGGERS' doesn't exist"
org.quartz.JobPersistenceException: Couldn't obtain job groups: Table 'cftasks.qrtz_TRIGGERS' doesn't exist [See nested exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'cftasks.qrtz_TRIGGERS' doesn't exist]


Expected Result:
ColdFusion should be querying using the appropriate case for the tables it created (ie; 'cftasks.QRTZ_TRIGGERS') .  Instead, it is using mixed case, which our mySQL database is configured to honor, instead of forcing all tablenames to be lowercase.


Any Workarounds:
Workaround 1 (Changing your mySQL DB to use mixed case):
You can alter the following setting in my.ini to force all tablenames and comparisons to be lowercased, but this can cause issues if you have software which relies on the mySQL DB to support Case Sensitivity.
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_lower_case_table_names

Workaround 2 (Alter your table names to be what CF expects):
This is the temporary workaround I'm using.  We've renamed the tables so that QRTZ in the table names is now qrtz.  This appears to get the setup working, but is less than ideal.

----------------------------- Additional Watson Details -----------------------------

Watson Bug ID:	3612846

External Customer Info:
External Company:  
External Customer Name: Fireye-
External Customer Email:  
External Test Config: My Hardware and Environment details:

ColdFusion 10 (at Update 11) - 64bit installation

RHEL5.9 - Linux xxxxxxxxxxx 2.6.18-348.12.1.el5 #1 SMP Mon Jul 1 17:54:12 EDT 2013 x86_64 x86_64 x86_64 GNU/Linux

Attachments:

Comments: