AnsweredAssumed Answered

DevTest 9.1 MySQL on Linux - case sensitivity

Question asked by JacquesBrierre62135525 on Oct 6, 2016
Latest reply on Aug 24, 2018 by MarcyNunns

Just installed 9.1 on servers for new environment I am setting up.

Decided to install a local MySQL DB to handle the Enterprise Dashboard DB space and speed requirements.

With new schema set to utf8 character set and utf8_unicode_ci character set and collation, I started the Enterprise Dashboard in interactive mode and saw teh following errors:

 

2016-09-29 16:10:00,662Z (12:10) [http-nio-1506-exec-10] WARN org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL Error: 1146, SQLState: 42S02  
2016-09-29 16:10:00,662Z (12:10) [http-nio-1506-exec-10] ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Table 'qd99emdb.COMPONENT' doesn't exist 
2016-09-29 16:10:00,663Z (12:10) [http-nio-1506-exec-10] ERROR com.ca.dradis.service.RegistryService - failed to get registries:

It turns out the 'component' table is created in Lower case (by the DDL) but referenced upper case in the Enterprise Dashboard code (or so it seems) and thus breaks when run on a system such as Linux where MySQL defaults to case sensitivity.

It turns out CA does not have a fix or patch for this. The immediate fix is to turn off case sensitivity in the DB startup configuration as follows for RedHat 2.6: 

  1.    in /usr/my.cnf             # or wherever your cnf file resides
  2. add the line
          lower_case_table_names=1
  3. Run
          sudo service mysql restart

While this fixes it, it also now commits any schema created on the MySQL db to case insensitivity and that may be a
liability down the road.
The right thing to do -- and I fervently hope CA addresses this in an upcoming release -- is to commit to either case
in the code, thereby fixing this inconsistency. Had I opted to use my external DB, I may have encountered a case issue.
Yes, the DDL could have been modified (I considered it) but where else is this table referenced? and how?
Definitely something to consider.

 

If details needed here's the case info

Case Number & Title:00516820: SqlExceptionHelper - Table 'qd99emdb.COMPONENT' doesn't exist

Outcomes