Foren

Home » Liferay Portal » English » 3. Development

Kombinierte Ansicht Flache Ansicht Baumansicht
Threads [ Zurück | Nächste ]
toggle
Gurumurthy Godlaveeti
How to make MySQL case sensitive
22. Mai 2012 23:09
Antwort

Gurumurthy Godlaveeti

Rang: Regular Member

Nachrichten: 143

Eintrittsdatum: 11. August 2011

Neue Beiträge

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
RE: How to make MySQL case sensitive
22. Mai 2012 23:31
Antwort

Hitesh Methani

Rang: Regular Member

Nachrichten: 169

Eintrittsdatum: 24. Juni 2010

Neue Beiträge

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
RE: How to make MySQL case sensitive
23. Mai 2012 00:06
Antwort

Gurumurthy Godlaveeti

Rang: Regular Member

Nachrichten: 143

Eintrittsdatum: 11. August 2011

Neue Beiträge

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
1
2          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
RE: How to make MySQL case sensitive
23. Mai 2012 00:34
Antwort

Hitesh Methani

Rang: Regular Member

Nachrichten: 169

Eintrittsdatum: 24. Juni 2010

Neue Beiträge

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
RE: How to make MySQL case sensitive
23. Mai 2012 00:56
Antwort

Gurumurthy Godlaveeti

Rang: Regular Member

Nachrichten: 143

Eintrittsdatum: 11. August 2011

Neue Beiträge

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
RE: How to make MySQL case sensitive
23. Mai 2012 05:33
Antwort

Hitoshi Ozawa

Rang: Liferay Legend

Nachrichten: 7990

Eintrittsdatum: 23. März 2010

Neue Beiträge

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

set-variable=lower_case_table_names=0
Gurumurthy Godlaveeti
RE: How to make MySQL case sensitive
23. Mai 2012 05:43
Antwort

Gurumurthy Godlaveeti

Rang: Regular Member

Nachrichten: 143

Eintrittsdatum: 11. August 2011

Neue Beiträge

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
set-variable=lower_case_table_names=0
in my.cnf . For new tables , it should follow case sensitive .
Hitoshi Ozawa
RE: How to make MySQL case sensitive
23. Mai 2012 05:52
Antwort

Hitoshi Ozawa

Rang: Liferay Legend

Nachrichten: 7990

Eintrittsdatum: 23. März 2010

Neue Beiträge

If you're getting duplicate table names, you want to make table names case insensitive.
set-variable=lower_case_table_names=1
Mudasar Kalwar
RE: How to make MySQL case sensitive
20. November 2012 00:16
Antwort

Mudasar Kalwar

Rang: New Member

Nachrichten: 23

Eintrittsdatum: 13. September 2012

Neue Beiträge

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
'
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
RE: How to make MySQL case sensitive
20. November 2012 14:17
Antwort

Hitoshi Ozawa

Rang: Liferay Legend

Nachrichten: 7990

Eintrittsdatum: 23. März 2010

Neue Beiträge

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. emoticon