installation issue

[Matt South]

Hi folks, I have a problem with my installation which stalls on step
15 of 16 - where the database connection is verified.

If I look at the logging information on the console I see the
following messages:
Sep 8, 2011 10:22:58 PM
uk.nhs.cfh.dsp.srth.distribution.DBConnectionVerifier verifyConnection
WARNING: Error creating connection. Nested exception is : SELECT
command denied to user 'snofyre'@'localhost' for table 'CONCEPT'
Sep 8, 2011 10:22:58 PM
uk.nhs.cfh.dsp.srth.distribution.JDBCConnectionValidator validateData
INFO: installPath = /home/matt/Applications/Snofyre
Sep 8, 2011 10:22:58 PM
uk.nhs.cfh.dsp.srth.distribution.JDBCConnectionValidator validateData
INFO: varMap = {sappheiros.db.password=[mypassword],
sappheiros.db.schema.name=fakedata2,
sappheiros.db.host.name=localhost, sappheiros.db.port.number=3306,
sappheiros.db.user.name=snofyre}

If I look at the two databases I see there is a "concept" table in
snomed which I can query using the credentials provided in the
installation wizard with "select * from concept limit 0, 10" in the
MySQL Workbench, but which fails if I use the query "select * form
CONCEPT limit 0, 10". Is this perhaps a case sensitivity problem? I
am using Ubuntu 10.04.

regards,

Matt

[Jay Kola]

Hi Matt,

Yes, this is a case sensitivity issue. One of our team members had this solution below. Please let us know if it works for you. Can you also please log this as an issue on the issue tracker?

http://code.google.com/p/snofyre/issues/list

Cheers,

Jay

==================== START OF SOLUTION ==================== 

So in summary, solution was to

 

Convert all table names to lowercase thus :

 

log into mysql

 

select concat('rename table ', table_name, ' to ' , lower(table_name) , ';') from information_schema.tables where table_schema = snomed';

use snomed;

# execute script that command generated (I just crtl-selected the block in the terminal and mid-click pasted it back)

 

# repeat as needed for other tables...

 

To configure mysql to use lowercase table names everywhere :

 

create a file

 

/etc/mysql/conf.d/lowercase_tables.cnf

 

containing

 

[mysqld]

# This forces all table aliases in queries to lowercase

lower_case_table_names = 1

 

At terminal

 

sudo restart mysql

 

Run application and hooray, it seems to work (or at least run query1.xml)

==================== END OF SOLUTION ==================== 

[Matt South]

Hey thanks for the reply. It leaves me slightly puzzled though. The
issue AFAICS is that the installer tries to use an upper case table
name where the table name is already lower case. So if I convert all
tables to lower case, aren't I going to have the same problem?
Shouldnt I be converting to upper case? Perhaps I am missing a
subtlety in the second step?

Matt

[Jay Kola]

Matt,

I can't remember exactly what causes the issue, but I think its to do with the table name being hard coded in one of the DAO classes…. Yes it bad practice but thats how it is at the moment! The DAO is one of the very few classes that has hard coded SQL at the moment.

I'd suggest trying upper case and then lower case and reporting what worked.. :) The fix provided by my ex-team member was for a very old version of Snofyre, so its very likely that things might be the other way around now.

Hope this helps… :)

Cheers,
Jay

[Matt South]

Thanks Jay, I'll let you know how I get on.

Matt

[Matt South]

The fix as you described it solved the problem Jay. I also had to
"lower case" all of the fakedata2 tables to see the query and data
functionality when I fired up the app.

Thanks for your help.

regards,

Matt