Collations, Charset's, Encoding and everything in between

kensaggy

Hello fello php programmers,

I have a problem with mysql's collations and encoding regarding utf-8 and hebrew texts.

I'm now working on a running site that has a table which contain a char(60) field.
the fields and the table and collation latin1_swedish_ci for some reason - and i say "for some reason" because the site is running utf-8 and the text value is hebrew.

now because of that, when someone enters text that comes close to 60 chars the utf-8 encoding breaks and i get the wierd ? symbol (? in a black dimond box).

i need to change the collations to utf-8 unicode and somehow recode the values... i thought of building a php scripts that:
1. runs over all the tables
2. changes the collations
3. selected the field
4. converts the encoding and updates' the row.

is this the correct way of solving this problem?

all my attempts were a failure :-( (tried using both iconv, and mbstring_convert_encoding). PHP already recognizes the text i select from the table as UTF-8...

what can/should i do to fix this? any idea's?

Thanks,
Ken.

MarkR

You have tables in latin1 but you've been putting utf-8 data into them - this almost certainly means they contain rubbish.

Normally the fix would be to change them into binary and then back again, hopefully this would preserve the content.

Also note that as you're using utf8, you must set the client connection to use utf8 (e.g. "USE NAMES utf8") throughout your entire application. However, doing so now will break due to the fact that your database is full of the wrong characters.

Clearly this is something which should be done carefully - test it on your test server.

I HOPE that doing two ALTER TABLEs on each table, converting the charsets to BINARY and then into utf8 will do it (A single ALTER TABLE would convert the data, which is not what you want).

Mark

kensaggy

Thanks for reply Mark...

First let me note that this stupid table design was not my doing... i was called upon to help fix the problem.. :bemused:

i did query the "SET NAMES 'utf8'" when i started the script...

from your reply i did not under what ALTER TABLE you were reffering to?
should i first ALTER the table's collation to utf8_unicode_ci, then the fields?

if so I've tried this but the data remains unchanged...

furthermore, are you saying i should covert the field to BINARY and back?

thx, Ken.

MarkR

Ok, this may work:

/* The following will change all varchars into binary */
ALTER TABLE foo CONVERT TO CHARACTER SET binary;
/* Do the following for each column in the table foo- this changes "username" back to a varchar- will not perform any conversion. */
 alter table foo change username username varchar(20);

Note that you can do several CHANGE operations in a single ALTER TABLE- this is a good idea if you have large tables.

Note that while I believe this may work in your case, you MUST test it on a NON-PRODUCTION server, and check the resulting data thoroughly.

Mark

kensaggy

Genius! I couldn't believe it but it worked!

Thanks a lot Mark, you really helped me.

My employer will be happy to hear there is a relatively easy solution thats based mainly on SQL (with no need to select, recode and update)...
although it will grind the hell out of their server (120+ DB's, thousands of tables and ...well.. a ton of row's as you can probably assume)

since we're only talking about one column (on each table) that needs recoding, it would be wiser to change only that column to binary and back, no? (plus set the table collation to utf8_unicode_ci ?)

Ken.

Sxooter

kensaggy wrote:
Hello fello php programmers,

I have a problem with mysql's collations and encoding regarding utf-8 and hebrew texts.

I'm now working on a running site that has a table which contain a char(60) field.
the fields and the table and collation latin1_swedish_ci for some reason - and i say "for some reason" because the site is running utf-8 and the text value is hebrew.

You seem to be confusing ENCODING with COLLATION

You can store Hebrew in a database encoded with UTF-8 and set to collate according to swedish rules.

When you select your data it will sort by the rules for swedish language.

When you match text, it will match by swedish rules.

But it will still be in Hebrew.

now because of that, when someone enters text that comes close to 60 chars the utf-8 encoding breaks and i get the wierd ? symbol (? in a black dimond box).

Are you sure this is the problem? It sounds to me like something might be happening in your browser / apache / php interface. If you have them enter a > 60 characters in a text field, does it get mutilated without the help of the database if you just re-display it?

i need to change the collations to utf-8 unicode and somehow recode the values... i thought of building a php scripts that:
1. runs over all the tables
2. changes the collations
3. selected the field
4. converts the encoding and updates' the row.

is this the correct way of solving this problem?

all my attempts were a failure :-( (tried using both iconv, and mbstring_convert_encoding). PHP already recognizes the text i select from the table as UTF-8...

That's because it IS UTF-8. What you need to do is change the collation of the columns.

Take a look here and see if it makes any more sense than I do. I'm no MySQL guru, but I'm pretty sure it's a simple change to get the right collation: http://dev.mysql.com/doc/refman/5.0/en/character-sets.html

kensaggy

I guess i am confused :-\

The field is collated as latin1_swedish_ci , i now understand that this only affects ORDER BY and GROUP BY and is like a set of rules as to how to order things..

my problem is this...
the field is defined as CHAR(60)... the website works on UTF-8...
why when people post a title (its a title field) thats 32 chars long , i see that mark at the 33'rd char? (that black box with a ? in the middle) ?

how do i get rid of it now, and how do i avoid it in the future?

I'm sorry i'm being so hard about this... i guess i just don't quite understand all this charset's and encoding.

Thanks for the patience
Ken.

Sxooter

kensaggy wrote:
I guess i am confused :-\

The field is collated as latin1_swedish_ci , i now understand that this only affects ORDER BY and GROUP BY and is like a set of rules as to how to order things..

my problem is this...
the field is defined as CHAR(60)... the website works on UTF-8...
why when people post a title (its a title field) thats 32 chars long , i see that mark at the 33'rd char? (that black box with a ? in the middle) ?

how do i get rid of it now, and how do i avoid it in the future?

The website working on UTF-8 accounts for encoding. It doesn't account for the language / character set. What's happening is that your php app is delivering one character set and your browser thinks it's another and doesn't know what to do with it. I doubt it's because of the encoding.

I'm not very familiar with setting languages from within PHP, as I've always worked with straight ASCII or english, and it's always just worked with php/postgresql.

I would suggest reading as much of the mysql docs on this as you can stomach. I followed one link to another in it about character sets, and found a lot of info. I know that in pgsql you have to set the client side encoding / character set and the server side will follow / translate the characters for you.