Solved

Windows 7 MySQLServer  5.5 - How to set  lower_case_table_names mysqld setting

Posted on 2011-09-28
14
2,318 Views
Last Modified: 2012-05-12
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
I tried adding the parameter to the startup:
--lower_case_table_names=0

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 ???

0
Comment
Question by:Robert Silver
  • 5
  • 5
  • 4
14 Comments
 
LVL 50

Accepted Solution

by:
Steve Bink earned 500 total points
Comment Utility
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.
0
 
LVL 50

Expert Comment

by:Steve Bink
Comment Utility
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.
0
 
LVL 23

Expert Comment

by:nemws1
Comment Utility
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

0
 
LVL 23

Expert Comment

by:nemws1
Comment Utility
Derrp.. should have refreshed my 2hr old tab before posting!  My bad!
0
 
LVL 2

Author Comment

by:Robert Silver
Comment Utility
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???
0
 
LVL 2

Author Comment

by:Robert Silver
Comment Utility
note in the my.ini file I placed:
[mysqld]
lower_case_table_names=0

which failed to work. I really want it to work!!!
0
 
LVL 23

Expert Comment

by:nemws1
Comment Utility
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.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 50

Expert Comment

by:Steve Bink
Comment Utility
>>> 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?
0
 
LVL 50

Expert Comment

by:Steve Bink
Comment Utility
@nemws1:  That's twice!  Get out of my head!  :P
0
 
LVL 23

Expert Comment

by:nemws1
Comment Utility
Haha, routinet!  Great minds think alike.  ;-)
0
 
LVL 2

Author Comment

by:Robert Silver
Comment Utility
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.
0
 
LVL 50

Expert Comment

by:Steve Bink
Comment Utility
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.
0
 
LVL 2

Author Closing Comment

by:Robert Silver
Comment Utility
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.
0
 
LVL 2

Author Comment

by:Robert Silver
Comment Utility
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.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
When you start your Windows 10 PC and got an "Operating system not found" error or just saw  "Auto repair for startup". After a while, you have entered a loop for Auto repair which does not fix anything and you will be in a  panic as all your work w…
This Micro Tutorial will teach you how to the overview of Microsoft Security Essentials. This is a free anti-virus software that guards your PC against viruses, spyware, worms, and other malicious software. This will be demonstrated using Windows…
The viewer will learn how to successfully download and install the SARDU utility on Windows 7, without downloading adware.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now