Re: Case sensitivity
- Original Message - From: Reindl Harald h.rei...@thelounge.net Subject: Re: Case sensitivity ALTER TABLE `my_table` DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci; Purely from memory, doesn't that change the table but add the old setting to individual text columns? I seem to recall running into this. -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Case sensitivity
On 05/21/2014 08:14 AM, Johan De Meersman wrote: ALTER TABLE `my_table` DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci; Purely from memory, doesn't that change the table but add the old setting to individual text columns? right, the above will only change the default for new columns added later, but existing columns will keep their current character set and collation. ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name [COLLATE collation_name] is needed to change the default *and* all already existing columns -- Hartmut Holzgraefe, Principal Support Engineer (EMEA) SkySQL - The MariaDB Company | http://www.skysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Case sensitivity
Am 21.05.2014 10:50, schrieb Hartmut Holzgraefe: On 05/21/2014 08:14 AM, Johan De Meersman wrote: ALTER TABLE `my_table` DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci; Purely from memory, doesn't that change the table but add the old setting to individual text columns? right, the above will only change the default for new columns added later, but existing columns will keep their current character set and collation. ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name [COLLATE collation_name] is needed to change the default *and* all already existing columns ah - i remember writing a script doing that for 5000 tables on the whole infrastructure as charset/collation support started to get rid of the ridicolous latin1_swedish_ci preset signature.asc Description: OpenPGP digital signature
Re: Case sensitivity
On Wed, May 21, 2014 at 4:50 AM, Hartmut Holzgraefe hart...@skysql.com wrote: On 05/21/2014 08:14 AM, Johan De Meersman wrote: ALTER TABLE `my_table` DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci; Purely from memory, doesn't that change the table but add the old setting to individual text columns? right, the above will only change the default for new columns added later, but existing columns will keep their current character set and collation. ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name [COLLATE collation_name] is needed to change the default *and* all already existing columns Thanks for all the help Hartmut. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Case sensitivity
I have 2 servers, one is running 5.5.25a and the other 5.0.77. They both have the same collation and character set: mysql SELECT COLLATION(VERSION()); +--+ | COLLATION(VERSION()) | +--+ | utf8_general_ci | +--+ 1 row in set (0.00 sec) mysql show variables like '%_server'; +--+--+ | Variable_name| Value| +--+--+ | character_set_server | utf8 | | collation_server | utf8_bin | +--+--+ 2 rows in set (0.00 sec) Yet the 5.5.25a server is case sensitive as you would expect, but the 5.0.77 is not. How can I make the 5.0.77 server case sensitive? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Case sensitivity
Am 20.05.2014 15:26, schrieb Larry Martell: I have 2 servers, one is running 5.5.25a and the other 5.0.77. They both have the same collation and character set: mysql SELECT COLLATION(VERSION()); +--+ | COLLATION(VERSION()) | +--+ | utf8_general_ci | +--+ 1 row in set (0.00 sec) mysql show variables like '%_server'; +--+--+ | Variable_name| Value| +--+--+ | character_set_server | utf8 | | collation_server | utf8_bin | +--+--+ 2 rows in set (0.00 sec) Yet the 5.5.25a server is case sensitive as you would expect, but the 5.0.77 is not. How can I make the 5.0.77 server case sensitive? and what collation have your tables and databases? what collation is the client *really* using? set collation_connection='utf8_general_ci'; what is in the servers environment variables and in the cli-client don't say that much about applications and in doubt your application should *always* set charset/collation signature.asc Description: OpenPGP digital signature
Re: Case sensitivity
On 05/20/2014 03:26 PM, Larry Martell wrote: Yet the 5.5.25a server is case sensitive as you would expect, but the 5.0.77 is not. How can I make the 5.0.77 server case sensitive? please provide the output of SHOW VARIABLES LIKE 'character_set_%'; and SHOW VARIABLES LIKE 'collation_%'; and explain what exactly you mean by is case sensitive. Are you referring to database and table names being case sensitive here, or to comparisons (=, LIKE) on textual values? Seeing a few example queries and the different results returned by 5.0 and 5.5 would sure help, too ... -- Hartmut Holzgraefe, Principal Support Engineer (EMEA) SkySQL - The MariaDB Company | http://www.skysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Case sensitivity
On Tue, May 20, 2014 at 9:38 AM, Hartmut Holzgraefe hart...@skysql.com wrote: On 05/20/2014 03:26 PM, Larry Martell wrote: Yet the 5.5.25a server is case sensitive as you would expect, but the 5.0.77 is not. How can I make the 5.0.77 server case sensitive? please provide the output of SHOW VARIABLES LIKE 'character_set_%'; and SHOW VARIABLES LIKE 'collation_%'; Here it is from the server that is case sensitive: mysql show variables like 'character_set_%'; +--++ | Variable_name| Value | +--++ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results| latin1 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /export/home/mysql/share/charsets/ | +--++ 8 rows in set (0.00 sec) mysql show variables like 'collation_%'; +--+---+ | Variable_name| Value | +--+---+ | collation_connection | latin1_swedish_ci | | collation_database | utf8_bin | | collation_server | utf8_bin | +--+---+ 3 rows in set (0.00 sec) And here is it from the server that is not case sensitive: mysql show variables like 'character_set_%'; +--++ | Variable_name| Value | +--++ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results| latin1 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--++ 8 rows in set (0.00 sec) mysql show variables like 'collation_%'; +--+---+ | Variable_name| Value | +--+---+ | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | utf8_bin | +--+---+ 3 rows in set (0.00 sec) Both servers have this in their config files, with no other character set or collation settings: character_set_server=utf8 collation_server=utf8_bin Clearly they're different, but I don't know how to get them to be the same. and explain what exactly you mean by is case sensitive. Are you referring to database and table names being case sensitive here, or to comparisons (=, LIKE) on textual values? I mean when querying data. Seeing a few example queries and the different results returned by 5.0 and 5.5 would sure help, too ... 5.5 server: mysql select distinct(pnlCurrency) from PnLDetail; +-+ | pnlCurrency | +-+ | USD | | USd | +-+ 2 rows in set (0.00 sec) 5.0 server: mysql select distinct(pnlCurrency) from PnLDetail; +-+ | pnlCurrency | +-+ | USD | +-+ 1 row in set (0.01 sec) Both have the same data in them. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Case sensitivity
On 05/20/2014 11:10 PM, Larry Martell wrote: 5.5 server: mysql select distinct(pnlCurrency) from PnLDetail; +-+ | pnlCurrency | +-+ | USD | | USd | +-+ 2 rows in set (0.00 sec) 5.0 server: mysql select distinct(pnlCurrency) from PnLDetail; +-+ | pnlCurrency | +-+ | USD | +-+ 1 row in set (0.01 sec) Both have the same data in them. ok, in this case the interesting piece of information would be the SHOW CREATE TABLE pnlCurrency; output from both. As the collation_database variables differ between the two servers (utf8_bin vs. latin1_swedish_ci) I assume that the same is the case for the actual tables, and that would perfectly explain why one treads lower/upper case as distinct while the other doesn't -- Hartmut Holzgraefe, Principal Support Engineer (EMEA) SkySQL - The MariaDB Company | http://www.skysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Case sensitivity
On Tue, May 20, 2014 at 5:52 PM, Hartmut Holzgraefe hart...@skysql.com wrote: On 05/20/2014 11:10 PM, Larry Martell wrote: 5.5 server: mysql select distinct(pnlCurrency) from PnLDetail; +-+ | pnlCurrency | +-+ | USD | | USd | +-+ 2 rows in set (0.00 sec) 5.0 server: mysql select distinct(pnlCurrency) from PnLDetail; +-+ | pnlCurrency | +-+ | USD | +-+ 1 row in set (0.01 sec) Both have the same data in them. ok, in this case the interesting piece of information would be the SHOW CREATE TABLE pnlCurrency; output from both. As the collation_database variables differ between the two servers (utf8_bin vs. latin1_swedish_ci) I assume that the same is the case for the actual tables, and that would perfectly explain why one treads lower/upper case as distinct while the other doesn't The create table on 5.5 has: DEFAULT CHARSET=uft8 COLLATE=uft8_bin and on the 5.0 server it has: DEFAULT CHARSET=latin1 But the tables on both servers were created from DDL files that do not specify either one of these. So are these inherited from the server settings at the time the tables are created? how do I fix this? Specify DEFAULT CHARSET and COLLATE on the CREATE TABLE statements in the DDL file and drop and recreate all the tables? Is there a way fo change this without doing that? Can the collation_database variables be set in the conf file? I didn't see them mentioned in the docs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Case sensitivity
Am 21.05.2014 00:48, schrieb Larry Martell: ok, in this case the interesting piece of information would be the SHOW CREATE TABLE pnlCurrency; output from both. As the collation_database variables differ between the two servers (utf8_bin vs. latin1_swedish_ci) I assume that the same is the case for the actual tables, and that would perfectly explain why one treads lower/upper case as distinct while the other doesn't The create table on 5.5 has: DEFAULT CHARSET=uft8 COLLATE=uft8_bin and on the 5.0 server it has: DEFAULT CHARSET=latin1 But the tables on both servers were created from DDL files that do not specify either one of these. So are these inherited from the server settings at the time the tables are created? how do I fix this? they are inherited from the *database* get phpMyAdmin, you can learn a lot of it outputs and the generated queries of most actions Specify DEFAULT CHARSET and COLLATE on the CREATE TABLE statements in the DDL file and drop and recreate all the tables? Is there a way fo change this without doing that? Can the collation_database variables be set in the conf file? I didn't see them mentioned in the docs DDL files which don't specify charset / collation are broken because whoever writes them has no idea of the default settings surely: http://dev.mysql.com/doc/refman/5.0/en/alter-table.html ALTER TABLE `my_table` DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci; signature.asc Description: OpenPGP digital signature
Getting mySQL to ignore case sensitivity of field names
Hi, Google isn't my friend :-( How can I tell mySQL 5.0 to ignore the case of field names i.e. FullName should also be able to be referenced as fullname,fullNAME etc ? I'm running it on a linux box at home but my copy at work (running on Windows 2000 server) has this by default - I certainly set any options. When I Google all I seem to get are hits about ignoring case in select query values not field names. Many thanks Adrian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Getting mySQL to ignore case sensitivity of field names
http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html You need to set the field format to a non binary one, and case insensitive will be the default. On Aug 27, 2009, at 2:57 PM, Adrian Aitken wrote: Hi, Google isn't my friend :-( How can I tell mySQL 5.0 to ignore the case of field names i.e. FullName should also be able to be referenced as fullname,fullNAME etc ? I'm running it on a linux box at home but my copy at work (running on Windows 2000 server) has this by default - I certainly set any options. When I Google all I seem to get are hits about ignoring case in select query values not field names. -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Getting mySQL to ignore case sensitivity of field names
Hi Scott, it's not the values I have a problem with, it's the fieldnames themselves. As an example the mysql.user table has 'Host' but when I do an update setting 'host' to a value it fails to update. I have to enter 'Host'. The mysql.com link seems to only talk about field values. Regards Adrian - Original Message - From: Scott Haneda To: Adrian Aitken Cc: mysql@lists.mysql.com Sent: Thursday, August 27, 2009 11:04 PM Subject: Re: Getting mySQL to ignore case sensitivity of field names http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html You need to set the field format to a non binary one, and case insensitive will be the default. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Getting mySQL to ignore case sensitivity of field names
Oops, sorry about that. My understanding is this is OS dependent: http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html You may be able to set lowercase tables names, but would always have to use lowercase. I would just stick to entering in the correct case, as the other methods seem prone to trouble. On Aug 27, 2009, at 3:04 PM, Scott Haneda wrote: http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html You need to set the field format to a non binary one, and case insensitive will be the default. On Aug 27, 2009, at 2:57 PM, Adrian Aitken wrote: Hi, Google isn't my friend :-( How can I tell mySQL 5.0 to ignore the case of field names i.e. FullName should also be able to be referenced as fullname,fullNAME etc ? I'm running it on a linux box at home but my copy at work (running on Windows 2000 server) has this by default - I certainly set any options. When I Google all I seem to get are hits about ignoring case in select query values not field names. -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Getting mySQL to ignore case sensitivity of field names
On Aug 27, 2009, at 5:11 PM, Adrian Aitken wrote: Hi Scott, it's not the values I have a problem with, it's the fieldnames themselves. As an example the mysql.user table has 'Host' but when I do an update setting 'host' to a value it fails to update. I have to enter 'Host'. The mysql.com link seems to only talk about field values. That should not happen. Column names are not case sensitive in MySQL. http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html: Column, index, and stored routine names are not case sensitive on any platform, nor are column aliases. Can we see your query? Regards Adrian - Original Message - From: Scott Haneda To: Adrian Aitken Cc: mysql@lists.mysql.com Sent: Thursday, August 27, 2009 11:04 PM Subject: Re: Getting mySQL to ignore case sensitivity of field names http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html You need to set the field format to a non binary one, and case insensitive will be the default. -- Paul DuBois Sun Microsystems / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
case sensitivity in stored procedure formal arguments
Could someone explain the logic of how case sensivity works on stored procedure formal argument names? Example: CREATE PROCEDURE sp_Test ( IN col1 INT, IN col2 INT ) BEGIN INSERT INTO Table SET COL1 = col1, COL2 = col2; END We've found that this is problematic in some cases because of the case insensitivity of column names, so I'm looking to see if there's a general logic for how to name formal arguments that have a similar name to a table column name (we ended up putting a '_' in the beginning of the argument name). thanks! -lev -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Case sensitivity problem (I think) on OS X
I'm running MySQL 5.0.18 on OS X 10.4.5, on an intel MacBook. My table definition is: CREATE TABLE `UNIT` ( `UNIT_ID` int(11) NOT NULL auto_increment, `UNIT_CODE` varchar(50) default NULL, `UNIT_DESC` varchar(50) default NULL, `USER_ID` int(11) NOT NULL, `USER_DT` datetime NOT NULL, `RESEARCH_SITE_ID` int(11) NOT NULL, PRIMARY KEY (`UNIT_ID`), UNIQUE KEY `UNIT_CODE` (`UNIT_CODE`), KEY `RESEARCH_SITE_ID` (`RESEARCH_SITE_ID`), KEY `USER_ID` (`USER_ID`), CONSTRAINT `unit_ibfk_2` FOREIGN KEY (`USER_ID`) REFERENCES `users` (`USER_ID`), CONSTRAINT `unit_ibfk_1` FOREIGN KEY (`RESEARCH_SITE_ID`) REFERENCES `research_site` (`RESEARCH_SITE_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 When I try inserting I am getting an error relating to the `UNIT_CODE` UNIQUE KEY. See below: mysql INSERT INTO UNIT(UNIT_ID, UNIT_CODE, UNIT_DESC, USER_ID, USER_DT, RESEARCH_SITE_ID) - VALUES(9, 'g/dL', NULL, 1, '2005-03-22 08:01:07 AM', 0); Query OK, 1 row affected, 1 warning (0.00 sec) This works fine. mysql INSERT INTO UNIT(UNIT_ID, UNIT_CODE, UNIT_DESC, USER_ID, USER_DT, RESEARCH_SITE_ID) - VALUES(10, 'g/dl', NULL, 1, '2005-03-22 08:01:07 AM', 0); ERROR 1062 (23000): Duplicate entry 'g/dl' for key 2 This fails however. 'g/dl' != 'g/dL' though. I don't get why a string field is being treated this way. I understand the HFS+ filesystem is not case sensitive but that shouldn't be an issued with varchar fields should it? -- Sterling Anderson email/aim: sterling_anderson [at] mac.com http://sterlinganderson.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Case sensitivity problem (I think) on OS X
At 15:22 -0600 3/16/06, Sterling Anderson wrote: I'm running MySQL 5.0.18 on OS X 10.4.5, on an intel MacBook. My table definition is: CREATE TABLE `UNIT` ( `UNIT_ID` int(11) NOT NULL auto_increment, `UNIT_CODE` varchar(50) default NULL, `UNIT_DESC` varchar(50) default NULL, `USER_ID` int(11) NOT NULL, `USER_DT` datetime NOT NULL, `RESEARCH_SITE_ID` int(11) NOT NULL, PRIMARY KEY (`UNIT_ID`), UNIQUE KEY `UNIT_CODE` (`UNIT_CODE`), KEY `RESEARCH_SITE_ID` (`RESEARCH_SITE_ID`), KEY `USER_ID` (`USER_ID`), CONSTRAINT `unit_ibfk_2` FOREIGN KEY (`USER_ID`) REFERENCES `users` (`USER_ID`), CONSTRAINT `unit_ibfk_1` FOREIGN KEY (`RESEARCH_SITE_ID`) REFERENCES `research_site` (`RESEARCH_SITE_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 When I try inserting I am getting an error relating to the `UNIT_CODE` UNIQUE KEY. See below: mysql INSERT INTO UNIT(UNIT_ID, UNIT_CODE, UNIT_DESC, USER_ID, USER_DT, RESEARCH_SITE_ID) - VALUES(9, 'g/dL', NULL, 1, '2005-03-22 08:01:07 AM', 0); Query OK, 1 row affected, 1 warning (0.00 sec) This works fine. mysql INSERT INTO UNIT(UNIT_ID, UNIT_CODE, UNIT_DESC, USER_ID, USER_DT, RESEARCH_SITE_ID) - VALUES(10, 'g/dl', NULL, 1, '2005-03-22 08:01:07 AM', 0); ERROR 1062 (23000): Duplicate entry 'g/dl' for key 2 This fails however. 'g/dl' != 'g/dL' though. I don't get why a string field is being treated this way. I understand the HFS+ filesystem is not case sensitive but that shouldn't be an issued with varchar fields should it? No, it's not a filesystem issue. It's a character set issue. The column has a character set of utf8, and the default collation (which determines the comparison and sorting characteristics is utf8_general_ci: mysql show collation like 'utf8%'; ++-+-+-+--+-+ | Collation | Charset | Id | Default | Compiled | Sortlen | ++-+-+-+--+-+ | utf8_general_ci| utf8| 33 | Yes | Yes | 1 | | utf8_bin | utf8| 83 | | Yes | 1 | | utf8_unicode_ci| utf8| 192 | | Yes | 8 | | utf8_icelandic_ci | utf8| 193 | | Yes | 8 | | utf8_latvian_ci| utf8| 194 | | Yes | 8 | | utf8_romanian_ci | utf8| 195 | | Yes | 8 | | utf8_slovenian_ci | utf8| 196 | | Yes | 8 | | utf8_polish_ci | utf8| 197 | | Yes | 8 | | utf8_estonian_ci | utf8| 198 | | Yes | 8 | | utf8_spanish_ci| utf8| 199 | | Yes | 8 | | utf8_swedish_ci| utf8| 200 | | Yes | 8 | | utf8_turkish_ci| utf8| 201 | | Yes | 8 | | utf8_czech_ci | utf8| 202 | | Yes | 8 | | utf8_danish_ci | utf8| 203 | | Yes | 8 | | utf8_lithuanian_ci | utf8| 204 | | Yes | 8 | | utf8_slovak_ci | utf8| 205 | | Yes | 8 | | utf8_spanish2_ci | utf8| 206 | | Yes | 8 | | utf8_roman_ci | utf8| 207 | | Yes | 8 | | utf8_persian_ci| utf8| 208 | | Yes | 8 | | utf8_esperanto_ci | utf8| 209 | | Yes | 8 | | utf8_hungarian_ci | utf8| 210 | | Yes | 8 | ++-+-+-+--+-+ ci means case insensitive and that's why you're seeing the results that you do. You could use the utf8_bin collation for this column if you want comparisons to be based on binary character values (which will, in effect, give you case sensitive behavior). -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Case sensitivity problem (I think) on OS X
On Mar 16, 2006, at 3:49 PM, Paul DuBois wrote: This fails however. 'g/dl' != 'g/dL' though. I don't get why a string field is being treated this way. I understand the HFS+ filesystem is not case sensitive but that shouldn't be an issued with varchar fields should it? No, it's not a filesystem issue. It's a character set issue. The column has a character set of utf8, and the default collation (which determines the comparison and sorting characteristics is utf8_general_ci: mysql show collation like 'utf8%'; ++-+-+-+--+-+ | Collation | Charset | Id | Default | Compiled | Sortlen | ++-+-+-+--+-+ | utf8_general_ci| utf8| 33 | Yes | Yes | 1 | | utf8_bin | utf8| 83 | | Yes | 1 | ... ci means case insensitive and that's why you're seeing the results that you do. You could use the utf8_bin collation for this column if you want comparisons to be based on binary character values (which will, in effect, give you case sensitive behavior). So, it sounds like it may be more logical for me to use utf8_bin universally or perhaps a different character set like latin1? I'm curious because we contracted someone at DoIT (the UW) to migrate an Oracle DB to MySQL, and they chose to use utf8. Now I'm having this problem. I almost contacted you directly. Not sure if you recognize my name, I'm with WNPRC. -- Sterling Anderson email/aim: sterling_anderson [at] mac.com http://sterlinganderson.net/ 608.577.7208 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Case sensitivity problem (I think) on OS X
At 16:01 -0600 3/16/06, Sterling Anderson wrote: On Mar 16, 2006, at 3:49 PM, Paul DuBois wrote: This fails however. 'g/dl' != 'g/dL' though. I don't get why a string field is being treated this way. I understand the HFS+ filesystem is not case sensitive but that shouldn't be an issued with varchar fields should it? No, it's not a filesystem issue. It's a character set issue. The column has a character set of utf8, and the default collation (which determines the comparison and sorting characteristics is utf8_general_ci: mysql show collation like 'utf8%'; ++-+-+-+--+-+ | Collation | Charset | Id | Default | Compiled | Sortlen | ++-+-+-+--+-+ | utf8_general_ci| utf8| 33 | Yes | Yes | 1 | | utf8_bin | utf8| 83 | | Yes | 1 | ... ci means case insensitive and that's why you're seeing the results that you do. You could use the utf8_bin collation for this column if you want comparisons to be based on binary character values (which will, in effect, give you case sensitive behavior). So, it sounds like it may be more logical for me to use utf8_bin universally or perhaps a different character set like latin1? I'm curious because we contracted someone at DoIT (the UW) to migrate an Oracle DB to MySQL, and they chose to use utf8. Now I'm having this problem. The character set is not the issue. The collation is. That's what determines the comparison and sorting properties. DoIT's choice of utf8 is neutral, and it makes sense that they chose to use the default case-insensitive collation unless you specified in advance that you require case sensitive comparisons. Changing to latin1 won't in itself make any difference because its default collation is case insensitive, too. You do have the choice of using an explicitly case-sensitive collation, though: latin1_general_cs -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
case sensitivity
I am running MySQL 5.0.15-nt on Windows 2000 PRO and use PHP as
front-end for word search in Bible. My goal is to set an option for
case sensitivity in text search. For example, if I want to search for
the word Jehovah* I would expect Jehovah not JEHOVAH. This
query works for my objective:
SELECT
bo.book,
b.chapter,
b.verse,
b.text
FROM
avkjv.books bo
LEFT JOIN
avkjv.bible b ON b.book=bo.number
WHERE
text
COLLATE
latin1_general_cs
LIKE
CONCAT('%','Jehovah','%');
But if I want to turn case sensitivity off I tried to issue the query:
SELECT
bo.book,
b.chapter,
b.verse,
b.text
FROM
avkjv.books bo
LEFT JOIN
avkjv.bible b
ON
b.book=bo.number
WHERE
text
LIKE
CONCAT('%','Jehovah','%');
Without COLLATE function, and the result acted as if it is still case
sensitive. I tried to look up in mysql to no avail. How can I turn
'case sensitivity' off?
Thanks in advance,
Scott
--
Please avoid sending me Word or PowerPoint attachments.
See http://www.gnu.org/philosophy/no-word-attachments.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: case sensitivity
Instead of using COLLATE you can try using the BINARY option
instead. It will work in older versions of MySQL and I think it makes
you select a bit more readable. Just put BINARY before the
comparison you want to be case sensitive.
WHERE BINARY text LIKE '%Jehovah%'
On Nov 8, 2005, at 9:15 AM, Scott Hamm wrote:
I am running MySQL 5.0.15-nt on Windows 2000 PRO and use PHP as
front-end for word search in Bible. My goal is to set an option for
case sensitivity in text search. For example, if I want to search for
the word Jehovah* I would expect Jehovah not JEHOVAH. This
query works for my objective:
SELECT
bo.book,
b.chapter,
b.verse,
b.text
FROM
avkjv.books bo
LEFT JOIN
avkjv.bible b ON b.book=bo.number
WHERE
text
COLLATE
latin1_general_cs
LIKE
CONCAT('%','Jehovah','%');
But if I want to turn case sensitivity off I tried to issue the query:
SELECT
bo.book,
b.chapter,
b.verse,
b.text
FROM
avkjv.books bo
LEFT JOIN
avkjv.bible b
ON
b.book=bo.number
WHERE
text
LIKE
CONCAT('%','Jehovah','%');
Without COLLATE function, and the result acted as if it is still case
sensitive. I tried to look up in mysql to no avail. How can I turn
'case sensitivity' off?
Thanks in advance,
Scott
--
Please avoid sending me Word or PowerPoint attachments.
See http://www.gnu.org/philosophy/no-word-attachments.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?
[EMAIL PROTECTED]
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: case sensitivity
Can this also be used? (just now figured out a way)
SELECT
bo.book,
b.chapter,
b.verse,
b.text
FROM
avkjv.books bo
LEFT JOIN
avkjv.bible b
ON
b.book=bo.number
WHERE
LCASE(text)
LIKE
CONCAT('%',LCASE('$srch'),'%');
On 11/8/05, Brent Baisley [EMAIL PROTECTED] wrote:
Instead of using COLLATE you can try using the BINARY option
instead. It will work in older versions of MySQL and I think it makes
you select a bit more readable. Just put BINARY before the
comparison you want to be case sensitive.
WHERE BINARY text LIKE '%Jehovah%'
On Nov 8, 2005, at 9:15 AM, Scott Hamm wrote:
I am running MySQL 5.0.15-nt on Windows 2000 PRO and use PHP as
front-end for word search in Bible. My goal is to set an option for
case sensitivity in text search. For example, if I want to search for
the word Jehovah* I would expect Jehovah not JEHOVAH. This
query works for my objective:
SELECT
bo.book,
b.chapter,
b.verse,
b.text
FROM
avkjv.books bo
LEFT JOIN
avkjv.bible b ON b.book=bo.number
WHERE
text
COLLATE
latin1_general_cs
LIKE
CONCAT('%','Jehovah','%');
But if I want to turn case sensitivity off I tried to issue the query:
SELECT
bo.book,
b.chapter,
b.verse,
b.text
FROM
avkjv.books bo
LEFT JOIN
avkjv.bible b
ON
b.book=bo.number
WHERE
text
LIKE
CONCAT('%','Jehovah','%');
Without COLLATE function, and the result acted as if it is still case
sensitive. I tried to look up in mysql to no avail. How can I turn
'case sensitivity' off?
Thanks in advance,
Scott
--
Please avoid sending me Word or PowerPoint attachments.
See http://www.gnu.org/philosophy/no-word-attachments.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?
[EMAIL PROTECTED]
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
Please avoid sending me Word or PowerPoint attachments.
See http://www.gnu.org/philosophy/no-word-attachments.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Forcing case sensitivity in queries
Hi, When I execute a query in MySQL client, it seems like I cannot get the case-sensitivity to be activated. If I ask: SELECT * FROM mytable WHERE field = 'd'; I get all records containing 'd' or 'D'. How do I enable case-sensitivity? Thanks, Yannick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Forcing case sensitivity in queries
You need BINARY http://dev.mysql.com/doc/mysql/en/Cast_Functions.html. SELECT * FROM mytable WHERE BINARY field = 'd'; Michael Yannick Warnier wrote: Hi, When I execute a query in MySQL client, it seems like I cannot get the case-sensitivity to be activated. If I ask: SELECT * FROM mytable WHERE field = 'd'; I get all records containing 'd' or 'D'. How do I enable case-sensitivity? Thanks, Yannick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Forcing case sensitivity in queries
Le dim 05/09/2004 à 00:14, Yannick Warnier a écrit : Hi, When I execute a query in MySQL client, it seems like I cannot get the case-sensitivity to be activated. If I ask: SELECT * FROM mytable WHERE field = 'd'; I get all records containing 'd' or 'D'. How do I enable case-sensitivity? OK sorry, I found that using 'binary' solves the problem. SELECT * FROM mytable WHERE field = binary 'd'; -- 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
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]
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
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
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
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
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]
case sensitivity of table names
I am using case sensitive table names when I create tables like : CREATE TABLE MyTest; If I want to do a select from this table, I have to do SELECT * FROM MyTest, not SELECT * FROM mytest. How can I make it so that the table name is still MyTest but selects work with mytest ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: case sensitivity of table names
At 12:22 -0500 12/17/03, Mayuran Yogarajah wrote: I am using case sensitive table names when I create tables like : CREATE TABLE MyTest; If I want to do a select from this table, I have to do SELECT * FROM MyTest, not SELECT * FROM mytest. How can I make it so that the table name is still MyTest but selects work with mytest ? You can set the lower_case_table_names server variable to 1. Then table names will not be treated as case sensitive, and you can write them in any lettercase in your queries. Two points to note, though: - Before setting the varable, rename all your tables to lowercase. Otherwise they won't be recognized properly when you set the variable. (The way it works is that if causes the server to lowercase the names of new tables when they are created.) - Although you will be able to refer to tables using any lettercase, *within a given query*, you must refer to the table consistently. See http://www.mysql.com/doc/en/Name_case_sensitivity.html for an example. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: case sensitivity of table names
Paul DuBois wrote: At 12:22 -0500 12/17/03, Mayuran Yogarajah wrote: I am using case sensitive table names when I create tables like : CREATE TABLE MyTest; If I want to do a select from this table, I have to do SELECT * FROM MyTest, not SELECT * FROM mytest. How can I make it so that the table name is still MyTest but selects work with mytest ? You can set the lower_case_table_names server variable to 1. Then table names will not be treated as case sensitive, and you can write them in any lettercase in your queries. Two points to note, though: - Before setting the varable, rename all your tables to lowercase. Otherwise they won't be recognized properly when you set the variable. (The way it works is that if causes the server to lowercase the names of new tables when they are created.) - Although you will be able to refer to tables using any lettercase, *within a given query*, you must refer to the table consistently. See http://www.mysql.com/doc/en/Name_case_sensitivity.html for an example. Is it possible to change the variable lower_case_table_names from mysql commandline? I tried to change it by doing this : mysql SET lower_case_table_names=1; and got the error : ERROR 1193: Unknown system variable 'lower_case_table_names' Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: case sensitivity of table names
At 13:00 -0500 12/17/03, Mayuran Yogarajah wrote: Paul DuBois wrote: At 12:22 -0500 12/17/03, Mayuran Yogarajah wrote: I am using case sensitive table names when I create tables like : CREATE TABLE MyTest; If I want to do a select from this table, I have to do SELECT * FROM MyTest, not SELECT * FROM mytest. How can I make it so that the table name is still MyTest but selects work with mytest ? You can set the lower_case_table_names server variable to 1. Then table names will not be treated as case sensitive, and you can write them in any lettercase in your queries. Two points to note, though: - Before setting the varable, rename all your tables to lowercase. Otherwise they won't be recognized properly when you set the variable. (The way it works is that if causes the server to lowercase the names of new tables when they are created.) - Although you will be able to refer to tables using any lettercase, *within a given query*, you must refer to the table consistently. See http://www.mysql.com/doc/en/Name_case_sensitivity.html for an example. Is it possible to change the variable lower_case_table_names from mysql commandline? I tried to change it by doing this : mysql SET lower_case_table_names=1; and got the error : ERROR 1193: Unknown system variable 'lower_case_table_names' No, it must be set at startup time. Besides, if you set it with a SET statement, the value would be lost the next time you start the server. Best to put it in an option file so that it's used each time the server starts up: [mysqld] lower_case_table_names=1 -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
case sensitivity in a delete query
hi i have a delete query that will delete a record based on the company name: delete from members where company='$company'; the query works fine and all but there is a problem because if there is more than 1 company listed but with different use of case in their name then allrecords that match $company are deleted.. so: BLACK black Black BlacK and so on are all considered the same entry by delete and will wipe them all even if all i wanted to do was to delete BLACK.. so was just wondering how to fix that if it is even possible.. i fear that the same problem will happen with my update statements also and want them changed too because if BLACK needs to be updated because the phone number changes then i dont need BlacK to inherrit the same phone number anybody know how to fix? tnx --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.458 / Virus Database: 257 - Release Date: 2/24/2003 - 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
Re: case sensitivity in a delete query
At 17:51 -0500 3/2/03, Sunfire wrote: hi i have a delete query that will delete a record based on the company name: delete from members where company='$company'; the query works fine and all but there is a problem because if there is more than 1 company listed but with different use of case in their name then allrecords that match $company are deleted.. so: BLACK black Black BlacK and so on are all considered the same entry by delete and will wipe them all even if all i wanted to do was to delete BLACK.. so was just wondering how to fix that if it is even possible.. i fear that the same problem will happen with my update statements also and want them changed too because if BLACK needs to be updated because the phone number changes then i dont need BlacK to inherrit the same phone number anybody know how to fix? If your column is CHAR, VARCHAR, or TEXT, use CHAR BINARY, VARCHAR BINARY, or BLOB instead. Or just change the query to ... where company = BINARY '$company'. Comparisons of binary strings are case sensitive. Best to read the sections in the manual that describe the string columns to get an idea of how they work. It'll help you avoid problems like this in the future. - 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
Case sensitivity
Hi, I have been searching tables based on search values which turn up tobe fine, however the results dont reflect case sensitivity. what i mean, if i run following query select field1 from table1 where name='text1'; this works fine but returns the same result set with text1 values of 'USER' or 'user'. i need to run the query which should run case sensitive. ie USER is different from user Thanks in advance who can throw some ligth Adamji - 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
RE: Case sensitivity
You have two choices, you can use a binary datatype for the field, or force a binary comparison using the BINARY operator. http://www.mysql.com/doc/en/Case_Sensitivity_Operators.html http://www.mysql.com/doc/en/Column_types.html query, sql Mike -Original Message- From: asp52 [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 19, 2002 9:06 AM To: [EMAIL PROTECTED] Subject: Case sensitivity Hi, I have been searching tables based on search values which turn up tobe fine, however the results dont reflect case sensitivity. what i mean, if i run following query select field1 from table1 where name='text1'; this works fine but returns the same result set with text1 values of 'USER' or 'user'. i need to run the query which should run case sensitive. ie USER is different from user Thanks in advance who can throw some ligth Adamji - 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 - 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
Re: Case sensitivity
Thanks, Just worked it out with binary statement in my query. all fine. Would it be wise to use binary type table? didnt try that as i wanted a flexible table of type varchar to perserve space. what would be its impact? cheers. Adamji From: Mike Grabski [EMAIL PROTECTED] To: 'asp52' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, December 19, 2002 2:29 PM Subject: RE: Case sensitivity You have two choices, you can use a binary datatype for the field, or force a binary comparison using the BINARY operator. http://www.mysql.com/doc/en/Case_Sensitivity_Operators.html http://www.mysql.com/doc/en/Column_types.html query, sql Mike -Original Message- From: asp52 [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 19, 2002 9:06 AM To: [EMAIL PROTECTED] Subject: Case sensitivity Hi, I have been searching tables based on search values which turn up tobe fine, however the results dont reflect case sensitivity. what i mean, if i run following query select field1 from table1 where name='text1'; this works fine but returns the same result set with text1 values of 'USER' or 'user'. i need to run the query which should run case sensitive. ie USER is different from user Thanks in advance who can throw some ligth Adamji - 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 - 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
Re: Case sensitivity
See http://www.mysql.com/doc/en/CHAR.html or http://www.mysql.com/doc/en/BLOB.html . If your column types are CHAR or VARCHAR insert BINARY. If your column type is TEXT, change to BLOB. -- You said Hi, I have been searching tables based on search values which turn up tobe fine, however the results dont reflect case sensitivity. what i mean, if i run following query select field1 from table1 where name='text1'; this works fine but returns the same result set with text1 values of 'USER' or 'user'. i need to run the query which should run case sensitive. ie USER is different from user Thanks in advance who can throw some ligth Adamji - 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
RE: Case sensitivity
You can specify a varchar field as binary, for instance: field1 varchar(50) binary query, sql Mike -Original Message- From: asp52 [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 19, 2002 9:50 AM To: Mike Grabski; [EMAIL PROTECTED] Subject: Re: Case sensitivity Thanks, Just worked it out with binary statement in my query. all fine. Would it be wise to use binary type table? didnt try that as i wanted a flexible table of type varchar to perserve space. what would be its impact? cheers. Adamji From: Mike Grabski [EMAIL PROTECTED] To: 'asp52' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, December 19, 2002 2:29 PM Subject: RE: Case sensitivity You have two choices, you can use a binary datatype for the field, or force a binary comparison using the BINARY operator. http://www.mysql.com/doc/en/Case_Sensitivity_Operators.html http://www.mysql.com/doc/en/Column_types.html query, sql Mike -Original Message- From: asp52 [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 19, 2002 9:06 AM To: [EMAIL PROTECTED] Subject: Case sensitivity Hi, I have been searching tables based on search values which turn up tobe fine, however the results dont reflect case sensitivity. what i mean, if i run following query select field1 from table1 where name='text1'; this works fine but returns the same result set with text1 values of 'USER' or 'user'. i need to run the query which should run case sensitive. ie USER is different from user Thanks in advance who can throw some ligth Adamji - 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 - 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
Case Sensitivity for CHECK TABLE TABLENAME??
Mac OS X mysql 3.23.47 I have a database with a table named: tname. After several inserts, I run CHECK TABLE tname; No errors are reported. I then run CHECK TABLE TNAME; And errors are reported! Which is it? Errors or no errors? Thanks Mark - 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
Re: Case Sensitivity for CHECK TABLE TABLENAME??
Mark: Do you have my.ini (or my.cfg) set to ignore case in table names? set-variable = lower_case_table_names=1 Gerald Jensen - Original Message - From: Mark Weiler [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, June 24, 2002 5:56 PM Subject: Case Sensitivity for CHECK TABLE TABLENAME?? Mac OS X mysql 3.23.47 I have a database with a table named: tname. After several inserts, I run CHECK TABLE tname; No errors are reported. I then run CHECK TABLE TNAME; And errors are reported! Which is it? Errors or no errors? Thanks Mark - 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 - 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
case-sensitivity broken in FK clause for MySQL+InnoDB
Description: When using MySQL with InnoDB table type, and turning on the table case-insensitivity flag in the config file (set-variable = lower_case_table_names=1), MySQL/InnoDB does not correctly handle the case-insensitivity of the target table name in the REFERENCES part of the FOREIGN KEY clause. This is true for both the CREATE TABLE and ALTER TABLE variations. Note the column names and the referer table name (in the case of the ALTER TABLE clause) are correctly treated as case insensitive. This problem may need to be posted to InnoDB, but I couldn't locate any method of notifying them of bug reports. Example script which shows the cases that work and the one that doesn't is attached in the How-To-Repeat. precise description of the problem (multiple lines) How-To-Repeat: drop table if exists foo1; create table foo1( pk1 integer NOT NULL, primary key(pk1) ) type = innodb; ## ## This will work fine ## drop table if exists foo2; create table foo2( pk1 integer NOT NULL, fk1 integer, foreign key (fk1) references foo1 (pk1), index ix1 (fk1) ) type = innodb; ## ## As will this ## drop table if exists foo2; create table foo2( pk1 integer NOT NULL, fk1 integer, foreign key (FK1) references foo1 (PK1), index ix1 (fk1) ) type = innodb; ## ## But this won't ## In my case, the specific error was: ##ERROR 1005: Can't create table './dbname/foo2.frm' (errno: 150) ## drop table if exists foo2; create table foo2( pk1 integer NOT NULL, fk1 integer, foreign key (fk1) references FOO1 (pk1), index ix1 (fk1) ) type = innodb; Fix: how to correct or work around the problem, if known (multiple lines) Submitter-Id: submitter ID Originator:Eugene Bekker Organization: organization of PR author (multiple lines) MySQL support: none Synopsis: case-sensitivity broken in FK clause for MySQL+InnoDB Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-3.23.50 (Official MySQL RPM) Environment: RedHat 7.1 Using pre-packaged 3.23.50 (Max) from www.mysql.com machine, os, target, libraries (multiple lines) System: Linux betelgeuse.powervision.com 2.4.2-2 #1 Sun Apr 8 20:41:30 EDT 2001 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-81) Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mcpu=pentiumpro' CXX='gcc' CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mcpu=pentiumpro' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Feb 2 07:17 /lib/libc.so.6 - libc-2.2.2.so -rwxr-xr-x1 root root 1236396 Apr 6 2001 /lib/libc-2.2.2.so -rw-r--r--1 root root 26350254 Apr 6 2001 /usr/lib/libc.a -rw-r--r--1 root root 178 Apr 6 2001 /usr/lib/libc.so Configure command: ./configure --disable-shared --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --with-other-libc=/usr/local/mysql-glibc --without-berkeley-db --without-innodb --enable-assembler --enable-local-infile --with-mysqld-user=mysql --with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/ --with-extra-charsets=complex --exec-prefix=/usr --libexecdir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share --localstatedir=/var/lib/mysql --infodir=/usr/info --includedir=/usr/include --mandir=/usr/man '--with-comment=Official MySQL RPM' - 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 mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Forcing case sensitivity via a query?
If I understand correctly, you have to define a char field as binary if you want the database to treat it as a 'case sensitive' field. Is there any way via a SQL query to force case sensitivity to be used for a non-binary char field? i.e.: for comparing strings in a case sensitive way. Or is there some way to force the database to use case sensitive string comparisons all the time (even for non-binary char fields)? I'm using the mm.mysql jdbc driver to access the database and it returns the data differently if a field is defined as binary. This is causing my string data to be garbled in my application for binary char fields. Rich Rich Bolen Senior Software Developer GretagMacbeth Advanced Technologies Center 79 T. W. Alexander Drive - Bldg. 4401 - Suite 250 PO Box 14026 Research Triangle Park, North Carolina 27709-4026 USA Phone: 919-549-7575 x239, Fax: 919-549-0421 http://www.gretagmacbeth.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
Re: Forcing case sensitivity via a query?
Hi Richard, Richard Bolen wrote: If I understand correctly, you have to define a char field as binary if you want the database to treat it as a 'case sensitive' field. Is there any way via a SQL query to force case sensitivity to be used for a non-binary char field? i.e.: for comparing strings in a case sensitive way. Or is there some way to force the database to use case sensitive string comparisons all the time (even for non-binary char fields)? Yes, try this: select * from yourtable where binary field1= 'CASEsensitive'; I'm using the mm.mysql jdbc driver to access the database and it returns the data differently if a field is defined as binary. This is causing my string data to be garbled in my application for binary char fields. Rich Rich Bolen Senior Software Developer GretagMacbeth Advanced Technologies Center 79 T. W. Alexander Drive - Bldg. 4401 - Suite 250 PO Box 14026 Research Triangle Park, North Carolina 27709-4026 USA Phone: 919-549-7575 x239, Fax: 919-549-0421 http://www.gretagmacbeth.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 -- -- \|/ __\|/ `@ / o . \ @' Microsoft? Por acaso é alguma nova /___| \/ |___\ marca de papel higiênico? \___U__/ .^. Luciano Barcaro - Linux User # 99517 /v\ Registre-se gratuitamente em // \\http://counter.li.org //(.)\\ ICQ # 17266954 ^`~'^ -- - 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
Re: case-sensitivity broken in FK clause for MySQL+InnoDB
Hi! The fact that foreign key definitions do not honor lower_case_table_names=1 is mentioned in the manual. I have added the fix to this on the TODO list for July 2002. You have found the right place, this mailing list is the right place to report bugs in InnoDB. Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com - Original Message - From: [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Wednesday, May 22, 2002 9:52 PM Subject: case-sensitivity broken in FK clause for MySQL+InnoDB Description: When using MySQL with InnoDB table type, and turning on the table case-insensitivity flag in the config file (set-variable = lower_case_table_names=1), MySQL/InnoDB does not correctly handle the case-insensitivity of the target table name in the REFERENCES part of the FOREIGN KEY clause. This is true for both the CREATE TABLE and ALTER TABLE variations. Note the column names and the referer table name (in the case of the ALTER TABLE clause) are correctly treated as case insensitive. This problem may need to be posted to InnoDB, but I couldn't locate any method of notifying them of bug reports. Example script which shows the cases that work and the one that doesn't is attached in the How-To-Repeat. precise description of the problem (multiple lines) How-To-Repeat: drop table if exists foo1; create table foo1( pk1 integer NOT NULL, primary key(pk1) ) type = innodb; ## ## This will work fine ## drop table if exists foo2; create table foo2( pk1 integer NOT NULL, fk1 integer, foreign key (fk1) references foo1 (pk1), index ix1 (fk1) ) type = innodb; ## ## As will this ## drop table if exists foo2; create table foo2( pk1 integer NOT NULL, fk1 integer, foreign key (FK1) references foo1 (PK1), index ix1 (fk1) ) type = innodb; ## ## But this won't ## In my case, the specific error was: ##ERROR 1005: Can't create table './dbname/foo2.frm' (errno: 150) ## drop table if exists foo2; create table foo2( pk1 integer NOT NULL, fk1 integer, foreign key (fk1) references FOO1 (pk1), index ix1 (fk1) ) type = innodb; Fix: how to correct or work around the problem, if known (multiple lines) Submitter-Id: submitter ID Originator: Eugene Bekker Organization: organization of PR author (multiple lines) MySQL support: none Synopsis: case-sensitivity broken in FK clause for MySQL+InnoDB Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-3.23.50 (Official MySQL RPM) Environment: RedHat 7.1 Using pre-packaged 3.23.50 (Max) from www.mysql.com machine, os, target, libraries (multiple lines) System: Linux betelgeuse.powervision.com 2.4.2-2 #1 Sun Apr 8 20:41:30 EDT 2001 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-81) Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mcpu=pentiumpro' CXX='gcc' CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors -fno-ex ceptions -fno-rtti -mcpu=pentiumpro' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Feb 2 07:17 /lib/libc.so.6 - libc-2.2.2.so -rwxr-xr-x1 root root 1236396 Apr 6 2001 /lib/libc-2.2.2.so -rw-r--r--1 root root 26350254 Apr 6 2001 /usr/lib/libc.a -rw-r--r--1 root root 178 Apr 6 2001 /usr/lib/libc.so Configure command: ./configure --disable-shared --with-mysqld-ldflags=-all-static --with-clien t-ldflags=-all-static --with-other-libc=/usr/local/mysql-glibc --without-ber keley-db --without-innodb --enable-assembler --enable-local-infile --with-my sqld-user=mysql --with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/ --with-extra-charsets=complex --exec-prefix=/usr --libexecdir=/usr/sbin --s ysconfdir=/etc --datadir=/usr/share --localstatedir=/var/lib/mysql --infodir =/usr/info --includedir=/usr/include --mandir=/usr/man '--with-comment=Official MySQL RPM' - 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 mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - 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
Re: Forcing case sensitivity via a query?
On 22 May 2002, at 14:52, Richard Bolen wrote: Is there any way via a SQL query to force case sensitivity to be used for a non-binary char field? i.e.: for comparing strings in a case sensitive way. SELECT * FROM users WHERE BINARY password = 'cAsE_MAttErS'; http://www.mysql.com/doc/C/a/Case_Sensitivity_Operators.html -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org - 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
Re: MySQL unix vs WinXP case sensitivity
At 11:03 3/4/2002 -0500, Eugene Mah wrote: Hi! Microsoft Windows 2000 [Versão 5.00.2195] (C) Copyright 1985-1999 Microsoft Corp. C:\c:\mysql\bin\mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 3.23.49-max-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql use test; Database changed mysql show variables like lower%; ++---+ | Variable_name | Value | ++---+ | lower_case_table_names | 0 | ++---+ 1 row in set (0.02 sec) mysql create table MyTesT (id int); Query OK, 0 rows affected (0.00 sec) mysql show tables; ++ | Tables_in_test | ++ | MyTesT | ++ 1 row in set (0.00 sec) mysql On the above sample running on Win2K I had set the variable: set-variable= lower_case_table_names = 0 on my.ini file. However, pay attention that the above only works for Win2K and maybe on XP and NT and for read is case insensitive. Regards, Miguel I seem to be having issues with the way MySQL names the table files under Unix and WinXP. I'm not sure if it's a mysql issue or a windows issue. Normally, when I create tables in MySQL, I like to capitalize the first letter. Under unix, it all works fine and dandy. But under WinXP, the table name gets converted to all lowercase, which consequently messes up my php files when I try to go back and forth between unix and windows. I suppose I could switch to all lower case, but old habits die hard and I sometimes forget when I'm typing away some PHP code. The MySQL I installed is from the download page at mysql.com. anybody else run into this issue? Eugene -- - Eugene Mah, M.Sc., DABR [EMAIL PROTECTED] Medical Physicist/Misplaced Canuck[EMAIL PROTECTED] Department of Radiology For I am a Bear of Very Little Medical University of South Carolina Brain, and long words Bother Charleston, South Carolina me. Winnie the Pooh http://home.netcom.com/~eugenem/ PGP KeyID = 0x1F9779FD, 0x319393F4 PGP keys available on request ICQ 3113529 O- - - 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 -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Miguel A. Solórzano [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Mogi das Cruzes - São Paulo, Brazil ___/ www.mysql.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
MySQL unix vs WinXP case sensitivity
I seem to be having issues with the way MySQL names the table files under Unix and WinXP. I'm not sure if it's a mysql issue or a windows issue. Normally, when I create tables in MySQL, I like to capitalize the first letter. Under unix, it all works fine and dandy. But under WinXP, the table name gets converted to all lowercase, which consequently messes up my php files when I try to go back and forth between unix and windows. I suppose I could switch to all lower case, but old habits die hard and I sometimes forget when I'm typing away some PHP code. The MySQL I installed is from the download page at mysql.com. anybody else run into this issue? Eugene -- - Eugene Mah, M.Sc., DABR [EMAIL PROTECTED] Medical Physicist/Misplaced Canuck[EMAIL PROTECTED] Department of Radiology For I am a Bear of Very Little Medical University of South Carolina Brain, and long words Bother Charleston, South Carolina me. Winnie the Pooh http://home.netcom.com/~eugenem/ PGP KeyID = 0x1F9779FD, 0x319393F4 PGP keys available on request ICQ 3113529 O- - - 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
Re: Case sensitivity in Mysql
At 02:05 AM 3/14/2002, you wrote: Hi, I run mysql on windows as well as linux. table creation and access right are given by java program. Problem im facing is., i create a table with name TEMP in windows in creates table with name temp and linux TEMP while giving rights thru GRANTstatement , it fails in windows cause it expect TEMP not temp How can i create table name with upper case in Windows create table TEMP ( x varchar(6), y varchar(6)) this creates a table with name temp..not TEMP help me with mysql Anis, Talking from experience, it is best to create all database names and table names in lower case. It will prevent a lot of grey hairs in the future. You can have upper and lower case column names, but I also put them in lower case to avoid mistyping the case of a letter. If you keep everything in lowercase, life will be much easier. Brent Why or why couldn't MySQL have used case insensitive names in the first place? Store everything in lower case internally, but allow the user to use Invoice_Number or invoice_number or INVOICE_NUMBER in the SQL statement and it should refer to the same column. Same with table names. I don't see why you would want to have 3 different columns in the same table with names Invoice_Number, invoice_number and INVOICE_NUMBER. Maybe it's just me, but it doesn't make a lot of sense to have case insensitive names. It just leads to typos and the code doesn't run. :-( _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.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
Re: Case sensitivity in Mysql
On Thursday 14 March 2002 9:32 am, BD wrote: Why or why couldn't MySQL have used case insensitive names in the first place? Store everything in lower case internally, but allow the user to use Invoice_Number or invoice_number or INVOICE_NUMBER in the SQL statement and it should refer to the same column. Same with table names. I don't see why you would want to have 3 different columns in the same table with names Invoice_Number, invoice_number and INVOICE_NUMBER. Maybe it's just me, but it doesn't make a lot of sense to have case insensitive names. It just leads to typos and the code doesn't run. :-( In SQL92, identifiers can only be uppercase letters and numerals. Therefore, if you are using lowercase, you are NOT writing valid SQL92. Most databases will allow this, of course. SQL99 adds support for lowercase characters but MySQL doesn't claim to implement SQL99 yet. - 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
Re: Case sensitivity in Mysql
At 11:17 AM 3/14/2002, you wrote: On Thursday 14 March 2002 9:32 am, BD wrote: Why or why couldn't MySQL have used case insensitive names in the first place? Store everything in lower case internally, but allow the user to use Invoice_Number or invoice_number or INVOICE_NUMBER in the SQL statement and it should refer to the same column. Same with table names. I don't see why you would want to have 3 different columns in the same table with names Invoice_Number, invoice_number and INVOICE_NUMBER. Maybe it's just me, but it doesn't make a lot of sense to have case insensitive names. It just leads to typos and the code doesn't run. :-( In SQL92, identifiers can only be uppercase letters and numerals. Therefore, if you are using lowercase, you are NOT writing valid SQL92. Most databases will allow this, of course. SQL99 adds support for lowercase characters but MySQL doesn't claim to implement SQL99 yet. I don't mind switching to uppercase entities if it means I won't have to be on the lookout for the SQL police. The next knock on the door could be SQL police coming to confiscate my database or levy heavy fines. :-) (Don't laugh. I don't know if you know this or not, but in Quebec there are language laws preventing store owners from having English only signs or signs where the English text is larger than the French text. The English text must be smaller than the French text otherwise the language police will come knocking on your door and will force you to take the sign down or fine you. The people who refuse end up in court. I hope the ANSI standards committee isn't listening because I could be in big trouble!) Seriously though, you're right, the table and column names should be in uppercase. It is strange though the MySQL related books I've read have kept table and column names to lower case, maybe because they're easier to read. (Hey, maybe I'll blame this on Paul.bg) I only wish MySQL could have the case insensitive names. I see no point in having case sensitive table and column names. Brent (Oops.. I've got to go. There is someone knocking on the door...) _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.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
Case sensitivity in Mysql
Hi, I run mysql on windows as well as linux. table creation and access right are given by java program. Problem im facing is., i create a table with name TEMP in windows in creates table with name temp and linux TEMP while giving rights thru GRANTstatement , it fails in windows cause it expect TEMP not temp How can i create table name with upper case in Windows create table TEMP ( x varchar(6), y varchar(6)) this creates a table with name temp..not TEMP help me with mysql reagrds, Anis - 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
Re: Case Sensitivity Question
Hi. On Sun, Feb 17, 2002 at 09:10:24AM -0700, [EMAIL PROTECTED] wrote: Since my first posting of this came out all screwed up, I currently have a PHP script performing the following query: mysql_query(SELECT * FROM Stuff WHERE Release LIKE \%$var2%\,$var1) It works great except that the search is coming out case sensitive. According to the MySQL Docs all searches are done without case sensitivity. Well, not completely. The default is case insensitivity, but depending on the context, you get case sensitive comparisons. See also http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#String_comparison_functions (and the following section) E.g. the behaviour you describe would be expected if Release has a column type that is regarded BINARY (like BLOB). I just have 2 questions: 1) I think the syntax I'm using is correct but incase it's not what is the correct syntax for a case insensitive search? See the next section of the above link. It suggest casting to uppercase for forcing case insensitive compares. 2) What version of MySQL did the default of case insensitive searches begin as I'm using 3.23.31. LIKE is case insensitive per default since 3.20.17 according to http://www.mysql.com/documentation/mysql/bychapter/manual_News.html Bye, Benjamin. - 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
Case Sensitivity Question
Since my first posting of this came out all screwed up, I currently have a PHP script performing the following query: mysql_query(SELECT * FROM Stuff WHERE Release LIKE \%$var2%\,$var1) It works great except that the search is coming out case sensitive. According to the MySQL Docs all searches are done without case sensitivity. I just have 2 questions: 1) I think the syntax I'm using is correct but incase it's not what is the correct syntax for a case insensitive search? 2) What version of MySQL did the default of case insensitive searches begin as I'm using 3.23.31. Thanks Mike What you're doing is the opposite of help. --Shrek - 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
Unique indexes and case sensitivity bug(?)
Description:
MYSQL returns ERROR 1062: Duplicate entry 'xx' for key x
when creating a UNIQUE KEY on CHAR fields that contain more
occurencies of the same value written with different cases.
See example below.
How-To-Repeat:
Please run these few SQL statements
CREATE TABLE dummy (a CHAR(1));
INSERT INTO dummy VALUES('b');
INSERT INTO dummy VALUES('B');
CREATE UNIQUE INDEX dummy_idx ON dummy(a);
Now you should get the error
Fix:
I read carefully the manual and I tried to change the column
type to CHAR(x) BYNARY
It seems to me that it is not possible to force MYSQL to treat
the fields literrally when creating indexes (though it is possible
in the SELECT statements)
I haven't find any server variable that concerns this feature.
More likely than not I am a dumb, and the solution is right
under my noose, but at the moment I am stucked
THANX
Submitter-Id: [EMAIL PROTECTED]
Originator: root
Organization:
Emmegi S.p.A. (www.emmegi.it)
MySQL support: [none ]
Synopsis: Unique indexes and case sensitivity bug(?)
Severity: serious
Priority: medium
Category: mysql
Class: sw-bug
Release: mysql-4.0.1-alpha (Official MySQL RPM)
Server: /usr/bin/mysqladmin Ver 8.18 Distrib 3.23.36, for pc-linux-gnu on
i686
Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 4.0.1-alpha-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 47 min 47 sec
Threads: 6 Questions: 2429 Slow queries: 1 Opens: 69 Flush tables: 2
Open tables: 24 Queries per second avg: 0.847
Environment:
machine, os, target, libraries (multiple lines)
System: Linux mandrake 2.4.3-20mdksmp #1 SMP Sun Apr 15 22:32:27 CEST 2001
i686 unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i586-mandrake-linux/2.96/specs
gcc version 2.96 2731 (Linux-Mandrake 8.0 2.96-0.48mdk)
Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium'
CXX='gcc'
-O6 -fno-omit-frame-pointer-felide-constructors -fno-exceptions
-fno-rtti -mpentium' LDFLAGS=''
LIBC:
lrwxrwxrwx1 root root 13 ago 27 00:40 /lib/libc.so.6 -
libc-2.2.2.so
-rwxr-xr-x1 root root 1216268 feb 21 2001 /lib/libc-2.2.2.so
-rw-r--r--1 root root 26366908 feb 21 2001 /usr/lib/libc.a
-rw-r--r--1 root root 178 feb 21 2001 /usr/lib/libc.so
Configure command:
./configure --disable-shared --with-mysqld-ldflags=-all-static --with-clien
t-ldflags=-all-static --without-berkeley-db --with-innodb --enable-assembler
--with-mysqld-user=mysql --with-unix-socket-path=/var/lib/mysql/mysql.sock
--prefix=/ --with-extra-charsets=complex --exec-prefix=/usr --libexecdir=/us
r/sbin --sysconfdir=/etc --datadir=/usr/share --localstatedir=/var/lib/mysql
--infodir=/usr/info --includedir=/usr/include --mandir=/usr/man --with-embe
dded-server --enable-thread-safe-client '--with-comment=Official MySQL RPM'
-
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
Fwd: Table names case sensitivity
Hi all, My problem goes away after I have upgraded MySQL from 3.23.41 to 3.23.46. Thanks you for anyone who have made suggestions or who have tried to figure out the solution. Kent. --- Mr Kent Cheung [EMAIL PROTECTED] wrote: Date: Wed, 30 Jan 2002 20:01:21 -0800 (PST) From: Mr Kent Cheung [EMAIL PROTECTED] Subject: Table names case sensitivity To: MySQL Discussion [EMAIL PROTECTED] Dear MySQL community, I am new MySQL user. I want to migrate a M$ SQL Server database to MySQL in a Linux box. Since table names in MySQL@Linux is case sensitive, I am having troubles connecting to the server via ODBC from a Windows box. I search the documentation and find that I may be able to disable the case sensitivity by setting the variable called lower_case_table_names to 1 when starting the server. I edit my.cnf and restart the server. Everything seems OK. mysqladmin and the Show Variables command in mysql confirm that this variable is now 1 instead of 0 before I make the changes. I have also changed the schema so that all table names are now in lower cases. However, when I perform a select, eg., SELECT * FROM Company; Then, an error occurs that tells me that the user xxx is not allowed select the table. Of course, there is no error when the following SQL is submitted: SELECT * FROM company; I change my.cnf back and repeat the above tests and the error persists. Thus, nothing seems to be changed by setting the variable. Have I missed anything? Thanks in advance. __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.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 __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.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
RE: Table names case sensitivity
Neil, Thank you for your suggestion. Unfortunately, the Grant command grants privileges to lower case table names also. I have also tried to find solution from archive of mysql mail lists but nothing useful have been found. Kent. --- Neil Silvester [EMAIL PROTECTED] wrote: -Original Message- From: Mr Kent Cheung [mailto:[EMAIL PROTECTED]] SELECT * FROM Company; Then, an error occurs that tells me that the user xxx is not allowed select the table. Of course, there is no error when the following SQL is submitted: SELECT * FROM company; I change my.cnf back and repeat the above tests and the error persists. Thus, nothing seems to be changed by setting the variable. I am not quite sure but, if you had set table privileges for your tables using grant statements, these could be incorrectly pointing to the wrong databases still. So if you granted GRANT ALL ON xxx.Company TO user@host IDENTIFIED BY password you would get errors because you only have permission to Company and not company. Just a thought. Neil Silvester Heat and Control Pty Ltd __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.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
Table names case sensitivity
Mr, Thursday, January 31, 2002, 6:01:21 AM, you wrote: MKC Dear MySQL community, MKC I am new MySQL user. I want to migrate a M$ SQL Server MKC database to MySQL in a Linux box. Since table names in MKC MySQL@Linux is case sensitive, I am having troubles MKC connecting to the server via ODBC from a Windows box. MKC I search the documentation and find that I may be able MKC to disable the case sensitivity by setting the MKC variable called lower_case_table_names to 1 when MKC starting the server. I edit my.cnf and restart the MKC server. Everything seems OK. mysqladmin and the Show MKC Variables command in mysql confirm that this variable MKC is now 1 instead of 0 before I make the changes. I MKC have also changed the schema so that all table names MKC are now in lower cases. MKC However, when I perform a select, eg., MKC SELECT * FROM Company; MKC Then, an error occurs that tells me that the user xxx MKC is not allowed select the table. Of course, there is MKC no error when the following SQL is submitted: MKC SELECT * FROM company; MKC I change my.cnf back and repeat the above tests and MKC the error persists. Thus, nothing seems to be changed MKC by setting the variable. MKC Have I missed anything? Yes, look in the manual: http://www.mysql.com/doc/N/a/Name_case_sensitivity.html If you set option lower_case_table_names=1, you should convert your old table names befor starting mysqld. -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.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
Fwd: Table names case sensitivity
Hi, As what I described in the original text, I have changed the table names to lower cases. Thus, I think I have done exactly what the manual says. Is this a bug or what have I missed? Thanks for your suggestion anyway. Kent. --- Egor Egorov [EMAIL PROTECTED] wrote: Date: Thu, 31 Jan 2002 12:12:01 +0200 To: [EMAIL PROTECTED] From: Egor Egorov [EMAIL PROTECTED] Subject: Table names case sensitivity Mr, Thursday, January 31, 2002, 6:01:21 AM, you wrote: MKC Dear MySQL community, MKC I am new MySQL user. I want to migrate a M$ SQL Server MKC database to MySQL in a Linux box. Since table names in MKC MySQL@Linux is case sensitive, I am having troubles MKC connecting to the server via ODBC from a Windows box. MKC I search the documentation and find that I may be able MKC to disable the case sensitivity by setting the MKC variable called lower_case_table_names to 1 when MKC starting the server. I edit my.cnf and restart the MKC server. Everything seems OK. mysqladmin and the Show MKC Variables command in mysql confirm that this variable MKC is now 1 instead of 0 before I make the changes. I MKC have also changed the schema so that all table names MKC are now in lower cases. MKC However, when I perform a select, eg., MKC SELECT * FROM Company; MKC Then, an error occurs that tells me that the user xxx MKC is not allowed select the table. Of course, there is MKC no error when the following SQL is submitted: MKC SELECT * FROM company; MKC I change my.cnf back and repeat the above tests and MKC the error persists. Thus, nothing seems to be changed MKC by setting the variable. MKC Have I missed anything? Yes, look in the manual: http://www.mysql.com/doc/N/a/Name_case_sensitivity.html If you set option lower_case_table_names=1, you should convert your old table names befor starting mysqld. -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.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 __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.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
Fwd: Table names case sensitivity
Mr, Thursday, January 31, 2002, 12:29:24 PM, you wrote: MKC Hi, MKC As what I described in the original text, I have MKC changed the table names to lower cases. Thus, I think MKC I have done exactly what the manual says. Is this a MKC bug or what have I missed? You have grants on database.Company instead of database.company You should update permissions for you. MKC Thanks for your suggestion anyway. MKC Kent. [skip] MKC I search the documentation and find that I may be able MKC to disable the case sensitivity by setting the MKC variable called lower_case_table_names to 1 when MKC starting the server. I edit my.cnf and restart the MKC server. Everything seems OK. mysqladmin and the Show MKC Variables command in mysql confirm that this variable MKC is now 1 instead of 0 before I make the changes. I MKC have also changed the schema so that all table names MKC are now in lower cases. MKC However, when I perform a select, eg., MKC SELECT * FROM Company; MKC Then, an error occurs that tells me that the user xxx MKC is not allowed select the table. Of course, there is MKC no error when the following SQL is submitted: MKC SELECT * FROM company; -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.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
Table names case sensitivity
Dear MySQL community, I am new MySQL user. I want to migrate a M$ SQL Server database to MySQL in a Linux box. Since table names in MySQL@Linux is case sensitive, I am having troubles connecting to the server via ODBC from a Windows box. I search the documentation and find that I may be able to disable the case sensitivity by setting the variable called lower_case_table_names to 1 when starting the server. I edit my.cnf and restart the server. Everything seems OK. mysqladmin and the Show Variables command in mysql confirm that this variable is now 1 instead of 0 before I make the changes. I have also changed the schema so that all table names are now in lower cases. However, when I perform a select, eg., SELECT * FROM Company; Then, an error occurs that tells me that the user xxx is not allowed select the table. Of course, there is no error when the following SQL is submitted: SELECT * FROM company; I change my.cnf back and repeat the above tests and the error persists. Thus, nothing seems to be changed by setting the variable. Have I missed anything? Thanks in advance. __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.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
Disable case sensitivity
Hi, Does anybody knows if is possible to disable case sensitivity in Linux MySQL?? Thanks in advance, Fernando Monteiro Duarte - 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
Re: Disable case sensitivity
On Thu, Jan 10, 2002 at 11:52:34AM -0300, Fernando Monteiro Duarte wrote: Hi, Does anybody knows if is possible to disable case sensitivity in Linux MySQL?? Case sensitivity in what part? Table names? Values in columns? -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 7 days, processed 189,274,654 queries (287/sec. avg) - 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
Re: Disable case sensitivity
Hi Mr. Zawodny, If is possible, in everything from a Database, but if is not, in columns is more important. Thanks in advance, Fernando Monteiro Duarte - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Fernando Monteiro Duarte [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, January 10, 2002 12:06 PM Subject: Re: Disable case sensitivity On Thu, Jan 10, 2002 at 11:52:34AM -0300, Fernando Monteiro Duarte wrote: Hi, Does anybody knows if is possible to disable case sensitivity in Linux MySQL?? Case sensitivity in what part? Table names? Values in columns? -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 7 days, processed 189,274,654 queries (287/sec. avg) - 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
RE: Disable case sensitivity
Jerry: Can you specify the hardware/software your MySQL 3.23.41-max is running on? Kam -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 10, 2002 9:07 AM To: Fernando Monteiro Duarte Cc: [EMAIL PROTECTED] Subject: Re: Disable case sensitivity On Thu, Jan 10, 2002 at 11:52:34AM -0300, Fernando Monteiro Duarte wrote: Hi, Does anybody knows if is possible to disable case sensitivity in Linux MySQL?? Case sensitivity in what part? Table names? Values in columns? -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 7 days, processed 189,274,654 queries (287/sec. avg) - 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 - 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
Re: Disable case sensitivity
Fernando Monteiro Duarte writes: Hi Mr. Zawodny, If is possible, in everything from a Database, but if is not, in columns is more important. Thanks in advance, Fernando Monteiro Duarte Hi! Column names are case insensitive. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.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
Re: Disable case sensitivity
On Thu, Jan 10, 2002 at 09:14:51AM -0500, Ho, Kam wrote: Can you specify the hardware/software your MySQL 3.23.41-max is running on? That machine is a dual-CPU Linux 2.4.9 box with 2GB of RAM and 4 36GB ultra-wide SCSI drives for the data and replication logs. Before the restart we did in December, it was approaching 2 billion queries since the time before. :-) Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 7 days, processed 190,083,461 queries (288/sec. avg) - 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
Re: Disable case sensitivity
But how about the fields, the content of a database ?? Fernando - Original Message - From: Sinisa Milivojevic [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, January 10, 2002 12:32 PM Subject: Re: Disable case sensitivity Fernando Monteiro Duarte writes: Hi Mr. Zawodny, If is possible, in everything from a Database, but if is not, in columns is more important. Thanks in advance, Fernando Monteiro Duarte Hi! Column names are case insensitive. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.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 - 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
RE: Disable case sensitivity
-Original Message- From: Sinisa Milivojevic [mailto:[EMAIL PROTECTED]] Subject: Re: Disable case sensitivity If is possible, in everything from a Database, but if is not, in columns is more important. Column names are case insensitive. Correct... Database names and Table names don't necessarily share this (according to the MySQL manual snippet below). 6.1.3 Case Sensitivity in Names In MySQL, databases and tables correspond to directories and files within those directories. Consequently, the case sensitivity of the underlying operating system determines the case sensitivity of database and table names. This means database and table names are case sensitive in Unix and case insensitive in Windows. See section 1.7.3 MySQL Extensions to ANSI SQL92. NOTE: Although database and table names are case insensitive for Windows, you should not refer to a given database or table using different cases within the same query. The following query would not work because it refers to a table both as my_table and as MY_TABLE: SELECT * FROM my_table WHERE MY_TABLE.col=1; - 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
Re: Disable case sensitivity
On Thu, Jan 10, 2002 at 12:58:03PM -0200, Fernando Monteiro Duarte wrote: But how about the fields, the content of a database ?? VARCHAR and CHAR fields are not case-sensitive unless you declare them with the BINARY attribute. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 7 days, processed 191,007,477 queries (288/sec. avg) - 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
RE: Disable case sensitivity
* Fernando Monteiro Duarte But how about the fields, the content of a database ?? char and varchar are by default not case sensitive, but can be made case sensitive using the BINARY operator... URL: http://www.mysql.com/doc/C/a/Case_Sensitivity_Operators.html -- Roger - 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
Re: Disable case sensitivity
At 16:22 10/01/2002, you wrote: On Thu, Jan 10, 2002 at 12:58:03PM -0200, Fernando Monteiro Duarte wrote: But how about the fields, the content of a database ?? VARCHAR and CHAR fields are not case-sensitive unless you declare them with the BINARY attribute. neither TEXT/LONGTEXT -- .thibaut allender ~ dev:network. @ magic:sq. .tel. : +32 2 / 209 83 71 ~ http://magicsquare.be - 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
Case Sensitivity in MySQL
Hi, I have a situation as follows: There is a table that stores the user name. One user name is 'David' and another is 'david' I want to select only the user with the name 'david'(all small and not 'David') MySQL is case insensitive in selecting records... how to make it case-sensitive in selecting records... shankar __ Do You Yahoo!? Send your FREE holiday greetings online! http://greetings.yahoo.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
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;
snip ... etc ...
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;
snip ... etc ...
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
case sensitivity issue
I'm having trouble with what appears to be a case sensitivity issue. Essentially, when I use the right case for a table name, the query fails, but when I use the wrong case, the query succeeds. Here's my table list: mysql show tables\g ++ | Tables_in_ac | ++ | Employee | | PerfAssessment | | PerfReview | | Workgroup | ++ 4 rows in set (0.00 sec) So it seems like the table is called Employee, but this query fails: mysql SELECT Email, FirstName, LastName FROM Employee WHERE ID = 3\g Empty set (0.00 sec) However, when I use the wrong case for the table name (employee), it works: mysql SELECT Email, FirstName, LastName FROM employee WHERE ID = 3\g +-+---+--+ | Email | FirstName | LastName | +-+---+--+ | [EMAIL PROTECTED] | Jack | Smith| +-+---+--+ 1 row in set (0.00 sec) The same results happen if I do SELECT * instead of specifying columns. My workaround is to call the table employee in my scripts, but this is extremely puzzling. Does anyone have any ideas? I've included my system information below. Thanks, -Alex Release: mysql-3.23.39 (Source distribution) System: Darwin ermine 1.3.7 Darwin Kernel Version 1.3.7: Sat Jun 9 11:12:48 PD\ T 2001; root:xnu/xnu-124.13.obj~1/RELEASE_PPC Power Macintosh powerpc Some paths: /usr/bin/perl /usr/bin/make /usr/bin/cc Compilation info: CC='cc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS='' LIBC: lrwxr-xr-x 1 root wheel 15 Jul 17 14:47 /usr/lib/libc.dylib - libSystem.dyl\ ib Configure command: ./configure --prefix=/usr/local/mysql - 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
Re: case sensitivity issue
I'm having trouble with what appears to be a case sensitivity issue. Essentially, when I use the right case for a table name, the query fails, but when I use the wrong case, the query succeeds. Well, it seems to depend on the system you are using. I noticed that when I created tables through phpMyAdmin on my local Win system, that the table(=file) was created on disk lower case only. It doesn't matter on Win, as the file system is not case sensitive, but it does on Linux. So you have to be careful if you transfer tables. I didn't see the create table syntax make any mistakes, so I think it is phpMyAdmin, but I don't know - I didn't care to check. Check the tablename(=filename) and rename the table or change the name in your scripts. -- Herzlich Werner Stürenburg _ ISIS Verlag, Teut 3, D-32683 Barntrup-Alverdissen Tel 0(049) 5224-997 407 · Fax 0(049) 5224-997 409 http://pferdezeitung.de - 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
Case Sensitivity Problem
Hi, Can somebody PLEASE help me If I send a query to get a list of names, like this: SELECT Name FROM users ORDER BY Name, I get a list of users like this... Bob Jon Mark Sally andy lucy mary steve when what I REALLY want is this... andy Bob Jon lucy Mark mary Sally Steve Can someone give me an idea on how I need to change my query in order to achieve this ? Thanks Keith Netrickery Website Designers - 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
RE: Case Sensitivity Problem
Select Name, lower(name) as sortOrder from users order by sortOrder; Not exactly what you wanted, but closer. Cal http://www.calevans.com -Original Message- From: Kif Henderson [mailto:[EMAIL PROTECTED]] Sent: Friday, March 09, 2001 7:38 PM To: [EMAIL PROTECTED] Subject: Case Sensitivity Problem Hi, Can somebody PLEASE help me If I send a query to get a list of names, like this: SELECT Name FROM users ORDER BY Name, I get a list of users like this... Bob Jon Mark Sally andy lucy mary steve when what I REALLY want is this... andy Bob Jon lucy Mark mary Sally Steve Can someone give me an idea on how I need to change my query in order to achieve this ? Thanks Keith Netrickery Website Designers - 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 - 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
