MySql Question about Tables case sensitivity

[rr1024]

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?

[Wiedmann]

Is there some other file that needs the same setting?

Maybe you are editing the wrong "my.cnf"?

[Nobbie]

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.

[Wiedmann]

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.

[Nobbie]

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

[Wiedmann]

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

[Nobbie]

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.

[rr1024]

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]

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]

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.

[László Balaton]

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ó