Re: New server where strings get utf8 encoded before being saved to MySQL. Any hints for me?
Thought that, for reference, I'd add some of the things that is needed when converting an existing app/database to utf-8 (utf8). Actually altering the table definitions, and connection configurations does not save data already inserted when the charset was set all wrong. How to convert existing data was a bit hard to find out. Here is what I found in a comment in some blog I can't remember now. It ia a bit tedious but it works as long as you can shut down your application for a few minutes. ALTER TABLE my_table CHANGE my_field my_field TEXT CHARACTER SET latin1; ALTER TABLE my_table CHANGE my_field my_field BLOB; ALTER TABLE my_table CHANGE my_field my_field TEXT CHARACTER SET utf8; The trick apparently is to first change the charset of the field back to what the database had when the data was inserted. Then you change it to a blob (no charset for binary data) and finally to the desired charset. The tedious part is doing this for many fields in many tables... but it sure saved my backside. The only alternative I have found is to do a search for each messed up character and doing a replace on them. But then you must first find each character that has been mangled by the charset change. For example: UPDATE my_table SET my_field = replace(my_field,'√•','å'); /Martin On Jun 16, 9:53 am, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > Yeah, I got that the reason is linguistic in its origin. It is great > when trying to search a mass of text. But when you try to do a > matching search for an exact string it does complicate things a lot > when you still think that = really means exactly equal. > > Doing WHERE username = 'myname' I (as a programmer) never ever want to > match anything else but exactly that. > Doing WHERE article LIKE '%cake%' I would not at all be this critical > or surprised since it is a different kind of searching in my world. > > Also, I was under the mistaken impression that COLLATE was ONLY > related to how to sort these special characters. This I have not > problem with either btw. Previously, I had no idea that collation also > affected simple matching searches. > > The equal sign has a special place in my heart. :) > I guess the binary collation will be my preference for general data. > > Do you have any advice for a web-application with multiple languages? > You can only take advantage of the linguistic advantages as long as > the language in the data and the collation match. How would cater for, > say, a blog in both german and french? Set the database defaults to > general or binary and then add COLLATE utf8_french_ci to the queries? > > thanks > Martin > > On Jun 13, 4:28 pm, "Jonathan Snook" <[EMAIL PROTECTED]> wrote: > > > > A am a bit shocked that it is a "feature" when å is the same as a in > > > MySQL. That sounds just plain wrong to me. If it had been so for > > > utf8_some_special_ci, fine, but not for general (the default default) > > > collations. To me that would be like PHP saying (1 == 1.2) is true > > > because it is "close enough". :) Very strange but I guess they must > > > have some very good reason for it. > > > It's not really the same thing and yes, there's a very good reason. > > Most languages, diacritics are meant to alter the pronunciation of a > > letter. In other words, e, é and è are the same "letter" but have > > different pronunciations because of the accent marks. Therefore, when > > a French person does a search for a word, they might simply type in > > "ecole" but they fully expect école to show up. Another example, I > > live in a city known as Orléans but has been known as Orleans (note > > the lack of accent) for a number of years (they only recently added > > the accent back in where it belongs). However, a search for Orleans > > should bring up either result. Also, collations determine how content > > is ordered when results are returned. Take Ecole A, ecole B, École C > > and école D. How should that be ordered? The _ci indicates > > case-insensitive so we get the order we expect (as I've listed). It'd > > be pretty confusing to do a search and get ecole B, Ecole A, [the rest > > of the latin character results], école D, École C. > > > I hope that explains it a little better. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: New server where strings get utf8 encoded before being saved to MySQL. Any hints for me?
Yeah, I got that the reason is linguistic in its origin. It is great when trying to search a mass of text. But when you try to do a matching search for an exact string it does complicate things a lot when you still think that = really means exactly equal. Doing WHERE username = 'myname' I (as a programmer) never ever want to match anything else but exactly that. Doing WHERE article LIKE '%cake%' I would not at all be this critical or surprised since it is a different kind of searching in my world. Also, I was under the mistaken impression that COLLATE was ONLY related to how to sort these special characters. This I have not problem with either btw. Previously, I had no idea that collation also affected simple matching searches. The equal sign has a special place in my heart. :) I guess the binary collation will be my preference for general data. Do you have any advice for a web-application with multiple languages? You can only take advantage of the linguistic advantages as long as the language in the data and the collation match. How would cater for, say, a blog in both german and french? Set the database defaults to general or binary and then add COLLATE utf8_french_ci to the queries? thanks Martin On Jun 13, 4:28 pm, "Jonathan Snook" <[EMAIL PROTECTED]> wrote: > > A am a bit shocked that it is a "feature" when å is the same as a in > > MySQL. That sounds just plain wrong to me. If it had been so for > > utf8_some_special_ci, fine, but not for general (the default default) > > collations. To me that would be like PHP saying (1 == 1.2) is true > > because it is "close enough". :) Very strange but I guess they must > > have some very good reason for it. > > It's not really the same thing and yes, there's a very good reason. > Most languages, diacritics are meant to alter the pronunciation of a > letter. In other words, e, é and è are the same "letter" but have > different pronunciations because of the accent marks. Therefore, when > a French person does a search for a word, they might simply type in > "ecole" but they fully expect école to show up. Another example, I > live in a city known as Orléans but has been known as Orleans (note > the lack of accent) for a number of years (they only recently added > the accent back in where it belongs). However, a search for Orleans > should bring up either result. Also, collations determine how content > is ordered when results are returned. Take Ecole A, ecole B, École C > and école D. How should that be ordered? The _ci indicates > case-insensitive so we get the order we expect (as I've listed). It'd > be pretty confusing to do a search and get ecole B, Ecole A, [the rest > of the latin character results], école D, École C. > > I hope that explains it a little better. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: New server where strings get utf8 encoded before being saved to MySQL. Any hints for me?
> A am a bit shocked that it is a "feature" when å is the same as a in > MySQL. That sounds just plain wrong to me. If it had been so for > utf8_some_special_ci, fine, but not for general (the default default) > collations. To me that would be like PHP saying (1 == 1.2) is true > because it is "close enough". :) Very strange but I guess they must > have some very good reason for it. It's not really the same thing and yes, there's a very good reason. Most languages, diacritics are meant to alter the pronunciation of a letter. In other words, e, é and è are the same "letter" but have different pronunciations because of the accent marks. Therefore, when a French person does a search for a word, they might simply type in "ecole" but they fully expect école to show up. Another example, I live in a city known as Orléans but has been known as Orleans (note the lack of accent) for a number of years (they only recently added the accent back in where it belongs). However, a search for Orleans should bring up either result. Also, collations determine how content is ordered when results are returned. Take Ecole A, ecole B, École C and école D. How should that be ordered? The _ci indicates case-insensitive so we get the order we expect (as I've listed). It'd be pretty confusing to do a search and get ecole B, Ecole A, [the rest of the latin character results], école D, École C. I hope that explains it a little better. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: New server where strings get utf8 encoded before being saved to MySQL. Any hints for me?
Sorry for that. I used the familiar terminology from utf8_encode() and utf8_decode() in php even though it is more a matter of interpertation of data then encoding of data. A am a bit shocked that it is a "feature" when å is the same as a in MySQL. That sounds just plain wrong to me. If it had been so for utf8_some_special_ci, fine, but not for general (the default default) collations. To me that would be like PHP saying (1 == 1.2) is true because it is "close enough". :) Very strange but I guess they must have some very good reason for it. Thanks for the link, btw. From what I understood there the safest bet is to use a binary collation unless you want some language-specific rules to apply. Clarification of the usernames: I agree with you 100%. These users are a clients legacy users. Naturally they allowed all sorts of impossible characters to be used, just to make life more interesting for me. :) For example they also allow ~:-)*_ and many other characters that are "reserved" for special use in many cases. Thanks again, Martin On Jun 13, 2:50 am, "David C. Zentgraf" <[EMAIL PROTECTED]> wrote: > You're mixing up a lot of terms and concepts here. > It's not about double-encoding, it's about interpreting the data. A > string 'ABCD' is not saved as 'ABCD', it's saved as 1101001001011001 > (just pulling that out of my behind for illustrational purposes, no > guarantee for accuracy). "1101001001011001" in the latin1 charset > means "[EMAIL PROTECTED]&", in shift-jis it means "馬鹿" and in UTF8 it means > 'ABCD'. Setting the right encoding basically means that you're telling > your application and database how to *interpret* the data. And if just > one part of the database/db-connection/application chain is handling > data in a different encoding, it'll get screwed up. > > > selecting "halla" would find both rows with "halla" and > > "hallå". > > That's the point of MySQL's UTF8 handling, it knows about similar > characters. That's why there are a bunch of different UTF8 collations. > Please read the manual to figure out which collation would serve you > best:http://dev.mysql.com/doc/refman/5.1/en/charset-general.html > > > This caused a world of problems since user > > names were not unique anymore when searching the database. > > Not sure if a non-alphanumeric username is such a good idea, for > exactly these reasons. For uniqueness you should stick to the lowest > common denominator (basic latin letters), unless you really know what > you're doing. > > > What I learned from that was that MySQL can ignore table-collation and > > table charsets. I had to alter my tables and modify all varchar fields > > to include charset and collation settings. > > MySQL doesn't ignore collations, it does exactly what you tell it to > do. And yes, it's always a good idea to explicitly specify charsets, > otherwise some default will be used and cause unintended consequences. > That's what you should have learned. > > Chrs, > Dav > > > On Jun 12, 10:17 am, leo <[EMAIL PROTECTED]> wrote: > >> I'm inclined to agree. I had the same problem. All my MySql > >> installations default to Swedish, so need to be converted. Have a > >> look > >> at this > >> page:http://ragrawal.wordpress.com/2008/02/15/dummys-guide-for-converting- > >> ... > > >> Be aware that MySql is 'utf8' whereas everything else is utf-8 or > >> UTF-8. I don't know how important the case is. > > >> On Jun 12, 9:06 am, "David C. Zentgraf" <[EMAIL PROTECTED]> wrote: > > >>> I'd say it's the other way around. > >>> Text gets stored in some non-UTF8 format in the database (latin-1 > >>> most > >>> likely), so it comes back as gobbledygook. When you explicitly force > >>> the gobbledygook to be interpreted as UTF8, it's being morphed back > >>> into what it was meant to be. > > >>> Check your database settings and set all collations to UTF8. > > >>> On 12 Jun 2008, at 15:58, [EMAIL PROTECTED] wrote: > > Hi, > I have started putting a project onto a rented "production server" > running a pretty standard Ubuntu installation AFAIK. > I have been able to fix some initial problems with charsets but one > thing I just can seem to figure out. > > On my dev system > $this->params['form']['hello_text'] // in MySQL = hallå > > On production system > $this->params['form']['hello_text'] // inMySQL = hallÃ¥ > utf8_decode( $this->params['form']['hello_text'] ) // inMySQL = > hallå > > Looks like the text gets encoded to utf8 before being put into the > database. Problem is that the text is already utf8 so I get these > nasty characters. > > Does anyone know what triggers Cake or PHP to encode the text > before > sending it to MySQL? > > Any pointer in the right direction would be great. thanks. > > Martin --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "CakePHP" gro
Re: New server where strings get utf8 encoded before being saved to MySQL. Any hints for me?
You're mixing up a lot of terms and concepts here. It's not about double-encoding, it's about interpreting the data. A string 'ABCD' is not saved as 'ABCD', it's saved as 1101001001011001 (just pulling that out of my behind for illustrational purposes, no guarantee for accuracy). "1101001001011001" in the latin1 charset means "[EMAIL PROTECTED]&", in shift-jis it means "馬鹿" and in UTF8 it means 'ABCD'. Setting the right encoding basically means that you're telling your application and database how to *interpret* the data. And if just one part of the database/db-connection/application chain is handling data in a different encoding, it'll get screwed up. > selecting "halla" would find both rows with "halla" and > "hallå". That's the point of MySQL's UTF8 handling, it knows about similar characters. That's why there are a bunch of different UTF8 collations. Please read the manual to figure out which collation would serve you best: http://dev.mysql.com/doc/refman/5.1/en/charset-general.html > This caused a world of problems since user > names were not unique anymore when searching the database. Not sure if a non-alphanumeric username is such a good idea, for exactly these reasons. For uniqueness you should stick to the lowest common denominator (basic latin letters), unless you really know what you're doing. > What I learned from that was that MySQL can ignore table-collation and > table charsets. I had to alter my tables and modify all varchar fields > to include charset and collation settings. MySQL doesn't ignore collations, it does exactly what you tell it to do. And yes, it's always a good idea to explicitly specify charsets, otherwise some default will be used and cause unintended consequences. That's what you should have learned. Chrs, Dav > On Jun 12, 10:17 am, leo <[EMAIL PROTECTED]> wrote: >> I'm inclined to agree. I had the same problem. All my MySql >> installations default to Swedish, so need to be converted. Have a >> look >> at this >> page:http://ragrawal.wordpress.com/2008/02/15/dummys-guide-for-converting- >> ... >> >> Be aware that MySql is 'utf8' whereas everything else is utf-8 or >> UTF-8. I don't know how important the case is. >> >> On Jun 12, 9:06 am, "David C. Zentgraf" <[EMAIL PROTECTED]> wrote: >> >>> I'd say it's the other way around. >>> Text gets stored in some non-UTF8 format in the database (latin-1 >>> most >>> likely), so it comes back as gobbledygook. When you explicitly force >>> the gobbledygook to be interpreted as UTF8, it's being morphed back >>> into what it was meant to be. >> >>> Check your database settings and set all collations to UTF8. >> >>> On 12 Jun 2008, at 15:58, [EMAIL PROTECTED] wrote: >> Hi, I have started putting a project onto a rented "production server" running a pretty standard Ubuntu installation AFAIK. I have been able to fix some initial problems with charsets but one thing I just can seem to figure out. >> On my dev system $this->params['form']['hello_text'] // in MySQL = hallå >> On production system $this->params['form']['hello_text'] // inMySQL = hallÃ¥ utf8_decode( $this->params['form']['hello_text'] ) // inMySQL = hallå >> Looks like the text gets encoded to utf8 before being put into the database. Problem is that the text is already utf8 so I get these nasty characters. >> Does anyone know what triggers Cake or PHP to encode the text before sending it to MySQL? >> Any pointer in the right direction would be great. thanks. >> Martin > > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: New server where strings get utf8 encoded before being saved to MySQL. Any hints for me?
Thanks for your replies. Apparently this sees to have been the problem: When the nothing was done at all to the charsets you could have stuff working ok since whenever garbled characters were saved they would ungarble when they were read out again. Tables were utf8 but not MySQl itself. Setting MySQL to utf8 (for a big import from a textfile in utf8) started the trouble i think. Now MySQL was all utf8 but the connection to php was confused and MySQL converted all incoming text to utf8. This led to the doubble encoding. Setting "encoding" to utf8 in CakePHPs config/database.php got php to force utf8 when connecting and MySQL stopped encoding text. The moral of the story is to leave everything along or make real sure that absolutely everything gets reset to your "new" encoding. The thing that threw me was that test imported info MySQL with proper utf8 characters would give strange and very very bad result when selecting. selecting "halla" would find both rows with "halla" and "hallå". Umlaut characters would be interpreted as if they did not have their dots and things. This caused a world of problems since user names were not unique anymore when searching the database. What I learned from that was that MySQL can ignore table-collation and table charsets. I had to alter my tables and modify all varchar fields to include charset and collation settings. I have never had this much trouble with charsets before... :) /Martin On Jun 12, 10:17 am, leo <[EMAIL PROTECTED]> wrote: > I'm inclined to agree. I had the same problem. All my MySql > installations default to Swedish, so need to be converted. Have a look > at this > page:http://ragrawal.wordpress.com/2008/02/15/dummys-guide-for-converting-... > > Be aware that MySql is 'utf8' whereas everything else is utf-8 or > UTF-8. I don't know how important the case is. > > On Jun 12, 9:06 am, "David C. Zentgraf" <[EMAIL PROTECTED]> wrote: > > > I'd say it's the other way around. > > Text gets stored in some non-UTF8 format in the database (latin-1 most > > likely), so it comes back as gobbledygook. When you explicitly force > > the gobbledygook to be interpreted as UTF8, it's being morphed back > > into what it was meant to be. > > > Check your database settings and set all collations to UTF8. > > > On 12 Jun 2008, at 15:58, [EMAIL PROTECTED] wrote: > > > > Hi, > > > I have started putting a project onto a rented "production server" > > > running a pretty standard Ubuntu installation AFAIK. > > > I have been able to fix some initial problems with charsets but one > > > thing I just can seem to figure out. > > > > On my dev system > > > $this->params['form']['hello_text'] // in MySQL = hallå > > > > On production system > > > $this->params['form']['hello_text'] // inMySQL = hallÃ¥ > > > utf8_decode( $this->params['form']['hello_text'] ) // inMySQL = hallå > > > > Looks like the text gets encoded to utf8 before being put into the > > > database. Problem is that the text is already utf8 so I get these > > > nasty characters. > > > > Does anyone know what triggers Cake or PHP to encode the text before > > > sending it to MySQL? > > > > Any pointer in the right direction would be great. thanks. > > > > Martin --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: New server where strings get utf8 encoded before being saved to MySQL. Any hints for me?
I'm inclined to agree. I had the same problem. All my MySql installations default to Swedish, so need to be converted. Have a look at this page: http://ragrawal.wordpress.com/2008/02/15/dummys-guide-for-converting-character-set-for-a-web-application/ Be aware that MySql is 'utf8' whereas everything else is utf-8 or UTF-8. I don't know how important the case is. On Jun 12, 9:06 am, "David C. Zentgraf" <[EMAIL PROTECTED]> wrote: > I'd say it's the other way around. > Text gets stored in some non-UTF8 format in the database (latin-1 most > likely), so it comes back as gobbledygook. When you explicitly force > the gobbledygook to be interpreted as UTF8, it's being morphed back > into what it was meant to be. > > Check your database settings and set all collations to UTF8. > > On 12 Jun 2008, at 15:58, [EMAIL PROTECTED] wrote: > > > > > Hi, > > I have started putting a project onto a rented "production server" > > running a pretty standard Ubuntu installation AFAIK. > > I have been able to fix some initial problems with charsets but one > > thing I just can seem to figure out. > > > On my dev system > > $this->params['form']['hello_text'] // in MySQL = hallå > > > On production system > > $this->params['form']['hello_text'] // inMySQL = hallÃ¥ > > utf8_decode( $this->params['form']['hello_text'] ) // inMySQL = hallå > > > Looks like the text gets encoded to utf8 before being put into the > > database. Problem is that the text is already utf8 so I get these > > nasty characters. > > > Does anyone know what triggers Cake or PHP to encode the text before > > sending it to MySQL? > > > Any pointer in the right direction would be great. thanks. > > > Martin --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: New server where strings get utf8 encoded before being saved to MySQL. Any hints for me?
I'd say it's the other way around. Text gets stored in some non-UTF8 format in the database (latin-1 most likely), so it comes back as gobbledygook. When you explicitly force the gobbledygook to be interpreted as UTF8, it's being morphed back into what it was meant to be. Check your database settings and set all collations to UTF8. On 12 Jun 2008, at 15:58, [EMAIL PROTECTED] wrote: > > Hi, > I have started putting a project onto a rented "production server" > running a pretty standard Ubuntu installation AFAIK. > I have been able to fix some initial problems with charsets but one > thing I just can seem to figure out. > > On my dev system > $this->params['form']['hello_text'] // in MySQL = hallå > > On production system > $this->params['form']['hello_text'] // inMySQL = hallÃ¥ > utf8_decode( $this->params['form']['hello_text'] ) // inMySQL = hallå > > Looks like the text gets encoded to utf8 before being put into the > database. Problem is that the text is already utf8 so I get these > nasty characters. > > Does anyone know what triggers Cake or PHP to encode the text before > sending it to MySQL? > > Any pointer in the right direction would be great. thanks. > > Martin > > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
New server where strings get utf8 encoded before being saved to MySQL. Any hints for me?
Hi, I have started putting a project onto a rented "production server" running a pretty standard Ubuntu installation AFAIK. I have been able to fix some initial problems with charsets but one thing I just can seem to figure out. On my dev system $this->params['form']['hello_text'] // in MySQL = hallå On production system $this->params['form']['hello_text'] // inMySQL = hallÃ¥ utf8_decode( $this->params['form']['hello_text'] ) // inMySQL = hallå Looks like the text gets encoded to utf8 before being put into the database. Problem is that the text is already utf8 so I get these nasty characters. Does anyone know what triggers Cake or PHP to encode the text before sending it to MySQL? Any pointer in the right direction would be great. thanks. Martin --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
