Windows 7 MySQLServer 5.5 - How to set lower_case_table_names mysqld setting

How do I set this value. I intend to solely use InnoDb so MyIsam corruption  is not an issue

I have tried adding the same statement that works on a windows 7 MySQL Server
 Dsitribution 5.1.48 but fails to work the same way on 5,5  distribution.
I tried adding the parameter to the startup:

Does anyone know other than:
C:\tmp\staffing>"C:\MySQL\MySQL Server 5.5\bin\mysqld" --lower_case_table_names=0 --defaults-file="C:\ProgramData\MySQL\Data\my.ini" MySQL55
110928  9:44:38 [Warning] You have forced lower_case_table_names to 0 through a command-line option, even though your file system 'C:\MySQL\MySQL Server 5.5\dat
a\' is case insensitive.  This means that you can corrupt a MyISAM table by accessing it with different cases. You should consider changing lower_case_table_nam
es to 1 or 2

Is there a way to get this working in 5.5 just for InnoDb Storage Engine tables??
I need to keep my table and column naming case sensitive whether on a Win 7 or Solaris machine

Does anyone have any ideas how to do this in Version 5.5 ???

Robert SilverSr. Software EngineerAsked:
Who is Participating?
Steve BinkCommented:
Based on the documentation:

If you are using InnoDB tables, you should set this variable to 1 on all platforms to force names to be converted to lowercase

Another part of the docs:

Use lower_case_table_names=0 on Unix and lower_case_table_names=2 on Windows. This preserves the lettercase of database and table names. The disadvantage of this is that you must ensure that your statements always refer to your database and table names with the correct lettercase on Windows. If you transfer your statements to Unix, where lettercase is significant, they do not work if the lettercase is incorrect.

Exception: 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.

Basically, you're asking for a big ol' headache by forcing case-sensitivity on a case-insensitive system.  IMO, you should not have object names that differ only in case anyways, so making everything lower-case should not be a big issue.
Steve BinkCommented:
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.
nemws1Database AdministratorCommented:
From the MySQL Manual:

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:

Open in new window

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

nemws1Database AdministratorCommented:
Derrp.. should have refreshed my 2hr old tab before posting!  My bad!
Robert SilverSr. Software EngineerAuthor Commented:
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???
Robert SilverSr. Software EngineerAuthor Commented:
note in the my.ini file I placed:

which failed to work. I really want it to work!!!
nemws1Database AdministratorCommented:
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.
Steve BinkCommented:
>>> 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?
Steve BinkCommented:
@nemws1:  That's twice!  Get out of my head!  :P
nemws1Database AdministratorCommented:
Haha, routinet!  Great minds think alike.  ;-)
Robert SilverSr. Software EngineerAuthor Commented:
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.
Steve BinkCommented:
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.
Robert SilverSr. Software EngineerAuthor Commented:
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.
Robert SilverSr. Software EngineerAuthor Commented:
Just to clearify the setting of lower_case_table_names=2
bridges both case insensitivity on on a windows or Mac OS UFS filesystem and
although it does provide a capability for keeping  cases in the names of tables and columns the behavior within MySQL does not allow two definitions of a table
ThisTestTable  and thistesttable are one and the same
So while I can use MySQL on windows and take the output mysqldump files created on a unix  or linux file system , MySql does not behave the same as it used to with lower_case_table_names=0
For my current purposes it will do though because when  I load schemas it keeps the letter case in the table and column names as it needs to.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.