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
Solved

How to Configure MYSQL Server for case insensitive queries

Posted on 2010-11-21
9
1,392 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Suggested Solutions

Title # Comments Views Activity
How to count in a table in php 22 43
change database name 2 36
mysql date time 14 38
Inserting data into database 10 34
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

808 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