Solved

How to Configure MYSQL Server for case insensitive queries

Posted on 2010-11-21
9
1,395 Views
Last Modified: 2012-08-13
I need to Configure MYSQL server to process Case Insensitive Queries , most of the ODBC   Developers written SELECT Queries using either Upper/Lower Case, I need to configure to work
for both the cases
0
Comment
Question by:tittu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
9 Comments
 
LVL 26

Expert Comment

by:ushastry
ID: 34185751
The SQL Keywords are case-insensitive (SELECT, FROM, WHERE, etc), but are often written in all caps. Case sensitivity of the underlying operating system plays a part in the case sensitivity of database and table names. This means database and table names are not case sensitive in Windows, and case sensitive in most varieties of Unix.    Mysql has a configuration option to enable/disable it. Usually case-sensitive table names are the default on Linux MySql and case-insensitive used to be the default on Windows...

Manual suggest - Although database, table, and trigger names are not case sensitive on some platforms, you should not refer to one of these using different cases within the same statement. The following statement would not work because it refers to a table both as my_table and as MY_TABLE.

mysql> SELECT * FROM my_table WHERE MY_TABLE.col=1;

Column, index, stored routine, and event names are not case sensitive on any platform, nor are column aliases.
However, names of logfile groups are case sensitive. This differs from standard SQL.
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;
However, this same statement is permitted on Windows. To avoid problems caused by such differences, it is best to adopt a consistent convention, such as always creating and referring to databases and tables using lowercase names. This convention is recommended for maximum portability and ease of use.

You can check it here for more

http://dev.mysql.com/doc/refman/5.1/en/identifier-case-sensitivity.html
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 34185755
Make sure the 'collation' on your tables and database to one of the 'ci' (case insensitive) variations for the contents.  Database and table names are case sensitive if the file system is.  Windows isn't, Unix/Linux is.  That is because those names are used as part of file names of the databases and tables.  http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html
0
 

Author Comment

by:tittu
ID: 34185934
I am using RHEL Linux 5 , please suggest me how to change to case insensitive
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 26

Expert Comment

by:ushastry
ID: 34185959
Hi,

Set the value of parameter lower_case_table_namets to 1 , If set to 1, table names are stored in lowercase on disk and comparisons are not case sensitive.

Please note that this parameter can't be set dynamically so you need to add this to conf file and thus require a restart of MySQL.

http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_lower_case_table_names


0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 34185964
I don't believe you can change the database and table names to case insensitive on Linux.  Those names are used as part of the filenames on that system.  Filenames are case sensitive on Linux/Unix.  If that's what you really need, you will have to move to a Windows server.
0
 
LVL 26

Expert Comment

by:ushastry
ID: 34185968
The changes would take place to only new tables created after setting the parameter and for old tables you need to modify to either small or cap
0
 
LVL 26

Accepted Solution

by:
ushastry earned 500 total points
ID: 34186025
Here is the TestCase proving what I commented earlier....


Platform: RHEL5, MySQL 5.0.91x

#### Assume that lower_case_table_names value os 0(default)

[root@nodea ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.91-enterprise-gpl MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 0     |
+------------------------+-------+
1 row in set (0.05 sec)

mysql> use test;create table Ushastry(id int not null);
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
Query OK, 0 rows affected (0.01 sec)

#####  We cannot refer it in other cases...

mysql> select * from ushastry;
ERROR 1146 (42S02): Table 'test.ushastry' doesn't exist
mysql> select * from USHASTRY;
ERROR 1146 (42S02): Table 'test.USHASTRY' doesn't exist
mysql> \q
Bye

##### Add the parameter to conf file lower_case_names=1

[root@nodea ~]# locate my.cnf
[root@nodea ~]# vi /etc/my.cnf

##### Restart MySQL server; to reflect the changes we made...

[root@nodea ~]# service mysql restart
Shutting down MySQL.                                       [  OK  ]
Starting MySQL.                                            [  OK  ]
[root@nodea ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.91-enterprise-gpl MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

########## Create a table by name UShastry and can be refered in any case....

mysql> create table UShastry(id int not null);
\Query OK, 0 rows affected (0.14 sec)

mysql> SELECT * from UShastry;
Empty set (0.00 sec)

mysql> SELECT * from ushastry;
Empty set (0.01 sec)

mysql> SELECT * from Ushastry;
Empty set (0.00 sec)

mysql> SELECT * from USHASTRY;
Empty set (0.08 sec)

mysql> insert into ushastry values(1),(2);
Query OK, 2 rows affected (0.11 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * from USHASTRY;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.01 sec)

mysql> SELECT * from Ushastry;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

mysql> SELECT * from ushastry;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.01 sec)

mysql>
0
 

Author Closing Comment

by:tittu
ID: 34195305
worked
0
 
LVL 26

Expert Comment

by:ushastry
ID: 34195387
Cool..

Thanks,
Umesh
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question