Chinese name not coming out correctly

[AlexT]

Hi Guys,

Using:
Sugar Suite 4.2.1b
PHP 5.1.2
MySQL 5.0.18

I created custom fields to give each of my accounts and contacts a Chinese name, but for Chinese names longer than 6 characters, it cuts them short and adds &# after the 6th character, e.g.

中国计算机用户 becomes 中国计算机用&#

电脑商情报家用电脑(网站) ( <- this one isn't coming out properly in this forum either ) becomes 电脑商情报家&#

I'm assuming this is some kind of Unicode problem... Can I manually change this anyway?

Cheers,

Alex

[ruchida]

It may be related to the MySQL database charset. What is the charset set to the database you're using?

How much max length did you set to the custom field? I assume you set it to 20 (bytes). Since a single unicode Chinese character requires 3 bytes, 7 Chinese chars need 21 bytes.

[AlexT]

Hi,

Thanks for your quick response.

MySQL charset: UTF-8 Unicode (utf8)

And the max size is set to 50 (that was the default).

Any other ideas? Could it be some kind of PHP limitations? I'm using Dreamhost, so I don't think I'd be able to change the core PHP on the server.

Thanks,

Alex

[ruchida]

Hi,

Thanks for your quick response.

MySQL charset: UTF-8 Unicode (utf8)

And the max size is set to 50 (that was the default).

Any other ideas? Could it be some kind of PHP limitations? I'm using Dreamhost, so I don't think I'd be able to change the core PHP on the server.

Thanks,

Alex

AlexT,

Your settings seem to be fine. I created a custom field for the Account module with the width of 50 chars, and it seems to be working for 6+ multi-byte chars as attached.

Can you see how the string is stored in MySQL? (by using phpMyAdmin or anything else.)

[AlexT]

Hey Ruchida,

Ok through your guidance I believe I am onto the right solution (but I'm not quite there yet).

I found the values in the table accounts_cstm.

Each Chinese character is using far more than 3 bytes, i.e. 中国电脑教育&#
and 中国计算机用&#

I'm not an expert on these things, but after counting each of those, they are cutting off after the 50th byte. So it shouldn't be a problem if I can extend the maximum number of characters in my custom field.

But now I am running into new problems. When I try and change the value from 50 to either 70 or 100, it says that it has changed, but it doesn't make any difference. When I go back to the edit custom field screen, it has changed back to 50!

I can't find, where this maximum value is stored in MySQL (phpMyAdmin) so that I can change it manually.

Do you know where it is?

Cheers,

Alex

[ruchida]

Hey Ruchida,

Ok through your guidance I believe I am onto the right solution (but I'm not quite there yet).

I found the values in the table accounts_cstm.

Each Chinese character is using far more than 3 bytes, i.e. 中国电脑教育&#
and 中国计算机用&#

I'm not an expert on these things, but after counting each of those, they are cutting off after the 50th byte. So it shouldn't be a problem if I can extend the maximum number of characters in my custom field.

But now I am running into new problems. When I try and change the value from 50 to either 70 or 100, it says that it has changed, but it doesn't make any difference. When I go back to the edit custom field screen, it has changed back to 50!

I can't find, where this maximum value is stored in MySQL (phpMyAdmin) so that I can change it manually.

Do you know where it is?

Cheers,

Alex

Hi,

The Studio behavior may be a bug?

By the way, do you setup the mbstrings variables in php.ini? It's worth trying to set the following lines which translate an input chars to UTF-8 and store it to a MySQL table.

 mbstring.internal_encoding = UTF-8
mbstring.encoding_translation = On 
  

[AlexT]

Because I'm on a shared server (Dreamhost.com), I don't believe I can edit the PHP settings.

While I know that the current encoding is less efficient, is there a way I can manually change the the Maximum characters in the MySQL database?

[moonese]

As ruchida says, This may be a bug of Sugar Studio. Seems the initial value has been stored in some table in the database.

[ruchida]

Because I'm on a shared server (Dreamhost.com), I don't believe I can edit the PHP settings.

While I know that the current encoding is less efficient, is there a way I can manually change the the Maximum characters in the MySQL database?

There is a table for a custom field by each module. For example, if you added a custom field for the Account module, the new table is created called "accounts_cstm" and the field is there. If you have an admin tool like phpMyAdmin, you can directly edit the scheme of the table.

[AlexT]

Yeah thanks, I managed to find that, but couldn't find any reference to the maximum characters.

I got advice from another poster saying to use something like:

ALTER TABLE accounts_cstm MODIFY chinese_name_c VARCHAR(100);

but not sure if that got me anywhere. However, I did just change the the max value within Sugar to 150 and then went ahead to add characters successfully. However, it still returns to 50 if I go back to change the max value.

I guess I simply need to make sure that I (and anyone else administering system) don't go into the max value and make any changes. Simple enough I guess, but would be nice if the system could remember the value originally entered.

Thanks for all your help,

Alex