From: GF (ganfabgmail.com)
Date: Mon Jun 18 2012 - 03:09:41 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

http://bugs.mysql.com/bug.php?id=65648

On Fri, Jun 15, 2012 at 7:00 PM, Rick James <rjamesyahoo-inc.com> wrote:
> You are very close to a standalone test case.  Please create such.  Then post it on bugs.mysql.com .
>
>> -----Original Message-----
>> From: GF [mailto:ganfabgmail.com]
>> Sent: Friday, June 15, 2012 12:45 AM
>> To: Rick James
>> Cc: Shawn Green; mysqllists.mysql.com
>> Subject: Re: Foreign key and uppercase / lowercase values
>>
>> I think the following might give complete information (I removed some
>> columns not involved in the problem)
>>
>> Server version: 5.1.49-3 (Debian)
>>
>> SET collation_connection = utf8_unicode_ci; Query OK, 0 rows affected
>> (0.00 sec)
>>
>>
>>
>> show variables like '%colla%';
>> +----------------------+-----------------+
>> | Variable_name        | Value           |
>> +----------------------+-----------------+
>> | collation_connection | utf8_unicode_ci |
>> | collation_database   | utf8_unicode_ci |
>> | collation_server     | utf8_unicode_ci |
>> +----------------------+-----------------+
>> 3 rows in set (0.00 sec)
>>
>> show variables like '%char%';
>> +--------------------------+----------------------------+
>> | Variable_name            | Value                      |
>> +--------------------------+----------------------------+
>> | character_set_client     | utf8                       |
>> | character_set_connection | utf8                       |
>> | character_set_database   | utf8                       |
>> | character_set_filesystem | binary                     |
>> | character_set_results    | utf8                       |
>> | character_set_server     | utf8                       |
>> | character_set_system     | utf8                       |
>> | character_sets_dir       | /usr/share/mysql/charsets/ |
>> +--------------------------+----------------------------+
>> 8 rows in set (0.00 sec)
>>
>>
>> select USER_ID from TBL_USER where USER_ID = 'GIUSEPPE';
>> +----------+
>> | USER_ID  |
>> +----------+
>> | GIUSEPPE |
>> +----------+
>> 1 row in set (0.00 sec)
>>
>>  select USER_ID from TBL_USER where USER_ID = 'giuseppe';
>> +----------+
>> | USER_ID  |
>> +----------+
>> | GIUSEPPE |
>> +----------+
>> 1 row in set (0.00 sec)
>>
>>
>> update TBL_USER set USER_ID = LOWER(USER_ID) where USER_ID =
>> 'GIUSEPPE'; ERROR 1451 (23000): Cannot delete or update a parent row: a
>> foreign key constraint fails (`myschema`.`TBL_COMMENT`, CONSTRAINT
>> `FK4F6E52581590B46E` FOREIGN KEY (`USER_ID`) REFERENCES `TBL_USER`
>> (`USER_ID`))
>>
>>
>>
>> | TBL_USER | CREATE TABLE `TBL_USER` (
>>   `USER_ID` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
>>
>>   << cut >>
>>   PRIMARY KEY (`USER_ID`),
>>   << cut >>
>> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
>>
>>
>>
>> | TBL_COMMENT | CREATE TABLE `TBL_COMMENT` (
>>   << cut >>
>>   `ID` bigint(20) NOT NULL AUTO_INCREMENT,
>>   << cut >>
>>   `USER_ID` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
>>   << cut >>
>>   PRIMARY KEY (`ID`),
>>   << cut >>
>>   KEY `FK4F6E52581590B46E` (`USER_ID`),
>>   << cut >>
>>   CONSTRAINT `FK4F6E52581590B46E` FOREIGN KEY (`USER_ID`) REFERENCES
>> `TBL_USER` (`USER_ID`),
>>   << cut >>
>> ) ENGINE=InnoDB AUTO_INCREMENT=7876 DEFAULT CHARSET=utf8
>> COLLATE=utf8_unicode_ci |

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]