Advanced search  

News:

cpg1.5.46 Security release - upgrade mandatory!
The Coppermine development team is releasing a security update for Coppermine in order to counter recently discovered vulnerabilities. It is important that all users who run version cpg1.5.44 or older update to this latest version as soon as possible.
[more]

Pages: [1]   Go Down

Author Topic: encoding issue (MySQL 4.1.15)  (Read 40427 times)

0 Members and 1 Guest are viewing this topic.

stanaka

  • Coppermine newbie
  • Offline Offline
  • Gender: Male
  • Posts: 3
    • Photo Gallery of Tanaka's Family
encoding issue (MySQL 4.1.15)
« on: November 26, 2005, 09:24:45 am »

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]
        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.

Quote
Fatal error :

Would you please tell me any advise?

Thank you.
« Last Edit: November 30, 2005, 07:31:11 am by GauGau »
Logged
Sho.

Joachim Müller

  • Dev Team member
  • Coppermine addict
  • ****
  • Offline Offline
  • Gender: Male
  • Posts: 47844
  • aka "GauGau"
    • gaugau.de
Re: encoding issue (MySQL 4.1.15)
« Reply #1 on: November 26, 2005, 09:32:12 am »

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?
Logged

stanaka

  • Coppermine newbie
  • Offline Offline
  • Gender: Male
  • Posts: 3
    • Photo Gallery of Tanaka's Family
Re: encoding issue (MySQL 4.1.15)
« Reply #2 on: November 26, 2005, 01:01:57 pm »

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.
Logged
Sho.

Joachim Müller

  • Dev Team member
  • Coppermine addict
  • ****
  • Offline Offline
  • Gender: Male
  • Posts: 47844
  • aka "GauGau"
    • gaugau.de
Re: encoding issue (MySQL 4.1.15)
« Reply #3 on: November 27, 2005, 11:22:07 am »

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.
Logged

artistsinhawaii

  • VIP
  • Coppermine addict
  • ***
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 856
    • evj art and photography
Re: encoding issue (MySQL 4.1.15)
« Reply #4 on: November 27, 2005, 08:07:03 pm »

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

Logged
Learn and live ... In January of 2011, after a botched stent attempt, the doctors told me I needed a multiple bypass surgery or I could die.  I told them I needed new doctors.

DJMaze

  • VIP
  • Coppermine addict
  • ***
  • Offline Offline
  • Posts: 831
    • Dragonfly CMS
Re: encoding issue (MySQL 4.1.15)
« Reply #5 on: November 28, 2005, 01:38:57 am »

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.
Logged
There are 2 kinds of users in this world: satisfied and complainers.
Why do we never hear something from the satisfied users?
http://coppermine-gallery.net/forum/index.php?topic=24315.0

stanaka

  • Coppermine newbie
  • Offline Offline
  • Gender: Male
  • Posts: 3
    • Photo Gallery of Tanaka's Family
Re: encoding issue (MySQL 4.1.15)
« Reply #6 on: November 29, 2005, 01:30:16 pm »

Thank you for all!!

I sloved!
Modified include/functions.inc.php
Code: [Select]
// 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,
Logged
Sho.

DJMaze

  • VIP
  • Coppermine addict
  • ***
  • Offline Offline
  • Posts: 831
    • Dragonfly CMS
Re: encoding issue (MySQL 4.1.15)
« Reply #7 on: November 29, 2005, 07:25:36 pm »

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
Logged
There are 2 kinds of users in this world: satisfied and complainers.
Why do we never hear something from the satisfied users?
http://coppermine-gallery.net/forum/index.php?topic=24315.0

DJMaze

  • VIP
  • Coppermine addict
  • ***
  • Offline Offline
  • Posts: 831
    • Dragonfly CMS
Re: encoding issue (MySQL 4.1.15)
« Reply #8 on: November 29, 2005, 08:22:07 pm »

Follow up

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

Code: [Select]
ALTER DATABASE <dbname> DEFAULT CHARACTER SET utf8;
Code: [Select]
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
Logged
There are 2 kinds of users in this world: satisfied and complainers.
Why do we never hear something from the satisfied users?
http://coppermine-gallery.net/forum/index.php?topic=24315.0

DJMaze

  • VIP
  • Coppermine addict
  • ***
  • Offline Offline
  • Posts: 831
    • Dragonfly CMS
Re: encoding issue (MySQL 4.1.15)
« Reply #9 on: November 29, 2005, 08:29:44 pm »

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 :P
Logged
There are 2 kinds of users in this world: satisfied and complainers.
Why do we never hear something from the satisfied users?
http://coppermine-gallery.net/forum/index.php?topic=24315.0

DJMaze

  • VIP
  • Coppermine addict
  • ***
  • Offline Offline
  • Posts: 831
    • Dragonfly CMS
Re: encoding issue (MySQL 4.1.15)
« Reply #10 on: December 02, 2005, 07:35:51 pm »

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]
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]
SET NAMES <value of character_set_database>
If both collations don't comply then use
Code: [Select]
SET CHARACTER SET <value of character_set_database>
So for example stanaka his code should be written as
Code: [Select]
<?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;
}
Logged
There are 2 kinds of users in this world: satisfied and complainers.
Why do we never hear something from the satisfied users?
http://coppermine-gallery.net/forum/index.php?topic=24315.0

yard2004

  • Coppermine newbie
  • Offline Offline
  • Posts: 5
Re: encoding issue (MySQL 4.1.15)
« Reply #11 on: March 12, 2006, 04:26:10 pm »

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]
<?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]
Logged

agila

  • Coppermine newbie
  • Offline Offline
  • Gender: Male
  • Posts: 6
  • Česky moc neumím zato anglicky vůbec
    • http://www.ondrej-mottl.cz/ja1.htm
Re: encoding issue (MySQL 4.1.15)
« Reply #12 on: June 16, 2006, 01:42:34 am »

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 :-/  ČŘŽŠ=?



« Last Edit: June 16, 2006, 02:05:27 am by agila »
Logged

Makc666

  • Translator
  • Coppermine addict
  • **
  • Offline Offline
  • Gender: Male
  • Posts: 1611
  • Русский (ISO-8859-1) - Russian - Ğóññêèé (Windows)
    • Makc's home page
Re: encoding issue (MySQL 4.1.15)
« Reply #13 on: December 28, 2007, 01:50:02 pm »

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]
mysql_query("SET CHARACTER SET utf8",$result);in include/functions.inc.php?

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

Joachim Müller

  • Dev Team member
  • Coppermine addict
  • ****
  • Offline Offline
  • Gender: Male
  • Posts: 47844
  • aka "GauGau"
    • gaugau.de
Re: encoding issue (MySQL 4.1.15)
« Reply #14 on: December 28, 2007, 04:21:32 pm »

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.
Logged
Pages: [1]   Go Up
 

Page created in 0.023 seconds with 21 queries.