CODEWITHSUNDEEP
phpmysqlsql
Juliano Bittencourt
Juliano Bittencourt

Reputation: 143

Case sensitive issues with Mysql queries

I'm using a legacy PHP framework which automatically assembles queries for me. By some reason, it is assembling a query like this:

SELECT s.status,c.client FROM client C LEFT JOIN status S ON C.id_status=S.id_status'

This isn't a problem on my MacOS X workstation. But when I test it on my production server mysql raises the following error:

#1054 - Unknown column 's.status' in 'field list'

It is definitively a case issue on s.status. If I manually runs the query changing s,c for S,C , it works perfectly.

A quick look on google didn't solved the issue. Any ideas?

Upvotes: 1

Views: 431

Answers (2)

Halil Özgür
Halil Özgür

Reputation: 15945

Tables in MySQL are stored as files. File names are case-insensitive in MacOS X and Windows, whereas they are case-sensitive in Linux. You can use table names without regarding case in MacOS X and Windows, but not in Linux. So you should choose a consistent casing for all your table names and use it throughout your code.

I'd suggest using all lowercase or uppercase names separated with underscore like tbl_etc, MY_TBL etc so that there'd be no confusion regarding case.

Upvotes: 0

raina77ow
raina77ow

Reputation: 106483

Well, it's said in the documentation:

By default, table aliases are case sensitive on Unix, but not so on Windows or Mac OS X. The following statement would not work on Unix, because it refers to the alias both as a and as A:

mysql> SELECT col_name FROM tbl_name AS a
    -> WHERE a.col_name = 1 OR A.col_name = 2;

There are also some solutions given in this section of the documentation as well. For example, you can set lower_case_table_names to 1 on all platforms to force names to be converted to lowercase - but you have to rename all your tables to lowercase as well in that case.

Upvotes: 1

Related Questions