List:General Discussion« Previous MessageNext Message »
From:Larry Martell Date:May 20 2014 10:48pm
Subject:Re: Case sensitivity
View as plain text  
On Tue, May 20, 2014 at 5:52 PM, Hartmut Holzgraefe <hartmut@stripped> 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.
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