Windows 7 MySQLServer 5.5 - How to set lower_case_table_names mysqld setting

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

FYI: MySQL is reporting a *warning*, which means the server is likely running with the options as you have set them. If it does appear to be working, that might just be a result of the underlying OS mechanics, and not an issue with MySQL.

From the MySQL Manual:

http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html

If you are using InnoDB tables and you are trying to avoid these data transfer problems, you should set lower_case_table_names to 1 on all platforms to force names to be converted to lowercase.

I take it you have *different* tables with the same name? If so, there's no way for InnoDB and MySQL to handle this.

You should change your table names so that there are no uppercase/lowercase collisions and set "lower_case_table_names" to 1 on all your platforms.

Furthermore, on Windows, you don't have to specify options like this on the command line. You should be able to put it into your config file (C:\ProgramData\MySQL\Data\my.ini). Look for the "[mysqld]" section. If you don't have one (or this file is empty), put this in it:

[mysqld]
lower_case_table_names=1

Open in new window

Derrp.. should have refreshed my 2hr old tab before posting! My bad!

ASKER

You guys just don't get it do you?
I want to have full compatibility between Win 7 and Unix mysql versions
I want mixed case table names that come with lower_case_table_names=0
I want that. How do I get it because when I do a show variables like '%lower_case%';
I see the value is set to 1 even when I try to set it to 0 in my.ini file.

I do not care about any answer that does not give me what I want here Namely
an ability to save mixed case table names and columns

Can someone please help me get this setting working please???

ASKER

note in the my.ini file I placed:
[mysqld]
lower_case_table_names=0

which failed to work. I really want it to work!!!

I'm sorry, but we do get it. What you're asking for is not only a horrible practice, but MySQL doesn't allow for the functionality you're looking for.

MySQL, in most aspects, is case in-sensitive (keywords, field names, table names, even search for strings with LIKE). This is a feature, not a bug.

You can set lower_case_table_names to 0 on UNIX, but on Windows or Mac, it doesn't (and won't) work.

I know you think it should work (I do, too!), especially since InnoDB uses it's own tablespace independent of the underlying operating system, but the setting is global and applies to all table engines.

There is no InnoDB-only setting for lower_case_table_names, only a global setting. I highly, HIGHLY, recommend you NOT use case sensitive table names and queries! If you don't, then all your cross-platform problems, regardless of engine, go away.

>>> You guys just don't get it do you?

I'm pretty sure we get it just fine...I'm inclined to believe you are missing the direction of our answers.

>>> I want to have full compatibility [...]
>>> I want mixed case table names [...]
>>> I really want it to work!!!

What you want is really not important or relevant. I want a lifetime of free Reese's cups delivered by rainbow unicorns. The point is that the underlying OS mechanics DO NOT ALLOW FOR YOUR REQUEST. If you really want Windows to be case-sensitive, then I suggest you get to coding a new OS. Let me know how that works out for you. If your goal truly is full compatibility, then you must use the lowest common denominator, which is all lower-case object names. If your goal is to use camel-case object names regardless of the underlying case-sensitivity, then you should set lower_case_table_names to 2, though that could still cause problems in your application.

As far as the variable not actually changing, it may be defined behavior. This bug report shows an excerpt of the source that automatically sets the variable appropriately on startup, regardless of INI/command-line parameters. This may or may not be the same in your version (the report was for v4), but it would not surprise me if it was. Data integrity should definitely outweigh user preference. Have you tried changing the variable after startup? What is the exact version of your server?

@nemws1: That's twice! Get out of my head! :P

Haha, routinet! Great minds think alike. ;-)

ASKER

Well the really stupid thing here is version 5.1 does this just fine. Yes there is the same problem with MyISam but really why wouldn't I want to be able to use InnoDb only with the setting? Why have
the setting if does not work.

The material I've seen suggests this is more of a compatibility-enabling setting than a user-choice setting, where 0=linux, 1=windows, and 2=pseudo-linux. Personally, I think it is a mistake for MySQL to allow differentiation by case only. There is no good reason to have MyTable, myTable, and mytable all in the same database, and a number of reasons to justify not allowing it at all. About 90% of my work is done on LAMP stacks where that type of difference is allowable, and I would never pursue that kind of naming strategy. The entire concept is just a development nightmare waiting to happen.

I'm not just giving this advice idly. I have already encountered bugs with mixed-case names for stored procedures in my distro's particular version. It is very annoying, and contrary to what I wanted to do, but there was no help for it unless I wanted to replace the entire package. In my case, it was even more frustrating because the revision before and after mine did not exhibit this issue...just my specific version. In the end, I did exactly what I'm recommending to you - I converted my SP names to lower-case and moved on.

As far as using camel-case on insensitive systems, I guess that is what the 2 value is for. Of course, this does not work on sensitive systems, so we're back to the LCD for cross-platform compatibility - converting everything to lower-case. Your only other option is to accept the difference between platforms and plan your installation procedures accordingly.

ASKER

I do not understand everything about this setting but it does allow me to dump mixed case table and column names which was what I was after.
I will read over the MySQL documentation to discover more about this setting.
This issue was just very frustrating because of past experience with version 5.1.
Your answer was first and foremost the right one
as a setting of lower_case_table_names=2 indeed solves that problem. as I have now verified.