encoding issue (MySQL 4.1.15)

[stanaka]

My environment is the following,

PHP 4.1.1
MySQL 4.1.15
I use Japanese charcter(UTF8).

In this environment CPG 1.3.5 has encode issue.
So I modified functions.inc.php.

Code Select Expand


        global $CONFIG;
        $result = @mysql_connect($CONFIG['dbserver'], $CONFIG['dbuser'], $CONFIG['dbpass']);
        if (!$result)
                return false;
        if (!mysql_select_db($CONFIG['dbname']))
                return false;
        $result = mysql_query( 'SET NAMES utf8' ); <--- insert code.
        return $result;
I think CPG 1.4.2 has same issue.
I tried same modify for CPG 1.4.2.
Unfortunatly I have error message.

Fatal error :

Would you please tell me any advise?

Thank you.

[Joachim Müller]

enable debug_mode to see the actual query that fails. Did you run the charset converter when upgrading from cpg1.3.5 to cpg1.4.2?

[stanaka]

GAUGAU san,
Thanks for your reply.

I try to enable debug mode. But CPG say only "Fatal error :".
And encode is already UTF8 from CPG1.3.5.
I think no need convert.
I check encoding by phpMyAdmin. All Japanese text is OK(=UTF8).

Thanks,
Sho.

[Joachim Müller]

use a tool like phpMyAdmin, go to coppermine's config page, set debug_mode to 1. Your cpg1.3.x database definitely isn't in unicode.

[artistsinhawaii]

stanaka,

The only problems I encountered with the use of Japanese when designing websites was in the keywords and search function which DMAZE recently addressed.  Files are available at the CVS Devel section.  These are:


http://cvs.sourceforge.net/viewcvs.py/coppermine/devel/editOnePic.php
http://cvs.sourceforge.net/viewcvs.py/coppermine/devel/thumbnails.php
http://cvs.sourceforge.net/viewcvs.py/coppermine/devel/include/functions.inc.php

You should replace your language files with those of 1.4.2 and maybe replace the functions.inc.php with the one above, or undo the changes you made to functions.inc.php


Dennis

[DJMaze]

It's great that MySQL 4.1.x supports UTF but to use that properly you have to think about several things:

