Solved

Windows 7 MySQLServer  5.5 - How to set  lower_case_table_names mysqld setting

Posted on 2011-09-28
14
2,476 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 4
14 Comments
 
LVL 51

Accepted Solution

by:
Steve Bink earned 500 total points
ID: 36810902
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 51

Expert Comment

by:Steve Bink
ID: 36811551
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
ID: 36812892
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Expert Comment

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

Author Comment

by:Robert Silver
ID: 36818935
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
ID: 36818937
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
ID: 36819162
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
 
LVL 51

Expert Comment

by:Steve Bink
ID: 36820459
>>> 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 51

Expert Comment

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

Expert Comment

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

Author Comment

by:Robert Silver
ID: 36858731
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 51

Expert Comment

by:Steve Bink
ID: 36873970
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
ID: 36899397
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
ID: 36899443
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

Back Up Your Microsoft Windows Server®

Back up 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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
This Micro Tutorial will give you a basic overview of Windows Live Photo Gallery and show you various editing filters and touches to photos you can apply. This will be demonstrated using Windows Live Photo Gallery on Windows 7 operating system.
The Task Scheduler is a powerful tool that is built into Windows. It allows you to schedule tasks (actions) on a recurring basis, such as hourly, daily, weekly, monthly, at log on, at startup, on idle, etc. This video Micro Tutorial is a brief intro…

705 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