GeoNetwork unusable on case sensitive DBMS (ie MySQL on Linux)

[gaellafond]

GeoNetwork setup the database with capitalised table name (Groups, Requests, etc.) but some requests are done with lower case table name (groups, requests, etc.)

---

Example of errors / Warnings shown in the logs:

ERROR [geonetwork.datamanager] - The metadata document index with id=29 is corrupt/invalid - ignoring it. Error: Table 'geonetwork.groups' doesn't exist
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'geonetwork.groups' doesn't exist

WARN  [geonetwork.search-logger] - an error occuring during QueryRequest database storage. Aborting :Table 'geonetwork.requests' doesn't exist
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'geonetwork.requests' doesn't exist

Queries executes on the DB, using lower case table names:

mysql> SELECT * FROM geonetwork.requests;
ERROR 1146 (42S02): Table 'geonetwork.requests' doesn't exist

mysql> SELECT * FROM geonetwork.groups;
ERROR 1146 (42S02): Table 'geonetwork.groups' doesn't exist

Queries executes on the DB, using capitalised table names:

mysql> SELECT * FROM geonetwork.Requests;
Empty set (0.00 sec)

mysql> SELECT * FROM geonetwork.Groups;
+----+----------+-----------------------+-------+----------+
| id | name     | description           | email | referrer |
+----+----------+-----------------------+-------+----------+
| -1 | GUEST    | self-registered users | NULL  |     NULL |
|  0 | intranet | NULL                  | NULL  |     NULL |
|  1 | all      | NULL                  | NULL  |     NULL |
|  2 | sample   | NULL                  | NULL  |     NULL |
+----+----------+-----------------------+-------+----------+
4 rows in set (0.00 sec)

[fxp]

MYSQL lower_case_table_names parameter could help: ​http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_lower_case_table_names

"If set to 1, table names are stored in lowercase on disk and comparisons are not case sensitive."

[gaellafond]

Thanks fxp,

I just spent a few hours and I end up deleting the DB and using lower_case_table_names=1 as you suggested. It works but the bug remain and should be fixed. At lease it's not as critical as it was since I now have an acceptable workaround.