Bug #9386 Major headache on a simple thing
Submitted: 24 Mar 2005 17:43 Modified: 25 Mar 2005 16:04
Reporter: Robbie Mappin Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Administrator Severity:S2 (Serious)
Version:Latest OS:Microsoft Windows (WinXP)
Assigned to: CPU Architecture:Any

[24 Mar 2005 17:43] Robbie Mappin
Description:
Dear Support

You might not class this as a bug, but due to the headaches it is causing me I would say that it is a major flaw that needs looking at.

We created a schema of our MySQL DBs as a MySQL script file so that we could execute it like any other script via SQLyog.  In our schema files we had all of the table names in uppercase and designed our web sites so that all of the scripts queried the tables in uppercase as well.

We use your MySQL Administrator to backup our databases and this worked fine until we found out that when this program backs up the data to an SQL script file it makes all of the table names in lowercase.

We have no facility to say whether we want these in uppercase, lowercase or basically left alone!

This probably wouldn't be a problem if it wasn't for the fact that our ISP's MySQL system is case-sensitive and none of our web sites can access the tables because their servers now don't recognise our uppercase requests for the new lowercase table names.

Is there anything you can do to help?

How to repeat:
Please see above.

Suggested fix:
An option to choose whether they should be in a certain case would be nice, but the essential requirement is for you to leave the table names alone.  You leave the field names alone so why not the table names?

Any assistance you can give would be very much appreciated.

Regards

Robbie
[24 Mar 2005 20:05] Mike Hillyer
Dumps are made using the case of the table name in the DB. If you are indeed using Windows for your database server, then even your UPPERCASE tablenames will be lowercase:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.1.10a-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Database changed
mysql> CREATE TABLE ALL_UPPERCASE(ID INT);
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE ALL_UPPERCASE;
+---------------+-----------------------------------------------------
------------------------------------+
| Table         | Create Table
                                    |
+---------------+-----------------------------------------------------
------------------------------------+
| ALL_UPPERCASE | CREATE TABLE `all_uppercase` (
  `ID` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------------+-----------------------------------------------------
------------------------------------+
1 row in set (0.05 sec)

If you want your tables stored in the same case as they were defined on windows, you need to look at the lower_case_table_names option documented at http://dev.mysql.com/doc/mysql/en/name-case-sensitivity.html

Can you use SHOW CREATE TABLE to confirm that your table names were indeed UPPER CASE on the server?
[29 Mar 2005 10:42] Robbie Mappin
Hi Mike

The results are as follows:

CREATE TABLE `accesslog` (
  `LOGID` int(11) NOT NULL auto_increment,
  `LOGINID` varchar(30) default NULL,
  PRIMARY KEY  (`LOGID`)
) TYPE=MyISAM

As you guessed, the tables in my WinXP v4.0.18-nt installation are in lcase.

Having read the web page that you kindly emailed me, I'm still not sure which option I should use and how to implement it.

For your ref, I use v4.0.18-nt on a WinXP machine and my ISP appears to be using MySQL 4.x on Unix/Linux Servers.

All I want to do is make sure that the databases that I create/restore locally are in Uppercase so that when I create or backup/restore these same DBs on my ISP's servers they will be compatible.

Could you please give me a few quick pointers on how to implement this so that I can leave you alone !!! :0)

Thanks

Robbie
[1 Apr 2005 9:32] Robbie Mappin
Hi Support

Look I know it has been classed as a setting change, but can you please just give me a few pointers so that this query is closed.

Thanks

Robbie