?
Solved

MySQL Table Names revert to LOWER CASE ?

Posted on 2004-08-23
7
Medium Priority
?
909 Views
Last Modified: 2012-06-21
Hi,

I currently administer our MySQL db version 3.23.56 on a LINUX Cobalt Qube server via an external fron-end piece of software.
My Table Names have capital letters at the beginning, i.e. StockMovements, OrderLines.....

We are now implementing a cross-over to a Windows Server 2003 machine.
I have installed MySQL version 4.0.20a and initially it was stated as 4.0.20a-debug. I managed to find another piece of software to take it out of debug mode as I thought this would help with the table names.
The version is now 4.0.20a-max.

If I rename the imported tables say from orderlines to OrderLines, it stays, but if I refresh or close/re-open my front-end, all the table names have reverted back to lower case ?

I have lots of .asp apps which are currently working of my Linux MySQL db using Capitals in the table names and fields. Obviously I do not wish to start re-coding all my web pages.

Please can anyone shed some light on this issue ?

Thanks

David
0
Comment
Question by:scene
[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
  • 4
  • 2
7 Comments
 
LVL 26

Expert Comment

by:Umesh
ID: 11872043
Hi,

Use this command to rename mysql tables.

RENAME TABLE oldName TO newName

http://dev.mysql.com/doc/mysql/en/RENAME_TABLE.html

Hope this helps!
0
 
LVL 7

Expert Comment

by:petoskey-001
ID: 11872509
In windows, there is no Case Sensitive options.

If you do "show variables like '%case%' you should get...
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 1     |
+------------------------+-------+

You can rename the tables, but it shouldn't matter.  Since your MySQL server is not case sensitive (on windows) you shouldn't have to recode your web apps.  They will just default to the lowercase versions.  Let me know if you end up getting errors.  My guess is it will work just fine as is.
0
 
LVL 7

Expert Comment

by:petoskey-001
ID: 11872538
Oh, the reason is that the table name case doesn't matter is the name of the table is taken from a file search (FRM, MYD, MYI) in the database directory.  So unless you have two tables like "orderlines" and "OrderLines", you won't have any problems.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:scene
ID: 11872602
Thanks for your posts.
IOn my current Linux version I have the variable

 lower_case_table_names = 0 (Not 1)

Should I not find a way to change the variable in the windows version to also show 0 ?
I have tried adding a line to the my.ini file, but this seems to make no difference ?

David
0
 
LVL 7

Expert Comment

by:petoskey-001
ID: 11872711
Well windows itself is not case sensitive.  So telling MySql to try to be case sensitive when the underlying operating system isn't doesn't work.  MySQL is really simple, and table names are taken from file names.

This is also the reason why it won't affect your scripts.  On Linux your select statements had to be case sensitive.  On Windows they don't have to be.  So "select * from orderlines" and "select * from OrderLines" will both work.  Try the MySQL commaind line client connected to your windows db server.  You should see that both work with no errors.
0
 
LVL 7

Accepted Solution

by:
petoskey-001 earned 375 total points
ID: 11930271
Hello scene.  It's been about a week.  Have you had a chance to test things out?  You should find it all works for you regardless of case on Windows.  The lower_case_table_names is more to make thing on Linux work the way they do on Windows the the other way around.
0
 

Author Comment

by:scene
ID: 11939667
Hi,

Yes you are quite right.....my scripts do work.
Thank you all for you prompt responses.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

765 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