Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?
share|improve this question
    
show your table structure with collation please. Also what collation is used in database? –  edvinas.me May 8 at 9:09

2 Answers 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.

share|improve this answer
    
I have updated my question with example query. –  ursitesion May 8 at 9:05
    
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 –  a_horse_with_no_name May 8 at 9:13

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

share|improve this answer
1  
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). –  ypercube May 8 at 8:02
    
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 :) –  Reaces May 8 at 8:04
    
I have updated my question with example query. –  ursitesion May 8 at 9:02
    
Try : Select A.id,A.title from tablename A; –  Reaces May 8 at 9:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.