2

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 ?
1
  • show your table structure with collation please. Also what collation is used in database?
    – ek9
    May 8, 2014 at 9:09

3 Answers 3

2

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

4
  • 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). May 8, 2014 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, 2014 at 8:04
  • I have updated my question with example query.
    – ursitesion
    May 8, 2014 at 9:02
  • Try : Select A.id,A.title from tablename A;
    – Reaces
    May 8, 2014 at 9:20
0

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.

2
  • I have updated my question with example query.
    – ursitesion
    May 8, 2014 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
    – user1822
    May 8, 2014 at 9:13
-1

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

1
  • Please incorporate the gist of the links into your answer. As it is this answer doesn't stand without them. Aug 7, 2014 at 15:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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