List:General Discussion« Previous MessageNext Message »
From:Larry Martell Date:May 20 2014 9:10pm
Subject:Re: Case sensitivity
View as plain text  
On Tue, May 20, 2014 at 9:38 AM, Hartmut Holzgraefe <hartmut@stripped> 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!
Thread
Case sensitivityLarry Martell20 May 2014
  • Re: Case sensitivityReindl Harald20 May 2014
  • Re: Case sensitivityHartmut Holzgraefe20 May 2014
    • Re: Case sensitivityLarry Martell20 May 2014
      • Re: Case sensitivityHartmut Holzgraefe20 May 2014
        • Re: Case sensitivityLarry Martell20 May 2014
          • Re: Case sensitivityReindl Harald20 May 2014
            • Re: Case sensitivityJohan De Meersman21 May 2014
              • Re: Case sensitivityHartmut Holzgraefe21 May 2014
                • Re: Case sensitivityReindl Harald21 May 2014
                • Re: Case sensitivityLarry Martell21 May 2014