PDO MySQL dsn charset not working

Question

I'm trying the following code over Lubuntu 12.04, Apache, PHP 5.5.3-1ubuntu2, pdo_mysql 5.5.32 and MySQL 5.5.32-0ubuntu7-log.

$dbh = new \PDO("mysql:host=$hostname;dbname=test_db;charset=utf-8", $username, $password);

$dbh->exec('SET NAMES utf8');

$sql = "SELECT name FROM test_table";
foreach ($dbh->query($sql) as $row) {
    error_log(bin2hex($row['name']));
}

And I get 44656d6f737472616369c3b36e. If I comment the SET NAMES utf8 query, I get 44656d6f737472616369f36e (the difference being c3b3 instead of f3).

From the MySQL client, When issuing SELECT HEX(name) FROM test_table limit 1; I get 44656D6F737472616369C3B36E.

So it seems the extra query is needed, but according to this it shouldn't.

Am I doing something wrong?

EDIT

I tried both with charset=utf-8 and charset=utf8 and I'm getting the same result.

Output of SHOW CREATE TABLE test_table:

CREATE TABLE `test_table` (
    `name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Relevant output of SHOW FULL COLUMNS FROM test_table:

+-------+--------------+-----------------+
| Field | Type         | Collation       |
+-------+--------------+-----------------+
| name  | varchar(255) | utf8_general_ci |
+-------+--------------+-----------------+

what's the collation/charset on the `test_table` and the `name` field? `show create table test_table` would show that. — Marc B, Oct 30 '13 at 18:34
@raina77ow I tried both, and I'm getting the same wrong result — hectorh30, Oct 31 '13 at 17:52
In my.cnf is `skip-character-set-client-handshake` set? If so, what is `character-set-server` value? — Anthony, Oct 31 '13 at 18:50
@Anthony `character-set-server = utf8`, but `skip-character-set-client-handshake` is not set. I cannot check the actual value because its not shown on `show variables`. — hectorh30, Nov 03 '13 at 23:11

score 1 · Answer 1 · edited Aug 12 '20 at 15:19

1

You'll have it in your connection string like:

"mysql:host=$host;dbname=$db;charset=utf8"

HOWEVER, prior to PHP 5.3.6, the charset option was ignored. If you're running an older version of PHP, you must do it like this:

$dbh = new PDO("mysql:$connstr",  $user, $password);
$dbh->exec("set names utf8");

Reference: PHP PDO: charset, set names?

Or if you want you can do this:

<?php

$dsn = 'mysql:host=localhost;dbname=testdb';

$username = 'username';
$password = 'password';

$options = array(
  PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
); 

$dbh = new PDO($dsn, $username, $password, $options);

edited Aug 12 '20 at 15:19

Dharman

30,962
25
85
135

answered Oct 30 '13 at 18:37

Acácio Veit Schneider

160
5

He mentioned the version of PHP in the question. – raina77ow Oct 30 '13 at 18:38
Answers are not just for OP.This answer may help users with older versions of PHP. – david strachan Oct 30 '13 at 18:51
yep, it helped me realize that we've got 5.3.3 instead of 5.3.6! – qbolec Nov 05 '14 at 11:50

PDO MySQL dsn charset not working

1 Answers1