How to make MySQL case sensitive

[Gurumurthy Godlaveeti, geändert vor 11 Jahren.]

Hello Everybody ,

Operating System :- Ubuntu 10.3
Database Server :- MySQL
Tomcat :- 6.0.16
Liferay Version :- 5.1.2

I have been working on LR51.2 from past 6 months . I haven't got any issue with MySQL case but I prepared some master data (Insert queries ) manually last day . Those are working fine in my local system but when i pushed those into Production server , there i got the issue with master data because of Production server MySQL is case sensitive . At last i changed all queries with respect to Production server and i ran those .

But from next time , i don't want to see same so how can i make my local system MySQL into case sensitive and how it will reflect on existed tables also ?


Thanks .

[Hitesh Methani, geändert vor 11 Jahren.]

Hi Gurumurthy Godlaveeti,
Case sensitivity also depends on underlying OS.
Go through below link to have better idea.
http://www.roseindia.net/sql/mysql-example/mysql-case-sensitive.shtml

Thanks and Regards,
Hitesh

[Gurumurthy Godlaveeti, geändert vor 11 Jahren.]

Hi Hitesh Methani ,

MySQL case sensitive means the table names , index names are sensitive or not . It's not about the table data is case sensitive or not like what you said in last forum , The table data is always case sensitive but the table structure can be case sensitive or not sensitive . I give one example then you all can give more relative answer to me .

The table name in local machine is " organization_ " so i wrote the query like

          select * from organization_ ;

But the above query won't work in Production server because the table name is " Organization_ " so it is telling the table is not exist . So I don't want change the database ( lportal ) and the I would like to have case sensitive for my database .

Even i make it as case sensitive also , it should not create the new tables for existing tables and more ever it has to create tables in case sensitive for upcoming tables .
After i mentioned MySQL as case sensitive , I called those existed portlets from browser then i got issue with JDBC .

Table doesn't exist .

At last , i deployed portal again so it's created tables with case sensitive ( Duplicate tables ) so i don't want to see those duplicate tables in my database ( lportal ) , So is there any chance to make it ?

Thanks .

[Hitesh Methani, geändert vor 11 Jahren.]

Yes Gurumurthy, table names and indexes are case insensitive, what you can try is configuring linux mysql to be case insensitive.
Following link for changing mysql configurations in unix machine may help
http://stackoverflow.com/questions/999643/how-can-i-get-mysqldump-to-preserve-the-case-of-table-names

Regards,
Hitesh

[Gurumurthy Godlaveeti, geändert vor 11 Jahren.]

Hi Hitesh Methani ,

My Linux system is already in case insensitive and now i want to make to case sensitive by removing property

lower_case_table_names = 1 in /etc/mysql/my.cnf

But it's reflecting on existing tables .
After i mentioned MySQL as case sensitive , I called those existed portlets from browser then i got issue with JDBC .

Table doesn't exist .

At last , i deployed portal again so it's created tables with case sensitive ( Duplicate tables ) so i don't want to see those duplicate tables in my database ( lportal ) , So is there any chance to make it ?

Thanks .

[Hitoshi Ozawa, geändert vor 11 Jahren.]

This actually isn't liferay question but mysql question. It should have been better to post to the mysql forum instead of here.

That said, sSince you're using Ubutu, mysql can be set to be case sensitive. Just set the following property in my.cnf
[mysqld]
set-variable=lower_case_table_names=0

[Gurumurthy Godlaveeti, geändert vor 11 Jahren.]

Hello Hitoshi Ozawa ,
I can post this forum in mysql forums as you said but portal is creating new tables when i deploy with the first letter of each word in Capital letters . so i got duplicate tables so how can i restrict portal to create new tables for portal portlets after i mention

[mysqld] set-variable=lower_case_table_names=0

in my.cnf . For new tables , it should follow case sensitive .

[Hitoshi Ozawa, geändert vor 11 Jahren.]

If you're getting duplicate table names, you want to make table names case insensitive.
set-variable=lower_case_table_names=1

[Mudasar Kalwar, geändert vor 11 Jahren.]

Hitoshi Ozawa:

If you're getting duplicate table names, you want to make table names case insensitive.
set-variable=lower_case_table_names=1

Thanks Hitoshi it works with some modifications on FreeBSD 8.3.
1. get the backup of database using 'mysqldump --databases db1 > db1.sql'
2.Drop the database
3.There isn't any package like '/etc/mysql/my.cnf'
so I did is , copied the my-medium.cnf file from package ' /usr/local/share/mysql/my-medium.cnf' to '/etc/my.cnf' using following command.
# cp /usr/local/share/mysql/my-medium.cnf /etc/my.cnf
4, Stop the server, add lower_case_table_names property as
'[mysqld]
lower_case_table_names = 1'
5.Reload the dump file for each database. and start the server.

if any case you find the problem please reboot the system.

Thanks

[Hitoshi Ozawa, geändert vor 11 Jahren.]

Thanks for the information. I've re-read my replies and see that I've forgotten to mention to dump and reload the database. I actually faced the same problem on one of my environment too.