Solved

How to Configure MYSQL Server for case insensitive queries

Posted on 2010-11-21
9
1,389 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
  • 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
 
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

863 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now