Solved

MySQL Table Names revert to LOWER CASE ?

Posted on 2004-08-23
7
896 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
  • 4
  • 2
7 Comments
 
LVL 26

Expert Comment

by:ushastry
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

773 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