MySQL: How to avoid case sensitivity in SQL queries when migrate from Windows to Linux?

Question

I have migrated my Database from Windows to Linux server. In many places, query has been written without focusing their cases (uppercase / lowercase). So, these queries are not getting executed and generating errors. As a result, functionalities based on these queries are breaking. Below is the example query to run in Windows and Linux:

Select A.id,A.title from tablename a;

This query will work in Windows while never work in Linux.

• Is there any way through which I can avoid these case sensitivity in MySQL queries and no need to check and fix every query ?

• ek9: show your table structure with collation please. Also what collation is used in database?

Answer 1

In MySQL the commands themselves are not case sensitive, however depending on the OS, file structure variables might be case sensitive.

Check if your OS is case sensitive, and if this is the case, find out the correct case for the tables, and make sure your query is consistent in naming. Don't forget though that you'll have to rewrite a lot of the queries to make sure they work in MySQL.

References:

MySQL docs: Identifier Case Sensitivity

• ypercubeᵀᴹ: Since the OP has already moved the database from Windows to Linux, it may be as easy as changing the lower_case_table_names setting from 0 to 1 (as the link you have suggests).
• Reaces: I've never personally changed the lower_case_table_names, so I didn't write it in my summary, but rereading the documentation I linked, indeed it might just be that simple, thanks for your comment :)
• ursitesion: I have updated my question with example query.
• Reaces: Try : Select A.id,A.title from tablename A

Answer 2

MySQL queries are not case-sensitive by default. It is possible that you have created case sensitive tables when importing data. Check if you have utf8_unicode_cs collation, that makes it case-sensitive. Reimport your data then using utf8_general_ci.

Also, if you have utf8_bin collation, it will make queries case sensitive.

Your collations changed when re-importing data.

• ursitesion: I have updated my question with example query.
• user1822: MySQL is case-sensitive by default on a Linux system. At least with MyISAM tables or when using innodb_file_per table - because the tablename maps to a filename and the filename is case-sensitive in the file system used by Linux

Answer 3

@ursitesion We also had a similar problem after we migrated our MySQL DB from Windows to Linux. We were lucky that our table names were already in small caps, the only issue we faced was that inside our application & some Stored Procedures (SP) we had mentioned the table names in Large Caps / Camel Case.

We followed the following steps and were able to solve our problem:

1) Edit the MySQL configuration file (i.e. /etc/my.cnf), and add the following line in [mysqld] heading.

lower_case_table_names=1 

Note: By running the 'mysqld' with lower_case_table_names=1, the database creates the tables in all lower-case names. The existing table names are unaffected.

2) Restart the database so that all database table names are now created in lower-case.

3) Verify that the change we made above did take effect in the database using this query

SHOW VARIABLES LIKE '%lower%';

The instructions are also mentioned here .

Now all our tables are in small caps (which was the case even before we made the change) & queries having table / SP names in large Cap work fine.

Just in case you have your table names also in CAPS this link might be of help (i have not tried it)

• Colin 't Hart: Please incorporate the gist of the links into your answer. As it is this answer doesn't stand without them.

Answer 4

I had a similar problem: I used lower case for all my table and column names, but in my php code I started table/column names with upper case in some places. This gave me no problem on my dev machine but after uploading to my hosting server, the table/column names could not be found. SOLUTION: I just edited the database collation from utf8mb4 to utf8_general_ci as stated above and the problem was solved.

NOTE: I used MySQL.

• Rohit Gupta: This adds nothing new to the table. Please tour the help.