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

3 Answers 3

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

@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)

share|improve this answer
    
Please incorporate the gist of the links into your answer. As it is this answer doesn't stand without them. –  Colin 't Hart Aug 7 at 15: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.