1. Is the data inside UTF or ANSI (if you used 1.3.x as KOI or any other then UTF then it's ANSI)
2. Are the datafields identified as cli_general_utf or something else ?
3. Is the table identified as general_utf or something else ?
4. Is the database identified as general_utf or something else ?
5. If they are utf which command did you use to convert it (table or table&data converted) ?

By default MySQL uses a ANSI charset as setup by the server however if someone thought utf would be cooler then they probably forgot to convert all databases or converted them incorrectly.
To provide you the most correct queries to fix the issue, please provide as much details as possible regarding previous setups.

[stanaka]

Thank you for all!!

I sloved!
Modified include/functions.inc.php

Code Select Expand

// Connect to the database

/**
* cpg_db_connect()
*
* Connect to the database
**/

function cpg_db_connect()
{
        global $CONFIG;
        $result = @mysql_connect($CONFIG['dbserver'], $CONFIG['dbuser'], $CONFIG['dbpass']);
        if (!$result) {
                return false;
        }
        if (!mysql_select_db($CONFIG['dbname']))
                return false;
        mysql_query("SET NAMES 'utf8'",$result);  <=== insert this code
        return $result;
}

Work fine!
This change is for matching charcter code database and client.

Thanks,

[DJMaze]

SET NAMES isn't the best method and relies on MySQL 4.1.x or 5.x for utf (4.0.x has no utf)

What SET NAMES realy does:

it identifies how queries and results from/to the client must be processed. In this case it tells the string is as UTF and MySQL itself then converts the data if the database table is latin1 or anything else.
For example a database table is KOI8 then each query is lossly converted to KOI. The result set is lossly converted from KOI to UTF.
The issue here is that the conversion is a slow process and that it's lossly so not fault free.

To prevent this you should either keep all data "as is" which means the binary data (utf) is stored as string and recieved as string (lossles) OR you convert all tables and maybe the data to UTF charset but that depends if the data inside already is UTF or not.

The second method is the best method but as i said you must know which charset the data is stored and then run the conversion in several steps with or without modifying the data.

As an example:

The database is general_latin1 but the data is KOI. Then convert the database, tables and fields to KOI but WITHOUT converting the content. Then convert everything from KOI to UTF including the data.

or

The database is general_latin1 but the data is UTF (you already used coppermine as utf). Then convert the database, tables and fields to UTF but WITHOUT converting the content.

As you can see the conversion deeply relies on the MySQL setup on your machine and due to that it shows you how difficult MySQL 4.1/5.x is for many people. It also explains why many hosts still use 4.0.x because many server admins don't have the knowledge or don't want to take the mess to support all website newbies

[DJMaze]

Follow up

Here are some query examples how to handle the above mentioned examples

Code Select Expand

ALTER DATABASE <dbname> DEFAULT CHARACTER SET utf8;

Code Select Expand

ALTER TABLE tbl_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

See:

http://dev.mysql.com/doc/refman/5.0/en/charset.html
http://dev.mysql.com/doc/refman/4.1/en/charset-upgrading.html

[DJMaze]

Another issue:

PHP 4.1.1 is very outdated and not designed to support MySQL 4.1/5.0 instead upgrade to PHP 5 which has the mysqli extension (note the 'i') but currently Coppermine isn't designed to support the mysqli extension.

Personally i'm designing a new database layer that supports mysqli and many others. I hope someday this will be implemented in Coppermine

[DJMaze]

As a final result i've tested MySQL 5.0.16 on windows and linux machines.
The type of client connection with MySQL is based on:
- my.ini / my.cnf
- libmysql.dll / libmysql.so
- php_mysql.dll / php_mysql.so / --with-mysql / etc.

If something in the setup doesn't comply to your setup like libmysql = 4.0.17 or my.ini "character_set_client = latin1" then the mostly the default connection is used (which is latin1)
This is no problem if your database, tables and columns use charset = latin1 and collation = latin1_general_ci but if something is different like connection = utf8 and database = latin1 or viceversa, then MySQL runs conversions.

For correct information about it run the following query in MySQL 4.1/5.0

Code Select Expand

SHOW VARIABLES;

About your connection read the records:
- character_set_client
- character_set_connection
- character_set_results
- collation_connection

They all should read utf8 except collation_connection which says utf8_general_ci OR latin1 and latin1_general_ci

About the server read the records:
- character_set_database
- collation_database

If character_set_database doesn't comply with the other character_set_* values you must run

Code Select Expand

SET NAMES <value of character_set_database>

If both collations don't comply then use

Code Select Expand

SET CHARACTER SET <value of character_set_database>

So for example stanaka his code should be written as

Code Select Expand

<?php
function cpg_db_connect()
{
        global $CONFIG;
        $result = @mysql_connect($CONFIG['dbserver'], $CONFIG['dbuser'], $CONFIG['dbpass']);
        if (!$result) {
                return false;
        }
        if (!mysql_select_db($CONFIG['dbname']))
                return false;

       # Fix SQL connection type
        mysql_query('SET NAMES utf8');
        $result = mysql_query('SET CHARACTER SET utf8');

        return $result;
}

[yard2004]

I have serious problem with Cpg144 and UTF-8 - please see
http://forum.coppermine-gallery.net/index.php?topic=29059.0

but this post doesn´t working I obtain a error, but the previous TRICK
     mysql_query("SET NAMES 'utf8'",$result);  <=== insert this code

was correct my problem...




So for example stanaka his code should be written as

Code Select Expand

<?php
function cpg_db_connect()
{
        global $CONFIG;
        $result = @mysql_connect($CONFIG['dbserver'], $CONFIG['dbuser'], $CONFIG['dbpass']);
        if (!$result) {
                return false;
        }
        if (!mysql_select_db($CONFIG['dbname']))
                return false;

       # Fix SQL connection type
        mysql_query('SET NAMES utf8');
        $result = mysql_query('SET CHARACTER SET utf8');

        return $result;
}
[/quote]

[agila]

insert in cpg148 ?

$result = mysql_query( 'SET NAMES utf8' );  >> Coppermine critical error: Unable to connect to database ! MySQL said:
--------------------------------------------
       # Fix SQL connection type
        mysql_query('SET NAMES utf8');
        $result = mysql_query('SET CHARACTER SET utf8');   > Coppermine critical error: Unable to connect to database ! MySQL said:

insert to cpg148 ?  http://www.agila77.com/cpg148/index.php
cpg135> mysql_query("SET NAMES 'utf8'",$result);  <=== insert this code >> update to 148 >> charsetmgr.php Convert from to iso-8859-2 --> UTF-8 :-/  ČŘŽŠ=?

[Makc666]

Little question to stuff as I was not able to find the answer with the search.

How will this particular thread problem with MYSQL 4.1.* and 5.* and encoding issue is going to be solved in upcoming Coppermine version?

Will you add something like:

Code Select Expand

mysql_query("SET CHARACTER SET utf8",$result);
in include/functions.inc.php?

Can you please comment this one in more detailed way.
Thanks

[Joachim Müller]

Currently, there is no difference between cpg1.4.x and cpg1.5.x database-wise. Please start a separate thread on the coppermine coding discussion board if you're ready to discuss this for cpg1.5.x; after all, this thread here is on the cpg1.4.x board, so only cpg1.4.x- issues should be discussed here, especially since this is a sticky thread. Discussions about future versions will only clutter a sticky thread.
Locking.