Home » Database » MDB2_Driver_mysql » Bug #9257

[2006-11-07 15:51 UTC] florian dot sonnenburg at pepperzak dot de

Description:
------------
I'm using MDB2 on a MySQL 4.1 database with UTF8 as character set for the server, client and collation. The used driver is mysql.

After creating the MDB2 object I call "setCharset('utf8')" to set all necessary parameters to UTF8. With this configuration, all SELECTs return values with the wrong charset ('latin1').

I checked "setCharset()" in the package MDB2_Driver_mysql, and the only parameter that is changed there is "character_set_client" (SET character_set_client = ...). The two remaining MySQL parameters that cover the complete client-server-communication ('character_set_results' and 'character_set_connection') are still set to the default settings of the PHP MySQL client ('latin1') after setCharset() is executed.

Test script:
---------------
$dsn = array(
'phptype' => 'mysql',
'username' => '...',
'password' => '...',
'hostspec' => '...',
'database' => 'hpro3',
'new_link' => true,
);
$db = MDB2::connect($dsn);
$db->setCharset('utf8');

$sql = "SHOW VARIABLES LIKE 'character_set%'";
print_r($db->queryAll($sql));
$sql = "SHOW VARIABLES LIKE 'collation%';";
print_r($db->queryAll($sql));

Expected result:
----------------
character_set%:
Array
[0] => Array
[0] => character_set_client
[1] => utf8
[1] => Array
[0] => character_set_connection
[1] => utf8
[2] => Array
[0] => character_set_database
[1] => utf8
[3] => Array
[0] => character_set_results
[1] => utf8
[4] => Array
[0] => character_set_server
[1] => utf8
[5] => Array
[0] => character_set_system
[1] => utf8
[6] => Array
[0] => character_sets_dir
[1] => [charsetDirectory]

collation%:
Array
[0] => Array
[0] => collation_connection
[1] => utf8_general_ci
[1] => Array
[0] => collation_database
[1] => utf8_general_ci
[2] => Array
[0] => collation_server
[1] => utf8_general_ci

Actual result:
--------------
character_set%:
Array
[0] => Array
[0] => character_set_client
[1] => utf8
[1] => Array
[0] => character_set_connection
[1] => latin1
[2] => Array
[0] => character_set_database
[1] => utf8
[3] => Array
[0] => character_set_results
[1] => latin1
[4] => Array
[0] => character_set_server
[1] => utf8
[5] => Array
[0] => character_set_system
[1] => utf8
[6] => Array
[0] => character_sets_dir
[1] => [charsetDirectory]

collation%:
Array
[0] => Array
[0] => collation_connection
[1] => latin1_swedish_ci
[1] => Array
[0] => collation_database
[1] => utf8_general_ci
[2] => Array
[0] => collation_server
[1] => utf8_general_ci

[2006-11-07 16:03 UTC] florian dot sonnenburg at pepperzak dot de

I use a simple solution to this problem: I change line 535 of MDB2/Driver/mysql.php from

$query =
"SET character_set_client = '".mysql_real_escape_string($charset, $connection)."'";

to

$query =
"SET NAMES '".mysql_real_escape_string($charset, $connection)."'";

so that all three parameters are changed. Works perfectly well, but since I'm patching the PEAR package, every update of the package 'MDB2_Driver_mysql' removes the patch. Could this patch be included into the package or if there is a problem with that fix that I don't see at the moment?