Re: Table Name Case Sensitivity
On Mon, 2004-02-23 at 08:56, Tim Hayes wrote: > OK > > There is still the possibility of an in-compatability between the 2 > platforms. > > However - in both Linux and Windows (MySQL 4.0.17) the variable is not > recognized / updateable using the set command! > > > I get - Unknown system variable 'lower_case_table_names' > This is startup option. Did you use it as such (in my.cnf) ? -- Peter Zaitsev, Senior Support Engineer MySQL AB, www.mysql.com Meet the MySQL Team at User Conference 2004! (April 14-16, Orlando,FL) http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Name Case Sensitivity
At 16:56 + 2/23/04, Tim Hayes wrote: OK There is still the possibility of an in-compatability between the 2 platforms. However - in both Linux and Windows (MySQL 4.0.17) the variable is not recognized / updateable using the set command! Correct. You must set it using an option at server startup time, either on the command line or (more usually) in an option file. I get - Unknown system variable 'lower_case_table_names' Tim Hayes -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Name Case Sensitivity
Hi! Please observe the following about InnoDB table names on Windows: http://www.innodb.com/ibman.php#Moving " On Windows InnoDB stores the database names and table names internally always in lower case. To move databases in a binary format from Unix to Windows or from Windows to Unix you should have all table and database names in lower case. A convenient way to accomplish this is to add on Unix the line set-variable=lower_case_table_names=1 to the [mysqld] section of your my.cnf before you start creating your tables. On Windows set-variable=lower_case_table_names=1 is the default setting. " I think it is bad programming style to have tables whose name only differs in case. I recommend using in my.cnf lower_case_table_names=1 on all platforms. Regards, Heikki .. List: MySQL General Discussion From: Tim Hayes Date:February 23 2004 5:56pm Subject: Re: Table Name Case Sensitivity OK There is still the possibility of an in-compatability between the 2 platforms. However - in both Linux and Windows (MySQL 4.0.17) the variable is not recognized / updateable using the set command! I get - Unknown system variable 'lower_case_table_names' Tim Hayes - Original Message - From: "Peter Zaitsev" <[EMAIL PROTECTED]> To: "Tim Hayes" <[EMAIL PROTECTED]> Sent: Monday, February 23, 2004 4:19 PM Subject: Re: Table Name Case Sensitivity > On Mon, 2004-02-23 at 08:15, Tim Hayes wrote: > > Can anyone offer advice? > > Run with lower_case_table_names=1 > > > > > > > > I have come across a MySQL database on Linux with duplicate table names - "Accounts" and "accounts". This seems fine on Linux, but does not transfer to the Windows environment - it is rejected because of the duplicate name. However I do see that Column Names have to be unique in both environments. > > > > Q. Is there some sort of practical advantage for having case sensitive table names in Linux, or is this a design "shortcoming"? Postgress rejects duplicate table names. > > > > Thanks > > Tim Hayes > -- > Peter Zaitsev, Senior Support Engineer > MySQL AB, www.mysql.com > > Meet the MySQL Team at User Conference 2004! (April 14-16, Orlando,FL) > http://www.mysql.com/uc2004/ > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Name Case Sensitivity
OK There is still the possibility of an in-compatability between the 2 platforms. However - in both Linux and Windows (MySQL 4.0.17) the variable is not recognized / updateable using the set command! I get - Unknown system variable 'lower_case_table_names' Tim Hayes - Original Message - From: "Peter Zaitsev" <[EMAIL PROTECTED]> To: "Tim Hayes" <[EMAIL PROTECTED]> Sent: Monday, February 23, 2004 4:19 PM Subject: Re: Table Name Case Sensitivity > On Mon, 2004-02-23 at 08:15, Tim Hayes wrote: > > Can anyone offer advice? > > Run with lower_case_table_names=1 > > > > > > > > I have come across a MySQL database on Linux with duplicate table names - "Accounts" and "accounts". This seems fine on Linux, but does not transfer to the Windows environment - it is rejected because of the duplicate name. However I do see that Column Names have to be unique in both environments. > > > > Q. Is there some sort of practical advantage for having case sensitive table names in Linux, or is this a design "shortcoming"? Postgress rejects duplicate table names. > > > > Thanks > > Tim Hayes > -- > Peter Zaitsev, Senior Support Engineer > MySQL AB, www.mysql.com > > Meet the MySQL Team at User Conference 2004! (April 14-16, Orlando,FL) > http://www.mysql.com/uc2004/ > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Name Case Sensitivity
"Tim Hayes" <[EMAIL PROTECTED]> wrote on 23/02/2004 16:15:36: > Can anyone offer advice? > > I have come across a MySQL database on Linux with duplicate table > names - "Accounts" and "accounts". This seems fine on Linux, but > does not transfer to the Windows environment - it is rejected > because of the duplicate name. However I do see that Column Names > have to be unique in both environments. > > Q. Is there some sort of practical advantage for having case > sensitive table names in Linux, or is this a design "shortcoming"? > Postgress rejects duplicate table names. It is a design feature - shortcoming if you will. For MyISAM tables, each table is a file (well, three files). The table name therefore shares the case (in)sensitivity of the underlying OS. The same will apply to database names, because they are directories, but not to column names because they are concealed inside the data files. There is no right answer to the question of case sensitivity. Mostly it will depend from which direction you approach the problem. As someone who uses both Windows and Linux, I can put on a different mental hat and get a different "correct" answer each time I approach the question. Anyway, theoretical elegance is irrelevant: you have to live with the world as you find it. In practice, I have found the only solution that works is lower case table names only. Obviously, this is too late for you - you have "duplicate" tables already. I imagine the problem would not occur with InnoDB tables, which put all the tables into a single data space (perhaps someone could confirm). In which case you could solve the problem by changing table types. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Name Case Sensitivity
Tim Hayes said: > Can anyone offer advice? > > I have come across a MySQL database on Linux with duplicate table > names - "Accounts" and "accounts". This seems fine on Linux, but > does not transfer to the Windows environment - it is rejected > because of the duplicate name. However I do see that Column Names > have to be unique in both environments. > > Q. Is there some sort of practical advantage for having case > sensitive table names in Linux, or is this a design "shortcoming"? The SQL standard says that table names inside identifier quotes should be matched "as is" against schema objects. Table names outside identifier quotes should first be converted to uppercase and then matched to schema objects. According to the SQL standard, double quotes are identifier quotes. So I would qualify the current behaviour of MySQL as a design shortcoming. And the fact that it behaves differently on Windows and Unix does not improve the situation. > Postgress rejects duplicate table names. MySQL rejects duplicate table names just as PostgreSQL does. Table names that differ in case are not duplicates on all platforms. And for the record, PostgreSQL's behaviour is not exactly standard compliant either. Instead of converting unquoted identifiers to uppercase they convert them to lowercase for readability. But at least it behaves consistent between platforms and it uses identifier quotes correctly. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table Name Case Sensitivity
Can anyone offer advice? I have come across a MySQL database on Linux with duplicate table names - "Accounts" and "accounts". This seems fine on Linux, but does not transfer to the Windows environment - it is rejected because of the duplicate name. However I do see that Column Names have to be unique in both environments. Q. Is there some sort of practical advantage for having case sensitive table names in Linux, or is this a design "shortcoming"? Postgress rejects duplicate table names. Thanks Tim Hayes
MySQL table name case sensitivity
Since Mac OS X is Unix it should use the same rules as a Unix machine
when it comes to MySQL and case sensitivity of table and database
names. According to Dubois: "DB and tables in MySQL correspond to
directories and files in the underlying file system on the server
host. As a result, case sensitivity of db and table names depends on
the way the OS on the host treats filenames. A server running on UNIX
treats db and table names as case sensitive since UNIX filenames are
case sensitive."
However, I don't find this to be true on Mac OS X. In the following,
I show that I have a table "Menu" (with capital M) but am able to
insert or select using the lower case "menu". Is there any
explanation for this?
mysql> show tables;
+--+
| Tables_in_bPublished |
+--+
| Menu |
+--+
1 row in set (0.00 sec)
mysql> select * from menu;
6 rows in set (0.01 sec)
mysql> INSERT INTO menu VALUES ('',3,1,'Cat Stuff','Cat
Stuff','/images/icons/Cat.gif',2), ('',4,1,
'Zebra Stuff','Zebra Stuff','/images/icons/Zebra.gif',2);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 2
mysql> select * from Menu;
8 rows in set (0.00 sec)
--
Michael
__
||| Michael Collins |||
||| Kuwago Web Services ||| mailto:[EMAIL PROTECTED]
||| Seattle, WA, USA ||| http://www.lassodev.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
