What language(s) are involved? What collation are you using now? The German sharp-S does not work well with case folding. utf8_unicode_ci could have stuff folding together. Etc.
If you can find a USER_ID that is causing trouble, please provide SELECT HEX(USER_ID) FROM ... WHERE ... for further discussion. Instead of changing the data, why not do the casefolding as you SELECT into Java? > -----Original Message----- > From: Ananda Kumar [mailto:anan...@gmail.com] > Sent: Wednesday, May 16, 2012 3:43 AM > To: GF > Cc: mysql@lists.mysql.com > Subject: Re: Foreign key and uppercase / lowercase values > > why are not using any where condition in the update statment > > On Wed, May 16, 2012 at 1:24 PM, GF <gan...@gmail.com> wrote: > > > Good morning, > > I have an application where the user ids were stored lowercase. > > Some batch import, in the user table some users stored a uppercase > > id, and for some applicative logic, in other tables that have a > > foreign key to the user table, their user ids are stored lowercase. > > MySQL didn't throw any error probalby because the collation used is > > "case insensitive". > > My problem is that the application is Java and java strings are case > > sensitive, so now I want to set user ids to lowercase EVERYWHERE. > > > > I supposed that I could execute with ease these commands: > > - update mytable1 set USER_ID = LOWER(USER_ID); > > - update mytable2 set USER_ID = LOWER(USER_ID); > > - update mytable3 set USER_ID = LOWER(USER_ID); > > > > But for some tables I got some Foreign key constraint to throw an > > error. (butwhy they didn't throw an error on the insert but just on > > the update???) And if I try to disable foreign key checks during > these > > updates, I get some "duplicate key" errors where USER_ID is a part of > > composite key with other columns. (but I don't have any data that > > might cause a real duplicate key error just changing the case of one > > column) > > > > Have you any idea how to solve this situation without > > stopping/recreating the DB? (it's a production environment) Thanks > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/mysql > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
