Case Sensitive Table Names in MySql

Question

While creating the Azure Database for MySQL, it does not ask if you want to have lower_case_table_name value to be 1 or 0 which controls the case sensitivity (no for 1 and yes for 0). It sets the default to 1 which causes the tables names to be all lower and it messes up the development.

Also, even with the lower-case table names, if you try to add or remove a column, it will throw error on synchronization and say that table_name already exists. Then, to add or remove that table you would have to drop all tables and recreate them, which is really dumb to do on a production table if we are adding or removing columns.

I'm using TypeORM and Sequelize. Any idea how to mitigate that? Is there any way to change the lower_case_table_name variable to 0 somehow and recreate the MySQL server?

I would really like any kind of solutions. Way too much frustrated due to this.

Answer 1

Hi, @Muhammad Salman Afzal Welcome to the Microsoft Q&A forum, and sorry for the trouble you are facing.

Renaming the table is only approach for now which can be taken up and later dump/restore to Flexible server.

Unfortunately, Azure don’t this option yet, but looks like your request is a valid option and needs to be implemented by the PG team.
https://learn.microsoft.com/en-us/azure/mysql/flexible-server/concepts-limitations

We cannot set server parameter “lower_case_table_names” to 0. This parameter only impacts table name case sensitivity. FYI. this parameter cannot be changed.
In the particular case of MySQL 8, even due to a limitation of MySQL (Not Azure limitation)it is prohibited to start the server with a lower_case_table_namessetting that is different from the setting used when the server was initialized (MySQL :: MySQL 8.0 Reference Manual :: 5.1.8 Server System Variables)

As per this documentation "Use lower_case_table_names=0 on Unix and lower_case_table_names=2 on Windows. This preserves the lettercase of database and table names. The disadvantage of this is that you must ensure that your statements always refer to your database and table names with the correct lettercase on Windows. If you transfer your statements to Unix, where lettercase is significant, they do not work if the lettercase is incorrect."

In case you have any questions or concerns, please let me know.

Regards
Geetha

Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer.