Can I declare lower_case_table_names=1 on my MySql without reinstalling it?
I have a web application that I want to deploy on RHEL based server with MySQL(8.0.18) installed on it. The problem is that I have tested my web apps' sanity on Windows OS with MySQL installed and didn't face any problem. But when I'm running the same on RHEL, MySQL gives me an error while executing queries like 'table USER_MASTER doesn't exist', while it's on my database but with small cases like 'user_master'.
I have done some searching and found that in UNIX case-sensitivity matters while in Windows it doesn't. So I use setting lower_case_table_names=1 in my.cnf and thought this will do the trick but soon I found that this variable must be declared during MySql server initialization time.
I have gone through following liks:
https://mysql.wisborg.dk/2019/04/14/install-mysql-8-on-linux-with-lower_case_table_names-1/
and
https://bugs.mysql.com/bug.php?id=90695
and all these say that I have to reinstall the MySql to make lower_case_table_names works.
Below is the error I get if I use lower_case_table_names=1 directly.
2019-11-08T05:30:17.331505Z 1 [ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('1') and data dictionary ('0').
I hope there must be some other way by which I can avoid reinstalling the MySql and setting that variable and avoid going through my code and replacing table names with lowercase ones.
Thanks.
No, it is not possible to change this setting on mysql v8.0 without reinstalling it. Mysql manual is very clear on this:
It is prohibited to start the server with a lower_case_table_names setting that is different from the setting used when the server was initialized. The restriction is necessary because collations used by various data dictionary table fields are determined by the setting defined when the server is initialized, and restarting the server with a different setting would introduce inconsistencies with respect to how identifiers are ordered and compared.
This was the answer given to the bug report you linked in as well, so I'm not entirely sure what answer you expected to get here.