[dukeyork]

I am migrating a site (cpgnuke 8.2) from existing server to a new server. The site is fo English and chinese. Old server is running mySQL 4.0 while that on new server is 4.1
After modify the DB definition and set default collation to utf8-general-ci, I successfully dump data from old server and imported into new server. Browsing the table from phpMyAdmin shows data is all fine.
However, displaying the same data via web site shows only ????????. Modifying the data again from admin screen and display again shows data only partially correct, a typical error of incorrect collation.

It looks like the database /tables are set correctly, as I can browse and update data through phpMyAdmin. Is there any place when cphnuke connects to mySQL to specify the connection collation? If not, where is the problem?

Thanks

[NanoCaiordo]

fill up your server specs and your web site address pls

[dukeyork]

php: 5.0.4
mysql: 4.1.7

I have just installed a brand new Dragonfly, without any change and posted a message in Chinese (though the site language is still English, as defaulted by install). You can go there to see the effect of wrong encodings. many characters are wrongly displayed. You can also see many boxes, the way the browser displays non-displayable characters. These should not be the case if everything is fine.

The database was set to utf8_general_ci through 'operations' tag in phpMyAdmin, before the tables were created through installer. I also checked that the table collation and field collation are set to utf8_general_ci as a result of database default.

The address of the site is
http://yorklbcnet.temp.powweb.com/df/index.php

The message is right on the top of the page.

Thanks

[NanoCaiordo]

try to create a story (news) in chinese instead of an 'administrator message' to see if is the problem come from the db or is php code fault, also try to enable the language block (left side - visible to all) just to see how the date and time display

update your server specs here pls

[dukeyork]

Basically before I upgrade to Dragonfly, the same problem exists (and the only reason I upgrade to DF in such a hurry is trying to isolate the problem). Before the upgrade, I was testing with 'news' with 'Chinese' language activated. All literals from language file was displayed fine, with database data having the same problem as what I see in message here.

Unfortunately, I have to cancel this new hosting service today, as my existing hosting, running php 4.0.? is expiring and I am forced to extend another year with them, and leaving the new one before my option runs out. So you will not be able to see the effect soon.

I am just wondering how come I am the first one hitting the problem, if this is an intrinsic problem of DF ( I just guess, as I am having problem with a brand-new installed DF, and phpmyadmin shows date correctly on server). If this is an intrinsic problem of DF, I am happy to help in testing, though I do not have time (and knowledge) to look into the code extensively.

If anyone using a similar server and database, and do not encounter such problem, please pm me and help me to figure out the issue.

Many thanks

[dukeyork]

Okay, I think I find the problem and solution.

A mySQL command should be issued prior to any sql calls:

$db->sql_query("SET NAMES 'utf8'");

I tested by putting it before the first SQL call in messagebox.php, and admin/modules/messages.php. Then I am able to edit and see Chinese characters properly. It looks like one call is enough for the entire page to display, because I can read the news following the message in home page.

When I move this line to news module, only the news is readable.

Apparently, CPGnume or Dragonfly is missing this statement in dealing mySQL 4.1+ (there is no problem with 4.0) in handling utf8 characters.

I would like someone from DF team to advise where this SQL call is best placed, and inform us so that we can modify the code in our system. Putting it in modules do not fix prblem under all situations, as some modules may be called earlier than others under different situation.

Thanks

[NanoCaiordo]

Handle utf-8 sometimes is pretty easy if we have to work on one server only, but when we deal with different servers and os there are few problems and those needs a manual fix.

Not all os (out of box) supports utf-8 comunication between mysql server, client and tools, becouse not installed (for example debian, ubuntu ....).

So even with SET NAMES we want get any results unless we kindly ask our hosting service.

Instead you could probaly use utf8_encode function.

[dukeyork]

NanoCaiordo wrote:

So even with SET NAMES we want get any results unless we kindly ask our hosting service.

Instead you could probaly use utf8_encode function.

So you mean there is no convenient place in Dragonfly to put the SET NAME command?

I have some questions regarding your suggestion, as I am not an expert on the database issues.

Is the scope of SET NAME command on db connect level or per server setting?

If I am resting on a shared server service, How can I ask the hosting service to make such changes? I mean technically is it possible to set such parameters on a shared server without impacting other accounts? If the answer is NOT definite, I am afraid no ISP will address the request if it is not a globally requested, and I have to go back to change code on my side.

utf8_encode is not a good solution as I need to add the code to each single SQL query. Is there a place (say, in the db class) I can place the SET NAME query or call utf8_encde function that will be effective for all modules inside DF? I don't mind to make change on my side if you do not think it is appropriate to make it part of the release.

Thanks

[NanoCaiordo]

Quote:

So you mean there is no convenient place in Dragonfly to put the SET NAME command?

I mean SET NAMES is not a solution applicable in any server

Quote:

Is the scope of SET NAMES command on db connect level or per server setting?

If we use init_connect='SET NAMES utf8' in my.cnf will be per server settings.

NanoCaiordo wrote:

unless we kindly ask our hosting service

I mean ask to install utf-8 at db comunication level.

Quote:

is it possible to set such parameters on a shared server without impacting other accounts?

No

Quote:

Is there a place (say, in the db class) I can place the SET NAME

Probably yes, but not sure if it will mess things around.

Quote:

I successfully dump data from old server and imported into new server

Probably you missed to encode the dump to utf, read this site's FAQ for more info, hmm this page looks a bit messy you could even try this FAQ

Or you could use UltraEdit or Notepad2 to convert the db.

[dukeyork]

Hi,

I appreciate all your efforts in answering my questions. I think you have missed an important fact, or I missed to stress it. To my understanding of what you said, you are assuming I am having problem with a mySQL server failing in handling utf-8 characters. However, the real situation is:
1. my existing web site is already in utf-8 so there is no need for code page conversion.
2. I export data from mySQL, via phpadmin and import onto the new server via phpmyAdmin SQL command screen, after creating db on new server and modying the collation to utf8_general_ci on db level.
3. Immediately, I veriied that all tables and columns are of correct collation and I am able to browse tables to read utf-8 data correctly. I am also able to insert new data with correct encoding.
4. Only when I try to bring up the web page (now in a brand new dragonfly installation) the problem appears.

Therfore I do not think (sorry, I do not mean to argue to you, but I do not understand) that the setting is because something is missing on the server, but with the cms. Or otherwise how come phpmyadmin is able to show utf-8 data correctly?

Hope this can give you a clear picture about what is happening.

I just like to know if there is anyone who is in similar situation (i.e. running a utf8-Chinese web site on a shared server with english locale OS and mySQL version 4.1 or above) without having my problem. I'd like to understand what is happening.

Thanks

[NanoCaiordo]

dukeyork wrote:

mysql:4.1.7

Your server specs lack of information, please your bug report needs feedback
All i need to know if you are using a Windows server.