Topic: Mysql and lower_case_table_names

[jysse]

I'm using a program that uses mysql database. I've managed to transfer database from original (windows)machine to Sme Server.
Program uses ODBC for the connection. Connection goes well but program fails to start because of an error message that tells about incompatible setting
"lower_case_table_names=0".
I read about it and it seems that this setting is "0" in Sme and "1" in Windows and is related to how it handles lower and upper cases.

Now question is that can I change this setting to "1" in Sme Server. Is webmail going to work after that ?
And of course, if it is possible to change it, what is the proper way to do it in Sme ?

Thanks,

jysse

[raem]

jysse

...I read about it and it seems that this setting is "0" in Sme and "1" in Windows and is related to how it handles lower and upper cases.

Windows programmers use upper & lower case to make the code more easily readable, as in Windows the case does not matter as it is treated the same.
Linux is case sensitive, so if you run the same code on a Linux machine you will have lot's of problems, apart from the fact the code won't even work.

I suggest you get the programmer to go through all the code and make the case consistent, and respect the fact that Linux reads
database
and
Database
as different things.

I am speaking from experience as we had Windows programmers (who were unfamiliar with Linux), develop a quite complex database for us. They developed it on Windows mysql and when transferred to Linux they got badly stung by the case sensitivity issue and the only answer was to scan through all the code and replace all upper case occurences with lower case. I suggest you use lower case only for ease of reading in Linux.

[jysse]

Unfortunately it is not possible to change code as this a third party program. I'm still considering to change setting in server.
If I do that what will stop working ? There is only "Horde" database at the server.
Will it affect webmail ?

jysse

[cactus]

Unfortunately it is not possible to change code as this a third party program. I'm still considering to change setting in server.
If I do that what will stop working ? There is only "Horde" database at the server.
Will it affect webmail ?

jysse

I think it will not be a problem with horde as all tables and fields of the horde database are all lowercase. Be sure to make the change in a template and not in the configuration file directly, otherwise changes are not permanent over updates/upgrades and reboots. The nice benefit of this is if problems do occur you can easily revert it by removing the custom template, re-expanding the configuration file and restarting affected services.

Also be sure to take not of this advice from the MySQL manual on Identifier Case Sensitivity

Note that if you plan to set the lower_case_table_names system variable to 1 on Unix, you must first convert your old database and table names to lowercase before restarting mysqld  with the new variable setting.

[jysse]

I'm not familiar with templates. Tried to read about it from wiki but too newbie for it...
Care to tell me how to make changes to a template ?
Also I have no idea how to convert old database to lowercase. Of course I will look at it from Mysql docs.
Thanks a lot so far !

jysse

[cactus]

I'm not familiar with templates. Tried to read about it from wiki but too newbie for it...
Care to tell me how to make changes to a template ?

You do not have to create a template but you will have to make a copy of the already existing template which are normally located in the /etc/e-smith/templates folder to the /etc/e-smith/templates-custom folder. The templates-custom tree has to have the same folder structure as the templates folder (but fragments of the configuration files you would like to change need to be in there as they will form the overwrite for the normal unmodified template). You will have to make desired changes in the copy in the /etc/e-smith/templates-custom tree.

More information on the SME Server template system can be found in the development documentation, be sure to read it very carefully as it will help you understand the system that SME Server uses under the bonnet.

Also I have no idea how to convert old database to lowercase. Of course I will look at it from Mysql docs.

Easiest way is to dump your database using the mysqldump application and make modifications in there. After that you will have to import the database again after dropping the old one. Make sure that nothing changes in the database in between dump and restore.