[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

MySQL Table Names revert to LOWER CASE ?

Posted on 2004-08-23
7
Medium Priority
?
913 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
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

656 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