MySql Question about Tables case sensitivity

Problems with the Windows version of XAMPP, questions, comments, and anything related.

Postby rr1024 » 29. April 2008 16:47

Well I ran that command this time exactly and it worked....lol

but it states it's set to 1 and not 2 per the my.cnf

Code: Select all
# The MySQL server
[mysqld]
port= 3306
socket= "/xampp/mysql/mysql.sock"
basedir="/xampp/mysql"
tmpdir="/xampp/tmp"
datadir="/xampp/mysql/data"
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
lower_case_table_names = 2


This probably explains my problem this morning when testing and getting lower case names....

So why doesn't it pickup the lower_case_table_names = 2 in my cnf file?

Is there some other file that needs the same setting?
rr1024
 
Posts: 35
Joined: 11. March 2008 04:28

Postby Wiedmann » 29. April 2008 16:53

Is there some other file that needs the same setting?

Maybe you are editing the wrong "my.cnf"?
Wiedmann
AF Moderator
 
Posts: 17106
Joined: 01. February 2004 12:38
Location: Stuttgart / Germany

Postby Nobbie » 29. April 2008 19:05

I think the answer to all questions is ... that windows does not support upper/lower case sensitive table names etc.

Did you ever read the documentation? See http://dev.mysql.com/doc/refman/5.1/en/ ... ivity.html for detailed information, you will find this note:

This means database, table, and trigger names are not case sensitive in Windows, but are case sensitive in most varieties of Unix


Obviously MySQL therefore ignores the setting of lower_case_table_names on Windows OS.
Nobbie
 
Posts: 8772
Joined: 09. March 2008 13:04

Postby Wiedmann » 29. April 2008 19:10

Nobbie wrote:Obviously MySQL therefore ignores the setting of lower_case_table_names on Windows OS.

No, he must just edit the correct my.ini/my.cnf and restart MySQL.
Wiedmann
AF Moderator
 
Posts: 17106
Joined: 01. February 2004 12:38
Location: Stuttgart / Germany

Postby Nobbie » 29. April 2008 19:12

And what about the MySQL Documentation which clearly states, that case sensitive table names are not supported on Windows
Nobbie
 
Posts: 8772
Joined: 09. March 2008 13:04

Postby Wiedmann » 29. April 2008 19:21

And what about the MySQL Documentation which clearly states, that case sensitive table names are not supported on Windows

That's one of the reason for the system variable lower_case_table_names.

(Of course, you can't have a table 'a' and a table 'B' ibn the same db with case insensitive filesystems.)
Wiedmann
AF Moderator
 
Posts: 17106
Joined: 01. February 2004 12:38
Location: Stuttgart / Germany

Postby Nobbie » 29. April 2008 21:38

Ich versuchs mal auf Deutsch, irgendwie reden wir sonst aneinander vorbei:

Unter Windows und MyISAM ist die Tabelle blabla physikalisch identisch mit BLABLA und auch mit BlaBla usw. - völlig egal wie Konfiguration von lower_case_table_names ist. Und das (scheint mir) hier das Problem des Fragestellers zu sein - man kann in Windows nicht zwei physikalisch verschiedene Tabellen blabla und BLABLA anlegen.

Unter Linux ginge das natürlich und offensichtlich ist das wohl geschehen. Das Problem, diese Datenbank unter Windows und MyISAM zu portieren ist nicht lösbar, auch nicht mit lower_case_table_names. Man hätte wohl damit schon unter Linux ein Verhalten wie unter Windows simulieren können, um dieses Problem zu vermeiden. Aber jetzt hilft leider gar nichts - außer alle Tabellennamen zu ändern.
Nobbie
 
Posts: 8772
Joined: 09. March 2008 13:04

Postby rr1024 » 30. April 2008 07:24

I did a search for my cnf file an the only one I found was
D:\xampp\mysql\bin\my.cnf

The contents of this one is
Code: Select all
[mysqld]
port= 3306
socket= "/xampp/mysql/mysql.sock"
basedir="/xampp/mysql"
tmpdir="/xampp/tmp"
datadir="/xampp/mysql/data"
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
lower_case_table_names = 2


I'm a bit confused because it worked i.e. I imported a DB once and apparently it was set correctly because all the table names did come in per the sql dump file i.e. with Case Sensitivity. This is when I was trying to do a command line lower_case_table_names change I think.

I'm sure I'm doing something wrong, just not sure what it is.

So I know it will work once I do something right because it worked once, just figuring out what I did and how to make it perminate seems ellude me at the moment.

I take it that the file I've been editing is the correct file since it appears to be the only one? Unless I'm missing something else?
rr1024
 
Posts: 35
Joined: 11. March 2008 04:28

Postby rr1024 » 30. April 2008 07:49

Ok I think I stumbled on it...but I can't be totally sure about this but here it goes.


I notice my sql was running as a service which I don't normally do so I stop my sql and started it normally, i.e. not a service


So I change the name of my.cnf to mysql_load.txt and edited the mysql start batch file
mysql\bin\mysql_load.txt

This also worked with case sensitivity.

So I changed everything back to my.cnf and tested again, this also worked.
rr1024
 
Posts: 35
Joined: 11. March 2008 04:28

Postby rr1024 » 30. April 2008 07:49

Ok I think I stumbled on it...but I can't be totally sure about this but here it goes.


I notice my sql was running as a service which I don't normally do so I stop my sql and started it normally, i.e. not a service


So I change the name of my.cnf to mysql_load.txt and edited the mysql start batch file
mysql\bin\mysql_load.txt

This also worked with case sensitivity.

So I changed everything back to my.cnf and tested again, this also worked.
rr1024
 
Posts: 35
Joined: 11. March 2008 04:28

The Solution

Postby László Balaton » 05. May 2008 23:42

Hello!

I had the same problem and finally found the solution.

You have to put the "lower_case_table_names" line to the beginning of the config file, just below "[mysqld]". Do not ask why, it is just working:

Code: Select all
[mysqld]
lower_case_table_names = 2
port= 3306
...


Regards,

László
László Balaton
 
Posts: 1
Joined: 05. May 2008 23:40

Previous

Return to XAMPP for Windows

Who is online

Users browsing this forum: No registered users and 64 guests